mysql 异步io_aiomysql异步操作mysql

#!/usr/bin/env python3#coding: utf-8

"""mysql 异步版本"""

importtracebackimportloggingimportaiomysqlimportasyncioimporttime

logobj= logging.getLogger('mysql')classPmysql:def __init__(self):

self.coon=None

self.pool=None

asyncdefinitpool(self):try:

logobj.debug("will connect mysql~")__pool =await aiomysql.create_pool(

minsize=5, #连接池最小值

maxsize=10, #连接池最大值

host='192.168.31.230',

port=3306,

user='root',

password='abcd1234',

db='test',

autocommit=True, #自动提交模式

)return __pool

except:

logobj.error('connect error.', exc_info=True)

asyncdefgetCurosr(self):

conn=await self.pool.acquire()#返回字典格式

cur =await conn.cursor(aiomysql.DictCursor)returnconn, cur

asyncdef query(self, query, param=None):"""查询操作

:param query: sql语句

:param param: 参数

:return:"""conn, cur=await self.getCurosr()try:

await cur.execute(query, param)returnawait cur.fetchall()except:

logobj.error(traceback.format_exc())finally:ifcur:

await cur.close()#释放掉conn,将连接放回到连接池中

await self.pool.release(conn)

asyncdef execute(self, query, param=None):"""增删改 操作

:param query: sql语句

:param param: 参数

:return:"""conn, cur=await self.getCurosr()try:

await cur.execute(query, param)if cur.rowcount ==0:returnFalseelse:returnTrueexcept:

logobj.error(traceback.format_exc())finally:ifcur:

await cur.close()#释放掉conn,将连接放回到连接池中

await self.pool.release(conn)

asyncdefgetAmysqlobj():

mysqlobj=Pmysql()

pool=await mysqlobj.initpool()

mysqlobj.pool=poolreturnmysqlobj

asyncdeftest_select():

mysqlobj=await getAmysqlobj()#UPDATE `youku`.`person` SET `psName` = '张三丰' WHERE (`id` = '3');

exeRtn = await mysqlobj.query("select * from users")#print("查询结果",exeRtn)

returnexeRtn

asyncdeftest_update():

mysqlobj=await getAmysqlobj()#UPDATE `youku`.`person` SET `psName` = '张三丰' WHERE (`id` = '3');

exeRtn = await mysqlobj.execute("update users set username='xiao1' where id='1'")#print("exeRtn", exeRtn, type(exeRtn))

ifexeRtn:#print('操作成功')

return '操作成功'

else:#print('操作失败')

return '操作失败'asyncdef main(): #调用方

tasks = [test_select(), test_update()] #把所有任务添加到task中

done, pending = await asyncio.wait(tasks) #子生成器

for r in done: #done和pending都是一个任务，所以返回结果需要逐个调用result()

#print('协程无序返回值：'+r.result())

print(r.result())if __name__ == '__main__':

start=time.time()

loop= asyncio.get_event_loop() #创建一个事件循环对象loop

try:

loop.run_until_complete(main())#完成事件循环，直到最后一个任务结束

finally:

loop.close()#结束事件循环

print('所有IO任务总耗时%.5f秒' % float(time.time() - start))