def PlaceOrder(customer_id,product_id,create_date):
buying_quantity = int(input("Enter quantity : "))
search_sql = f"select product_quantity,product_price from product where product_id = {product_id};"
# product_quantity,product_price
db,cursor = Connection()
#print(create_date)
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
product_price = int(rs[0][1])
# print(product_price)
# print(f"Type of product id : {type(rs[0][0])}")
if(int(rs[0][0]) >= buying_quantity):
insert_sql = f"""insert into buying(customer_id,product_id,
buying_quantity,total_bill,buying_date)
values({customer_id},{product_id},{buying_quantity},{product_price*buying_quantity},
'{create_date}'
);
"""
cursor.execute(insert_sql)
update_sql = f"""update product set product_quantity = product_quantity - {buying_quantity}
where product_id = {product_id};
"""
cursor.execute(update_sql)
db.commit()
print("Added to your cart!!")
else:
print("Out of Stock")
except Exception as e:
print(e)
db.rollback()
else:
db.close()
def RegisterAdmin(name,
username,
password,
contact_no,
register_date,
access_token='1234'):
db, cursor = Connection()
search_sql = f"select username from admin where username = '******';"
insert_sql = f"""insert into admin(name,username,password,contact_no,register_date,access_token)
values('{name}','{username}','{password}','{contact_no}','{register_date}','{access_token}');
"""
flag = 0
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
# print(rs)
if (len(rs) > 0):
flag = 1
# print(rs[3])
except Exception as e:
print(e)
print("Error connecting to database")
else:
if (flag == 1):
print("username already taken")
# return False
elif (flag == 0):
cursor.execute(insert_sql)
db.commit()
print("Registered successfully as admin")
# return True
finally:
db.close()
def RegisterCustomer(customer_name,customer_contact,customer_address,customer_type,customer_email,register_date):
db,cursor = Connection()
search_sql = f"select * from customer where customer_contact = '{customer_contact}';"
insert_sql =f"""insert into customer(customer_name,customer_contact,customer_address,customer_type,
customer_email,register_date)
values('{customer_name.lower()}','{customer_contact}','{customer_address}','{customer_type}',
'{customer_email}','{register_date}');
"""
flag = 0
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
# print(rs)
if(len(rs)>0):
flag = 1
except Exception as e:
print(e)
print("Error connecting to database")
else:
if(flag==1):
# return False
print("Customer is already registered")
elif(flag==0):
cursor.execute(insert_sql)
db.commit()
print("Successfully created account")
# return True
finally:
db.close()
def DeleteProduct(product_id):
db, cursor = Connection()
delete_sql = f"delete from product where product_id={product_id};"
try:
cursor.execute(delete_sql)
db.commit()
except Exception:
db.rollback()
print("Some error in deleting the product")
else:
print("Product deleted successfully")
finally:
db.close()
def UpdateQuantity(product_id, product_quantity):
try:
db, cursor = Connection()
update_sql = f"""
update product set product_quantity = product_quantity + {product_quantity}
where product_id like '{product_id}';
"""
cursor.execute(update_sql)
except Exception:
db.rollback()
else:
db.commit()
print("Quantity updated successfully !")
finally:
db.close()
def main():
print "Connecting"
db = Connection()
db.connect()
print "Compressing thumbnails"
db.execute("""SELECT id FROM thumbnail""")
for (id,) in db.cursor.fetchall():
db.execute("""SELECT thumbnail FROM thumbnail WHERE id = %d""" % id)
thumbnail, = db.cursor.fetchone()
new_thumbnail = compress(id, thumbnail)
if new_thumbnail:
db.execute(
"""UPDATE thumbnail SET thumbnail = %(tn)s WHERE id = %(id)s""",
{"tn": db.make_binary(new_thumbnail), "id": id},
)
db.commit()
print "Compressed thumbnail %d from %d bytes to %d bytes" % (id, len(thumbnail), len(new_thumbnail))
def DeleteCustomer(customer_id):
db,cursor = Connection()
# flag=0
delete_sql = f"delete from customer where customer_id = {customer_id};"
search_sql = f"select * from customer where customer_id = {customer_id};"
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
if(len(rs)>0):
cursor.execute(delete_sql)
db.commit()
print("Customer deleted successfully")
else:
print("No such customer exists please check all the customers")
except Exception:
db.rollback()
print("Some error in deleting the customer")
else:
pass
finally:
db.close()
def RegisterProduct(product_name, product_price, product_quantity,
create_date):
db, cursor = Connection()
insert_sql = f""" insert into product(product_name,product_price,product_quantity,create_date)
values('{product_name}','{product_price}','{product_quantity}','{create_date}');
"""
search_sql = f""" select * from product
where product_name like '{product_name}';
"""
flag = 0 # used to check whether the new product details are already present in db or not
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
if (len(rs) > 0):
flag = 1
except Exception:
print("There is some error in databse connection!")
else:
if (flag == 1):
data = input(
"The product is already present inside the databse do you want to update the quantity (y/n) ? "
)
if (data == 'y' or data == 'Y'):
product_id = rs[0][0]
# product_quantity = int(input("Enter the quantity to be added : "))
# since rs is a tuple of ((data))
# quantity updation
UpdateQuantity(product_id, product_quantity)
elif (flag == 0):
cursor.execute(insert_sql)
print("Inserted successfully into the database")
db.commit()
finally:
db.close()
