CODEWITHSUNDEEP
pythonpython-3.xpython-asyncio
resolvewang
resolvewang

Reputation: 103

How can I use connection pool in aiomysql

I just don't know what to do to reuse aiomysql connection pool by reading the aiohttp examples or by google. Here is my code

import aiomysql
import asyncio


async def select(loop, sql):
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='123456',
                                      db='test', loop=loop)
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql)
            r = await cur.fetchone()
            print(r)


async def insert(loop, sql):
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='123456',
                                      db='test', loop=loop)
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql)
            await conn.commit()


async def main(loop):
    c1 = select(loop=loop, sql='select * from minifw')
    c2 = insert(loop=loop, sql="insert into minifw (name) values ('hello')")
    tasks = [
        asyncio.ensure_future(c1),
        asyncio.ensure_future(c2)
    ]
    return await asyncio.gather(*tasks)

if __name__ == '__main__':
    cur_loop = asyncio.get_event_loop()
    cur_loop.run_until_complete(main(cur_loop))

If i run this code, the create_pool will be executed twice.So I want to know how to change this code to reuse aiomysql connecton pool.

Thanks!

Upvotes: 6

Views: 8244

Answers (1)

tacy_lee
tacy_lee

Reputation: 169

You can define pool in main func, like this:

import aiomysql
import asyncio


async def select(loop, sql, pool):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql)
            r = await cur.fetchone()
            print(r)


async def insert(loop, sql, pool):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql)
            await conn.commit()


async def main(loop):
    pool = await aiomysql.create_pool(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        db='test',
        loop=loop)
    c1 = select(loop=loop, sql='select * from minifw limit 1', pool=pool)
    c2 = insert(loop=loop, sql="insert into minifw (name) values ('hello')", pool=pool)

    tasks = [asyncio.ensure_future(c1), asyncio.ensure_future(c2)]
    return await asyncio.gather(*tasks)


if __name__ == '__main__':
    cur_loop = asyncio.get_event_loop()
    cur_loop.run_until_complete(main(cur_loop))

Upvotes: 12

Related Questions