Python之MySQL数据库连接驱动aiomysql的使用

　　在上一篇博文介绍了MySQL数据库取得pymysql的使用，参考：https://www.cnblogs.com/minseo/p/15597428.html

　　本文介绍异步MySQL异步驱动aiomysql的使用

　　1，安装异步模块

　　如果没有模块则先使用pip安装模块

pip3 install asyncio
pip3 install aiomysql

　　2，创建MySQL数据库连接池

　　和同步方式不一样的是使用异步不能直接创建数据库连接conn，需要先创建一个数据库连接池对象__pool通过这个数据库连接池对象来创建数据库连接

　　数据库配置信息和介绍pymysql同步使用的数据库是一样的

import asyncio,aiomysql,time
# 数据库配置dict
db_config = {
    'host': 'localhost',
    'user': 'www-data',
    'password': 'www-data',
    'db': 'awesome'
}

# 创建数据库连接池协程函数
async def create_pool(**kw):
    global __pool
    __pool = await aiomysql.create_pool(
        host=kw.get('host', 'localhost'),
        port=kw.get('port', 3306),
        user=kw['user'],
        password=kw['password'],
        db=kw['db']
    )

loop=asyncio.get_event_loop()
loop.run_until_complete(create_pool(**db_config))
# 在事件循环中运行了协程函数则生成了全局变量__pool是一个连接池对象 <aiomysql.pool.Pool object at 0x00000244AD1724C8>
print(__pool)
# <aiomysql.pool.Pool object at 0x00000244AD1724C8>

　　3，创建执行sql语句的协程函数

　　因为是异步模块，只能在事件循环中通过await关键字调用，使用需要创建执行sql语句的协程函数

　　在协程函数内使用全局上一步创建的连接池对象来创建连接conn和浮标对象cur，通过浮标对象来执行sql语句，执行方法和pymysql模块的执行方法是一样的

cursor.execute(sql,args)
sql # 需要执行的sql语句例如'select * from table_name'
args # 替换sql语句的格式化字符串，即sql语句可以使用%s代表一个字符串，然后在args中使用对应的变量或参数替换，args为一个list或元组，即是一个有序的序列需要和sql中的%s一一对应
# 例如sql='select * from table_name where id=%s'  args=['12345']
# 相当于使用args中的参数替换sql中的%s
# select * from table_name where id='12345'

　　下面分别创建两个协程函数select execute一个用来执行搜索操作，一个用来执行insert，update，delete等修改操作

# 执行select函数
async def select(sql,args,size=None):
    with await __pool as conn:
        cur = await conn.cursor(aiomysql.DictCursor)
        await cur.execute(sql.replace('?','?s'),args or ())
        if size:
            rs = await cur.fetchmany(size)
        else:
            rs = await cur.fetchall()
        await cur.close()
        return rs


# 执行insert update delete函数
async def execute(sql,args):
    with await __pool as conn:
        try:
            cur = await conn.cursor()
            await cur.execute(sql.replace('?','%s'),args)
            affetced = cur.rowcount
            await conn.commit()
            await cur.close()
        except BaseException as e:
            raise
        return affetced

　　4，实践执行sql语句

　　实践执行sql语句前我们首先在本机创建一个数据库和对应的表用于测试

　　数据库对应的主机，用户名，密码，库名，表名如下

host: localhost
user: www-data
password: www-data
db：awesome
table_name: users

　　创建表名的sql语句如下，需要在数据库中创建好对应的表

