def update_television_resolution(old_resolution: str, new_resolution: str): query = """UPDATE Television SET resolution = ? WHERE resolution = ?""" parameters = [new_resolution, old_resolution] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_Payer_profile_address(old_address: str, new_address: str): query = """UPDATE Payer_profile SET address = ? WHERE address = ?""" parameters = [new_address, old_address] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def create_table_internet(): query = """CREATE TABLE IF NOT EXISTS Internet( id INTEGER PRIMARY KEY AUTOINCREMENT, IP TEXT, bill FLOAT)""" with DatabaseContextManager("db") as db: db.execute(query)
def create_table_heating(): query = """CREATE TABLE IF NOT EXISTS Heating( id INTEGER PRIMARY KEY AUTOINCREMENT, heating_costs FLOAT )""" with DatabaseContextManager("db") as db: db.execute(query)
def update_electricity_kWh(old_kwh: float, new_kwh: float): query = """UPDATE Electricity SET kWh = ? WHERE kWh = ?""" parameters = [new_kwh, old_kwh] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_Plan_exclusives(old_exclusives: str, new_exclusives: str): query = """UPDATE Plan SET exclusives = ? WHERE exclusives = ?""" parameters = [new_exclusives, old_exclusives] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_internet_ip(old_ip: str, new_ip: str): query = """UPDATE Internet SET IP = ? WHERE IP = ?""" parameters = [new_ip, old_ip] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_plan_packages(old_price: float, new_price: float): query = """UPDATE Plan_packages SET price = ? WHERE price = ?""" parameters = [new_price, old_price] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_heating_costs(old_heating_costs: float, new_heating_costs: float): query = """UPDATE Heating SET heating_costs = ? WHERE heating_costs = ?""" parameters = [new_heating_costs, old_heating_costs] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_payer_identification_number(identification_number: int, electricity_id: int, id: int): query = """UPDATE Payer SET identification_number = ?, electricity_id = ? WHERE id = ?""" parameters = [identification_number, electricity_id, id] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def get_payer_profile_payers(): query = """SELECT * FROM Payer_profile JOIN Payer ON Payer_profile.id = Payer.payer_profile_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def create_table_payer_profile(): query = """CREATE TABLE IF NOT EXISTS Payer_profile( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, last_name TEXT, address TEXT)""" with DatabaseContextManager("db") as db: db.execute(query)
def create_table_electricity(): query = """CREATE TABLE IF NOT EXISTS Electricity( id INTEGER PRIMARY KEY AUTOINCREMENT, kWh FLOAT, bill FLOAT )""" with DatabaseContextManager("db") as db: db.execute(query)
def create_table_plan(): query = """CREATE TABLE IF NOT EXISTS Plan( id INTEGER PRIMARY KEY AUTOINCREMENT, channel_type TEXT, channel_list TEXT, exclusives TEXT)""" with DatabaseContextManager("db") as db: db.execute(query)
def get_payer_internet(): query = """SELECT * FROM Internet JOIN Payer ON Internet.id = Payer.internet_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def get_plan_packages_television(): query = """SELECT * FROM Television JOIN Plan_packages ON Television.id = Plan_packages.television_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def get_payer_electricity(): query = """SELECT * FROM Electricity JOIN Payer ON Electricity.id = Payer.electricity_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def get_payer_plan_packages(): query = """SELECT * FROM Plan_packages JOIN Payer ON Plan_packages.id = Payer.plan_packages_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def get_payer_heating(): query = """SELECT * FROM Heating JOIN Payer ON Heating.id = Payer.heating_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def create_table_television(): query = """CREATE TABLE IF NOT EXISTS Television( id INTEGER PRIMARY KEY AUTOINCREMENT, display_diagonal INTEGER, display_technology TEXT, resolution TEXT, HD_type TEXT )""" with DatabaseContextManager("db") as db: db.execute(query)
def create_payer(identification_number: int, payer_profile: int, internet_id: int, electricity_id: int, heating_id: int, plan_packages_id: int): query = """INSERT INTO Payer(identification_number, payer_profile_id, internet_id, electricity_id, heating_id, plan_packages_id) VALUES(?,?,?,?,?,?)""" parameters = [ identification_number, payer_profile, internet_id, electricity_id, heating_id, plan_packages_id ] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def create_table_plan_packages(): query = """CREATE TABLE IF NOT EXISTS Plan_packages( id INTEGER PRIMARY KEY AUTOINCREMENT, television_id INTEGER, plan_id INTEGER, price FLOAT, date_of_purchase DATE, FOREIGN KEY (television_id) REFERENCES Television(id), FOREIGN KEY (plan_id) REFERENCES Plan(id) )""" with DatabaseContextManager("db") as db: db.execute(query)
def get_all_tables(): query = """SELECT name, Electricity.bill FROM Payer JOIN Electricity ON Payer.electricity_id = Electricity.id JOIN Heating ON Payer.heating_id = Heating.id Join Internet ON Payer.internet_id = Internet.id Join Payer_profile ON Payer.payer_profile_id = Payer_profile.id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def create_table_payer(): query = """CREATE TABLE IF NOT EXISTS Payer( id INTEGER PRIMARY KEY AUTOINCREMENT, identification_number TEXT, payer_profile_id INTEGER, internet_id INTEGER, electricity_id INTEGER, heating_id INTEGER, plan_packages_id INTEGER, FOREIGN KEY (payer_profile_id) REFERENCES Payer_profile(id), FOREIGN KEY (internet_id) REFERENCES Internet(id), FOREIGN KEY (electricity_id) REFERENCES Electricity(id), FOREIGN KEY (heating_id) REFERENCES Heating(id), FOREIGN KEY (plan_packages_id) REFERENCES Plan_packages(id) )""" with DatabaseContextManager("db") as db: db.execute(query)
def delete_payer(identification_number: str): query = """DELETE FROM Payer WHERE identification_number = ?""" parameters = [identification_number] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def create_plan_packages(television_id: int, plan_id: int, price: float, date_of_purchase: str): query = """INSERT INTO Plan_packages(television_id, plan_id, price, date_of_purchase) VALUES(?,?,?,?)""" parameters = [television_id, plan_id, price, date_of_purchase] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def get_payer(): query = """SELECT * FROM Payer""" with DatabaseContextManager("db") as db: db.execute(query) for record in db.fetchall(): print(record)
def delete_plan_packages(date_of_purchase: dict): query = """DELETE FROM Plan_packages WHERE date_of_purchase = ?""" parameters = [date_of_purchase] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def create_heating(heating_costs: float): query = """INSERT INTO Heating(heating_costs) VALUES(?)""" parameters = [heating_costs] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def delete_electricity(heating_costs: float): query = """DELETE FROM Heating WHERE heating_costs = ?""" parameters = [heating_costs] with DatabaseContextManager("db") as db: db.execute(query, parameters)