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