SQLAlchemy KeyError when using connection pool

Question

I created a repository class for Person with the below method in it:

def find_by_order_id(self, order_id: str) -> [Person]:
    results = []

    client_table = Table(TBL_CLIENT, self._metadata, autoload=True,
                             autoload_with=self._connection)
    order_table = Table(TBL_ORDER, self._metadata, autoload=True,
                             autoload_with=self._connection)

    query = select([client_table.c.first_name, client_table.c.last_name, client_table.c.date_of_birth])
    .select_from(
        order_table.join(client_table, client_table.c.order_id = order_table.c.order_id)
    ).where(order_table.c.order_id == order_id).distinct()

    for row in self._connection.execute(query).fetchall():
        results.append(dict(row))

    return results

for some odd reason which I have no explanation for, I sometimes get KeyError from SQLAlchemy on a key that actually exists when I debug the code:

File "/home/tghasemi/miniconda3/envs/myproj/lib/python3.6/site-packages/sqlalchemy/util/_collections.py", line 210, in __getattr__
    return self._data[key]
KeyError: 'order_id'

I noticed this only happens when I pull the connection from a connection pool while multithreading (each thread only uses one connection - I know connections are not threadsafe):

if use_pooling:
        self._engine = create_engine(connection_string, pool_size=db_pool_size,
                                     pool_pre_ping=db_pool_pre_ping, echo=db_echo)
else:
        self._engine = create_engine(connection_string, echo=db_echo)

Considering the fact that the key exists(even checked it when the exception happens) when I put a breakpoint where the exception happens, I suspect loading the table is not completed yet when the query is being constructed.

Does anyone have any idea why something like this can happen? I gave up!

This blog post might have something relevant, in particular it notes, multiple threads attempting to access the same connection concurrently may raise an exception as one of the threads close the connection, although I wouldn't expect a KeyError — Jamie Phan
– Jamie Phan, Commented Apr 17, 2020 at 5:20

Tohid · Accepted Answer · 2020-04-20 14:03:11Z

Well, I think I managed to fix the problem.

Something I did not mention in my question (in fact I did not think of it as a possibility) was that both the Engine and the MetaData object come from a Singleton class I created for my Database:

class Database(metaclass=SingletonMetaClass):

    def __init__(self, config: Config, use_pooling: bool = True, logger: Logger = None):
        # Initializing the stuff here ...


    @property
    def metadata(self): # used to return self._metadata here 
        return MetaData(self._engine, reflect=False, schema=self._db_schema)

    @property
    def engine(self):
        return self._engine

Although the SQLAlchemy's official documentation says that the MetaData object is thread-safe for read operations, but for some reason in my case (which is a read operation) it was causing this issue. Somehow after not sharing this object among my threads the issue went away (not 100% sure if it literally went away, but it's not happening anymore).

Collectives™ on Stack Overflow

SQLAlchemy KeyError when using connection pool

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related