def InsertPriceHistory(price, productId):
query = f"insert into pricehistory (price, product_id) values ('{price}','{productId}')"
connection = connectToDataBase()
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
cursor.close()
connection.close()
def GetDatabaseProduct(product):
query = f"select current_price from product where asin = '{product.asin}' and country = '{product.country}'"
connection = connectToDataBase()
cursor = connection.cursor()
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
connection.close()
price = result[0]['current_price']
return price
def GetOneDB(check_for, attr, value):
if check_for == 'product':
query = f"select * from product where {attr} = '{value}'"
else:
query = f"select * from user_table where {attr} = '{value}'"
connection = connectToDataBase()
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
fetchAll = cursor.fetchall()
cursor.close()
connection.close()
result = fetchAll[0]
return result
def UpdateProduct(product):
query = f"update product set current_price = '{product.price}' where asin = '{product.asin}' and country = '{product.country}' returning id"
price = GetDatabaseProduct(product)
if price != product.price:
priceChanged = True
else:
priceChanged = False
connection = connectToDataBase()
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
result = cursor.fetchall()
cursor.close()
connection.close()
productId = result[0]['id']
InsertPriceHistory(product.price, productId)
return {productId, priceChanged}
def DoExist(check_for, data):
if check_for == 'product':
query = f"select exists(select * from product where asin = '{data.asin}' and country = '{data.country}')"
else:
query = f"select exists(select * from user_table where email = '{data}')"
connection = connectToDataBase()
cursor = connection.cursor()
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
connection.close()
doExists = result[0]['exists']
if doExists:
return True
else:
return False
def InsertDB(check_for, data):
if check_for == 'product':
query = f"insert into product (title, country, ASIN, current_price, price_string, image_url, product_url) " \
f"values ('{data.title}', '{data.country}', '{data.asin}', '{data.price}'," \
f" '{data.price_string}', '{data.image_url}', '{data.url}') returning id"
else:
query = f"insert into user_table (email, first_name, last_name, password) " \
f"VALUES ('{data.email}', '{data.first_name}', '{data.last_name}', '{data.password}') returning id"
connection = connectToDataBase()
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
result = cursor.fetchall()
cursor.close()
connection.close()
resultId = result[0]['id']
if check_for == 'product':
InsertPriceHistory(data.price, resultId)
return resultId