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)
