CODEWITHSUNDEEP
pythonpython-3.xsqlalchemypython-multithreading
Tohid
Tohid

Reputation: 22533

SQLAlchemy KeyError when using connection pool

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!

Upvotes: 3

Views: 786

Answers (1)

Tohid
Tohid

Reputation: 22533

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).

Upvotes: 2

Related Questions