打算用python做一个上位机程序,其中涉及到数据库的增删查找操作,顺便学习一下,并踩了不少坑

1
import pymysql

预处理

1
2
3
4
5
6
7
conn = pymysql.connect(
       host='127.0.0.1',        #数据库地址
       user='root',             #用户名称 
       password='123456',       #密码
       database='2022project',  #数据库名称
       # charset='utf8mb4'
   )

查找

1
2
3
4
5
6
7
8
sql = "SELECT username FROM user WHERE phoneNum='%s'" % data         
															 #数据库语句,data为变量
cursor.execute(sql)											 #执行sql语句
results = cursor.fetchall()									 #获取结果,通常为list
															 #fetchall为所有,为list
															 #fetchone获取单个数据 
data = str(results[0][0])                                    #获取result的[0][0]
print(data)

insert

1
2
3
4
5
6
7
8
9
sql = "INSERT INTO package_inf " \
"(barCode,phoneNum,place,cardId,sig) " \
"VALUES (%s,%s,%s,%s,%s)"
par = [(barCodeData, phoneData, pp, cardIdData, si)]    #加变量
cursor.executemany(sql, par) 							# 插入多个数值时,用executemany
cursor.connection.commit()  							# 插入数据得加这行,血泪教训
sql = "UPDATE place_inf SET useif = 1 WHERE place = %s " % pp
cursor.execute(sql)
cursor.connection.commit()  							# 插入数据得加这行

update

1
2
3
sql = "UPDATE place_inf SET useif = 1 WHERE place = %s " % pp
cursor.execute(sql)
cursor.connection.commit()  							# 插入数据得加这行

delete

1
2
3
sql = "DELETE FROM package_inf WHERE place = %s" % data
cursor.execute(sql)
cursor.connection.commit()