Sanic: Question about Asyncpg example.

[feluxe]

Hi there!

this is from the asyncpg example:

@app.get('/')
async def root_get(request):
    async with app.pool.acquire() as connection:
        results = await connection.fetch('SELECT * FROM sanic_post')
        return json({'posts': jsonify(results)})

I wonder, should I use this line async with app.pool.acquire() as connection for every route? E.g.:

@app.get('/')
async def root_get(request):
    async with app.pool.acquire() as connection:
        results = await connection.fetch('SELECT * FROM sanic_post')
        return json({'posts': jsonify(results)})

@app.get('/foo')
async def foo_get(request):
    async with app.pool.acquire() as connection:
        results = await connection.fetch('SELECT * FROM foo')
        return json({'posts': jsonify(results)})

# etc...

[r0fls]

Yes, for every route that is accessing the database you should use that format.

[feluxe]

Thanks for your reply!

I wrote some wrappers to reduce the boilerplate:

db.py

from functools import partial
from asyncpg import create_pool
from sanic import Blueprint


bp = Blueprint('dp')


async def _pg_fetch(pg_pool, sql, *args, **kwargs):
    async with pg_pool.acquire() as connection:
        return await connection.fetch(sql, *args, **kwargs)


async def _pg_execute(pg_pool, sql, *args, **kwargs):
    async with pg_pool.acquire() as connection:
        return await connection.execute(sql, *args, **kwargs)


# More wrapper functions...


class pg:
    def __init__(self, pg_pool):
        self.fetch = partial(_pg_fetch, pg_pool)
        self.execute = partial(_pg_execute, pg_pool)
        # More wrapper functions...


@bp.listener('before_server_start')
async def init_pg(app, loop):
    """
    Init Postgresql DB.
    """
    app.pg_pool = await create_pool(
        **app.config.PG_CFG,
        loop=loop,
        max_size=100,
    )
    app.pg = pg(app.pg_pool)

I can call asyncpg like this now:

@app.route("/")
async def root(req):
    result = await app.pg.fetch('SELECT * FROM foo')

[ddehghan]

Hi @feluxe Thanks for the example. I wonder why you use partial function. I have never used that before. Is this a simpler way to do the same?

from asyncpg import create_pool
from sanic import Blueprint

bp = Blueprint('dp')

class pg:
    def __init__(self, pg_pool):
        self.pg_pool = pg_pool

    async def fetch(self, sql, *args, **kwargs):
        async with self.pg_pool.acquire() as connection:
            return await connection.fetch(sql, *args, **kwargs)

    async def execute(self, sql, *args, **kwargs):
        async with self.pg_pool.acquire() as connection:
            return await connection.execute(sql, *args, **kwargs)

@bp.listener('before_server_start')
async def init_pg(app, loop):
    """
    Init Postgresql DB.
    """
    bp.pg_pool = await create_pool(
        **app.config.PG_CFG,
         @max_inactive_connection_lifetime=60,
        min_size=1,
        max_size=3,
        loop=loop,
    )
    bp.pg = pg(bp.pg_pool)
    print('-------- setup connection pool --------')

I can call asyncpg like this now:

@app.route("/")
async def root(req):
    result = await app.pg.fetch('SELECT * FROM foo')

[feluxe]

@ddehghan I think your solution is fine!

I have a functional programming background and the choice of partial was just intuitive in this situation. partial application allows for some decoupled, flexible code. In this case it decouples the functions from the class. But I don't think it matters much in the situation at hand. Either way would be fine, IMO. :)