def insert_account_subscription(purchase_date, expire_date, account_id, subscription_id): sql = "INSERT INTO account_subscription(purchase_date, expire_date, account_id, subscription_id) VALUES(%s,%s,%s,%s)" values = (str(purchase_date), str(expire_date), str(account_id), str(subscription_id)) cursor.execute(sql, values) connection.commit()
def account_with_highest_income_for_cinema(): sql = """select accounts.login, SUM(IF(modifiers.type = 1, shows.price - (modifiers.value * shows.price/100), shows.price - modifiers.value )) as earnings FROM tickets_archive, modifiers, shows, accounts WHERE tickets_archive.modifier_id = modifiers.id and tickets_archive.show_id = shows.id and tickets_archive.account_id = accounts.id""" cursor.execute(sql) return cursor.fetchall()
def branches_with_highest_income_for_cinema(): sql = """select branches.city, branches.address, SUM( IF(modifiers.type = 1, shows.price - (modifiers.value * shows.price/100), shows.price - modifiers.value )) as earnings FROM tickets_archive, modifiers, shows, branches, rooms, room_show WHERE tickets_archive.modifier_id = modifiers.id and tickets_archive.show_id = shows.id and shows.id = room_show.show_id and room_show.room_id = rooms.id and rooms.branch_id = branches.id""" cursor.execute(sql) return cursor.fetchall()
def GenerateForeignKeys(self, table_name): sql = "SELECT id FROM " + table_name cursor.execute(sql) records = cursor.fetchall() id_list = [] for string_val in records: id_list.append(int(string_val[0])) id_list.sort() return id_list
def taken_seats_on_show(): sql = """ SELECT seat_ticket.ticket_id AS 'ticket', tickets.show_id, seats.number AS 'taken seat', movies.title FROM seat_ticket, seats, shows, movies, tickets WHERE seat_ticket.seat_id = seats.id AND seat_ticket.ticket_id=tickets.id AND tickets.show_id = shows.id AND shows.movie_id = movies.id AND shows.id = 4 ORDER BY seats.id;""" cursor.execute(sql) return cursor.fetchall()
def top3_genres_in_year(year="2020"): sql = f"""SELECT GenresList.Genre, COUNT(GenresList.Genre) AS CountValue FROM (( SELECT genres.name AS Genre, tickets.id, shows.show_date FROM ((movies INNER JOIN (genres INNER JOIN genre_movie ON genres.id = genre_movie.genre_id) ON movies.id = genre_movie.movie_id) INNER JOIN shows ON movies.id = shows.movie_id) INNER JOIN tickets ON shows.id = tickets.show_id GROUP BY genres.name, tickets.id, shows.show_date HAVING (YEAR(shows.show_date)={ year })) AS GenresList ) GROUP BY GenresList.Genre ORDER BY CountValue DESC LIMIT 3""" cursor.execute(sql) return cursor.fetchall()
def average_price_of_ticket_in_this_month(): sql = """SELECT AVG(IF(modifiers.type=1, shows.price - (modifiers.value * shows.price/100), shows.price-modifiers.value )) AS "average price this month" FROM shows, modifiers, tickets_archive, accounts WHERE shows.show_date IN( SELECT shows.show_date FROM shows WHERE DATE(show_date) >= DATE_SUB(NOW(), INTERVAL 1 MONTH) AND tickets_archive.modifier_id = modifiers.id AND tickets_archive.show_id = shows.id AND tickets_archive.account_id = accounts.id )""" cursor.execute(sql) return cursor.fetchall()
def most_often_played_movie_in_year(): sql = """SELECT shows.movie_id, movies.title, shows.show_date, COUNT(shows.movie_id) AS times_played FROM shows, movies WHERE shows.movie_id = movies.id AND shows.show_date IN ( SELECT shows.show_date FROM shows WHERE DATE(show_date)>=DATE_SUB(NOW(),INTERVAL 1 YEAR) ) GROUP BY movie_id ORDER BY times_played DESC LIMIT 1;""" cursor.execute(sql) return cursor.fetchall()
def most_popular_seats(): sql = """SELECT seats.number AS seat_number,rows.number AS row_number, COUNT(seat_ticket.seat_id) as popularity FROM seats INNER JOIN seat_ticket ON seat_ticket.seat_id = seats.id INNER JOIN rows ON seats.rows_id=rows.id GROUP BY seat_ticket.seat_id ORDER BY COUNT(seat_ticket.seat_id) DESC LIMIT 10""" cursor.execute(sql) return cursor.fetchall()
def insert_account(login, password, email): sql = "INSERT INTO accounts(login, password,email) VALUES(%s,%s,%s)" values = (login, password, email) cursor.execute(sql, values) connection.commit()
def insert_restriction(rest_name): sql = "INSERT INTO restrictions(name) VALUE(%s)" values = (rest_name, ) cursor.execute(sql, values) connection.commit()
def insert_discount(name, mod_id): sql = "INSERT INTO discounts(name, modifier_id) VALUES(%s,%s)" values = (name, str(mod_id)) cursor.execute(sql, values) connection.commit()
def insert_subscription(name, description, mod_id): sql = "INSERT INTO subscriptions(name, description, modifier_id) VALUES(%s,%s,%s)" values = (name, description, str(mod_id)) cursor.execute(sql, values) connection.commit()
def insert_ticket(show_id, seat_id, modifier_id, account_id, email): sql = "INSERT INTO tickets(show_id, seat_id, modifier_id, account_id, email) VALUES(%s,%s,%s,%s,%s)" values = (str(show_id), str(seat_id), str(modifier_id), str(account_id), email) cursor.execute(sql, values) connection.commit()
def insert_genre_movie(movie_id, genre_id): sql = "INSERT INTO genre_movie(movie_id, genre_id) VALUES(%s,%s)" values = (str(movie_id), str(genre_id)) cursor.execute(sql, values) connection.commit()
def insert_movie(title, description, rest_id): sql = "INSERT INTO movies(title, description, restriction_id) VALUES(%s,%s,%s)" values = (title[:50], description, str(rest_id)) cursor.execute(sql, values) connection.commit()
def insert_genre(genre_name): sql = "INSERT INTO genres(name) VALUE(%s)" values = (genre_name, ) cursor.execute(sql, values) connection.commit()
def insert_branch(city, address): sql = "INSERT INTO branches(city, address) VALUES(%s,%s)" values = (city, address) cursor.execute(sql, values) connection.commit()
def today_movies(date="2020-11-04"): sql = "SELECT movies.title FROM shows INNER JOIN movies ON shows.movie_id = movies.id WHERE shows.show_date = '" + date + "'" cursor.execute(sql) return cursor.fetchall()
def insert_room(number, branch_id): sql = "INSERT INTO rooms(number, branch_id) VALUES(%s,%s)" values = (str(number), str(branch_id)) cursor.execute(sql, values) connection.commit()
def movie_with_highest_income(): sql = "select movies.title, SUM(IF(modifiers.type = 1, shows.price-(modifiers.value * shows.price/100),shows.price -modifiers.value )) as earnings FROM tickets_archive,modifiers,shows,movies WHERE tickets_archive.modifier_id = modifiers.id and tickets_archive.show_id = shows.id and shows.movie_id = movies.id" cursor.execute(sql) return cursor.fetchall()
def insert_row(number, room_id): sql = "INSERT INTO rows(number, room_id) VALUES(%s,%s)" values = (str(number)[:1], str(room_id)) cursor.execute(sql, values) connection.commit()
def insert_seat(number, vip, row_id): sql = "INSERT INTO seats(number, vip, rows_id) VALUES(%s,%s,%s)" values = (str(number), str(vip), str(row_id)) cursor.execute(sql, values) connection.commit()
def insert_show(show_date, show_time, price, movie_id): sql = "INSERT INTO shows(show_date, show_time, price, movie_id) VALUES(%s,%s,%s,%s)" values = (str(show_date), str(show_time), str(price), str(movie_id)) cursor.execute(sql, values) connection.commit()
def today_played(): sql = "SELECT show_date, show_time, price, movies.title FROM shows, movies WHERE DATE(show_date)=CURDATE() AND shows.movie_id=movies.id" cursor.execute(sql) return cursor.fetchall()
def insert_room_show(room_id, show_id): sql = "INSERT INTO room_show(room_id, show_id) VALUES(%s,%s)" values = (str(room_id), str(show_id)) cursor.execute(sql, values) connection.commit()
def find_by_id(cls, _id): result = cursor.execute("SELECT quote FROM quotes WHERE _id = %s", (_id,)) row = cursor.fetchone() if row: return cls(*row)
def insert_modifier(type, name, value): sql = "INSERT INTO modifiers(type, name, value) VALUES(%s,%s,%s)" values = (str(type), name, str(value)) cursor.execute(sql, values) connection.commit()