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)