前言

1>項目來自網上一個教學視頻，小白博主對其進行了小小的優化，實現了相關密碼加密，下訂單等相關操作。

一：項目準備

1>數據庫準備

這是我為該項目所建的表：

表內數據(因為博主有些私人信息就不公開了，這里給出表的結構：

其中的customer存儲用戶信息(包含姓名、住址、電話、密碼等)。

good_bands存儲商品的品牌。

good_cate存儲商品的種類。

goods存儲商品的具體信息(名稱、價格、是否售空、以及庫存數量)。

order_information存儲訂單的訂單號，商品以及購買數量。

orders存儲顧客的購買時間和昵稱。

2>python相關類庫的準備：

該項目主要使用了python的pymyql(用于連接mysql)、time(登錄時間以及訂單號的生成)、hashlib(密碼的加密)

第三方庫的下載可以使用pip指令也可以在pycharm中直接在python packages點擊搜索進行下載。

pip install hashlib

二：項目主要功能介紹

1>數據庫基本連接

def __init__(self):# 產生連接self.conner = connect(host="localhost", port=3306, user='root', password="liu20020822",database="python_01", charset='utf8')# 獲得Cursor對象self.cursor = self.conner.cursor()num = Falsename = ""passwd = ""def __del__(self):self.cursor.close()self.conner.close()

每次運行時均會對用戶昵稱和密碼進行初始化，其中num用于判斷是否進行了登錄或者注冊等操作。

2>顧客注冊以及登錄

# 進行注冊def get_index_count(self):print("--------登錄賬號--------")self.name = str(input("請輸入你的賬號："))self.passwd = str(input("請輸入你的密碼："))# 利用類庫進行加密self.passwd = self.passwd.strip()md5 = hashlib.md5()md5.update(self.passwd.encode(encoding='utf-8'))self.passwd = md5.hexdigest()sql = ('select * from customer where name="%s" and passwd="%s";' % (self.name, self.passwd))self.cursor.execute(sql)change = str(self.cursor.fetchone())+"1"if change == 'None1':print("登錄失敗、請核對賬號和密碼或點擊7進行注冊")else:# 下面這串代碼無意義本想刪的，但是懶得改了，可以直接用成功替代sql_user_frequency = "select user_frequency from customer where name ='%s';" % self.nameself.cursor.execute(sql_user_frequency)good_number_list = list(self.cursor.fetchall())good_number_tuple = good_number_list[0]sql_goods_update_number = ("update customer set user_frequency = %d where name='%s';"% (good_number_tuple[0] + 1, self.name))self.cursor.execute(sql_goods_update_number)print("賬號登錄成功")self.num = Trueself.conner.commit()# 進行顧客的注冊def get_registered_count(self):print("--------注冊賬號--------")while True:self.name = str(input("請輸入你的昵稱:"))sql_name_search = ('select name from customer where name="%s"' % self.name)self.cursor.execute(sql_name_search)if self.cursor.fetchall():print("此用戶名那已經存在，請重新輸入")else:breakaddress = str(input("請輸入你的家庭地址:"))telephone = str(input("請輸入你的電話:"))self.passwd = str(input("請輸入你的密碼:"))# 密碼同樣需要加密 以免在登錄中進行密碼比對中出錯(可以寫成一個函數的，有點懶)self.passwd = self.passwd.strip()md5 = hashlib.md5()md5.update(self.passwd.encode(encoding='utf-8'))self.passwd = md5.hexdigest()sql = "select table_name from information_schema.tables where table_name ='customer';"if not self.cursor.execute(sql):sql = """create table if not exists Customer(id int unsigned primary key not null auto_increment,name varchar(10) not null default "老王",address varchar(30) not null,telephone varchar(11) not null,passwd varchar(30) not null);"""self.cursor.execute(sql)sql = ('insert into Customer values(0,"%s","%s",%s,"%s");' % (self.name, address, telephone, self.passwd))self.cursor.execute(sql)print("表單注冊成功")else:sql = ('insert into Customer values( 0, "%s", "%s", %s, "%s", 0 );'% (self.name, address, telephone, self.passwd))self.cursor.execute(sql)print("成功注冊")# 將num屬性進行更改self.num = Trueself.conner.commit()

3>商品品牌等展示

# 這些是表中數據的一些查詢，隨手寫寫就好def show_all_items(self):sql = 'select name from goods;'self.execute_sql(sql)def show_all_bands(self):sql = 'select name from good_cate;'self.execute_sql(sql)def show_all_type(self):sql = 'select band from good_bands;'self.execute_sql(sql)

4>下訂單

def get_info_sold(self):print("--------開始下訂單--------")# 下訂單的時間now_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())customer_name = self.name# 訂單號，用時間數據代替，第一個使用年是為了防止重復(寫文章的時候才改的，，)order_number = str(time.strftime('%Y%M%S', time.localtime(time.time()))+ str(time.time()).replace('.', '')[-7:])good = input("請輸入要購買的商品：")number = eval(input("請輸入要購買的數量："))# 首先判斷商品是否存在在goods表中是否存在該商品sql = "select name from goods where name='%s';" % goodif self.cursor.execute(sql):# 查詢表是否存在，當時傻逼了，其實都應該表是提前建好的，省去這個步驟sql = "select table_name from information_schema.tables where table_name ='orders';"if self.cursor.execute(sql):sql_good_number = "select number from goods where name ='%s';" % goodself.cursor.execute(sql_good_number)good_number_list = list(self.cursor.fetchall())good_number_tuple = good_number_list[0]if good_number_tuple[0]-number > 0:sql_goods_update_number = ("update goods set number=%d where name='%s';"% (good_number_tuple[0] - number, good))sql_good_insert = ("insert into Orders values(0,'%s','%s');" % (now_time, customer_name))self.cursor.execute(sql_good_insert)self.cursor.execute(sql_goods_update_number)sql_good_information_insert = ("insert into Order_Information values(0,'%s','%s','%s');"% (order_number, good, number))self.cursor.execute(sql_good_information_insert)print("購買成功")else:print("該商品貨物不足，只有%d件了" % (good_number_tuple[0]))else:# 這里就是建表sql_good_create = """create table if not exists Orders(id int unsigned not null auto_increment primary key,date_time datetime not null,customer varchar(30) not null)"""self.cursor.execute(sql_good_create)sql_good_information_create = """create table if not exists Order_Information(id int unsigned primary key not null auto_increment,order_number varchar(30) not null default "8888888",goods varchar(15) not null,number int unsigned not null default 0);"""self.cursor.execute(sql_good_information_create)sql_good_insert = ("insert into Orders values(0,'%s','%s');" % (now_time, customer_name))self.cursor.execute(sql_good_insert)sql_good_information_insert = ("insert into Order_Information values(0,'%s','%s','%s');"% (order_number, good, number))self.cursor.execute(sql_good_information_insert)print("該表成功創建")self.conner.commit()else:print("商品不存在")

5>主函數調用

def run(self):while True:print("--------京東商城--------\n\n"+"--------1.0 所有的商品名稱\n"+"--------2.0 所有的商品品牌\n"+ "--------3.0 所有的商品屬性\n"+"--------4.0 添加一個商品 ")print("--------5.0 根據名字查詢商品\n"+"--------6.0 登錄賬號\n"+"--------7.0 注冊賬號\n"+"--------8.0 下訂單\n"+"--------0 退出")select = eval(input("請輸入功能對應選項:\n"))if select == 1: # 查詢所有的商品名稱if self.num:self.show_all_items()else:print("請重新登錄")elif select == 2: # 查詢你所有商品品牌if self.num:self.show_all_bands()else:print("請重新登錄")elif select == 3: # 查詢你所有的商品屬性if self.num:self.show_all_type()else:print("請重新登錄")elif select == 4:if self.num:self.get_add_type()else:print("請重新登錄")elif select == 5:if self.num:self.get_info_by_name()else:print("請重新登錄")elif select == 6:self.get_index_count()elif select == 7:self.get_registered_count()elif select == 8:if self.num:self.get_info_sold()else:print("請重新登錄")elif select == 0:breakelse:print("輸入錯誤 請重新輸入 或重新登錄")

我使用了簡單的選擇，字典也可以，有點冗余了。

三：完整源碼附錄：

from pymysql import * import time import hashlibclass Good(object):def __init__(self):# 產生連接self.conner = connect(host="localhost", port=3306, user='root', password="liu20020822",database="python_01", charset='utf8')# 獲得Cursor對象self.cursor = self.conner.cursor()num = Falsename = ""passwd = ""def __del__(self):self.cursor.close()self.conner.close()def execute_sql(self, sql):self.cursor.execute(sql)for temp in self.cursor.fetchall():print(temp)def show_all_items(self):sql = 'select name from goods;'self.execute_sql(sql)def show_all_bands(self):sql = 'select name from good_cate;'self.execute_sql(sql)def show_all_type(self):sql = 'select band from good_bands;'self.execute_sql(sql)def get_add_type(self):item = input("輸入待加入的商品名稱：")cate_id = eval(input("請輸入品牌id：（數字）"))brand_id = input("請輸入品牌屬性id：")price = eval(input("請輸入價格："))number = eval(input("請輸入代售數量："))sql = ("""insert into goods values(0,"%s","%d","%s","%d",default,default,"%d")"""% (item, cate_id, brand_id, price, number))self.cursor.execute(sql)self.conner.commit()def get_info_by_name(self):item = input("輸入待搜查的商品品牌名稱：")sql = "select * from goods where name=%s;"self.cursor.execute(sql, [item]) # 執行sql語句、同時防止sql語句注入print(self.cursor.fetchall())def get_index_count(self):print("--------登錄賬號--------")self.name = str(input("請輸入你的賬號："))self.passwd = str(input("請輸入你的密碼："))self.passwd = self.passwd.strip()md5 = hashlib.md5()md5.update(self.passwd.encode(encoding='utf-8'))self.passwd = md5.hexdigest()sql = ('select * from customer where name="%s" and passwd="%s";' % (self.name, self.passwd))self.cursor.execute(sql)change = str(self.cursor.fetchone())+"1"if change == 'None1':print("登錄失敗、請核對賬號和密碼或點擊7進行注冊")else:sql_user_frequency = "select user_frequency from customer where name ='%s';" % self.nameself.cursor.execute(sql_user_frequency)good_number_list = list(self.cursor.fetchall())good_number_tuple = good_number_list[0]sql_goods_update_number = ("update customer set user_frequency = %d where name='%s';"% (good_number_tuple[0] + 1, self.name))self.cursor.execute(sql_goods_update_number)print("賬號登錄成功")self.num = Trueself.conner.commit()def get_registered_count(self):# 如果不存在創建一個新表、填充顧客的id（不進行手動輸入自動進行填充） 呢稱 住址 電話 密碼# 如果存在則只有填充不新建print("--------注冊賬號--------")while True:self.name = str(input("請輸入你的昵稱:"))sql_name_search = ('select name from customer where name="%s"' % self.name)self.cursor.execute(sql_name_search)if self.cursor.fetchall():print("此用戶名那已經存在，請重新輸入")else:breakaddress = str(input("請輸入你的家庭地址:"))telephone = str(input("請輸入你的電話:"))self.passwd = str(input("請輸入你的密碼:"))self.passwd = self.passwd.strip()md5 = hashlib.md5()md5.update(self.passwd.encode(encoding='utf-8'))self.passwd = md5.hexdigest()sql = "select table_name from information_schema.tables where table_name ='customer';"if not self.cursor.execute(sql):sql = """create table if not exists Customer(id int unsigned primary key not null auto_increment,name varchar(10) not null default "老王",address varchar(30) not null,telephone varchar(11) not null,passwd varchar(30) not null);"""self.cursor.execute(sql)sql = ('insert into Customer values(0,"%s","%s",%s,"%s");' % (self.name, address, telephone, self.passwd))self.cursor.execute(sql)print("表單注冊成功")else:sql = ('insert into Customer values( 0, "%s", "%s", %s, "%s", 0 );'% (self.name, address, telephone, self.passwd))self.cursor.execute(sql)print("成功注冊")self.num = Trueself.conner.commit()def get_info_sold(self):print("--------開始下訂單--------")now_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())customer_name = self.nameorder_number = str(time.strftime('%H%M%S', time.localtime(time.time()))+ str(time.time()).replace('.', '')[-7:])# 首次創建訂單表和訂單詳情表、兩表存在關聯同時存在或者消失 id 訂單號 時間 顧客 均自動生成、選填數量和商品good = input("請輸入要購買的商品：")number = eval(input("請輸入要購買的數量："))# 首先判斷商品是否存在在goods表中是否存在該商品sql = "select name from goods where name='%s';" % goodif self.cursor.execute(sql):sql = "select table_name from information_schema.tables where table_name ='orders';"if self.cursor.execute(sql):sql_good_number = "select number from goods where name ='%s';" % goodself.cursor.execute(sql_good_number)good_number_list = list(self.cursor.fetchall())good_number_tuple = good_number_list[0]if good_number_tuple[0]-number > 0:sql_goods_update_number = ("update goods set number=%d where name='%s';"% (good_number_tuple[0] - number, good))sql_good_insert = ("insert into Orders values(0,'%s','%s');" % (now_time, customer_name))self.cursor.execute(sql_good_insert)self.cursor.execute(sql_goods_update_number)sql_good_information_insert = ("insert into Order_Information values(0,'%s','%s','%s');"% (order_number, good, number))self.cursor.execute(sql_good_information_insert)print("購買成功")else:print("該商品貨物不足，只有%d件了" % (good_number_tuple[0]))else:sql_good_create = """create table if not exists Orders(id int unsigned not null auto_increment primary key,date_time datetime not null,customer varchar(30) not null)"""self.cursor.execute(sql_good_create)sql_good_information_create = """create table if not exists Order_Information(id int unsigned primary key not null auto_increment,order_number varchar(30) not null default "8888888",goods varchar(15) not null,number int unsigned not null default 0);"""self.cursor.execute(sql_good_information_create)sql_good_insert = ("insert into Orders values(0,'%s','%s');" % (now_time, customer_name))self.cursor.execute(sql_good_insert)sql_good_information_insert = ("insert into Order_Information values(0,'%s','%s','%s');"% (order_number, good, number))self.cursor.execute(sql_good_information_insert)print("該表成功創建")self.conner.commit()else:print("商品不存在")def run(self):while True:print("--------京東商城--------\n\n"+"--------1.0 所有的商品名稱\n"+"--------2.0 所有的商品品牌\n"+ "--------3.0 所有的商品屬性\n"+"--------4.0 添加一個商品 ")print("--------5.0 根據名字查詢商品\n"+"--------6.0 登錄賬號\n"+"--------7.0 注冊賬號\n"+"--------8.0 下訂單\n"+"--------0 退出")select = eval(input("請輸入功能對應選項:\n"))if select == 1: # 查詢所有的商品名稱if self.num:self.show_all_items()else:print("請重新登錄")elif select == 2: # 查詢你所有商品品牌if self.num:self.show_all_bands()else:print("請重新登錄")elif select == 3: # 查詢你所有的商品屬性if self.num:self.show_all_type()else:print("請重新登錄")elif select == 4:if self.num:self.get_add_type()else:print("請重新登錄")elif select == 5:if self.num:self.get_info_by_name()else:print("請重新登錄")elif select == 6:self.get_index_count()elif select == 7:self.get_registered_count()elif select == 8:if self.num:self.get_info_sold()else:print("請重新登錄")elif select == 0:breakelse:print("輸入錯誤 請重新輸入 或重新登錄")def main():client = Good()client.run()if __name__ == '__main__':main()

結束語：博主的第一篇，以后還會繼續寫作，如果上述項目有不同見解的，歡迎大家在評論區或者私聊我。

總結

以上是生活随笔為你收集整理的京东商城(mysql+python)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。