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()