SQLAlchemy session.execute() return value CursorResult , return row as dict

Question

I am currently using sqlalchemy 1.4.2 I am using session.execute(statement) to fetch rows from my Db table. This is the code I have used below:

    query = f"SELECT * FROM mytable WHERE entity_guid IN {entity_guids}"
    results = session.execute(query)
    for result in results:
        print(results)
        final.append({**result})
    return final

The above code throws me the error:

*TypeError: tuple indices must be integers or slices, not str*
File "/opt/python/lib/python3.8/site-packages/layers/core/read_simple_entities/read_simple_entities.py", line 15, in read_entities
    final.append({**result})
  File "/opt/python/lib/python3.8/site-packages/sqlalchemy/engine/row.py", line 104, in __getitem__
    return self._data[key]

So I updated my code to use _asdict() method:

    results = session.execute(query)
    for row in results:
        final.append(row._asdict())
    return final

But I want to know the reason its passing for some devs with the first block of code where we just unpack result dict even though i use same version of 1.4.2

user_7941944 · Answer

This is a bug in 1.4.2 version of SQLAlchemy and was fixed in later versions. To answer my question the error happens when we use dict(row) to return CursorResult’s row as a dictionary. This happens even when unpacking row to dictionary i.e; {**row}

Updating to newer versions of sqlalchemy should be able to fix it. But if you want to use 1.4 and do not want to upgrade the version, the simple workaround for this is to use _asdict() instead of dict(row) or {**row} if you face this error.

The error is also difficult to reproduce on different environments as it happens only on specific os/environments

It was fixed in this commit: https://github.com/sqlalchemy/sqlalchemy/commit/dca3a43de60b064d863abb9b86b1f629fbd4b14d#diff-b4f6b6f09daad6f7b357a69e2c2c5844941fb51167de1c32ec34e018412700fd

Here’s the link to discussion on this issue on Github: https://github.com/sqlalchemy/sqlalchemy/issues/6218

SQLAlchemy session.execute() return value CursorResult , return row as dict

1 Answers