Project Overview
This project allows users to:
1. Add movies and showtimes.
2. View available shows.
3. Book tickets for a selected show.
4. Display booking summaries and calculate total revenue.
Tech Stack
Database: MySQL or SQLite
Python Libraries:
o sqlite3 (or mysql.connector for MySQL)
o pandas for generating booking reports (optional)
o tkinter for GUI (optional, CLI-based system also works)
Database Schema
Create the following tables:
1. Movies
Column Name Data Type Description
movie_id INTEGER (PK) Unique movie identifier
movie_name TEXT Name of the movie
genre TEXT Genre of the movie
2. Shows
Column Name Data Type Description
show_id INTEGER (PK) Unique show identifier
movie_id INTEGER (FK) Links to Movies.movie_id
show_time TEXT Time of the show
seats_left INTEGER Available seats for the show
3. Bookings
Column Name Data Type Description
booking_id INTEGER (PK) Unique booking identifier
show_id INTEGER (FK) Links to Shows.show_id
customer_name TEXT Name of the customer
tickets INTEGER Number of tickets booked
Steps to Implement
1. Set Up the Database
If you're using SQLite:
python
Copy code
import sqlite3
# Create a connection
conn = sqlite3.connect('movie_booking.db')
cursor = conn.cursor()
# Create tables
cursor.execute("""
CREATE TABLE IF NOT EXISTS Movies (
movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
movie_name TEXT NOT NULL,
genre TEXT NOT NULL
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS Shows (
show_id INTEGER PRIMARY KEY AUTOINCREMENT,
movie_id INTEGER NOT NULL,
show_time TEXT NOT NULL,
seats_left INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES Movies(movie_id)
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS Bookings (
booking_id INTEGER PRIMARY KEY AUTOINCREMENT,
show_id INTEGER NOT NULL,
customer_name TEXT NOT NULL,
tickets INTEGER NOT NULL,
FOREIGN KEY(show_id) REFERENCES Shows(show_id)
)
""")
conn.commit()
print("Database initialized successfully!")
2. Add Movies
python
Copy code
def add_movie(conn, movie_name, genre):
cursor = conn.cursor()
cursor.execute("INSERT INTO Movies (movie_name, genre) VALUES (?, ?)",
(movie_name, genre))
conn.commit()
print("Movie added successfully!")
3. Add Shows
python
Copy code
def add_show(conn, movie_id, show_time, seats_left):
cursor = conn.cursor()
cursor.execute("INSERT INTO Shows (movie_id, show_time, seats_left) VALUES
(?, ?, ?)",
(movie_id, show_time, seats_left))
conn.commit()
print("Show added successfully!")
4. View Available Shows
python
Copy code
def view_shows(conn):
cursor = conn.cursor()
cursor.execute("""
SELECT Shows.show_id, Movies.movie_name, Movies.genre, Shows.show_time,
Shows.seats_left
FROM Shows
JOIN Movies ON Shows.movie_id = Movies.movie_id
""")
rows = cursor.fetchall()
for row in rows:
print(f"Show ID: {row[0]}, Movie: {row[1]}, Genre: {row[2]}, Time:
{row[3]}, Seats Left: {row[4]}")
5. Book Tickets
python
Copy code
def book_tickets(conn, show_id, customer_name, tickets):
cursor = conn.cursor()
cursor.execute("SELECT seats_left FROM Shows WHERE show_id = ?",
(show_id,))
result = cursor.fetchone()
if result and result[0] >= tickets:
# Update seats
cursor.execute("UPDATE Shows SET seats_left = seats_left - ? WHERE
show_id = ?", (tickets, show_id))
# Insert booking
cursor.execute("INSERT INTO Bookings (show_id, customer_name, tickets)
VALUES (?, ?, ?)",
(show_id, customer_name, tickets))
conn.commit()
print("Booking successful!")
else:
print("Not enough seats available.")
6. Generate Booking Report
python
Copy code
def generate_report(conn):
cursor = conn.cursor()
cursor.execute("""
SELECT Movies.movie_name, Shows.show_time, SUM(Bookings.tickets) as
Total_Tickets
FROM Bookings
JOIN Shows ON Bookings.show_id = Shows.show_id
JOIN Movies ON Shows.movie_id = Movies.movie_id
GROUP BY Movies.movie_name, Shows.show_time
""")
rows = cursor.fetchall()
print("Booking Report:")
for row in rows:
print(f"Movie: {row[0]}, Show Time: {row[1]}, Tickets Sold: {row[2]}")
7. Main Menu
python
Copy code
def main():
conn = sqlite3.connect('movie_booking.db')
while True:
print("\nMovie Ticket Booking System")
print("1. Add Movie")
print("2. Add Show")
print("3. View Shows")
print("4. Book Tickets")
print("5. Generate Booking Report")
print("6. Exit")
choice = input("Enter your choice: ")
if choice == "1":
movie_name = input("Enter movie name: ")
genre = input("Enter genre: ")
add_movie(conn, movie_name, genre)
elif choice == "2":
movie_id = int(input("Enter movie ID: "))
show_time = input("Enter show time: ")
seats_left = int(input("Enter number of seats: "))
add_show(conn, movie_id, show_time, seats_left)
elif choice == "3":
view_shows(conn)
elif choice == "4":
show_id = int(input("Enter show ID: "))
customer_name = input("Enter your name: ")
tickets = int(input("Enter number of tickets: "))
book_tickets(conn, show_id, customer_name, tickets)
elif choice == "5":
generate_report(conn)
elif choice == "6":
break
else:
print("Invalid choice. Please try again.")
conn.close()
if __name__ == "__main__":
main()
How It Works
1. Database Initialization: Run the setup script to create the database and tables.
2. Adding Data: Use the menu to add movies and shows.
3. View and Book Tickets: Users can view available shows, book tickets, and see real-time
seat availability updates.
4. Reports: Generate a summary of ticket sales for management insights
Project Overview
This project allows users to:
1. Add movies and showtimes.
2. View available shows.
3. Book tickets for a selected show.
4. Display booking summaries and calculate total revenue.
Tech Stack
Database: MySQL or SQLite
Python Libraries:
o sqlite3 (or mysql.connector for MySQL)
o pandas for generating booking reports (optional)
o tkinter for GUI (optional, CLI-based system also works)
Database Schema
Create the following tables:
1. Movies
Column Name Data Type Description
movie_id INTEGER (PK) Unique movie identifier
movie_name TEXT Name of the movie
genre TEXT Genre of the movie
2. Shows
Column Name Data Type Description
show_id INTEGER (PK) Unique show identifier
movie_id INTEGER (FK) Links to Movies.movie_id
show_time TEXT Time of the show
seats_left INTEGER Available seats for the show
Column Name Data Type Description
3. Bookings
Column Name Data Type Description
booking_id INTEGER (PK) Unique booking identifier
show_id INTEGER (FK) Links to Shows.show_id
customer_name TEXT Name of the customer
tickets INTEGER Number of tickets booked
Steps to Implement
1. Set Up the Database
If you're using SQLite:
python
Copy code
import sqlite3
# Create a connection
conn = sqlite3.connect('movie_booking.db')
cursor = conn.cursor()
# Create tables
cursor.execute("""
CREATE TABLE IF NOT EXISTS Movies (
movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
movie_name TEXT NOT NULL,
genre TEXT NOT NULL
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS Shows (
show_id INTEGER PRIMARY KEY AUTOINCREMENT,
movie_id INTEGER NOT NULL,
show_time TEXT NOT NULL,
seats_left INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES Movies(movie_id)
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS Bookings (
booking_id INTEGER PRIMARY KEY AUTOINCREMENT,
show_id INTEGER NOT NULL,
customer_name TEXT NOT NULL,
tickets INTEGER NOT NULL,
FOREIGN KEY(show_id) REFERENCES Shows(show_id)
)
""")
conn.commit()
print("Database initialized successfully!")
2. Add Movies
python
Copy code
def add_movie(conn, movie_name, genre):
cursor = conn.cursor()
cursor.execute("INSERT INTO Movies (movie_name, genre) VALUES (?, ?)",
(movie_name, genre))
conn.commit()
print("Movie added successfully!")
3. Add Shows
python
Copy code
def add_show(conn, movie_id, show_time, seats_left):
cursor = conn.cursor()
cursor.execute("INSERT INTO Shows (movie_id, show_time, seats_left) VALUES
(?, ?, ?)",
(movie_id, show_time, seats_left))
conn.commit()
print("Show added successfully!")
4. View Available Shows
python
Copy code
def view_shows(conn):
cursor = conn.cursor()
cursor.execute("""
SELECT Shows.show_id, Movies.movie_name, Movies.genre, Shows.show_time,
Shows.seats_left
FROM Shows
JOIN Movies ON Shows.movie_id = Movies.movie_id
""")
rows = cursor.fetchall()
for row in rows:
print(f"Show ID: {row[0]}, Movie: {row[1]}, Genre: {row[2]}, Time:
{row[3]}, Seats Left: {row[4]}")
5. Book Tickets
python
Copy code
def book_tickets(conn, show_id, customer_name, tickets):
cursor = conn.cursor()
cursor.execute("SELECT seats_left FROM Shows WHERE show_id = ?",
(show_id,))
result = cursor.fetchone()
if result and result[0] >= tickets:
# Update seats
cursor.execute("UPDATE Shows SET seats_left = seats_left - ? WHERE
show_id = ?", (tickets, show_id))
# Insert booking
cursor.execute("INSERT INTO Bookings (show_id, customer_name, tickets)
VALUES (?, ?, ?)",
(show_id, customer_name, tickets))
conn.commit()
print("Booking successful!")
else:
print("Not enough seats available.")
6. Generate Booking Report
python
Copy code
def generate_report(conn):
cursor = conn.cursor()
cursor.execute("""
SELECT Movies.movie_name, Shows.show_time, SUM(Bookings.tickets) as
Total_Tickets
FROM Bookings
JOIN Shows ON Bookings.show_id = Shows.show_id
JOIN Movies ON Shows.movie_id = Movies.movie_id
GROUP BY Movies.movie_name, Shows.show_time
""")
rows = cursor.fetchall()
print("Booking Report:")
for row in rows:
print(f"Movie: {row[0]}, Show Time: {row[1]}, Tickets Sold: {row[2]}")
7. Main Menu
python
Copy code
def main():
conn = sqlite3.connect('movie_booking.db')
while True:
print("\nMovie Ticket Booking System")
print("1. Add Movie")
print("2. Add Show")
print("3. View Shows")
print("4. Book Tickets")
print("5. Generate Booking Report")
print("6. Exit")
choice = input("Enter your choice: ")
if choice == "1":
movie_name = input("Enter movie name: ")
genre = input("Enter genre: ")
add_movie(conn, movie_name, genre)
elif choice == "2":
movie_id = int(input("Enter movie ID: "))
show_time = input("Enter show time: ")
seats_left = int(input("Enter number of seats: "))
add_show(conn, movie_id, show_time, seats_left)
elif choice == "3":
view_shows(conn)
elif choice == "4":
show_id = int(input("Enter show ID: "))
customer_name = input("Enter your name: ")
tickets = int(input("Enter number of tickets: "))
book_tickets(conn, show_id, customer_name, tickets)
elif choice == "5":
generate_report(conn)
elif choice == "6":
break
else:
print("Invalid choice. Please try again.")
conn.close()
if __name__ == "__main__":
main()
How It Works
1. Database Initialization: Run the setup script to create the database and tables.
2. Adding Data: Use the menu to add movies and shows.
3. View and Book Tickets: Users can view available shows, book tickets, and see real-time seat
availability updates.
4. Reports: Generate a summary of ticket sales for management insights.
Let me know if you'd like additional features, such as GUI integration with Tkinter or a detailed
explanation of any part! 😊
4o
You said:
give me implementation or a detailed explanation how to integrate Python with Sql on movie ticket
booking system with mysql.connecot
ChatGPT said:
ChatGPT
Here’s a detailed implementation of a Movie Ticket Booking System using Python and MySQL
with the mysql.connector library.
Prerequisites
1. Install MySQL: Ensure MySQL is installed and running on your system.
2. Install Python Packages:
Install mysql-connector-python using pip:
bash
Copy code
pip install mysql-connector-python
Database Schema in MySQL
Create Database and Tables
Run these SQL commands in MySQL Workbench or any MySQL client:
sql
Copy code
CREATE DATABASE MovieBooking;
USE MovieBooking;
CREATE TABLE Movies (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
movie_name VARCHAR(255) NOT NULL,
genre VARCHAR(100) NOT NULL
);
CREATE TABLE Shows (
show_id INT AUTO_INCREMENT PRIMARY KEY,
movie_id INT NOT NULL,
show_time VARCHAR(50) NOT NULL,
seats_left INT NOT NULL,
FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
);
CREATE TABLE Bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
show_id INT NOT NULL,
customer_name VARCHAR(255) NOT NULL,
tickets INT NOT NULL,
FOREIGN KEY (show_id) REFERENCES Shows(show_id)
);
Python Integration with MySQL
1. Database Connection
python
Copy code
import mysql.connector
# Establish a connection to MySQL
def create_connection():
conn = mysql.connector.connect(
host="localhost",
user="root", # Replace with your MySQL username
password="password", # Replace with your MySQL password
database="MovieBooking"
)
return conn
2. Add a Movie
python
Copy code
def add_movie(conn, movie_name, genre):
cursor = conn.cursor()
query = "INSERT INTO Movies (movie_name, genre) VALUES (%s, %s)"
cursor.execute(query, (movie_name, genre))
conn.commit()
print(f"Movie '{movie_name}' added successfully!")
3. Add a Show
python
Copy code
def add_show(conn, movie_id, show_time, seats_left):
cursor = conn.cursor()
query = "INSERT INTO Shows (movie_id, show_time, seats_left) VALUES (%s,
%s, %s)"
cursor.execute(query, (movie_id, show_time, seats_left))
conn.commit()
print(f"Show added successfully for Movie ID {movie_id}!")
4. View Available Shows
python
Copy code
def view_shows(conn):
cursor = conn.cursor()
query = """
SELECT Shows.show_id, Movies.movie_name, Movies.genre, Shows.show_time,
Shows.seats_left
FROM Shows
JOIN Movies ON Shows.movie_id = Movies.movie_id
"""
cursor.execute(query)
rows = cursor.fetchall()
print("\nAvailable Shows:")
for row in rows:
print(f"Show ID: {row[0]}, Movie: {row[1]}, Genre: {row[2]}, Time:
{row[3]}, Seats Left: {row[4]}")
5. Book Tickets
python
Copy code
def book_tickets(conn, show_id, customer_name, tickets):
cursor = conn.cursor()
# Check seat availability
check_query = "SELECT seats_left FROM Shows WHERE show_id = %s"
cursor.execute(check_query, (show_id,))
result = cursor.fetchone()
if result and result[0] >= tickets:
# Update seats
update_query = "UPDATE Shows SET seats_left = seats_left - %s WHERE
show_id = %s"
cursor.execute(update_query, (tickets, show_id))
# Insert booking
booking_query = "INSERT INTO Bookings (show_id, customer_name,
tickets) VALUES (%s, %s, %s)"
cursor.execute(booking_query, (show_id, customer_name, tickets))
conn.commit()
print(f"Booking successful for {customer_name}! {tickets} ticket(s)
booked.")
else:
print("Not enough seats available.")
6. Generate Booking Report
python
Copy code
def generate_report(conn):
cursor = conn.cursor()
query = """
SELECT Movies.movie_name, Shows.show_time, SUM(Bookings.tickets) AS
Total_Tickets
FROM Bookings
JOIN Shows ON Bookings.show_id = Shows.show_id
JOIN Movies ON Shows.movie_id = Movies.movie_id
GROUP BY Movies.movie_name, Shows.show_time
"""
cursor.execute(query)
rows = cursor.fetchall()
print("\nBooking Report:")
for row in rows:
print(f"Movie: {row[0]}, Show Time: {row[1]}, Tickets Sold: {row[2]}")
7. Main Menu
python
Copy code
def main():
conn = create_connection()
while True:
print("\nMovie Ticket Booking System")
print("1. Add Movie")
print("2. Add Show")
print("3. View Shows")
print("4. Book Tickets")
print("5. Generate Booking Report")
print("6. Exit")
choice = input("Enter your choice: ")
if choice == "1":
movie_name = input("Enter movie name: ")
genre = input("Enter genre: ")
add_movie(conn, movie_name, genre)
elif choice == "2":
movie_id = int(input("Enter movie ID: "))
show_time = input("Enter show time: ")
seats_left = int(input("Enter number of seats: "))
add_show(conn, movie_id, show_time, seats_left)
elif choice == "3":
view_shows(conn)
elif choice == "4":
show_id = int(input("Enter show ID: "))
customer_name = input("Enter your name: ")
tickets = int(input("Enter number of tickets: "))
book_tickets(conn, show_id, customer_name, tickets)
elif choice == "5":
generate_report(conn)
elif choice == "6":
conn.close()
print("Goodbye!")
break
else:
print("Invalid choice. Please try again.")
if __name__ == "__main__":
main()
How It Works
1. Database Connection: Establishes a connection to the MySQL database using
mysql.connector.
2. CRUD Operations:
o Create: Add movies and shows.
o Read: View available shows and bookings.
o Update: Reduce seat availability after booking.
o Delete: Not implemented here but could be added for advanced features.
3. Reports: Summarize ticket sales by movie and showtime.
4. Real-Time Interaction: Use the menu to interact dynamically with the system.
Enhancements
1. Error Handling: Add try-except blocks for robust error handling.
2. GUI: Integrate Tkinter for a graphical interface.
3. Data Visualization: Use Matplotlib or Seaborn to plot revenue and sales trends.
