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 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 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 check_connection(self, tbl, pw): con = Connection(table=tbl, password=pw) con.open() if con.status is not None: self.error = con.status return False con.close() return True
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 GetAllCustomers(): db,cursor = Connection() search_sql = "select * from customer;" try: cursor.execute(search_sql) rs = cursor.fetchall() except Exception: print("Error in loading customers") else: print("Customer Id".center(20)+"Customer Name".center(30)+"Customer contact".center(30)) for x in rs: print(f"{str(x[0]).center(20)}{x[1].center(30)}{x[2].center(30)}") 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 GetProductDetails(product_id): try: db, cursor = Connection() search_sql = f""" select * from product where product_id like {product_id}; """ cursor.execute(search_sql) rs = cursor.fetchall() except Exception: print("Error loading database") else: for x in rs: print(f"{'Product ID'.ljust(40)}{str(x[0]).ljust(40)}") print(f"{'Product Name'.ljust(40)}{x[1].ljust(40)}") print(f"{'Product Price'.ljust(40)}{x[2].ljust(40)}") print(f"{'Product Quantity Available'.ljust(40)}{x[3].ljust(40)}") print(f"{'Added'.ljust(40)}{x[4].ljust(40)}") finally: db.close()
def CheckAdminLogin(username, password): flag = 0 db, cursor = Connection() search_sql = f"select * from admin where username like '{username}' and password like '{password}';" try: cursor.execute(search_sql) rs = cursor.fetchall() if (len(rs) > 0): flag = 1 except Exception: print("Something went wrong") else: if (flag == 1): # registerd user return True else: # invalid admin return False # print("Do you wish to register") finally: db.close()
def CustomerPersonalDetails(customer_id): search_sql = f"select * from customer where customer_id = {customer_id};" db,cursor = Connection() try: cursor.execute(search_sql) rs = cursor.fetchall() except Exception: pass else: print("Personal Details".center(80,'-')) for x in rs: print("Customer Id".ljust(40) + str(x[0]).ljust(40)) print("Customer Name".ljust(40) + str(x[1]).ljust(40)) print("Customer Contact".ljust(40) + str(x[2]).ljust(40)) print("Customer address".ljust(40) + str(x[3]).ljust(40)) print("Customer type".ljust(40) + str(x[4]).ljust(40)) print("Customer email".ljust(40) + str(x[5]).ljust(40)) print("Registration date".ljust(40) + str(x[6]).ljust(40)) print("".center(80)) finally: db.close()
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 process_item(self, item, spider): """For each item, check if uname exists. Write tweet to database """ db = Connection(host = HOST_NAME, database = MYSQL_DB_NAME, user = MYSQL_USER_NAME, password = MYSQL_PASSWORD) tweet = item["tweet"] user = db.get("SELECT id FROM users WHERE username=%s", item["username"]) twt = db.get("SELECT * FROM tweets WHERE tweet=%s", tweet) if not twt: if user: db.execute("INSERT into tweets (user, tweet) VALUES (%s, %s)", user["id"], tweet) else: db.execute("INSERT into users (username) VALUES (%s)", item["username"]) user = db.get("SELECT id FROM users WHERE username=%s", item["username"]) db.execute("INSERT into tweets (user, tweet) VALUES (%s, %s)", user["id"], tweet) db.close() return item
def ViewMyBuyingDetails(customer_id): search_sql = f"select * from buying where customer_id = {customer_id}" db,cursor = Connection() total_bill = 0 try: cursor.execute(search_sql) rs = cursor.fetchall() except Exception: print("Sorry unable to connect to database") else: print("Buying details".center(0,'-')) print("Product Name".center(20) + "buying Quantity".center(20) + "Price (Rs.)".center(20) +"Buying date".center(20)) for x in rs: search_sql = f"select product_name from product where product_id = {x[2]};" cursor.execute(search_sql) product_name = cursor.fetchall()[0][0] print(f"{product_name.center(20)}{str(x[3]).center(20)}{str(x[4]).center(20)}{x[5].center(20)}") total_bill += int(x[4]) print("".center(80,'-')) print(f"Your Total bill is Rs. {total_bill}".center(80)) finally: db.close()
def GetAllProducts(): try: db, cursor = Connection() search_sql = """ select * from product; """ cursor.execute(search_sql) rs = cursor.fetchall() except Exception: print("Error in connection") else: print("Product ID".center(20) + "Product_Name".center(20) + "Product Quantity Available".center(20)) for x in rs: print( f"{str(x[0]).center(20)}{x[1].center(20)}{str(x[3]).center(20)}" ) finally: db.close() # GetAllProducts()
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()
def CheckCustomerLogin(customer_name,customer_contact): flag = 0 db,cursor = Connection() search_sql = f"select * from customer where customer_name like '{customer_name}' and customer_contact like '{customer_contact}';" try: cursor.execute(search_sql) rs = cursor.fetchall() if(len(rs)>0): flag = 1 except Exception as e: print(e) print("Something went wrong") else: if(flag == 1): # registerd user # print("Registered customer") return True,rs[0][0] else: # invalid customer # print("invalid customer") return False,None # print("Do you wish to register") finally: db.close()
def export_query_results(self, parent, name, tbl, sql, pw): """ Writes a sql query to a csv. """ start_datetime = timestr() start_time = just_time() logger = logging.getLogger( 'main.sql_exporter.ExportSql.export_query_results') logger.debug('Pulling query ' + name) fso = FSO() fso.make_dir('output') csv_path = 'output\\' + name + '_' + start_datetime + '.csv' con = Connection(table=tbl, password=pw) def result_iter(cursor, chunksize=1000): while True: results = cursor.fetchmany(chunksize) if not results: break for result in results: yield result def call_grandfather(status, done=False): if done == True: finish = just_time() else: finish = '...' parent.parent.callback( name , start_time , finish , status ) call_grandfather(status='Connecting') if con.open(): cursor = con.connection.cursor() call_grandfather(status='Executing query') try: cursor.execute(sql) with open(csv_path, 'w', newline='') as csv_file: call_grandfather(status='Writing csv') writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL) writer.writerow([i[0] for i in cursor.description]) # header for r, row in enumerate(result_iter(cursor, 1000)): if r > 100000: break if r % 1000 == 0: logger.info('Writing row ' + str(r)) writer.writerow(list(row) + ['', '', '', '']) call_grandfather(status='Great Success!', done=True) fso.open_file(csv_path) except Exception as e: err = str(e) logger.error(err) call_grandfather(status=str(e), done=True) finally: con.close() parent.call_dad()