Step 1: Create MySQL Database and Tables
Run the following SQL code to create a database and the required tables.
CREATE DATABASE MovieTicketBooking;
USE MovieTicketBooking;
-- Table for movies
CREATE TABLE movies (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
duration TIME NOT NULL,
genre VARCHAR(100) NOT NULL,
rating DECIMAL(3, 1) NOT NULL
);
-- Table for showing times
CREATE TABLE showings (
showing_id INT AUTO_INCREMENT PRIMARY KEY,
movie_id INT NOT NULL,
show_time DATETIME NOT NULL,
available_seats INT NOT NULL,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
);
-- Table for bookings
CREATE TABLE bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
showing_id INT NOT NULL,
customer_name VARCHAR(255) NOT NULL,
num_tickets INT NOT NULL,
total_price DECIMAL(10, 2) NOT NULL,
booking_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (showing_id) REFERENCES showings(showing_id)
);
Step 2: Python Code to Interface with MySQL
Here’s a simple Python code using mysql-connector to interact with the MySQL database.
Install MySQL Connector
You need to install the mysql-connector library if you don't already have it:
pip install mysql-connector-python
Python Script for Movie Ticket Booking
import mysql.connector
from mysql.connector import Error
def connect_to_db():
try:
connection = mysql.connector.connect(
host='localhost',
user='your_username', # Replace with your MySQL username
password='your_password', # Replace with your MySQL password
database='MovieTicketBooking'
)
if connection.is_connected():
print("Connected to the database")
return connection
except Error as e:
print(f"Error: {e}")
return None
def show_movies(connection):
cursor = connection.cursor()
cursor.execute("SELECT * FROM movies")
movies = cursor.fetchall()
print("Available Movies:")
for movie in movies:
print(f"{movie[0]}: {movie[1]} | Genre: {movie[3]} | Rating: {movie[4]} |
Duration: {movie[2]}")
cursor.close()
def book_ticket(connection, movie_id, customer_name, num_tickets):
cursor = connection.cursor()
# Check available showings for the selected movie
cursor.execute("SELECT * FROM showings WHERE movie_id = %s AND available_seats > 0",
(movie_id,))
showings = cursor.fetchall()
if not showings:
print("No available showings for this movie.")
cursor.close()
return
print("Available showings:")
for showing in showings:
print(f"Showing ID: {showing[0]}, Time: {showing[2]}, Available Seats:
{showing[3]}")
showing_id = int(input("Enter the showing ID to book tickets: "))
# Get the chosen showing details
cursor.execute("SELECT * FROM showings WHERE showing_id = %s", (showing_id,))
showing = cursor.fetchone()
if showing and showing[3] >= num_tickets:
total_price = num_tickets * 10 # Assuming each ticket costs 10
cursor.execute("INSERT INTO bookings (showing_id, customer_name, num_tickets,
total_price) VALUES (%s, %s, %s, %s)",
(showing_id, customer_name, num_tickets, total_price))
cursor.execute("UPDATE showings SET available_seats = available_seats - %s WHERE
showing_id = %s",
(num_tickets, showing_id))
connection.commit()
print(f"Booking successful! Total Price: ${total_price}")
else:
print("Insufficient seats or invalid showing.")
cursor.close()
def main():
connection = connect_to_db()
if not connection:
return
while True:
print("\n1. View Movies")
print("2. Book Tickets")
print("3. Exit")
choice = input("Enter your choice: ")
if choice == '1':
show_movies(connection)
elif choice == '2':
movie_id = int(input("Enter movie ID to book tickets: "))
customer_name = input("Enter your name: ")
num_tickets = int(input("Enter number of tickets: "))
book_ticket(connection, movie_id, customer_name, num_tickets)
elif choice == '3':
break
else:
print("Invalid choice. Please try again.")
connection.close()
if __name__ == '__main__':
main()
Step 3: Running the Code
1. Run the Python script: Make sure your MySQL server is running and the database is properly set up.
2. Add Movies and Showings: Before running the booking functionality, you’ll need to insert some
movies and showtimes into the database. You can do this directly via the MySQL command line or by
adding a function in the Python code for inserting records.
For example, adding a movie:
def add_movie(connection, title, duration, genre, rating):
cursor = connection.cursor()
cursor.execute("INSERT INTO movies (title, duration, genre, rating) VALUES (%s, %s,
%s, %s)",
(title, duration, genre, rating))
connection.commit()
cursor.close()
You can call this function within the script to add new movies to the system.
Final Notes:
You can expand this script to include additional features like:
o Canceling bookings
o Updating movies or showtimes
o Showing booking history
o More complex pricing logic (e.g., discounts based on time, loyalty points, etc.)
This should give you a solid starting point for a movie ticket booking system with Python and MySQL!