CREATE TABLE `users` (
  `id` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `passwd` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `name` varchar(50) NOT NULL,
  `image` varchar(500) NOT NULL,
  `created_at` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　创建好的表对应的结构如下

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(50)  | NO   | PRI | NULL    |       |
| email      | varchar(50)  | NO   | UNI | NULL    |       |
| passwd     | varchar(50)  | NO   |     | NULL    |       |
| admin      | tinyint(1)   | NO   |     | NULL    |       |
| name       | varchar(50)  | NO   |     | NULL    |       |
| image      | varchar(500) | NO   |     | NULL    |       |
| created_at | double       | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (2.68 sec)

　　①执行insert操作

# insert start
import time
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
async def insert():
    await execute(sql,args)
loop.run_until_complete(insert())
# insert end

　　执行方式和pymysql没有区别，不同的是需要在事件循环中使用关键字await调用

　　执行完毕在MySQL中查看插入的数据

mysql> select * from users;
+--------+-------------+----------+-------+------+-------------+------------------+
| id     | email       | passwd   | admin | name | image       | created_at       |
+--------+-------------+----------+-------+------+-------------+------------------+
| 111111 | test@qq.com | password |     1 | test | about:blank | 1637738541.48629 |
+--------+-------------+----------+-------+------+-------------+------------------+
1 row in set (0.00 sec)

　　②执行update操作

　　直接在loop事件循环中执行execute协程函数也可以

# update start
import time
sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111']
loop.run_until_complete(execute(sql,args))
# update end

　　执行以后把email和name都修改了

　　③执行delete操作

# delete start
sql = 'delete from `users` where `id`=?'
args = ['111111'] 
loop.run_until_complete(execute(sql,args))
# delete end

　　同样根据关键字id指定的值删除了这条数据

　　④执行selete操作

　　在执行select操作前我们保证数据库里面至少有一条数据

# select start
sql = 'select * from users'
args = []
rs = loop.run_until_complete(select(sql,args))
print(rs)
# select end

　　这里直接执行搜索的协程函数select根据函数的定义返回的是所有结果的list，元素是查询结果的字典

　　输出为

[{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637739212.74493}]

　　如果结果有多个则使用list的下标取出

　　

　　补充

　　同步模块pymysql和异步模块aiomysql执行速度对比

　　假如我们需要往数据库插入20000条数据，我们分别使用同步模式和异步模式

　　首先删除数据库所有测试数据

delete from users;

　　同步的代码

　　d:/learn-python3/学习脚本/pymysql/use_pymysql.py

import pymysql
db_config = {
    'host': 'localhost',
    'user': 'www-data',
    'password': 'www-data',
    'db': 'awesome'
}
# 创建连接，相当于把字典内的键值对传递
# 相当于执行pymysql.connect(host='localhost',user='www-data',password='www-data',db='awesome')
conn = pymysql.connect(**db_config)
# 创建游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from users'
args = []
# 执行查询返回结果数量
# 执行查询
rs=cursor.execute(sql,args)
# 获取查询结果
# 获取查询的第一条结果，返回一个dict，dict元素是查询对应的键值对
# 如果查询结果有多条则执行一次，游标移动到下一条数据，在执行一次又返回一条数据
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchall())
# print(cursor.fetchmany())
# {'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}
# 获取查询的所有结果，返回一个list，list元素是dict，dict元素是查询对应的键值对
# print(cursor.fetchall())
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]
# 获取查询的前几条结果，返回一个dict，dict元素是查询对应的键值对
# print(cursor.fetchmany(1))
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]
# 执行修改操作
import time
# # insert start
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test1@qq.com','password',1,'test','about:blank',time.time(),'1111121']
# 使用replace 把'?'替换成'%s'
# rs = cursor.execute(sql.replace('?','%s'),args)
# print(cursor.rowcount)
# conn.commit()
# print(rs)
# insert end

# update start
# sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
# args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111'] 
# print(cursor.execute(sql.replace('?','%s'),args))
# conn.commit()
# update end

# delete start
# sql = 'delete from `users` where `id`=?'
# args = ['111111'] 
# print(cursor.execute(sql.replace('?','%s'),args))
# conn.commit()
# delete end


# 写成函数调用,函数内部使用了数据库连接对象conn
# 可以先定义成全局变量global
def select(sql,args,size=None):
    
    cursor =  conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql.replace('?','%s'),args or ())
    if size:
        rs = cursor.fetchmany(size)
    else:
        rs = cursor.fetchall()
    cursor.close
    # logging.info('rows returned: %s' % len(rs))
    return rs
 
def execute(sql,args):
     
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute(sql.replace('?','%s'),args)
        # rowcount方法把影响函数返回
        rs = cursor.rowcount
        cursor.close()
        conn.commit()
    except:
        raise
    return rs

