def load_basket_data(conn, row):
#this will always need to be uploaded
for item in row["basket"]:
SQL = ("INSERT INTO baskets (id, transaction_id, product_id)"
"VALUES (%s,%s,%s)")
new_id = str(uuid.uuid4())
val = (new_id, row["location"], item)
update(conn, SQL, val, should_commit=False, should_return=False)
return row
def load_franchise_table(
data
): # see transform.convert_locations_to_keys for the locations_dict
conn = connection()
for i in (data):
SQL = ("INSERT INTO franchise (id, cafe_location)" "VALUES (%s,%s)")
val = (data[i], i)
update(conn, SQL, val, False)
conn.commit()
conn.close()
def load_payment_table():
#this is a very simple reference table which will contain CASH, CARD or OTHER
#This should only ever happen ONCE
conn = connection()
options = ["CASH", "CARD", "OTHER"]
for num, i in enumerate(options):
SQL = ("INSERT INTO payment (id, type)" "VALUES (%s,%s)")
val = (num + 1, i)
update(conn, SQL, val, False)
conn.commit()
conn.close()
def Load_Transaction_Data(data):
conn = connection()
for transaction in data:
try:
update(
conn,
"INSERT INTO transaction(date_time, franchise, payment, id) VALUES (%s, %s, %s, %s)",
transaction.values(), False)
except:
print("Invalid input, skipping insertion")
conn.commit()
conn.close()
def Load_Basket_Data(data):
conn = connection()
for basket in data:
try:
update(
conn,
"INSERT INTO basket(transaction, product, id) VALUES (%s, %s, %s)",
basket.values(), False)
except:
print("Invalid input, skipping insertion")
conn.commit()
conn.close()
def Load_Product_Data(data):
conn = connection()
for product in data:
try:
update(
conn,
"INSERT INTO product(size, name, price, id) VALUES (%s, %s, %s, %s)",
product.values(), False)
except:
print("Invalid input, skipping insertion")
conn.commit()
conn.close()
def load_transaction_data(conn, row):
#this will always need to be uploaded
SQL = (
"INSERT INTO transactions (id, payment, franchise, date_time, cost_total)"
"VALUES (%s,%s,%s,%s,%s)")
new_id = str(uuid.uuid4())
val = (new_id, row["payment_method"], row["location"], row["datetime"],
row["total_price"])
update(conn, SQL, val, should_commit=False, should_return=False)
row["location"] = new_id
#row["location"] = new_id #currently we are changing location to be the transaction ID ( this can be changed! Ask for others input pls adam)
return row
def load_franchise_data(conn, row):
cafe_location = row["location"]
SQL = ("SELECT * FROM franchises WHERE cafe_location = %s")
values = (cafe_location, )
result = update(conn, SQL, values, should_commit=False, should_return=True)
if len(result) == 0:
SQL = ("INSERT INTO franchises (id, cafe_location)" "VALUES (%s,%s)")
new_id = str(uuid.uuid4())
val = (new_id, cafe_location)
update(conn, SQL, val, should_commit=False, should_return=False)
row["location"] = new_id
return row
row["location"] = result[0][0]
return row
def load_transaction(row, location_id, conn):
# load transaction
transaction_id = str(uuid.uuid4())
date_time = row["datetime"]
payment_type = row["payment_method"]
total_cost = row["total_cost"]
sql_insert_transaction = "INSERT INTO transaction (transaction_id, location_id, date_time, payment_type, total_cost) VALUES (%s, %s, %s, %s, %s)"
update(
conn,
sql_insert_transaction,
(transaction_id, location_id, date_time, payment_type, total_cost),
)
return transaction_id
def load_payment_data(conn, row):
#this is a very simple reference table which will contain CASH, CARD or OTHER
#This should only ever happen ONCE
payment_method = row["payment_method"]
SQL = ("SELECT * FROM payments WHERE payment_type = %s")
values = (payment_method, )
result = update(conn, SQL, values, should_commit=False, should_return=True)
if len(result) == 0:
SQL = ("INSERT INTO payments (id, payment_type)" "VALUES (%s,%s)")
new_id = str(uuid.uuid4())
val = (new_id, payment_method)
update(conn, SQL, val, should_commit=False, should_return=False)
row["payment_method"] = new_id
return row
row["payment_method"] = result[0][0]
return row
def load(data, conn):
# load Location
loc_id = load_location(data[0], conn)
# load Transaction
transaction_id = load_transaction(data[0], loc_id, conn)
for row in data:
# load product
product_size = row["size"]
product_name = row["name"]
product_type = row["type"]
# check if product already exists in the database
get_products = (
"SELECT * FROM product WHERE name = %s AND type = %s AND size = %s"
)
result = check(conn, get_products,
[product_name, product_type, product_size])
if result == []:
product_id = str(uuid.uuid4())
sql_insert_products = "INSERT INTO product (product_id, name, type, size) VALUES (%s, %s, %s, %s)"
update(
conn,
sql_insert_products,
(product_id, product_name, product_type, product_size),
)
else:
product_id = result[0][0]
# load basket
price = row["price"]
basket_id = str(uuid.uuid4())
sql_insert_basket = "INSERT INTO basket(basket_id, transaction_id, product_id, price) VALUES (%s, %s, %s, %s)"
update(conn, sql_insert_basket,
(basket_id, transaction_id, product_id, price))
def load_location(row, conn):
# load location
location_name = row["location_name"]
location_id = str(uuid.uuid4())
# check if location already exists in the database
get_locations = "SELECT * FROM location WHERE name = %s"
result = check(conn, get_locations, [location_name])
# if the location doesn't already exists in db, make new id and add to db
if result == []:
sql_insert_locations = (
"INSERT INTO location (location_id, name) VALUES (%s, %s)")
update(conn, sql_insert_locations, (location_id, location_name))
else:
# else get the existing id returned from the db
location_id = result[0][0]
return location_id
def load_product_data(conn, row):
product_list = row["basket"]
product_id_list = []
for product in product_list:
SQL = ("SELECT * FROM products WHERE price = %s and product_name = %s")
values = (product["price"], product["product"])
result = update(conn,
SQL,
values,
should_commit=False,
should_return=True)
if len(result) == 0:
SQL = ("INSERT INTO products (id, product_name, price, size)"
"VALUES (%s,%s,%s,%s)")
new_id = str(uuid.uuid4())
val = (new_id, product["product"], product["price"],
product["size"])
update(conn, SQL, val, should_commit=False, should_return=False)
product_id_list.append(new_id)
else:
product_id_list.append(result[0][0])
row["basket"] = product_id_list
return row