Python操作MySQL数据库
操作步骤
建立数据库连接
import pymysql db = pymysql.connect(host="127.0.0.1", user="root", password="123456", db="student_mis", port=3306) cur = db.cursor()
执行数据库操作
execute(self, query, args)
# 使用sql语句,这里要接收的参数都用%s占位符 sql = "insert into xxx values(%s,%s,%s,%s,%s)" # param应该为tuple或者list param = (1, 2, 3, 4, 5) # 执行,如果成功,result的值为1 result=cur.execute(sql,param)
executemany(self, query, args)
# 需要批量的插入数据 sql = "insert into cdinfo values(0,%s,%s,%s)" # 每个值的集合为一个tuple,整个参数集组成一个tuple,或者list param = ((1, 2, 3), (4, 6, 5)) result = cur.executemany(sql, param)
fetchone(self)
sql = "select * from xxx where id='1'" cur.execute(sql) #查询一条结果 result=cur.fetchone()
fetchall(self)
sql = "select * from xxx" cur.execute(sql) #查询所有结果 每条结果都是一个tuple类型的数据 result=cur.fetchall() for row in result: data = {} result['id'] = row[0] data['xxx'] = row[1] data['xxx'] = row[2]
fetchmany(self, size=None)
sql = "select * from xxx where id='1'" cur.execute(sql) # 查询size条结果 result = cur.fetchmany(size=4)
关闭连接
cur.close()
db.close()
注意点
在执行完插入或删除或修改操作后,需要调用如下方法提交事务,这样才会保存数据
db.commit()