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!
The MySQL Connection Pool operates on the client side to ensure that a MySQL client does not constantly connect to and disconnect from the MySQL server. It is designed to cache idle connections in the MySQL client for use by other users as they are needed.
Connection pooling is great for scalability - if you have 100 threads/clients/end-users, each of which need to talk to the database, you don't want them all to have a dedicated connection open to the database (connections are expensive resources), but rather to share connections (via pooling).
For optimal performance, use a pool with eight to 16 connections per node. For example, if you have four nodes configured, then the steady-pool size must be set to 32 and the maximum pool size must be 64.
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With