python aiomysql实例

代码重复太多，还要自己写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( )