数据库异步操作
基于 aiomysql 异步操作mysql数据库
异步操作 MySQL 的话，需要使用一个 aiomysql，直接 pip install aiomysql
- 入门案例
# -*- coding: utf-8 -*-
# 导入异步操作的工具类库
import asyncio
import aiomysql.sa as aio_sa
"""
# 标记点
1. 必须使用 关键字 async 定义 需要异步处理的函数
2. await 处理的函数中,遇到io操作标识
3. 开启引擎
4. 执行异步sql操作
5. 关闭引擎
6. 开启 事件循环对象
7. 添加执行的异步函数
8. 关闭 事件循环对象
"""
async def main():
# 通过 aio_sa 创建 引擎
engine = await aio_sa.create_engine(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='flaskdb',
connect_timeout=10 # 连接超时 10秒
)
# 通过 engine.acquire 获取一个mysql 数据库的连接
async with engine.acquire() as conn:
# 异步执行, 返回一个 异步对象
result = await conn.execute('select * from t1;')
print(type(result)) # <class 'aiomysql.sa.result.ResultProxy'>
# 获取一条记录
data = await result.fetchone()
print(type(data)) # <class 'aiomysql.sa.result.RowProxy'>
print(data.keys())
print(type(data.keys()))# collections.abc.KeysView
# 转换成列表
print(list(data.keys()))
# 关闭引擎
engine.close()
await engine.wait_closed()
# 创建循环loop事件队列
loop = asyncio.get_event_loop()
# 添加 要异步执行的函数
loop.run_until_complete(main())
# 当完成 loop队列中的所有事件,关闭事件队列
loop.close()
- 入门案例2
### 基于 with 开启引擎和开启连接
# -*- coding: utf-8 -*-
from pprint import pprint
import asyncio
import aiomysql.sa as aio_sa
"""
# 标记点
1. with 自动关闭 连接和引擎
"""
async def main():
async with aio_sa.create_engine(host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='flaskdb',
connect_timeout=10 # 连接超时 10秒
) as engine:
async with engine.acquire() as conn:
result = await conn.execute('select * from t1;')
data = await result.fetchall()
pprint(list(map(dict, data)))
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
# fetchmany 指定查询的条数
data = await result.fetchmany(2)
# fetchall 查询全部
data = await result.fetchall()
# fetchone 查询一条
data = await result.fetchone()
借助SQLAlchemy,通过 aiomysql 查询数据库SQL
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import aiomysql.sa as aio_sa
from sqlalchemy.sql.selectable import Select
from sqlalchemy import text
async def main():
async with aio_sa.create_engine(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='flaskdb',
connect_timeout=10 # 连接超时 10秒
) as engine:
async with engine.acquire() as conn:
###
# 1. whereclause 条件
# 2. from_obj 表
# 3. text 编辑 sql 条件
# 利用 text / Select 生成 SQL语句
sql = Select([text("id , name , day")],whereclause=text("id != 1"),from_obj=text("t1"))
# 执行SQL
result = await conn.execute(sql)
data = await result.fetchall()
pprint(list(map(dict,data)))
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
借助SQLAlchemy,通过 aiomysql , 执行新增操作
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import aiomysql.sa as aio_sa
from sqlalchemy import Table,MetaData,create_engine
async def main():
async with aio_sa.create_engine(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='flaskdb',
connect_timeout=10 # 连接超时 10秒
) as engine:
async with engine.acquire() as conn:
# 创建 SQLAlchemy 中的引擎, 用来将表反射出来
s_enging = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/flaskdb')
# 反射表, MetaData是绑定引擎,获取元数据
t1_table = Table('t1',MetaData(bind=s_enging),autoload=Table)
inset_sql = t1_table.insert().values(
[
{
"id":1236,
"name":'alex',
"year":'2021',
"month":"06",
"day":"16",
"datetime":"2021-04-28 14:32:46"
}, {
"id":1237,
"name":'wusir',
"year":'2021',
"month":"06",
"day":"16",
"datetime":"2021-04-28 14:32:46"
},
]
)
### 必须开启事务,否则数据是不会插入到数据库中
async with conn.begin():
# 执行SQL
result = await conn.execute(inset_sql)
# 查看最后一条记录
print(result.lastrowid) # 0
# 查看影响的行数
print(result.rowcount) # 2
# 再次查询, 查看是否插入到数据库
async with engine.acquire() as conn:
data = await (await conn.execute('select * from t1')).fetchall()
data = list(map(dict,data))
pprint(data)
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
借助SQLAlchemy,通过 aiomysql , 执行更新操作
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import aiomysql.sa as aio_sa
from sqlalchemy import Table, MetaData, create_engine, text
async def main():
async with aio_sa.create_engine(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='flaskdb',
connect_timeout=10 # 连接超时 10秒
) as engine:
async with engine.acquire() as conn:
# 创建 SQLAlchemy 中的引擎, 用来将表反射出来
s_enging = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/flaskdb')
# 反射表, MetaData是绑定引擎,获取元数据
t1_table = Table('t1',MetaData(bind=s_enging),autoload=Table)
update_sql = t1_table.update().where(text('name="alex"')).values({"year":'2222'})
### 必须开启事务,否则数据是不会更新到数据库中
async with conn.begin():
# 执行SQL
result = await conn.execute(update_sql)
# 查看最后一条记录
print(result.lastrowid) # 0
# 查看影响的行数
print(result.rowcount) # 1
# 再次查询, 查看是否更新到数据库
async with engine.acquire() as conn:
data = await (await conn.execute('select * from t1 where name="alex" ')).fetchall()
data = list(map(dict,data))
pprint(data)
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
借助SQLAlchemy,通过 aiomysql , 执行删除操作
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import aiomysql.sa as aio_sa
from sqlalchemy import Table, MetaData, create_engine, text
async def main():
async with aio_sa.create_engine(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='flaskdb',
connect_timeout=10 # 连接超时 10秒
) as engine:
async with engine.acquire() as conn:
# 创建 SQLAlchemy 中的引擎, 用来将表反射出来
s_enging = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/flaskdb')
# 反射表, MetaData是绑定引擎,获取元数据
t1_table = Table('t1',MetaData(bind=s_enging),autoload=Table)
delete_sql = t1_table.delete() # 全部删除
### 必须开启事务,否则数据是不会插入到数据库中
async with conn.begin():
# 执行SQL
result = await conn.execute(delete_sql)
# 查看最后一条记录
print(result.lastrowid) # 0
# 查看影响的行数
print(result.rowcount) # 1237
# 再次查询, 查看是否删除了所有数据
async with engine.acquire() as conn:
data = await (await conn.execute('select * from t1 where name="alex" ')).fetchall()
data = list(map(dict,data))
pprint(data) # []
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
基于 asyncpg 对 PostgreSQL 执行异步操作
异步操作 PostgreSQL，需要使用一个 asyncpg ，直接 pip install asyncpg . asyncpg相比较于psycopg2 效果更好
异步查询
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import asyncpg
async def main():
# 创建 数据库连接驱动
conn = await asyncpg.connect(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10
)
# - fetchrow 获取满足调价的单条记录
row1 = await conn.fetchrow('select * from company;')
pprint(row1)
print(type(row1)) # <class 'asyncpg.Record'>
##### <class 'asyncpg.Record'> Record 对象的用法
# 1. 通过字典的方式获取数据值
print(row1['id'],row1['name']) # 8 Paul
# 2. 字典的get方式获取数据
print(row1.get('id'))
# 3. keys , values ,items
print(list(row1.keys())) # tuple_iterator 字段名字
print(list(row1.values())) # RecordIterator 值
print(dict(row1.items())) # RecordItemsIterator 字典形式 {字段名:值}
print(dict(row1))
# - fetch 执行 获取满足条件的全部记录
row2 = await conn.fetch('select * from company;')
pprint(row2)
# 关闭连接
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
基于sqlalchemy异步PSQL查询
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import asyncpg
from sqlalchemy import text
from sqlalchemy.sql import Select
async def main():
# 创建 数据库连接驱动
conn = await asyncpg.connect(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10
)
# 利用 sqlalchemy 工具类 ,组装sql语句
sql = Select([text('id,name,age')],whereclause=text('id != 1'),from_obj=text('company'))
rows = await conn.fetch(str(sql)) # asyncpg 只支持字符串查询
pprint(list(map(dict,rows)))
# conn.fetch 也支持 占位符 . $ 符号 作为占位符
rows2 = await conn.fetch("select * from company where id != $1", 1)
pprint(list(map(dict,rows2)))
# 关闭连接
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
异步PSQL添加 INSERT
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import asyncpg
"""
# 1. executemany 执行多条sql语句 , 不具备事务功能
# 2. execute 执行一条SQL语句 , 具有事务功能
"""
async def main():
# 创建 数据库连接驱动
conn = await asyncpg.connect(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10
)
### 执行 insert 语句
# execute 执行单条 SQL语句
row = await conn.execute("insert into company(id,name,age,address,salary) values ($1,$2,$3,$4,$5)", 1,'zhangsan', 18, 'bj',
10000)
pprint(row) # 'INSERT 0 1'
pprint(type(row)) # <class 'str'>
# executemany 执行多条语句 .第一条参数是一个模板，第二条命令是包含多个元组的列表
rows = await conn.executemany("insert into company(id,name,age,address,salary) values ($1,$2,$3,$4,$5)",
[(2,'lisi', 18, 'sh',
2222), (3,'wangwu', 18, 'hn',
3333)])
# 关闭连接
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
异步PSQL批量添加之事务
import asyncio
from pprint import pprint
import asyncpg
"""
1. 开启事务后,执行多个插入语句 . 遇到异常则自动回滚事务
"""
async def main():
# 创建 数据库连接驱动
conn = await asyncpg.connect(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10
)
# 开启 一步事务 , 会开启一个事务，使用异步上下文管理
async with conn.transaction():
await conn.executemany("insert into company(id,name,age,address,salary) values ($1,$2,$3,$4,$5)",
[(2, 'lisi', 18, 'sh',2222),
(3, 'wangwu', 18, 'hn', 3333)])
# 关闭连接
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
异步PSQL修改
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import asyncpg
async def main():
# 创建 数据库连接驱动
conn = await asyncpg.connect(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10
)
# 修改一条记录
row = await conn.execute('update company set name = $1 where name = $2 ','zhangsan','geigei')
print(row) # UPDATE 0
# 修改多条记录
rows = await conn.executemany('update company set name = $1 where name = $2',[('zhangsangeigei','zhangsan'),('lisi','geigei2')])
print(rows) # None
# returning 返回 数据 id
row = await conn.fetch("update company set name = 'zhangsan' where name ='zhangsan' returning id")
print(row)
# 关闭连接
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
异步PSQL删除
# -*- coding: utf-8 -*-
import asyncio
from pprint import pprint
import asyncpg
async def main():
# 创建 数据库连接驱动
conn = await asyncpg.connect(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10
)
async with conn.transaction():
# 删除一条
row = await conn.execute("delete from company where name in ($1,$2)","张三","李四")
print(row)
# 删除多条
rows = await conn.executemany("delete from company where id = $1 ",[(2,),(3,)])
print(rows)
# 返回 id
rows = await conn.fetch("delete from company where name in ($1,$2) returning id ",'zhangsan','lisi' )
print(rows)
# 关闭连接
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
loop.close()
连接池
# -*- coding: utf-8 -*-
import asyncio
import asyncpg
# asyncpg 还提供了连接池，需要的话往池子里面去取即可。
async def main():
pool = await asyncpg.create_pool(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10,
# 连接池初始化时默认的最小连接数, 默认为 10
min_size=10,
# 连接池最大数量 默认是 10,
max_size=10,
# 每个链接最大查询数量, 超过了就换新的连接, 默认 5000
max_queries=5000,
# 最大不活跃时间, 默认 300.0, 超过这个时间的连接就会被关闭, 传入 0 的话则永不关闭
max_inactive_connection_lifetime=300
)
async with pool.acquire() as conn:
async with conn.transaction():
row = await conn.fetchrow("select '100'::int + '200' ") # <Record ?column?=300>
print(row)
# 起别名 as
row = await conn.fetchrow("select '100'::int + '200' as result ")
print(row)
if __name__ == '__main__':
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
异步版PSQL
# -*- coding: utf-8 -*-
import time
import asyncio
import asyncpg
async def run_sql(conn, query_list):
result = []
for query in query_list:
result.append(await conn.fetch(query))
await conn.close()
return len(result)
async def main():
async with asyncpg.create_pool(
host='192.168.236.128',
port=5432,
user='postgres',
password='123456',
database='mydb',
timeout=10,
# 连接池初始化时默认的最小连接数, 默认为 10
min_size=10,
# 连接池最大数量 默认是 10,
max_size=10,
# 每个链接最大查询数量, 超过了就换新的连接, 默认 5000
max_queries=5000,
# 最大不活跃时间, 默认 300.0, 超过这个时间的连接就会被关闭, 传入 0 的话则永不关闭
max_inactive_connection_lifetime=300,
) as pool:
query_list = [('select * from company;') for _ in range(20)]
# 1. 创建 4 个 异步连接
count = len(query_list) // 5
# 2. 切片, 将任务分成 5 份
query_list = [query_list[c * 4:(c + 1) * 4] for c in range(count + 1)]
tasks = []
for q in query_list:
conn = await pool.acquire()
tasks.append(run_sql(conn, q))
# gather通常被用来阶段性的一个操作，做完第一步才能做第二步
results = await asyncio.gather(*tasks)
return results
if __name__ == '__main__':
start = time.perf_counter()
loop = asyncio.get_event_loop()
results = loop.run_until_complete(main())
end = time.perf_counter()
for res in results:
print(res)
print(f'耗时: {end - start}')
异步操作 Redis
import asyncio
import aioredis
async def main():
conn = await aioredis.create_connection('redis://:password@localhost:6379')
# 指令: ex 超时
data = await conn.execute("set","name","alex","ex",10)
await conn.close()
print(conn.closed) # True
asyncio.run(main())