pymysql的使用(笔记)
首先,连接数据库:
import pymysql
def conn_db(): # 连接数据库函数
conn = pymysql.connect(
host='localhost',
user='root',
passwd='root',
db='swjtu_z16',
charset='utf8mb4')
cur = conn.cursor()
return conn,cur
def exe_update(cur, sql): # 更新语句,可执行update,insert语句
sta = cur.execute(sql)
return sta
# def exe_delete(cur, ids): # 删除语句,可批量删除
# for eachID in ids.split(' '):
# sta = cur.execute('delete from cms where id =%d' % int(eachID))
# return sta
def exe_query(cur, sql): # 查询语句
cur.execute(sql)
return cur
def exe_commit(cur):
cur.connection.commit() # 执行commit操作,插入语句才能生效
def conn_close(conn, cur): # 关闭所有连接
cur.close()
conn.close()
查询
# 调用连接数据库的函数
conn, cur = condb.conn_db()
sql_query = "select * from bridge_preprocessing_params"
params = defaultdict(str)
try:
sta = condb.exe_query(cur,sql_query)
results = sta.fetchall()
for row in results:
name = row[0]
param1 = row[1]
param2 = row[2]
param3 = row[3]
params[name] = (param1,param2,param3)
condb.exe_commit(cur)
except Exception as e:
print(e)
finally:
condb.conn_close(conn, cur)
另一种写法(无封装):(参考)
import pymysql #导入 pymysql
#打开数据库连接
db= pymysql.connect(host="localhost",user="root",
password="123456",db="test",port=3307)
# 使用cursor()方法获取操作游标
cur = db.cursor()
#1.查询操作
# 编写sql 查询语句 user 对应我的表名
sql = "select * from user"
try:
cur.execute(sql) #执行sql语句
results = cur.fetchall() #获取查询的所有记录
print("id","name","password")
#遍历结果
for row in results :
id = row[0]
name = row[1]
password = row[2]
print(id,name,password)
except Exception as e:
raise e
finally:
db.close() #关闭连接
插入(参考)
import pymysql
#2.插入操作
db= pymysql.connect(host="localhost",user="root",
password="123456",db="test",port=3307)
# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_insert ="insert into user(id,username,password) values(4,'liu','1234')"
try:
cur.execute(sql_insert)
#提交
db.commit()
except Exception as e:
#错误回滚
db.rollback()
finally:
db.close()
更新
def updateFunctions(denosing,outliers,standard):
sql_update = "update bridge_preprocessing set denosing = '%s',outliers = '%s',standard = '%s' where id = '%s'"
# 调用连接数据库的函数
conn, cur = condb.conn_db()
sta = condb.exe_update(cur, sql_update % (denosing, outliers, standard, "1"))
condb.exe_commit(cur)
condb.conn_close(conn, cur)
return sta
另一种写法(无封装):(参考)
import pymysql
#3.更新操作
db= pymysql.connect(host="localhost",user="root",
password="123456",db="test",port=3307)
# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_update ="update user set username = '%s' where id = %d"
try:
cur.execute(sql_update % ("xiongda",3)) #像sql语句传递参数
#提交
db.commit()
except Exception as e:
#错误回滚
db.rollback()
finally:
db.close()
删除
import pymysql
#4.删除操作
db= pymysql.connect(host="localhost",user="root",
password="123456",db="test",port=3307)
# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_delete ="delete from user where id = %d"
try:
cur.execute(sql_delete % (3)) #像sql语句传递参数
#提交
db.commit()
except Exception as e:
#错误回滚
db.rollback()
finally:
db.close()
【参考】https://blog.csdn.net/qq_37176126/article/details/72824106