Preparing your learning space...
92% through Real-World Projects tutorials
This project is a command-line application for booking movie tickets. You enter movies, schedule shows on screens, and book or cancel tickets from the terminal. It teaches you to build a small database with foreign keys and to keep data consistent with transactions.
You will write the database schema, seed it with sample data, and then build Python functions that talk to MySQL using parameterized queries.
The Movie Ticket Booking System stores movies, screens, shows, customers, and bookings in MySQL. A booking is the tricky part: when a customer books seats, the system must check availability, reduce the free seats on the show, and insert the booking row. All of this has to happen together or not at all, which is a perfect excuse to use a transaction.
This kind of system is common behind cinema apps, event booking sites, and even bus or train reservation portals. You will learn how to design a normalized schema, how to relate rows with foreign keys, and how to use commit() and rollback() to protect your data. By the end, you will have a working menu application that performs real booking operations.
mysql-connector-python package for Python.CREATE TABLE, INSERT, SELECT, and UPDATE.try/except.Install the connector package:
pip install mysql-connector-python
Create the database and tables by running the schema file against MySQL. You will be prompted for your MySQL root password:
mysql -u root -p < database.sql
movie-ticket-booking/
├── database.sql
├── db_connection.py
├── main.py
├── requirements.txt
└── README.md
database.sql creates the movie_booking database, all five tables, and inserts sample data.db_connection.py holds one helper function, get_connection(), that opens a connection to the database.main.py contains the menu loop and one function for each business operation.requirements.txt lists the Python package you need to install.README.md gives a short description of the project and how to run it.Five tables: movies, screens, shows, customers, and bookings. The shows table links a movie to a screen, and bookings links a show to a customer.
Booking data needs clear relationships. A show must belong to a real movie and a real screen. A booking must belong to a real show and a real customer. Foreign keys enforce those rules so the database rejects bad data before it ever gets in.
Create a new file called database.sql and write the schema.
DROP DATABASE IF EXISTS movie_booking;
CREATE DATABASE movie_booking;
USE movie_booking;
CREATE TABLE movies (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
duration_minutes INT NOT NULL
);
CREATE TABLE screens (
screen_id INT AUTO_INCREMENT PRIMARY KEY,
screen_name VARCHAR(20) NOT NULL,
total_seats INT NOT NULL
);
CREATE TABLE shows (
show_id INT AUTO_INCREMENT PRIMARY KEY,
movie_id INT NOT NULL,
screen_id INT NOT NULL,
show_date DATE NOT NULL,
show_time TIME NOT NULL,
available_seats INT NOT NULL,
price DECIMAL(8,2) NOT NULL,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id) ON DELETE CASCADE,
FOREIGN KEY (screen_id) REFERENCES screens(screen_id) ON DELETE CASCADE
);
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
show_id INT NOT NULL,
customer_id INT NOT NULL,
seats_booked INT NOT NULL,
total_amount DECIMAL(8,2) NOT NULL,
status ENUM('confirmed', 'cancelled') DEFAULT 'confirmed',
FOREIGN KEY (show_id) REFERENCES shows(show_id) ON DELETE CASCADE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);
The DROP DATABASE IF EXISTS line makes the file safe to re-run; it clears the old database before building a fresh one. Each table has an AUTO_INCREMENT primary key so MySQL assigns IDs for you. The FOREIGN KEY lines on shows guarantee that the movie and screen you reference actually exist. ON DELETE CASCADE means that deleting a movie also deletes its shows and bookings, keeping the database clean. The ENUM on bookings.status restricts the column to the two allowed values.
Sample rows for every table so the program has something to work with immediately.
An empty database makes a menu application boring to test. With a few movies, screens, shows, and customers already in place, you can run the program and try booking a ticket on the very first try.
Append these INSERT statements to database.sql.
INSERT INTO movies (title, duration_minutes) VALUES
('The Last Signal', 132),
('Ocean of Stars', 118),
('A Winter Letter', 95),
('Neon Highway', 145);
INSERT INTO screens (screen_name, total_seats) VALUES
('Screen 1', 120),
('Screen 2', 80),
('Screen 3', 60);
INSERT INTO shows (movie_id, screen_id, show_date, show_time, available_seats, price) VALUES
(1, 1, '2026-08-05', '18:30:00', 118, 250.00),
(1, 2, '2026-08-05', '21:00:00', 80, 300.00),
(2, 2, '2026-08-06', '16:00:00', 76, 220.00),
(3, 3, '2026-08-06', '19:30:00', 60, 180.00),
(4, 1, '2026-08-07', '20:00:00', 117, 320.00);
INSERT INTO customers (name, phone) VALUES
('Aisha Verma', '9876543210'),
('Rahul Mehta', '9123456780'),
('Priya Nair', '9988776655'),
('Karan Shah', '9012345678');
INSERT INTO bookings (show_id, customer_id, seats_booked, total_amount) VALUES
(1, 1, 2, 500.00),
(3, 2, 4, 880.00),
(5, 3, 3, 960.00);
The movie IDs in the shows inserts match the movies inserted just above them. For example, show 1 uses movie 1, which is "The Last Signal". Shows 1, 3, and 5 already have confirmed bookings in the seed data, so their available_seats start a little below the screen total: show 1 has 118 seats free because booking 1 took 2 of its 120, show 3 has 76 free because booking 2 took 4 of 80, and show 5 has 117 free because booking 3 took 3 of 120. Shows 2 and 4 have no bookings yet, so they start at full capacity. The total_amount on each booking is seats times price, so the numbers stay consistent.
A small module called db_connection.py with one function that returns a MySQL connection.
Every operation in main.py needs a connection. Putting that code in one place means you only change your password or database name once, instead of in ten different functions. It also makes the code in main.py much shorter to read.
Create db_connection.py.
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password_here",
database="movie_booking"
)
The function calls mysql.connector.connect() with the usual settings. host is localhost because MySQL runs on the same machine. Replace your_password_here with your actual MySQL password. The database argument selects movie_booking, so every query you run through this connection works inside that database automatically.
The main menu loop that keeps showing options until the user quits, plus the first real operation: adding a movie.
The whole application is menu-driven. Users pick an option by number, and the program calls the matching function. Adding a movie is the simplest operation, so it is the right place to learn the pattern that every other function will follow.
Create main.py. Start with the imports, the menu loop, and the add-movie function.
import mysql.connector
from db_connection import get_connection
def add_movie():
title = input("Movie title: ").strip()
duration = int(input("Duration in minutes: "))
conn = get_connection()
cursor = conn.cursor()
try:
query = "INSERT INTO movies (title, duration_minutes) VALUES (%s, %s)"
cursor.execute(query, (title, duration))
conn.commit()
print(f"Movie '{title}' added.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
def main():
while True:
print("\n--- Movie Ticket Booking ---")
print("1. Add movie")
print("2. Schedule show")
print("3. Book tickets")
print("4. Cancel booking")
print("5. List shows of a movie")
print("6. Show available seats")
print("7. Revenue per movie")
print("8. Exit")
choice = input("Choose an option: ").strip()
if choice == "1":
add_movie()
elif choice == "2":
schedule_show()
elif choice == "3":
book_tickets()
elif choice == "4":
cancel_booking()
elif choice == "5":
list_shows_of_movie()
elif choice == "6":
show_available_seats()
elif choice == "7":
revenue_per_movie()
elif choice == "8":
print("Goodbye.")
break
else:
print("Invalid choice, try again.")
if __name__ == "__main__":
main()
The query uses %s placeholders and passes the values as a tuple. Never build the SQL string by joining user input with +; a user could type SQL that changes your statement. Placeholders make the driver escape the values, which prevents SQL injection. The try/except/finally block commits a successful insert, rolls back on error, and always closes the cursor and connection. The menu loop simply checks which number was typed and calls the matching function.
The schedule_show() function. It takes a movie, a screen, a date, a time, and a price, then creates a new show whose available seats start at the screen's capacity.
A movie cannot be sold until someone schedules it. This operation connects the movies and screens tables and makes the initial seat count correct by copying it from the screen.
Add this function to main.py.
def schedule_show():
movie_id = int(input("Movie ID: "))
screen_id = int(input("Screen ID: "))
show_date = input("Show date (YYYY-MM-DD): ")
show_time = input("Show time (HH:MM:SS): ")
price = float(input("Ticket price: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute("SELECT total_seats FROM screens WHERE screen_id = %s", (screen_id,))
row = cursor.fetchone()
if row is None:
print("Screen not found.")
return
total = row[0]
query = ("INSERT INTO shows "
"(movie_id, screen_id, show_date, show_time, available_seats, price) "
"VALUES (%s, %s, %s, %s, %s, %s)")
cursor.execute(query, (movie_id, screen_id, show_date, show_time, total, price))
conn.commit()
print(f"Show scheduled with {total} available seats.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
The function first looks up the screen's total_seats. If the screen does not exist, fetchone() returns None and we stop early with a friendly message. Otherwise we use that number as the starting available_seats. This way the show always opens fully empty, with as many seats as the screen physically has.
The most important function in the project: book_tickets(). It must check that enough seats remain, reserve them, and record the booking, all as one unit.
If you only did UPDATE shows SET available_seats = available_seats - N and the INSERT into bookings failed, you would have fewer seats on the show than bookings say. If the insert succeeded but the update failed, you would sell seats you no longer have. A transaction makes both statements succeed or both fail.
Add this function to main.py.
def book_tickets():
show_id = int(input("Show ID: "))
customer_id = int(input("Customer ID: "))
seats = int(input("Number of seats: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute("SELECT available_seats, price FROM shows WHERE show_id = %s", (show_id,))
row = cursor.fetchone()
if row is None:
print("Show not found.")
return
available, price = row
if available < seats:
print(f"Only {available} seats left. Booking failed.")
return
cursor.execute(
"UPDATE shows SET available_seats = available_seats - %s WHERE show_id = %s",
(seats, show_id)
)
cursor.execute(
"INSERT INTO bookings (show_id, customer_id, seats_booked, total_amount) "
"VALUES (%s, %s, %s, %s)",
(show_id, customer_id, seats, seats * price)
)
conn.commit()
print(f"Booking confirmed for {seats} seat(s), total Rs {seats * price:.2f}.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
The check available < seats is the guard that stops overselling. Notice it reads the available seats inside the same transaction that updates them. If the update or the insert raises an error, the except block calls conn.rollback(), which undoes both statements, so the show keeps all its seats and no half-booking is stored. Only when both statements succeed does commit() make them permanent. seats * price is computed in Python, keeping the insert simple.
The cancel_booking() function. It marks a booking as cancelled and puts the seats back on the show.
Customers cancel. If we only changed the booking status, the seats would stay occupied forever and the show would lose revenue it could still earn. The seat count and the booking status must change together, so this is another transaction.
Add this function to main.py.
def cancel_booking():
booking_id = int(input("Booking ID: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT show_id, seats_booked, status FROM bookings WHERE booking_id = %s",
(booking_id,)
)
row = cursor.fetchone()
if row is None:
print("Booking not found.")
return
show_id, seats, status = row
if status == "cancelled":
print("Booking is already cancelled.")
return
cursor.execute(
"UPDATE shows SET available_seats = available_seats + %s WHERE show_id = %s",
(seats, show_id)
)
cursor.execute(
"UPDATE bookings SET status = 'cancelled' WHERE booking_id = %s",
(booking_id,)
)
conn.commit()
print("Booking cancelled, seats returned to the show.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
First we load the booking's show, seat count, and status. Cancelling twice would add the seats back twice, so the early check for status == "cancelled" prevents that. Then the transaction adds the seats back to the show and marks the booking cancelled. Both updates happen inside the transaction, so if either one fails, neither change sticks.
Three read-only functions: list shows of a movie, show available seats for a show, and revenue per movie.
A booking system is not useful without visibility. Customers want to know which shows exist and how many seats are left, and the cinema owner wants to know how much each movie has earned. These queries join tables to produce that picture.
Add these three functions to main.py, and also add the requirements.txt and README.md files.
def list_shows_of_movie():
movie_id = int(input("Movie ID: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT s.show_id, s.screen_id, s.show_date, s.show_time, s.available_seats "
"FROM shows s WHERE s.movie_id = %s ORDER BY s.show_date, s.show_time",
(movie_id,)
)
rows = cursor.fetchall()
if not rows:
print("No shows found for this movie.")
for r in rows:
print(f"Show {r[0]}: screen {r[1]}, {r[2]} at {r[3]}, "
f"{r[4]} seats available")
except mysql.connector.Error as err:
print("Error:", err)
finally:
cursor.close()
conn.close()
def show_available_seats():
show_id = int(input("Show ID: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT available_seats FROM shows WHERE show_id = %s", (show_id,)
)
row = cursor.fetchone()
if row is None:
print("Show not found.")
else:
print(f"Show {show_id} has {row[0]} seats available.")
except mysql.connector.Error as err:
print("Error:", err)
finally:
cursor.close()
conn.close()
def revenue_per_movie():
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT m.title, COALESCE(SUM(b.total_amount), 0) "
"FROM movies m "
"LEFT JOIN shows s ON s.movie_id = m.movie_id "
"LEFT JOIN bookings b ON b.show_id = s.show_id "
"AND b.status = 'confirmed' "
"GROUP BY m.movie_id, m.title "
"ORDER BY 2 DESC"
)
rows = cursor.fetchall()
for r in rows:
print(f"{r[0]}: Rs {r[1]:.2f}")
except mysql.connector.Error as err:
print("Error:", err)
finally:
cursor.close()
conn.close()
list_shows_of_movie filters shows by movie and orders them by date and time so the soonest show appears first. show_available_seats is a simple lookup of one column. The revenue query is the trickiest: it joins movies to shows to bookings, and only counts bookings whose status is still confirmed. A cancelled booking would otherwise inflate the revenue. COALESCE(SUM(...), 0) turns a movie with no bookings at all into a clean zero instead of NULL.
Create requirements.txt with one line:
mysql-connector-python
Create a short README.md:
# Movie Ticket Booking System
A CLI application built with Python and MySQL for booking movie tickets.
## Setup
1. pip install mysql-connector-python
2. mysql -u root -p < database.sql
3. Edit the password in db_connection.py
4. python main.py
DROP DATABASE IF EXISTS movie_booking;
CREATE DATABASE movie_booking;
USE movie_booking;
CREATE TABLE movies (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
duration_minutes INT NOT NULL
);
CREATE TABLE screens (
screen_id INT AUTO_INCREMENT PRIMARY KEY,
screen_name VARCHAR(20) NOT NULL,
total_seats INT NOT NULL
);
CREATE TABLE shows (
show_id INT AUTO_INCREMENT PRIMARY KEY,
movie_id INT NOT NULL,
screen_id INT NOT NULL,
show_date DATE NOT NULL,
show_time TIME NOT NULL,
available_seats INT NOT NULL,
price DECIMAL(8,2) NOT NULL,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id) ON DELETE CASCADE,
FOREIGN KEY (screen_id) REFERENCES screens(screen_id) ON DELETE CASCADE
);
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE bookings (
booking_id INT AUTO_INCREMENT PRIMARY KEY,
show_id INT NOT NULL,
customer_id INT NOT NULL,
seats_booked INT NOT NULL,
total_amount DECIMAL(8,2) NOT NULL,
status ENUM('confirmed', 'cancelled') DEFAULT 'confirmed',
FOREIGN KEY (show_id) REFERENCES shows(show_id) ON DELETE CASCADE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);
INSERT INTO movies (title, duration_minutes) VALUES
('The Last Signal', 132),
('Ocean of Stars', 118),
('A Winter Letter', 95),
('Neon Highway', 145);
INSERT INTO screens (screen_name, total_seats) VALUES
('Screen 1', 120),
('Screen 2', 80),
('Screen 3', 60);
INSERT INTO shows (movie_id, screen_id, show_date, show_time, available_seats, price) VALUES
(1, 1, '2026-08-05', '18:30:00', 118, 250.00),
(1, 2, '2026-08-05', '21:00:00', 80, 300.00),
(2, 2, '2026-08-06', '16:00:00', 76, 220.00),
(3, 3, '2026-08-06', '19:30:00', 60, 180.00),
(4, 1, '2026-08-07', '20:00:00', 117, 320.00);
INSERT INTO customers (name, phone) VALUES
('Aisha Verma', '9876543210'),
('Rahul Mehta', '9123456780'),
('Priya Nair', '9988776655'),
('Karan Shah', '9012345678');
INSERT INTO bookings (show_id, customer_id, seats_booked, total_amount) VALUES
(1, 1, 2, 500.00),
(3, 2, 4, 880.00),
(5, 3, 3, 960.00);
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password_here",
database="movie_booking"
)
import mysql.connector
from db_connection import get_connection
def add_movie():
title = input("Movie title: ").strip()
duration = int(input("Duration in minutes: "))
conn = get_connection()
cursor = conn.cursor()
try:
query = "INSERT INTO movies (title, duration_minutes) VALUES (%s, %s)"
cursor.execute(query, (title, duration))
conn.commit()
print(f"Movie '{title}' added.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
def schedule_show():
movie_id = int(input("Movie ID: "))
screen_id = int(input("Screen ID: "))
show_date = input("Show date (YYYY-MM-DD): ")
show_time = input("Show time (HH:MM:SS): ")
price = float(input("Ticket price: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute("SELECT total_seats FROM screens WHERE screen_id = %s", (screen_id,))
row = cursor.fetchone()
if row is None:
print("Screen not found.")
return
total = row[0]
query = ("INSERT INTO shows "
"(movie_id, screen_id, show_date, show_time, available_seats, price) "
"VALUES (%s, %s, %s, %s, %s, %s)")
cursor.execute(query, (movie_id, screen_id, show_date, show_time, total, price))
conn.commit()
print(f"Show scheduled with {total} available seats.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
def book_tickets():
show_id = int(input("Show ID: "))
customer_id = int(input("Customer ID: "))
seats = int(input("Number of seats: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute("SELECT available_seats, price FROM shows WHERE show_id = %s", (show_id,))
row = cursor.fetchone()
if row is None:
print("Show not found.")
return
available, price = row
if available < seats:
print(f"Only {available} seats left. Booking failed.")
return
cursor.execute(
"UPDATE shows SET available_seats = available_seats - %s WHERE show_id = %s",
(seats, show_id)
)
cursor.execute(
"INSERT INTO bookings (show_id, customer_id, seats_booked, total_amount) "
"VALUES (%s, %s, %s, %s)",
(show_id, customer_id, seats, seats * price)
)
conn.commit()
print(f"Booking confirmed for {seats} seat(s), total Rs {seats * price:.2f}.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
def cancel_booking():
booking_id = int(input("Booking ID: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT show_id, seats_booked, status FROM bookings WHERE booking_id = %s",
(booking_id,)
)
row = cursor.fetchone()
if row is None:
print("Booking not found.")
return
show_id, seats, status = row
if status == "cancelled":
print("Booking is already cancelled.")
return
cursor.execute(
"UPDATE shows SET available_seats = available_seats + %s WHERE show_id = %s",
(seats, show_id)
)
cursor.execute(
"UPDATE bookings SET status = 'cancelled' WHERE booking_id = %s",
(booking_id,)
)
conn.commit()
print("Booking cancelled, seats returned to the show.")
except mysql.connector.Error as err:
conn.rollback()
print("Error:", err)
finally:
cursor.close()
conn.close()
def list_shows_of_movie():
movie_id = int(input("Movie ID: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT s.show_id, s.screen_id, s.show_date, s.show_time, s.available_seats "
"FROM shows s WHERE s.movie_id = %s ORDER BY s.show_date, s.show_time",
(movie_id,)
)
rows = cursor.fetchall()
if not rows:
print("No shows found for this movie.")
for r in rows:
print(f"Show {r[0]}: screen {r[1]}, {r[2]} at {r[3]}, "
f"{r[4]} seats available")
except mysql.connector.Error as err:
print("Error:", err)
finally:
cursor.close()
conn.close()
def show_available_seats():
show_id = int(input("Show ID: "))
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT available_seats FROM shows WHERE show_id = %s", (show_id,)
)
row = cursor.fetchone()
if row is None:
print("Show not found.")
else:
print(f"Show {show_id} has {row[0]} seats available.")
except mysql.connector.Error as err:
print("Error:", err)
finally:
cursor.close()
conn.close()
def revenue_per_movie():
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT m.title, COALESCE(SUM(b.total_amount), 0) "
"FROM movies m "
"LEFT JOIN shows s ON s.movie_id = m.movie_id "
"LEFT JOIN bookings b ON b.show_id = s.show_id "
"AND b.status = 'confirmed' "
"GROUP BY m.movie_id, m.title "
"ORDER BY 2 DESC"
)
rows = cursor.fetchall()
for r in rows:
print(f"{r[0]}: Rs {r[1]:.2f}")
except mysql.connector.Error as err:
print("Error:", err)
finally:
cursor.close()
conn.close()
def main():
while True:
print("\n--- Movie Ticket Booking ---")
print("1. Add movie")
print("2. Schedule show")
print("3. Book tickets")
print("4. Cancel booking")
print("5. List shows of a movie")
print("6. Show available seats")
print("7. Revenue per movie")
print("8. Exit")
choice = input("Choose an option: ").strip()
if choice == "1":
add_movie()
elif choice == "2":
schedule_show()
elif choice == "3":
book_tickets()
elif choice == "4":
cancel_booking()
elif choice == "5":
list_shows_of_movie()
elif choice == "6":
show_available_seats()
elif choice == "7":
revenue_per_movie()
elif choice == "8":
print("Goodbye.")
break
else:
print("Invalid choice, try again.")
if __name__ == "__main__":
main()
mysql-connector-python
# Movie Ticket Booking System
A CLI application built with Python and MySQL for booking movie tickets.
## Setup
1. pip install mysql-connector-python
2. mysql -u root -p < database.sql
3. Edit the password in db_connection.py
4. python main.py
Open a terminal in the project folder and run these commands in order.
Install the connector:
pip install mysql-connector-python
Load the schema and seed data:
mysql -u root -p < database.sql
Start the program:
python main.py
Your first run should look something like this:
--- Movie Ticket Booking ---
1. Add movie
2. Schedule show
3. Book tickets
4. Cancel booking
5. List shows of a movie
6. Show available seats
7. Revenue per movie
8. Exit
Choose an option: 3
Show ID: 1
Customer ID: 1
Number of seats: 2
Booking confirmed for 2 seat(s), total Rs 500.00.
Test 1 – Book tickets successfully
Input
Option 3
Show ID: 2
Customer ID: 2
Number of seats: 3
Expected Output
Booking confirmed for 3 seat(s), total Rs 900.00.
Explanation
Show 2 has 80 seats available at Rs 300 per ticket. Three seats at Rs 300 gives 900. After this booking the show should show 77 seats available if you run option 6 with show ID 2.
Test 2 – Try to book more seats than are available
Input
Option 3
Show ID: 4
Customer ID: 3
Number of seats: 65
Expected Output
Only 60 seats left. Booking failed.
Explanation
Show 4 is on Screen 3, which has 60 seats, and no one has booked any of them yet. The guard available < seats catches the oversell before any write happens, so no partial booking is stored and the seat count is unchanged.
Test 3 – Cancel a booking and confirm seats return
Input
Option 4
Booking ID: 1
Expected Output
Booking cancelled, seats returned to the show.
Explanation
Booking 1 reserved 2 seats on show 1. After cancellation, show 1 goes from 118 available seats back to 120. Running option 6 for show 1 should now print 120 seats available, and option 7 should no longer count the Rs 500 in revenue.
Test 4 – View revenue per movie
Input
Option 7
Expected Output
The Last Signal: Rs 500.00
Ocean of Stars: Rs 880.00
Neon Highway: Rs 960.00
A Winter Letter: Rs 0.00
Explanation
The seed data has confirmed bookings for shows of the first three movies. "A Winter Letter" has no confirmed bookings, so COALESCE turns the NULL sum into a clean 0.00.
Problem: ModuleNotFoundError: No module named 'mysql.connector'
Reason: The mysql-connector-python package is not installed, or it was installed for a different Python version than the one running the script.
Solution: Run pip install mysql-connector-python. If you have multiple Python versions, use python -m pip install mysql-connector-python so the package goes to the interpreter you actually run.
Problem: mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'movie_booking.shows' doesn't exist
Reason: You started the program before loading the schema, or the schema file failed partway through.
Solution: Run mysql -u root -p < database.sql and check the output for errors. Then restart the program.
Problem: Changes are not saved after an insert or update, but no error appears.
Reason: You forgot to call conn.commit(). MySQL connectors keep the transaction open until you commit it.
Solution: Call conn.commit() after the statements that change data. In this project every writing operation already does this inside its try block.
Problem: mysql.connector.errors.IntegrityError: 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
Reason: You referenced a show_id, customer_id, movie_id, or screen_id that does not exist in the parent table.
Solution: Print the table with a quick SELECT * FROM shows; in MySQL Workbench or the mysql client, and use an ID that actually exists.
Problem: ERROR 1050 (42S01): Table 'movies' already exists when re-running the SQL file
Reason: The database already has these tables from a previous run, and your file does not drop them first.
Solution: Put DROP DATABASE IF EXISTS movie_booking; at the top of the schema file, as this tutorial does, so every run starts clean.
Problem: mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'
Reason: The password in db_connection.py does not match your real MySQL root password, or the user has no access from this host.
Solution: Edit the password argument in db_connection.py. If the machine uses socket authentication, create a dedicated user with a password instead of logging in as root.
seats column layout per screen and book specific seat numbers instead of just a count.schedule_show guard: refuse to insert a show when another show already exists on the same screen with an overlapping date and time. Hint: compare times with BETWEEN.payment table linked to bookings and record a payment when a booking is created, using a transaction so a booking always has a matching payment.Save your progress and earn XP for completing tutorials.
Technology
MySQL
Lesson group
Real-World Projects
Progress
92% complete