def update_orders(new_order_id: int, shop_customer_id: int, product_id: int): query = """UPDATE ORDERS SET order_id = ?, WHERE shopcustomer_id = ?, product_id = ? """ parameters = [new_order_id, shop_customer_id, product_id] with DatabaseContextManager("db_relashionships_execise") as db: db.execute(query, parameters)
def get_customers(): query = """SELECT * FROM SHOPCUSTOMERS""" with DatabaseContextManager("db_relashionships_execise") as db: db.execute(query) for record in db.fetchall(): print(record) print("------------------------------------------------------")
def get_products(): query = """SELECT * FROM PRODUCTS""" with DatabaseContextManager("db_relashionships_execise") as db: db.execute(query) for record in db.fetchall(): print(record) print("------------------------------------------------------")
def delete_customer(first_name: str): query = """DELETE FROM Customers WHERE first_name = ?""" parameters = [first_name] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_order_on_id(custom_id: int, product_id: int, order_id: int): query = """UPDATE Products SET Customer_id = ?, Product_id = ? WHERE Order_id = ?""" parameters = [custom_id, product_id, order_id] with DatabaseContextManager("order") as db: db.execute(query, parameters)
def update_customer_first_name(old_first_name: str, new_first_name: str): query = """UPDATE Customers SET first_name = ? WHERE first_name = ?""" parameters = [new_first_name, old_first_name] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def get_orders(): query = """SELECT * FROM Orders""" with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query) for record in ordersdb.fetchall(): print(record) print("------------------------------------------------------")
def update_company_name(old_company_name: str, new_company_name: str): query = """UPDATE Companies SET company_name = ? WHERE company_name = ?""" parameters = [new_company_name, old_company_name] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_customer_age(old_age: str, new_age: str): query = """UPDATE Customers SET age = ? WHERE age =?""" parameters = [old_age, new_age] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_custumer(first_name: str, new_age: int): query = """UPDATE Custumers SET age = ? WHERE first_name = ?""" parameters = [new_age, first_name] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def delete_custumer(id: int): query = """DELETE FROM Custumers WHERE id = ?""" parameters = [id] with DatabaseContextManager("db") as db: db.execute(query, parameters) print("------------------------------------------------------")
def get_companies(): query = """SELECT * FROM Companies""" with DatabaseContextManager("db") as db: db.execute(query) for record in db.fetchall(): print(record) print("------------------------------------------------------")
def update_product(id: int, product_name: str, description: str, price: float): query = """UPDATE Product SET product_name = ?, description = ?, price = ? WHERE id = ?""" parameters = [id, product_name, description, price] with DatabaseContextManager("db2") as db: db.execute(query, parameters)
def update_order_id(old_order_id: int, new_order_id: int): query = """UPDATE Orders SET first_name = ? WHERE first_name = ?""" parameters = [new_order_id, old_order_id] with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query, parameters)
def get_customers_products(): query = """SELECT * FROM Customers_Products""" with DatabaseContextManager("db") as db: db.execute(query) for record in db.fetchall(): print(record) print("------------------------------------------------------")
def update_product_price(old_price: int, new_price: int): query = """UPDATE Products SET price = ? WHERE price = ?""" parameters = [new_price, old_price] with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query, parameters)
def update_customer_amount_spent(old_amount_spent: int, new_amount_spent: int): query = """UPDATE Customers SET amount_spent = ? WHERE amount_spent = ?""" parameters = [new_amount_spent, old_amount_spent] with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query, parameters)
def get_table_product(): query = """SELECT * FROM Product""" with DatabaseContextManager("db2") as db: db.execute(query) for row in db.fetchall(): print(row) print("-------------------------------------------")
def create_table_companies(): query = """CREATE TABLE IF NOT EXISTS Companies( company_id INTEGER PRIMARY KEY AUTOINCREMENT, company_name TEXT, employee_count INTEGER)""" with DatabaseContextManager("db") as db: db.execute(query)
def update_company(old_employee_count: int, new_employee_count: int): query = """UPDATE Companies SET company_name = ? WHERE company_name = ?""" params = [new_employee_count, old_employee_count] with DatabaseContextManager("db") as db: db.execute(query, params)
def get_customers_companies(): query = """SELECT * FROM Customers NATURAL JOIN Companies""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row) print("------------------------------------------------------")
def get_customers_companies(): query = """SELECT * FROM Custumers JOIN Companies ON Custumer.company_id = Companies.company_id""" with DatabaseContextManager("db") as db: db.execute(query) for row in db.fetchall(): print(row)
def update_customer_on_id(first_name: str, last_name: str, age: int, Customer_id: int): query = """UPDATE Customers SET first_name = ?, last_name = ?, age = ? WHERE Customer_id = ?""" parameters = [first_name, last_name, age, Customer_id] with DatabaseContextManager("db") as db: db.execute(query, parameters)
def get_customers_products_orders(): query = """SELECT * FROM Customers, Products NATURAL JOIN Orders""" with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query) for row in ordersdb.fetchall(): print(row) print("------------------------------------------------------")
def create_customer(first_name: str, last_name: str, age: int, company_id: int): query = """INSERT INTO Customers(first_name, last_name, age, company_id) VALUES(?,?,?,?)""" # Question marks are used in initial query to have placeholders for upcoming parameters. # (This is used to protect ourselves from SQL Injection attacks) parameters = [first_name, last_name, age, company_id] # Parameters are used to pass values that were given when calling the function. with DatabaseContextManager("db") as db: db.execute(query, parameters)
def update_customer(id: int, first_name: str, last_name: str, amount_spent: float): query = """UPDATE Customer SET first_name = ?, last_name = ?, amount_spent = ? WHERE id = ?""" parameters = [id, first_name, last_name, amount_spent] with DatabaseContextManager("db2") as db: db.execute(query, parameters)
def create_table_customers(): query = """CREATE TABLE IF NOT EXISTS Customers( customer_id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT, amount_spent INTEGER)""" with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query)
def update_customer(first_name, last_name, age): query = """UPDATE Customer SET age = ? WHERE first_name = ? AND last_name = ?""" parameters = [age, first_name, last_name] with DatabaseContextManager("CRUD") as db: db.execute(query, parameters)
def create_table_products(): query = """CREATE TABLE IF NOT EXISTS Products( product_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, price INTEGER)""" with DatabaseContextManager("ordersdb") as ordersdb: ordersdb.execute(query)
def update_shop_customer(first_name: str, last_name: str, new_amount_spent: int): query = """UPDATE CUSTOMER SET AMOUNT_SPENT = ?, WHERE first_name = ?, last_name =? """ parameters = [new_amount_spent, first_name, last_name] with DatabaseContextManager("db_relashionships_execise") as db: db.execute(query, parameters)