start_time = time.time()
for n in range(20000):
    sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
    email = 'test%s@qq.com' %n
    args = [email,'password',1,'test','about:blank',time.time(),n] 
    execute(sql,args)
end_time = time.time()
# 打印开始和结束时间的差
print(end_time - start_time)

　　我们使用一个循环20000次往数据库插入数据

　　执行，插入数据比较多需要等待一段时间输出

D:\learn-python3\函数式编程>C:/Python37/python.exe d:/learn-python3/学习脚本/pymysql/use_pymysql.py
77.46903562545776

　　可以在数据库查询到这20000条数据，而且这个表的字段created_at存储了创建这条数据的时间戳，我们可以看到，id越往后的时间戳越往后，说明数据是同步按顺序一一插入的

　　我们按照字段created_at排序查询

　　下面我们删除所有数据使用异步方式插入

　　异步的代码如下

　　d:/learn-python3/学习脚本/aiomysql/use_aiomysql.py

import asyncio,aiomysql,time
# 数据库配置dict
db_config = {
    'host': 'localhost',
    'user': 'www-data',
    'password': 'www-data',
    'db': 'awesome'
}

# 创建数据库连接池协程函数
async def create_pool(**kw):
    global __pool
    __pool = await aiomysql.create_pool(
        host=kw.get('host', 'localhost'),
        port=kw.get('port', 3306),
        user=kw['user'],
        password=kw['password'],
        db=kw['db']
    )

loop=asyncio.get_event_loop()
loop.run_until_complete(create_pool(**db_config))
# 在事件循环中运行了协程函数则生成了全局变量__pool是一个连接池对象 <aiomysql.pool.Pool object at 0x00000244AD1724C8>
print(__pool)
# <aiomysql.pool.Pool object at 0x00000244AD1724C8>

# 执行select函数
async def select(sql,args,size=None):
    with await __pool as conn:
        cur = await conn.cursor(aiomysql.DictCursor)
        await cur.execute(sql.replace('?','?s'),args or ())
        if size:
            rs = await cur.fetchmany(size)
        else:
            rs = await cur.fetchall()
        await cur.close()
        return rs


# 执行insert update delete函数
async def execute(sql,args):
    with await __pool as conn:
        try:
            cur = await conn.cursor()
            await cur.execute(sql.replace('?','%s'),args)
            affetced = cur.rowcount
            await conn.commit()
            await cur.close()
        except BaseException as e:
            raise
        return affetced

# insert start
# import time
# sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
# args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
# async def insert():
#     await execute(sql,args)
# loop.run_until_complete(insert())
# insert end

# update start
# import time
# sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
# args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111']
# loop.run_until_complete(execute(sql,args))

# update end

# delete start
# sql = 'delete from `users` where `id`=?'
# args = ['111111'] 
# loop.run_until_complete(execute(sql,args))
# delete end

# select start
# sql = 'select * from users'
# args = []
# rs = loop.run_until_complete(select(sql,args))
# print(rs)
# select end

async def insert1():
     for n in range(10000):
        sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
        email = 'test%s@qq.com' %n
        args = [email,'password',1,'test','about:blank',time.time(),n] 
        await execute(sql,args)

async def insert2():
     for n in range(10001,20001):
        sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
        email = 'test%s@qq.com' %n
        args = [email,'password',1,'test','about:blank',time.time(),n] 
        await execute(sql,args)

async def main():
    # 需要组合成一个事件才会异步执行即在执行insert1的时候同步执行insert2
    await asyncio.gather(insert1(),insert2())

start_time = time.time()
loop.run_until_complete(main())
end_time = time.time()
print(end_time - start_time)

　　这里我们定义了两个协程函数，分别用来执行前10000个数据和后10000个数据的插入，在main()把这两个协程函数组合成一个事件循环

　　等待一段时间后执行输出如下，忽略这个warning，可以看到执行时间明显比同步时间短

d:/learn-python3/学习脚本/aiomysql/use_aiomysql.py:42: DeprecationWarning: with await pool as conn deprecated, useasync with pool.acquire() as conn instead
  with await __pool as conn:
39.794615507125854

　　我们去数据库查询一下数据也可以看到id从0开始和id从10001开始几乎是同时插入的