def create_movies():
query = """INSERT INTO `movies` VALUES (1,'Men in Black: International','Action',114,'The Men in Black have always protected the Earth from the scum of the universe. In this new adventure, they tackle their biggest threat to date: a mole in the Men in Black organization.'),
(2,'Joker','Crime, Drama, Thriller ',122,'In Gotham City, mentally troubled comedian Arthur Fleck is disregarded and mistreated by society. He then embarks on a downward spiral of revolution and bloody crime. This path brings him face-to-face with his alter-ego: the Joker.'),
(3,'Frozen II','Animation, Adventure, Comedy ',103,'Anna, Elsa, Kristoff, Olaf and Sven leave Arendelle to travel to an ancient, autumn-bound forest of an enchanted land. They set out to find the origin of Elsas powers in order to save their kingdom.'),
(4,'Parasite','Comedy, Crime, Drama',132,'A poor family, the Kims, con their way into becoming the servants of a rich family, the Parks. But their easy life gets complicated when their deception is threatened with exposure.')"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_cinema_rooms_table():
query = """CREATE TABLE IF NOT EXISTS Rooms(
roomId INTEGER PRIMARY KEY AUTOINCREMENT,
number INTEGER NOT NULL,
maxSeats INTEGER NOT NULL,
location varchar(45) DEFAULT NULL)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def get_unscheduled_movies():
query = """SELECT * FROM Movies
JOIN Schedules ON Movies.movieId = Schedules.movieId
WHERE scheduleId == NULL"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
print(db.fetchall())
def create_cinema_ticket_category_table():
query = """CREATE TABLE IF NOT EXISTS TicketCategories (
ticketCategoryId INTEGER PRIMARY KEY AUTOINCREMENT,
type varchar(45) NOT NULL,
price INTEGER NOT NULL)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def get_movies_today():
query = """SELECT * FROM Movies
JOIN Schedules ON Movies.movieId = Schedules.movieId
WHERE startTime >= ?"""
date = datetime.today()
params = [date]
with DatabaseContextManager(DB_NAME) as db:
db.execute(query, params)
print(db.fetchall())
def create_cinema_movies_table():
query = """CREATE TABLE IF NOT EXISTS Movies (
movieId INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(45) NOT NULL,
category VARCHAR(45) NOT NULL,
durationInMinutes INTEGER NOT NULL,
description VARCHAR(255) NOT NULL)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_cinema_seats_table():
query = """CREATE TABLE IF NOT EXISTS Seats(
seatId INTEGER PRIMARY KEY AUTOINCREMENT,
row INTEGER NOT NULL,
number INTEGER NOT NULL,
roomId INTEGER NOT NULL,
FOREIGN KEY (roomId) REFERENCES Rooms(roomId))"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_cinema_client_table():
query = """CREATE TABLE IF NOT EXISTS Clients(
clientId INTEGER PRIMARY KEY AUTOINCREMENT,
firstName varchar(45) NOT NULL,
lastName varchar(45) NOT NULL,
email varchar(45) NOT NULL,
dateOfBirth date NOT NULL)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_cinema_reservation_seat_table():
query = """CREATE TABLE IF NOT EXISTS Reservation_seat(
reservationSeatId INTEGER PRIMARY KEY AUTOINCREMENT,
reservationId INTEGER NOT NULL,
seatId INTEGER NOT NULL,
FOREIGN KEY (reservationId) REFERENCES reservations(reservationId) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (seatId) REFERENCES Seats(seatId) ON DELETE NO ACTION ON UPDATE NO ACTION)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_cinema_reservation_table():
query = """CREATE TABLE IF NOT EXISTS Reservations(
reservationId INTEGER PRIMARY KEY AUTOINCREMENT,
isPaid INTEGER NOT NULL,
clientId INTEGER NOT NULL,
scheduleId INTEGER NOT NULL,
FOREIGN KEY (clientId) REFERENCES Clients (clientId) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (scheduleId) REFERENCES Schedules(scheduleId) ON DELETE NO ACTION ON UPDATE NO ACTION)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def get_movies_by_name(name):
query = """SELECT name, firstName FROM Clients
JOIN Reservations ON Clients.clientId = Reservations.clientId
JOIN Schedules ON Reservations.scheduleId = Schedules.scheduleId
JOIN Movies ON Movies.movieId = Schedules.movieId
WHERE firstName = ?"""
params = [name]
with DatabaseContextManager(DB_NAME) as db:
db.execute(query, params)
print(db.fetchall())
def create_cinema_schedules_table():
query = """CREATE TABLE IF NOT EXISTS Schedules(
scheduleId INTEGER PRIMARY KEY AUTOINCREMENT,
startTime DATE NOT NULL,
movieId INTEGER NOT NULL,
roomId INTEGER NOT NULL,
FOREIGN KEY (movieId) REFERENCES Movies(movieId),
FOREIGN KEY (roomId) REFERENCES Rooms(roomId)
)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_schedules():
query = """ INSERT INTO schedules VALUES(1, '2020-01-01 20:00:00', 1, 1),
(2, '2020-01-01 20:00:00', 2, 2),
(3, '2020-01-01 23:00:00', 3, 1),
(4, '2020-01-02 18:00:00', 1, 1)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
# create_schedules()
def get_seat_rows_and_numbers_by_client(client):
query = """SELECT row, number FROM Clients
JOIN Reservations ON Reservations.clientId = Clients.clientId
JOIN Reservation_seat ON Reservations.reservationId = Reservation_seat.reservationId
JOIN Seats ON Seats.seatId = Reservation_seat.seatId
WHERE firstName = ?"""
params = [client]
with DatabaseContextManager(DB_NAME) as db:
db.execute(query, params)
print(db.fetchall())
def create_cinema_tickets_table():
query = """CREATE TABLE IF NOT EXISTS Tickets(
ticketId INTEGER PRIMARY KEY AUTOINCREMENT,
scheduleId INTEGER NOT NULL,
seatId INTEGER NOT NULL,
categoryId INTEGER NOT NULL,
FOREIGN KEY (scheduleId) REFERENCES Schedules(scheduleId),
FOREIGN KEY (seatId) REFERENCES Seats(seatId),
FOREIGN KEY (categoryId) REFERENCES ticketCategories(ticketCategoryId))"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_seats():
query = """INSERT INTO Seats VALUES (1,1,1,1),(2,1,2,1),(3,1,3,1),(4,2,1,1),(5,2,2,1),(6,2,3,1),(12,1,1,2),(13,1,2,2),(14,1,3,2),(15,2,1,2),(16,2,2,2),(17,2,3,2)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_reservations():
query = """INSERT INTO reservations VALUES (1,0,1,1)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_tickets():
query = """INSERT INTO tickets VALUES (1,2,1,1),(2,2,1,2)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def create_ticket_categories():
query = """INSERT INTO ticketCategories VALUES (1,'ADULT',100),(2,'CHILDREN',80),(3,'ELDERLY',50)"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
def get_all_movies():
query = """SELECT * FROM Movies"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)
print(db.fetchall())
def create_clients():
query = """INSERT INTO clients VALUES (1,'John','Johnson','*****@*****.**','1980-01-01'),(2,'Jim','Jameson','*****@*****.**','1990-02-02'),(3,'Sofia','Sophie','sofia@sophie','2000-01-01');"""
with DatabaseContextManager(DB_NAME) as db:
db.execute(query)