1家凯、引入模塊
在py文件中引入pymysql模塊
from pymysql import connect
2缓醋、Connection 對(duì)象
用于建立與數(shù)據(jù)庫的連接
創(chuàng)建對(duì)象：調(diào)用connect()方法
conn=connect(參數(shù)列表)
參數(shù)host：連接的mysql主機(jī)，如果本機(jī)是'localhost'
參數(shù)port：連接的mysql主機(jī)的端口绊诲，默認(rèn)是3306
參數(shù)database：數(shù)據(jù)庫的名稱sephore
參數(shù)user：連接的用戶名root
參數(shù)password：連接的密碼
對(duì)象的方法
close()關(guān)閉連接
commit()提交
cursor()返回Cursor對(duì)象送粱，用于執(zhí)行sql語句并獲得結(jié)果
3、Cursor對(duì)象
用于執(zhí)行sql語句掂之，使用頻度最高的語句為select抗俄、insert、update世舰、delete
獲取Cursor對(duì)象：調(diào)用Connection對(duì)象的cursor()方法
cs1=conn.cursor()
對(duì)象的方法
1.????close()關(guān)閉
2.????execute(operation [, parameters ])執(zhí)行語句动雹，返回受影響的行數(shù)，主要用于執(zhí)行insert跟压、update胰蝠、delete語句，也可以執(zhí)行create、alter茸塞、drop等語句
3.????fetchone()執(zhí)行查詢語句時(shí)躲庄，獲取查詢結(jié)果集的第一個(gè)行數(shù)據(jù)，返回一個(gè)元組
4.????fetchall()執(zhí)行查詢時(shí)翔横，獲取結(jié)果集的所有行读跷，一行構(gòu)成一個(gè)元組，再將這些元組裝入一個(gè)元組返回
對(duì)象的屬性
rowcount只讀屬性禾唁，表示最近一次execute()執(zhí)行后受影響的行數(shù)
connection獲得當(dāng)前連接對(duì)象
數(shù)據(jù)查詢
from pymysql import connect
class SEPHORE(object):
def __init__(self):
# 創(chuàng)建Connection連接
self.conn = connect(host='localhost',port=3306,database='sephore',user='root',password='123456',charset='utf8')
# 獲得Cursor對(duì)象
self.cursor = self.conn.cursor()
def __del__(self):
# 關(guān)閉Cursor對(duì)象
self.cursor.close()
self.conn.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 * from goods;"
self.execute_sql(sql)
def show_cates(self):
sql = "select name from goods_cates;"
self.execute_sql(sql)
def show_brands(self):
sql = "select name from goods_brands;"
self.execute_sql(sql)
@staticmethod
def ptint_menu():
print('-----絲芙蘭-----')
print("1:所有的商品")
print("2:所有的商品分類")
print("3:所有的商品品牌分類")
return input('請(qǐng)輸入功能對(duì)應(yīng)的序號(hào)：')
def run(self):
while True:
num = self.ptint_menu()
if num == '1':
# 查詢所有商品
self.show_all_items()
elif num == '2':
# 查詢分類
self.show_cates()
elif num == '3':
# 查詢品牌分類
self.show_brands()
else:
print('輸入有誤效览，請(qǐng)重新輸入....')
def main():
#1,創(chuàng)建一個(gè)絲芙蘭商城對(duì)象
sephore = SEPHORE()
# 2,調(diào)用這個(gè)對(duì)象run 方法，讓其運(yùn)行
sephore.run()
if __name__ == '__main__':
main()
添加一個(gè)商品分類
from pymysql import connect
class SEPHORE(object):
def __init__(self):
# 創(chuàng)建Connection連接
self.conn = connect(host='localhost',port=3306,database='sephore',user='root',password='123456',charset='utf8')
# 獲得Cursor對(duì)象
self.cursor = self.conn.cursor()
def __del__(self):
# 關(guān)閉Cursor對(duì)象
self.cursor.close()
self.conn.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 * from goods;"
self.execute_sql(sql)
def show_cates(self):
sql = "select * from goods_cates;"
self.execute_sql(sql)
def show_brands(self):
sql = "select * 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()
@staticmethod
def ptint_menu():
print('-----絲芙蘭-----')
print("1:所有的商品")
print("2:所有的商品分類")
print("3:所有的商品品牌分類")
print("4:添加商品分類")
return input('請(qǐng)輸入功能對(duì)應(yīng)的序號(hào)：')
def run(self):
while True:
num = self.ptint_menu()
if num == '1':
# 查詢所有商品
self.show_all_items()
elif num == '2':
# 查詢分類
self.show_cates()
elif num == '3':
# 查詢品牌分類
self.show_brands()
elif num == '4':
# 添加商品分類
self.add_cates()
else:
print('輸入有誤荡短，請(qǐng)重新輸入....')
def main():
#1,創(chuàng)建一個(gè)絲芙蘭商城對(duì)象
sephore = SEPHORE()
# 2,調(diào)用這個(gè)對(duì)象run 方法丐枉，讓其運(yùn)行
sephore.run()
if __name__ == '__main__':
main()
SQL注入（sephore增刪改查）
from pymysql import connect
class SEPHORE(object):
def __init__(self):
self.is_login = False
# 創(chuàng)建Connection連接
self.conn = connect(host='localhost',port=3306,database='sephore',user='root',password='123456',charset='utf8')
# 獲得Cursor對(duì)象
self.cursor = self.conn.cursor()
def __del__(self):
# 關(guān)閉Cursor對(duì)象
self.cursor.close()
self.conn.close()
def execute_sql(self,sql):
self.cursor.execute(sql)
for temp in self.cursor.fetchall():
print(temp)
def add_customers(self):
# item_id = input('請(qǐng)輸入id')
item_name = input('請(qǐng)輸入姓名')
item_tel = input("請(qǐng)輸入手機(jī)號(hào)")
item_passwd = input('請(qǐng)輸入密碼')
item_address = input('請(qǐng)輸入收貨地址')
sql = "insert into customers values(0,%s,%s,%s,password(%s))"
# sql = "insert into customers where id=%d,name=%s,tel=%d,passwd=%d,address=%s"
self.cursor.execute(sql,[item_name,item_tel,item_passwd,item_address])
self.conn.commit()
print("注冊(cè)成功了")
def login_customers(self):
login_name = input('請(qǐng)輸入name')
login_passwd = input('請(qǐng)輸入密碼')
sql = "select id from customers where name=%s and passwd=password(%d)"
self.cursor.execute(sql,[login_name,login_passwd])
self.uid = self.cursor.fetchone()
if self.uid:
self.uid = self.uid[0]
print(self.uid)
self.is_login = True
return self.is_login
# print(self.cursor.fetchall())
def show_all_items(self):
"""顯示所有商品"""
sql = "select * from goods;"
self.execute_sql(sql)
def show_cates(self):
sql = "select * from goods_cates;"
self.execute_sql(sql)
def show_brands(self):
sql = "select * 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? delete_cates(self):
id = 0
while True:
try:
id = int(input("請(qǐng)輸入要?jiǎng)h除的商品分類的id"))
except Exception as ret:
print("請(qǐng)輸入正確的id編號(hào)")
else:
break
isdel = input("您確認(rèn)要?jiǎng)h除編號(hào)為%s的商品分類？按y確認(rèn)掘托，其他鍵返回主菜單")
if isdel == 'y':
sql = "update goods_cates set is_show=0 where id=%s;"
self.cursor.execute(sql,[id])
self.conn.commit()
pass
def update_cates(self):
id = 0
while True:
try:
id = int(input("請(qǐng)輸入要修改的商品分類的id："))
except Exception as ret:
print("請(qǐng)輸入正確的id編號(hào)")
else:
break
item_name = input("請(qǐng)輸入要新的商品分類的名稱")
sql = "update name from goods_cates set name=%s where id=%s"
self.cursor.execute(sql,[item_name,id])
self.conn.commit()
def get_info_by_name(self):
find_name = input('請(qǐng)輸入要查詢的商品的名字')
sql = "select * from goods where name=%s"
self.cursor.execute(sql,[find_name]) #防止SQL注入
print(self.cursor.fetchall())
def create_order(self):
pid = input("請(qǐng)輸入商品的編號(hào)")
num = input("請(qǐng)輸入商品的數(shù)量")
sql = "select * from goods where id=%s and is_show=1 and is_saleoff=1;"
result = self.cursor.execute(sql,[pid])
if result:
sql = "insert into orders values(0,now(),%s);"
self.cursor.execute(sql,[self.uid])
# print("cursor.lastrowid-->",self.cursor.lastrowid)
sql = "insert into order_detail values(0,%s,%s,%s);"
self.cursor.execute(sql,[self.cursor.lastrowid,pid,num])
self.conn.commit()
print('下單成功')
else:
print("您要購(gòu)買的商品已下架瘦锹！")
@staticmethod
def ptint_customers():
print('-----絲芙蘭-----')
print('1:注冊(cè)')
print('2:登錄')
print("3:退出")
return input('請(qǐng)輸入功能對(duì)應(yīng)的序號(hào)：')
@staticmethod
def ptint_menu():
print('-----絲芙蘭-----')
print("1:所有的商品")
print("2:所有的商品分類")
print("3:所有的商品品牌分類")
print("4:添加商品分類")
print("5:刪除商品分類")
print("6:修改商品分類")
print("7:根據(jù)名字查詢商品")
print("8下單")
return input('請(qǐng)輸入功能對(duì)應(yīng)的序號(hào)：')
def run(self):
while True:
num = self.ptint_customers()
if num == '1':
self.add_customers()
elif num == '2':
if self.login_customers():
print("登錄成功")
break
else:
print("用戶名或密碼有誤，請(qǐng)重新輸入")
elif num == "3":
breakx
else:
print('輸入有誤闪盔，請(qǐng)重新輸入....')
# def run(self):
if self.is_login:
while True:
num = self.ptint_menu()
if num == '1':
# 查詢所有商品
self.show_all_items()
elif num == '2':
# 查詢分類
self.show_cates()
elif num == '3':
# 查詢品牌分類
self.show_brands()
elif num == '4':
# 添加商品分類
self.add_cates()
elif num == '5':
# 刪除商品分類
self.delete_cates()
elif num == '6':
# 修改商品分類
self.update_cates()
elif num == '7':
# 根據(jù)名字查詢商品
self.get_info_by_name()
else:
print('輸入有誤弯院，請(qǐng)重新輸入....')
def main():
#1,創(chuàng)建一個(gè)絲芙蘭商城對(duì)象
sephore = SEPHORE()
# 2,調(diào)用這個(gè)對(duì)象run 方法，讓其運(yùn)行
sephore.run()
if __name__ == '__main__':
main()
