Connection not returning to pool

[dszmaj]

Hey,
I have confusing problem. There is an endpoint that makes for example 15 queries. On development environment I create pool of exactly 10 to 20 connections and I expect that after hitting this endpoint I will be able to hit it again, especially that it don't use those connections all at once, but I get concurrent.futures._base.CancelledError error instead so it looks like connection didn't return to pool and framework triggered standard RequestTimeoutError (I made this assumption because increasing pool size solves the problem).

I make queries with this code:

from collections import namedtuple

from sanic.exceptions import NotFound


def _row_namedtuple(cursor):
    return namedtuple('Row', _field_names(cursor))


def _field_names(cursor):
    return [name[0].lower() for name in cursor.description]


class DbExecutor:
    def __init__(self, sql, pool, params=()):
        self.sql = sql
        self.params = params
        self.pool = pool
        self._connection = None
        self._cursor = None

    async def __aenter__(self):
        self._connection = await self.pool.acquire()
        self._cursor = await self._connection.cursor()
        await self._cursor.execute(self.sql, self.params)
        return self._cursor

    async def __aexit__(self, exc_type, exc, tb):
        await self._cursor.close()
        await self._connection.close()


async def fetch_dict_all(sql, pool, params=()):
    async with DbExecutor(sql, pool, params) as cursor:
        return [dict(zip(_field_names(cursor), row)) for row in await cursor.fetchall()]


async def fetch_dict_row(sql, pool, params=()):
    async with DbExecutor(sql, pool, params) as cursor:
        row = await cursor.fetchone()
        return dict(zip(_field_names(cursor), row)) if row else []

create pool with this code:

@app.listener('before_server_start')
async def create_pool(app, loop):
    dsn = 'DRIVER=OracleODBC;DBQ=server;UID=login;PWD=password'
    app.db = await aioodbc.create_pool(dsn=dsn, loop=loop, minsize=10, maxsize=10)


@app.listener('after_server_stop')
async def discard_pool(app, loop):
    app.db.close()
    await app.db.wait_closed()

example of query:

details = await fetch_dict_all('sql query', app.db, params)

Ubuntu 16.04
Oracle ODBC Driver
aioodbc == 0.2.0
pyodbc == 4.0.21

[jettify]

You need to return connection to the pool explicitly if you do not use context manager:

    conn = await pool.acquire()
    cur = await conn.cursor()
    await cur.execute("SELECT 42;")
    r = await cur.fetchall()
    await cur.close()
    await pool.release(conn)

or

    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT 42;")
        r = await cur.fetchall()
        await cur.close()

[dszmaj]

Hey, thanks a lot, it solved issue and can be closed. You saved my ass actually :P

So to speak, Oracle connection working well and fast.

[jettify]

You are welcome, glad that worked for you.