aiomysql + sqlalchemy(ORM) 配合使用

官方文档：https://aiomysql.readthedocs.io/en/latest/sa.html (过时)
其他教程：https://gzm1997.github.io/2018/05/26/%E4%BD%BF%E7%94%A8%E5%BC%82%E6%AD%A5ORM-aiomysql-sa/
项目地址：https://github.com/aio-libs/aiomysql

import asyncio
import sqlalchemy as sa

from aiomysql.sa import create_engine


metadata = sa.MetaData()
tbl = sa.Table('tbl', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('val', sa.String(255))
)

## 插入查询
async def go(loop):
    engine = await create_engine(user='root', db='test_pymysql',
                                 host='127.0.0.1', password='', loop=loop)
    async with engine.acquire() as conn:
    	async with conn.begin() as transaction:
        	await conn.execute(tbl.insert().values(val='abc'))  # 插入
        	await conn.execute(tbl.insert().values(val='xyz'))
        
        	# res = await conn.execute(tbl.select(tbl.c.id == 5))  # 条件查询
        	# await conn.execute(tbl.update().where(tbl.c.id==5).values(val='update'))
        	
        	async for row in conn.execute(tbl.select()):  #    列出所有
            	print(row.id, row.val)
            await transaction.commit()

    engine.close()
    await engine.wait_closed()

使用事务

async def go():              
    engine = await create_engine(user='root',
        db='test_my',
        host='192.168.1.8',          
        password='123456')
    async with engine.acquire() as conn:
        trans = await conn.begin()  # 事务开始
        ... # do something
        await trans.commit()  # 事务结束  
                                            
        
    engine.close()
    await engine.wait_closed()