代码重复太多,还要自己写sql语句的实例
增删该查还有点用处吧
# coding=utf-8
import asyncio
import aiomysql
class P_Mysql:
def __init__(self, host, port, user, password, db):
self.host = host
self.port = port
self.user = user
self.password = password
self.db = db
self.conn = None
self.mysql_pool = None
# 连接池
async def init_pool(self):
try:
__pool = await aiomysql.create_pool(host = self.host, port = self.port, user = self.user,
password = self.password, db = self.db, charset = "utf8",
autocommit = False)
return __pool
except:
pass
# 获取游标
async def get_curosr(self):
conn = await self.mysql_pool.acquire( )
cur = await conn.cursor( )
return conn, cur
# 数据更新
async def update_data(self, update_sql, param=None):
conn, cur = await self.get_curosr( )
try:
await cur.execute(update_sql, param) # 如果量比较大,换别的方式
await conn.commit( )
print(f"数据更新成功")
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
# 创建数据表
async def create_table(self, create_sql, param=None):
conn, cur = await self.get_curosr( )
try:
await cur.execute(create_sql, param)
await conn.commit( )
print(f"数据表操作成功")
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
# 查找数据
async def query_data(self, query_sql, size=0, param=None):
conn, cur = await self.get_curosr( )
# 查找一条数据---------------------------------
if size == 1:
try:
count = await cur.execute(query_sql, param)
await conn.commit( )
print(f"影响的行数:{count}")
return await cur.fetchone( )
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
# 查找几条数据---------------------------------
elif 1 < size <= 10:
try:
count = await cur.execute(query_sql, param)
await conn.commit( )
print(f"影响的行数:{count}")
return await cur.fetchmany(size)
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
# 查找全部数据---------------------------------
else:
try:
count = await cur.execute(query_sql, param)
await conn.commit( )
print(f"影响的行数:{count}")
return await cur.fetchall( )
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
# 插入数据
async def insert_data(self, insert_sql, info=None, param=None):
conn, cur = await self.get_curosr( )
try:
await cur.executemany(insert_sql, info) # 如果量比较大,换别的方式
await conn.commit( )
print(f"插入数据成功")
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
# 数据删除
async def delete_data(self, delete_sql, param=None):
conn, cur = await self.get_curosr( )
try:
await cur.execute(delete_sql, param)
await conn.commit( )
print(f"删除成功")
except:
await conn.rollback( )
finally:
if cur:
await cur.close( )
# 释放conn,将连接放回连接池中
await self.mysql_pool.release(conn)
async def get_mysql_obj( ):
mysql_obj = P_Mysql(host = 'localhost', port = 3316, user = 'root',
password = '', db = 'stuinfo')
pool = await mysql_obj.init_pool( )
mysql_obj.mysql_pool = pool
return mysql_obj
async def query_01(mysql_obj):
results = await mysql_obj.query_data("select * from hello02;")
return results
async def query_02(mysql_obj):
results = await mysql_obj.query_data("select * from hello03;")
return results
async def create_01(mysql_obj):
await mysql_obj.create_table("drop table if exists hello03;")
await mysql_obj.create_table("create table hello03(id int,name varchar(30));")
async def insert_01(mysql_obj):
insert_data = "insert into hello02 values(%s,%s);"
info = [(i, 'westos-%s' % (i,)) for i in range(1, 11)]
await mysql_obj.insert_data(insert_data, info)
async def insert_02(mysql_obj):
insert_data = "insert into hello03 values(%s,%s);"
info = [(i, 'westos-%s' % (i,)) for i in range(3, 101)]
await mysql_obj.insert_data(insert_data, info)
async def update_01(mysql_obj):
await mysql_obj.update_data("update hello02 set id=%s where name='%s';" % (3, 'kelanj'))
async def update_02(mysql_obj):
await mysql_obj.update_data("update hello03 set id=%s where name='%s';" % (4, 'kelanj'))
async def delete_01(mysql_obj):
await mysql_obj.delete_data("delete from hello02 where id='%d'" % (2,))
async def delete_02(mysql_obj):
await mysql_obj.delete_data("delete from hello03 where id='%d'" % (3,))
async def main( ):
mysql_obj = await get_mysql_obj( )
# 创建数据表
# tasks = [asyncio.ensure_future(create_01(mysql_obj))]
# await asyncio.wait(tasks)
# 查询数据
# results = await asyncio.gather(query_01(mysql_obj), query_02(mysql_obj))
# for result in results:
# print(result)
# 插入数据
# await asyncio.gather(insert_01(mysql_obj), insert_02(mysql_obj))
# 更新数据
# await asyncio.gather(update_01(mysql_obj), update_02(mysql_obj))
# 删除数据
# await asyncio.gather(delete_01(mysql_obj), delete_02(mysql_obj))
if __name__ == "__main__":
event_loop = asyncio.get_event_loop( )
try:
# 用这个协程启动循环,协程返回时这个方法将停止循环。
event_loop.run_until_complete(main( ))
except KeyboardInterrupt:
for task in asyncio.Task.all_tasks( ):
print(task.cancel( ))
event_loop.stop( )
finally:
event_loop.close( )