Acquiring pool connections in Python Gino (async)

Question

I'm using Postgres, Python3.7 with asyncio + asyncpg + gino (ORM-ish) + aiohttp (routing, web responses).

I created a small postgres table users in my database testdb and inserted a single row:

testdb=# select * from users;
 id | nickname
----+----------
  1 | fantix

I'm trying to set up my database such that I can make use of the ORM within routes as requests come in.

import time
import asyncio
import gino

DATABASE_URL = os.environ.get('DATABASE_URL')

db = gino.Gino()

class User(db.Model):
  __tablename__ = 'users'
  id = db.Column(db.Integer(), primary_key=True)
  nickname = db.Column(db.Unicode(), default='noname')

kwargs = dict(
  min_size=10,
  max_size=100,
  max_queries=1000,
  max_inactive_connection_lifetime=60 * 5,
  echo=True
)

async def test_engine_implicit():
  await db.set_bind(DATABASE_URL, **kwargs)
  return await User.query.gino.all()      # this works

async def test_engine_explicit():
  engine = await gino.create_engine(DATABASE_URL, **kwargs)
  db.bind = engine
  async with engine.acquire() as conn:
    return await conn.all(User.select())  # this doesn't work!

users = asyncio.get_event_loop().run_until_complete(test_engine_implicit())
print(f'implicit query: {users}')

users = asyncio.get_event_loop().run_until_complete(test_engine_explicit())
print(f'explicit query: {users}')

The output is:

web_1    | INFO gino.engine._SAEngine SELECT users.id, users.nickname FROM users
web_1    | INFO gino.engine._SAEngine ()
web_1    | implicit query: [<db.User object at 0x7fc57be42410>]
web_1    | INFO gino.engine._SAEngine SELECT
web_1    | INFO gino.engine._SAEngine ()
web_1    | explicit query: [()]

which is strange. The "explicit" code essentially runs a bare SELECT against the database, which is useless.

I can't find in the documentation a way to both 1) use the ORM, and 2) explicitly check out connections from the pool.

Questions I have:

Does await User.query.gino.all() check out a connection from the pool? How is it released?
How would I wrap queries in a transaction? I am uneasy that I am not able to explicitly control when / where I acquire a connection from the pool, and how I release it.

I'd essentially like the explicitness of the style in test_engine_explicit() to work with Gino, but perhaps I'm just not understanding how the Gino ORM works.

ololobus · Accepted Answer · 2019-10-30T14:45:26.790

I have never used GINO before, but after a quick look into the code:

GINO connection simply executes provided clause as is. Thus, if you provide bare User.select(), then it adds nothing to that.
If you want to achieve the same as using User.query.gino.all(), but maintaining connection yourself, then you could follow the docs and use User.query instead of plain User.select():

async with engine.acquire() as conn:
    return await conn.all(User.query)

Just tested and it works fine for me.

Regarding the connections pool, I am not sure that I got the question correctly, but Engine.acquire creates a reusable connection by default and then it is added to the pool, which is actually a stack:

   :param reusable: Mark this connection as reusable or otherwise. This
      has no effect if it is a reusing connection. All reusable connections
      are placed in a stack, any reusing acquire operation will always
      reuse the top (latest) reusable connection. One reusable connection
      may be reused by several reusing connections - they all share one
      same underlying connection. Acquiring a connection with
      ``reusable=False`` and ``reusing=False`` makes it a cleanly isolated
      connection which is only referenced once here.

There is also a manual transaction control in GINO, so e.g. you can create a non-reusable, non-reuse connection and control transaction flow manually:

async with engine.acquire(reuse=False, reusable=False) as conn: 
     tx = await conn.transaction() 
     try: 
          await conn.status("INSERT INTO users(nickname) VALUES('e')") 
          await tx.commit() 
     except Exception: 
          await tx.rollback() 
          raise

As for connection release, I cannot find any evidence that GINO releases connections itself. I guess that pool is maintained by SQLAlchemy core.

I definitely have not answered your questions directly, but hope it will help you somehow.

Acquiring pool connections in Python Gino (async)

1 Answers1