from pymysql import *
# 在進(jìn)行刪增改操作的時(shí)候要進(jìn)行comit提交
class JD(object):
"""docstring for JD"""
def __init__(self):
self.conn = connect(database = 'jd',password = '123456',charset = 'utf8',host = 'localhost',port = 3306 , user = 'root')
self.cursor = self.conn.cursor()
self.tc = False
def __del__(self):
self.cursor.close()
self.conn.close()
def execute_sql(self,sql):
self.cursor.execute(sql)
for i in self.cursor.fetchall():
print(i)
# 登錄
def login(self):
while True:
self.account = input("請(qǐng)輸入你的賬號(hào)")
passwd = input('請(qǐng)輸入你的密碼')
sql = "select * from customers where tel = '%s'" %(self.account)
YZ = self.cursor.execute(sql)
if YZ == 0:
print('您沒有登錄，請(qǐng)您先注冊(cè)后在的登錄')
opt = input('1：注冊(cè)，2：退出')
if opt == '1':
self.register()
else:
self.tc = True
break
else:
acc = str(self.cursor.fetchone())
b = acc.split(',')
if b[3][1:] == str(self.account) and b[4][2:-2] == str(passwd):
break
else:
print('賬號(hào)或者密碼不正確，請(qǐng)從新輸入')
opt = input('1:重輸，2：退出')
if opt == '2':
self.tc = True
break
# 注冊(cè)
def register(self):
name = input('請(qǐng)輸入你的用戶名')
address = input("請(qǐng)輸入你的地址")
tel = int(input('請(qǐng)輸入你的賬號(hào)：以后該手機(jī)號(hào)將作為你的賬號(hào)進(jìn)行登錄'))
passwd = input('請(qǐng)輸入你的密碼：')
passwd2 = input('請(qǐng)您重新在輸入一次密碼')
if passwd == passwd2:
sql = 'insert into customers values (null,"%s","%s",%d,"%s")' %(name,address,tel,passwd)
self.cursor.execute(sql)
self.conn.commit()
else:
print('兩次密碼輸入不正確，請(qǐng)您重新輸入')
def show_all_items(self):
# '顯示所有的商品'
sql = 'select * from goods;'
self.execute_sql(sql)
def show_cates(self):
sql = 'select name from goods_cates;'
self.execute_sql(sql)
def show_crands(self):
sql = 'select name from goods_brands;'
self.execute_sql(sql)
def add_cates(self):
item_name = input('請(qǐng)輸入新商品分類的名稱;')
sql = """insert into goods_cates(name) values ('%s'); """ %item_name
self.cursor.execute(sql)
self.conn.commit()
def del_cate(self):
item_name = input('請(qǐng)輸入你要?jiǎng)h除的商品分類的\n')
sql = """ delete from goods_cates where name = ('%s')""" %item_name
self.cursor.execute(sql)
self.conn.commit()
def update_cates(self):
while True:
item_name = input('請(qǐng)輸入你要修改的數(shù)據(jù)\n')
data = input('請(qǐng)輸入你要更新的數(shù)據(jù)\n')
try:
sql = """ upate goods_cates set name = ('%s') where name = '%s'""" %(data,item_name)
self.cursor.execute(sql)
self.conn.commit()
break
except:
print('沒有這個(gè)數(shù)據(jù)，請(qǐng)檢查從新輸入')
def find_shop(self):
try:
shop = input('請(qǐng)輸入你要查找的商品的名字\n')
# sql = """ select * from goods where name = '%s'""" %shop
# self.execute_sql(sql)
# 防止sql注入
sql = " select * from goods where name = %s"
self.cursor.execute(sql,[shop])
print(self.cursor.fetchall())
except:
shop = False
return shop
def buy_shop(self):
# 往訂單表里面添加數(shù)據(jù) orders
sql = 'select id from? customers where tel = %s' %self.account
self.cursor.execute(sql)
id = str(self.cursor.fetchone())
id = id[1:-2]
# print(id[1:-2])
# 將顧客表customers 的id填到訂單表里面
sql = 'insert into orders( customer_id) values ("%s")'%id
self.cursor.execute(sql)
# 獲取訂單表自己的id
sql = 'select id from orders where customer_id = "%s"' %id
self.cursor.execute(sql)
orders_id = str(self.cursor.fetchone())
orders_id = orders_id[1:-2]
# print(orders_id)
# 往訂單詳情表里面添加數(shù)據(jù)
num = int(input('請(qǐng)輸入你要購買的個(gè)數(shù)'))
shop = input('請(qǐng)輸入你要購買的商品')
# 查找商品表的ID
sql = 'select id from goods where name = "%s"'%(shop)
# print(sql)
self.cursor.execute(sql)
goods_id = str(self.cursor.fetchone())
goods_id = goods_id[1:-2]
# print(type(goods_id))
# print(goods_id)
# 查看商品表里面有沒有這個(gè)商品，如果有返回他的id，如果沒有就會(huì)返回o
if goods_id == 'o':
print('請(qǐng)您檢查您要購買的商品，該商店沒有此商品')
# print("這里是商品的id",goods_id)
# print(goods_id)
# print(goods_id[1:-2])
else:
# 往訂單詳情表里面添加數(shù)據(jù)
sql = 'insert into order_detail values ( null ,"%s", "%s", %d)' %(orders_id,goods_id,num)
# print("------>",sql,"<-----")
# print(type(orders_id))
# print(type(goods_id))
self.cursor.execute(sql)
self.conn.commit()
return
def add_brand(self):
brand = input('請(qǐng)輸入你要增加的品牌')
sql = "insert into goods_brands(name) values ('%s')" %brand
print(sql)
self.cursor.execute(sql)
self.conn.commit()
def minus_brand(self):
brand = input('請(qǐng)輸入你要?jiǎng)h除的品牌')
sql = "delete from goods_brands where name = '%s' " %brand
print(sql)
self.cursor.execute(sql)
self.conn.commit()
def update_brand(self):
brand = input('請(qǐng)輸入你要更改的品牌')
new_brand = input('請(qǐng)輸入你更改后的品牌')
sql = 'update goods_brands set name = "%s" where name = "%s" '%(brand,new_brand)
print(sql)
self.cursor.execute(sql)
self.conn.commit()
@staticmethod
def print_menu():
print('----京東----')
print('1：所有的商品')
print('2：所有的商品分類')
print('3：所有的商品品牌分類')
print('4:插入商品分類的名稱')
print('5:刪除商品分類的名稱')
print('6:更改商品分類的名稱')
print('7:根據(jù)名字查詢商品')
print('8:購買商品')
# input收到的數(shù)據(jù)是字符串
print('9:添加品牌')
print('10:刪除品牌')
print('11:更新品牌')
print('12:退出')
num = input('請(qǐng)輸入功能相對(duì)應(yīng)的序號(hào):\n')
return num
def run(self):
self.login()
while True:
if self.tc :
break
else:
num = self.print_menu()
if num == '1':
self.show_all_items()
elif num == '2':
# 查詢分類
self.show_cates()
elif num == '3':
# 查詢品牌分類
self.show_crands()
elif num == '4':
self.add_cates()
elif num == '5':
self.del_cate()
elif num == '6':
self.update_cates()
elif num == '7':
self.find_shop()
elif num == '8':
self.buy_shop()
elif num == '9':
self.add_brand()
elif num == '10':
self.minus_brand()
elif num == '11':
self.update_brand()
elif num == '12':
# print(self.tc)
break
else:
print('輸入有誤，請(qǐng)重新輸入……')
def main():
jd = JD()
jd.run()
if __name__ == '__main__':
main()
