pymysql
PyMySql 是 python 操作 MYSQL 数据库最常用的库。对于 MYSQL 的安装使用不在这里记录,只学习 python 部分。
一、安装
直接使用 pip
进行安装
pip install pymysql
二、连接到数据库
导入 pymysql 包后,使用 pymsql.connect()
函数创建数据库对象。pymsql.connect()
的常用参数有:
参数 | 说明 |
---|---|
host=None | 数据库连接地址 |
user=None | 数据库用户名 |
password=‘’ | 数据库用户密码 |
database=None | 要连接的数据库名称 |
db=None | 等同于 database |
port=3306 | 端口号,默认为3306 |
charset=‘’ | 要连接的数据库的字符编码(可以在终端登陆mysql后使用 \s 查看) |
connect_timeout=10 | 连接数据库的超时时间,默认为10 |
autocommit=True | 自动提交事务 |
cursorclass=pymysql.cursors.DictCursor | 设置返回值为字典,默认返回元组,多个数据则存放在列表中 |
数据库对象创建示例:
import pymysql
db = pymysql.connect(
host="localhost",
port=3306,
user='root', #在这里输入用户名
password='888888', #在这里输入密码
charset='utf8mb4'
) #连接数据库
三、数据库对象的操作
一般使用数据库对象的相应方法进行游标操作、提交事务、回滚事务和关闭数据库连接。
方法名 | 说明 |
---|---|
cursor() | 获取游标对象,操作数据库,如执行DML操作,调用存储过程等 |
commit() | 提交事务 |
rollback() | 回滚事务 |
close() | 关闭数据库的连接 |
cursor = db.cursor() # 创建游标对象
db.commit() # 手动提交事务
db.rollback() # 进行回滚操作
db.close() # 关闭数据库连接
pymysql通过游标对象进行数据库操作,需注意的是pymysql默认autocommit=False
,即不自动提交事务。
在通常的查询中不提交事务也可以获取结果,但是可能会出现返回旧数据的查询结果,所以需要设置为自动提交事务或手动提交。
四、游标操作
pymysql 通过游标来进行数据库操作,游标对象的常用方法有:
方法名 | 说明 |
---|---|
callproc(procname,[,parameters]) | 调用存储过程,需要数据库支持 |
close() | 关闭当前游标 |
execute(operation,[,parameters]) | 执行数据库操作,sql语句或者数据库命令 |
executemany(operation, seq_of_params) | 用于批量操作 |
fetchone() | 获取查询结果集合中的下一条记录 |
fetchmany(size) | 获取指定数量的记录 |
fetchall() | 获取查询结果集合所有记录 |
nextset() | 跳至下一个可用的数据集 |
arraysize | 指定使用fetchmany()获取的行数,默认为1 |
setinputsizes(size) | 设置调用execute*()方法时分配的内存区域大小 |
setoutputsizes(size) | 设置列缓冲区大小,对大数据列尤其有用 |
需注意的是,获取查询结果时是对游标进行操作,例如使用 fetchone()
方法后获取一条结果记录,再次使用则获取下一条,如果更换为 fetchall()
方法,已经获取的记录也不再重复获取。
cursor.execute('show databases;') # 执行sql语句
one = cursor.fetchone() #获取一条数据,元组类型
print('one:',one)
many = cursor.fetchmany(3) #获取指定条数的数据,不写默认为1
print('many:',many)
all = cursor.fetchall() #获取全部数据
print('all:',all)
execute
方法也支持传入 *args
参数
sql = 'insert into test values (%s, %s, %s, %s, %s);'
cursor.exectue(sql, ('数据1', '数据2', '数据3', '数据4', '数据5'))
五、完整实例
import pymysql
db = pymysql.connect(
host="localhost",
port=3306,
user='root', #在这里输入用户名
password='888888', #在这里输入密码
charset='utf8mb4',
database='justtest' #指定操作的数据库
)
cursor = db.cursor() #创建游标对象
try:
tableName = 'user'
sql = 'create table %s (id varchar(20) not null, name varchar(20) not null, primary key(id));'%(tableName)
cursor.execute(sql) #执行sql语句,创建表
sql = 'show tables;'
cursor.execute(sql)
print('显示创建的表:',cursor.fetchall()) #显示创建的表
sql = 'desc %s;'%(tableName)
cursor.execute(sql)
print('显示表结构:',cursor.fetchall()) #显示表结构
except Exception as e:
print(e)
db.rollback() #回滚事务
finally:
cursor.close()
db.close() #关闭数据库连接
aiomysql
aiomysql 是基于协程的异步操作 mysql 数据库的包。
pip install aiomysql
简单异步操作
使用 aiomysql 和使用 pymysql 及其类似,区别在于所有的操作都是异步,所以需要添加 await
关键字。另外创建连接对象时,需要传入创建的事件循环对象。
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
async def test_example():
conn = await aiomysql.connect(host='127.0.0.1', port=3306,
user='root', password='root', db='py_spider',
loop=loop) # 将事件循环对象传入
cursor = await conn.cursor()
await cursor.execute("SELECT * from ali_work")
# 打印输出当前表中的字段信息
print(cursor.description)
result = await cursor.fetchall()
print(result)
await cursor.close()
conn.close()
loop.run_until_complete(test_example())
连接池操作
有时候需要限制访问并发数量,使用连接池连接。
import aiomysql
# 创建数据库连接池并获取游标对象
async with aiomysql.create_pool(user='root', password='root', db='py_spider', maxsize=10) as pool:
async with pool.acquire() as conn: # 连接对象
async with conn.cursor() as cursor: # 游标对象
# 创建表
create_table_sql = """
create table car_info(
id int primary key auto_increment,
name varchar(100),
price varchar(100),
brand varchar(100),
altitude varchar(100),
breadth varchar(100),
length varchar(100)
);
"""
# 在异步代码中必须先要检查表是否存在, 直接使用if not语句无效
check_table_query = "show tables like 'car_info'"
result = await cursor.execute(check_table_query) # 如果表存在返回1 不存在返回0
if not result:
await cursor.execute(create_table_sql)
需要注意的是,异步 mysql 中的 sql 语句 if not
是不生效的,所以判断不能使用 sql 来进行判断,而是使用 like
先查找表是否存在。
DBUtils
DBUtils 是基于多线程异步操作数据库的包,支持 mysql 等常用主流数据库。使用前需要安装
pip install DBUtils
需要注意的是,DBUtils 只是个多线程的库,并不能够直接操作数据库。例如使用 mysql 数据库,必须保证正确的安装了 pymysql 库。
使用连接池
from dbutils.pooled_db import PooledDB
pool = PooledDB(
creator=pymysql, # 使用连接数据库的模块
maxconnections=6, # 允许最大连接数,0或None表示无限制
mincached=2, # 初始化时,连接池中至少创建的空闲连接,0表示不创建
maxcached=5, # 连接池中最多闲置的连接,超出设置会销毁。0和None表示不限制
maxshared=3, # 连接池中最多共享的连接数量,0和None表示全部共享。注:pymysql和mysqldb等模块并不支持共享连接
blocking=True, # 连接池中如果没有可用的连接时,是否阻塞等待。如不等待则报错。
maxusage=None, # 一个连接最多被重复使用的次数,None表示无限制
host='127.0.0.1',
port=3306,
user='<username>',
password='<password>',
database='<database>',
charset='utf8'
)
db_cursor = pool.connection().cursor() # 从池中获取连接和游标
# 游标的使用和使用的数据库连接模块一致。
pool.close() # 关闭连接池