本文详细介绍了如何使用Python的pymysql库连接MySQL数据库，包括安装库、连接、游标操作、SQL执行（包括单条和批量插入、查询、更新、删除）、事务管理和回滚。涵盖了创建数据库、表和执行各种CRUD操作的关键步骤。
Python访问数据库
一、Python操作mysql数据库的相关库安装
需要的库为 mymysql
安装命令：pip install pymysql
我们查看当前已经安装的包可以使用命令:pip list
二、Python连接mysql数据库
使用的函数为：pymysql.connect()
连接成功后，这个函数会返回一个连接对象
# coding=utf-8
import pymysql
# 连接mysql数据库
conn = pymysql.connect(host='localhost',user='root',password='root',database='test',charset='utf8')
print(conn)
print(type(conn))
三、获取连接下的游标
通过上面的实例我们可以拿到数据库连接，但是只是拿 到连接还是不够的，光有连接是不行的，还要拿到对应的游标，拿到了这个游标才能进行后续的操作（读取数据、添加数据），获取到连接下的游标如下所示
# coding=utf-8
import pymysql
# 连接mysql数据库
conn = pymysql.connect(host='localhost',user='root',password='root',database='test',charset='utf8')
print(conn)
print(type(conn))
# 获取连接下的游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))
四、执行sql语句
执行sql语句有两个函数
execute(query,args=None)
用来执行单条sql语句，执行成功后会返回受影响的行数
其参数说明：
query:要执行的sql语句，字符串类型
args:可选的序列或映射，用于query的参数值。如果args为序列，query中必须使用%s做占位符，如果args为映射，query中必须使用%(key)s做占位符
executemany(query,args=None)
用来批量执行sql语句，如：批量插入数据，执行成功后返回受影响的行数
其参数说明：
query:要执行的sql语句，字符串类型
args:嵌套的序列和映射，用于query的参数值
注意点：
1、数据库性能瓶颈很大一部分在于网络IO和磁盘IO，把多个sql语句放在一起执行，只占用一次IO，可以有效提升数据库性能，推荐使用executemany
2、executemany方法一次性批量执行sql语句也有负作用，一次传入过多server端可能导致server端的buffer溢出，这样会导致一性处理麻烦，因而在使用时要注意合理，分批次地使用executemany方法
五、创建数据库
创建一个数据库pythondb
六、创建数据表
在pythondb下创建数据表user
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host = "localhost",user='root',password='root',database='pythondb',charset='utf8')
# 获取游标
cursor = conn.cursor()
# 在pythondb库下创建表user
cursor.execute("drop table if exists user")
sql = """
create table if not exists user(
id int(11) not null auto_increment,
name varchar(255) not null,
age int(3) not null,
primary key(id)
)
"""
cursor.execute(sql) # 执行sql语句
cursor.close() # 关闭游标
conn.close() # 关闭数据库连接
print("创建数据库表成功")
七、向数据表中插入单条数据
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host = "localhost",user='root',password='root')
# 选择数据库
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
# 向user表中插入一条语句，方法一：直接使用一条insert sql语句
sql = r"insert into user(name,age) values('张三',19)"
rows = cur.execute(sql)
print("insert语句执行完成，受影响行的记录行数:{}".format(rows))
# 向user表中插入一条语句，方法二：在sql语句中指定占位符，通过字符串传入值
sql = r"insert into user(name,age) values(%s,%s)"
rows = cur.execute(sql,('李四',20))
print("insert语句执行完成，受影响行的记录行数:{}".format(rows))
cur.close() # 关闭游标
conn.commit() # 提交数据
conn.close() # 关闭数据库连接
八、向数据表中批量插入多条语句
# coding=utf-8
import pymysql
# 打开数据库连接，不指定数据库
conn = pymysql.connect(host='localhost',user='root',password='root')
# 选择数据库
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
# 批量插入数据
sql = r'insert into user(name,age) values(%s,%s)'
rows = cur.executemany(sql,[('韩梅梅',18),('李雷',20)])
print("批量插入数据影响数据行数：{}".format(rows))
cur.close()
conn.commit()
conn.close()
关于executemany(sql,args)函数，返回的结果是影响的行数，args参数是一个包含多个元组的列表，每一个元组对应对一条sql语句，%s是不需加上引号的，加上后可能会导致数据类型错误。
九、数据查询
上面我们执行的sql使用execute,executemany返回的结果是指的影响的记录数，并不能拿到查询的内容，cursor对象提供了三种方法来获取数据，fetchone，fetchmany，fetchall，每一个方法都会导致游标的移动，所以在使用时要注意游标的移动。
fetchone():获取游标所在处的一行数据，返回一个元组，没有数据则返回None
fetchmany(size):接受size行返回结果行。如果size大于返回的结果行数量，则返回cursor.arraysize条数据
fetchall():接收全部的返回结果行
# coding=utf-8
import pymysql
# 打开数据库边接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
rows = cur.execute('select * from user where age>19')
print("-----查询记数数：{}------".format(rows))
while True:
res = cur.fetchone() # 每次執行游標向后移动一个位置
if res is None:
# 已经没有数据了
break
print(res)
cur.close()
conn.commit()
conn.close()
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
cur.execute("select * from user;")
#取两条数据
resTuple = cur.fetchmany(2)
if len(resTuple) == 0:
print("未查询到记录！")
else:
for res in resTuple:
print(res)
cur.close()
conn.commit()
conn.close()
execute()函数结果中取游标所在处size条数据，并以元组的形式返回，元组的每一个元素都是一个由一行数据组成的元组，如果size大于有效的结果行数，会返回cursor.arraysize条数据，如果游标所在处没有数据则会返回空元组，查询几条记录，则游标会向后移几个位置。
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
cur = conn.cursor()
cur.execute("select * from user;")
resTuple = cur.fetchall()
print("一共获取到的记录数：{}".format(len(resTuple)))
cur.close()
conn.commit()
conn.close()
十、数据更新
更新一条记录
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
# 更新一条数据
rows = cur.execute("update user set age = 19 where name = '韩梅梅'")
print("影响的记录数：",rows)
# 查询一条数据
cur.execute('select * from user where name = "韩梅梅"')
res = cur.fetchone()
if res is None:
print("未查询到记录！")
else:
print(res)
cur.close()
conn.commit()
conn.close()
更新多条记录
# coding=utf-8
import pymysql
# 打开数据库连接并选择数据库
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
cur.execute("select * from user where name in ('韩梅梅','李雷')")
print("更新前查询数据：")
resTuple = cur.fetchall()
if len(resTuple) == 0:
print("未查询到任何记录!")
else:
for res in resTuple:
print(res)
# 更新数据
sql = "update user set age=%s where name=%s"
rows = cur.executemany(sql,[(18,res[1])])
print("{}已更".format(res[1]))
# 更新数据完成后再查询两条记录
cur.execute("select * from user where name in ('韩梅梅','李雷')")
print("更新后的数据：")
resTuple = cur.fetchall()
if len(resTuple) == 0:
print("未查询到任何记录!")
else:
for res in resTuple:
print(res)
cur.close()
conn.commit()
conn.close()
十一、删除数据
删除单条数据
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
cur = conn.cursor()
# 查询所有记录
cur.execute("select * from user")
print("删除前的数据：")
resTuple = cur.fetchall()
if len(resTuple) == 0:
print("删除前没有查询到任何数据！")
else:
for res in resTuple:
print(res)
# 删除一条记录
cur.execute("delete from user where id = 1")
# 查询删除后的所有记录
cur.execute("select * from user")
print("删除后的数据：")
resTuple = cur.fetchall()
if len(resTuple) == 0:
print("删除后没有查询到任何数据！")
else:
for res in resTuple:
print(res)
cur.close()
conn.commit()
conn.close()
删除多条记录
# coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
# 获取游标
cur = conn.cursor()
# 删除前查询所有记录
cur.execute("select * from user")
print("删除前的数据：")
resTuple = cur.fetchall()
if len(resTuple) == 0:
print("删除前没有查询到任何记录！")
else:
for res in resTuple:
print(res)
# 删除两条记录
sql = 'delete from user where id=%s'
cur.executemany(sql,[(1),(2)])
print('删除后的数据为：')
cur.execute("select * from user")
print("删除后的数据：")
resTuple = cur.fetchall()
if len(resTuple) == 0:
print("删除后没有查询到任何记录！")
else:
for res in resTuple:
print(res)
cur.close()
conn.commit()
conn.close()
十二、事务回滚操作
# coding=utf-8
import pymysql
import sys
# 打开数据库连接
conn = pymysql.connect(host='localhost',user='root',password='root')
conn.select_db('pythondb')
# 游标
cur = conn.cursor()
# 修改前的数据
cur.execute('select name from user where id = 5')
print("修改前id为5的名称为")
res = cur.fetchone()
if res is None:
print("没有找到id为5的记录")
sys.exit(0) # 退出程序
else:
print(res[0])
# 更新数据
cur.execute('update user set name = "二炮" where id = 5')
# 修改后的数据
print('修改后id为5的名称为')
cur.execute("select name from user where id = 5")
print(cur.fetchone()[0])
# 进行事务回滚操作
conn.rollback()
# 修改后的数据
print('事务回滚后id为5的名称为')
cur.execute("select name from user where id = 5")
print(cur.fetchone()[0])
cur.close()
conn.commit()
conn.close()
在这个程序中，我们import了sys模块，在我们没有找到要更新的数据时调用sys.exit(0) 让程序正常退出不需要执行后面的操作。
扫一扫
562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
