dict(row) causing TypeError: tuple indices must be integers or slices, not str

[lindycoder]

DISCLAIMER: This is a very weird bug, I don't know if SQLAlchemy is the problem, but I couldn't reproduce without it, there is a very easy workaround, but I felt like i should report this as it may hide something else.

Describe the bug
Using dict() on an sqlalchemy.engine.Row object is producing TypeError: tuple indices must be integers or slices, not str in a very specific setup (see To Reproduce)

Expected behavior
dict(row) should return the row as a dict.

To Reproduce
This Dockerfile can show the error:

FROM python:3-alpine

RUN apk add --no-cache g++

RUN pip install --upgrade pip && pip install greenlet

RUN apk del g++

RUN pip install sqlalchemy

RUN echo $'\
from sqlalchemy import create_engine \n\
from sqlalchemy.orm import sessionmaker \n\
\n\
session = sessionmaker(bind=create_engine("sqlite://"))() \n\
result = session.execute("select 1, 2, 3").fetchall() \n\
row = result[0] \n\
\n\
print(dict(row)) \n\
' > script.py

RUN python script.py

Inline code is

from sqlalchemy import create_engine 
from sqlalchemy.orm import sessionmaker 

session = sessionmaker(bind=create_engine("sqlite://"))() 
result = session.execute("select 1, 2, 3").fetchall() 
row = result[0] 

print(dict(row))

Error

Traceback (most recent call last):
  File "//script.py", line 8, in <module>
    print(dict(row))
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/row.py", line 104, in __getitem__
    return self._data[key]
TypeError: tuple indices must be integers or slices, not str

Versions.

• OS: Alpine 3.13
• Python: 3.9.4
• SQLAlchemy: 1.4.6
• Database: sqlite
• DBAPI: ?

Additional context

The provided Dockerfile is a shrunk down version of what we use:

• A base image with build dependencies installing all libs requiring compilation (greenlet)
• A service image that install extra libs not requiring dependencies (sqlalchemy)

I have noticed that REMOVING or MOVING the RUN apk del g++ AFTER the installation of SQL Alchemy will solve the problem.

I also tried to reproduce in debian, compiling libs instead of wheels. with this Dockerfile and could not reproduce, so maybe it's related to Alpine OR there's something else installed in slim-buster.

FROM python:3-slim-buster

RUN apt-get update && apt-get install -y g++

RUN pip install --upgrade pip && pip install greenlet --no-binary :all:

RUN apt-get remove -y g++

RUN pip install sqlalchemy --no-binary :all:

RUN echo '\
from sqlalchemy import create_engine \n\
from sqlalchemy.orm import sessionmaker \n\
\n\
session = sessionmaker(bind=create_engine("sqlite://"))() \n\
result = session.execute("select 1, 2, 3").fetchall() \n\
row = result[0] \n\
\n\
print(dict(row)) \n\
' > script.py

RUN python script.py

WORKAROUND
Using row._asdict() will solve the problem.

Have a nice day! and thank you for your amazing work.

[jvanasco]

I haven't been able to recreate this either, this looks related to your environment. Maybe there is something in the compile args for Python in the image? Alpine images can often be odd, and are famous for being hard to debug.

[lindycoder]

You cannot recreate with the alpine dockerfile?

[jvanasco]

@lindycoder Sorry, like you I could not recreate this on any other environment. I tried a few ubuntu and osx variants. I left the triage label on this, so other contributors will take a look.

[lindycoder]

@jvanasco okay i understand

The fact that NOT removing g++ fixes the problem might be an indication of an installation thing. Is SQL Alchemy doing things differently if a compiler is available?

[CaselIT]

It may be a bug in the python version of the library.

Can you reproduce by installing it on debian (or other) with DISABLE_SQLALCHEMY_CEXT=1 pip install --no-binary sqlalchemy?
This will install slqlachemy without the compiled extension.

The reason that on debian and other system install with compiled extension is because we publish wheel with them active

[zzzeek]

we should also keep in mind that dict(row) is deprecated in any case. you can work around this now by using row._mapping.

[jvanasco]

Is SQL Alchemy doing things differently if a compiler is available?

@lindycoder There are some c-extensions.

Can you reproduce by installing it on debian (or other) with DISABLE_SQLALCHEMY_CEXT=1 pip install --no-binary sqlalchemy?

@CaselIT What about testing on alpine with g++ and disabling cext?

[lindycoder]

we should also keep in mind that dict(row) is deprecated in any case. you can work around this now by using row._mapping.

@zzzeek We started using this a couple weeks ago when we upgraded to a recent version of SQLAlchemy and this stopped working dict(row.items()).

I could work around using row._asdict() which returns _mapping :)

If this is deprecated, what is the official way of translating a row to a dict?

[CaselIT]

@CaselIT What about testing on alpine with g++ and disabling cext?

alpine will download the source distribution, then if it finds the compile it will compile the c-extensions, if it does not find the compile no c-extension is compile. the evn variable DISABLE_SQLALCHEMY_CEXT=1 disables compiling the c-extension even if the compiler is present

[CaselIT]

I could work around using row._asdict() which returns _mapping :)

_asdict is the official way. The idea is that in sa 1.4+ row behaves like a named tuple with the same underscore methods https://docs.python.org/3/library/collections.html#namedtuple-factory-function-for-tuples-with-named-fields

[lindycoder]

@jvanasco @CaselIT
I CAN reproduce on debian AND alpine with g++ installed with DISABLE_SQLALCHEMY_CEXT=1

Alpine

FROM python:3-alpine

RUN apk add --no-cache g++

RUN pip install --upgrade pip && pip install greenlet

RUN DISABLE_SQLALCHEMY_CEXT=1 pip install sqlalchemy

RUN echo $'\
from sqlalchemy import create_engine \n\
from sqlalchemy.orm import sessionmaker \n\
\n\
session = sessionmaker(bind=create_engine("sqlite://"))() \n\
result = session.execute("select 1, 2, 3").fetchall() \n\
row = result[0] \n\
\n\
print(dict(row)) \n\
' > script.py

RUN python script.py

Debian

FROM python:3-slim-buster

RUN apt-get update && apt-get install -y g++

RUN pip install --upgrade pip && pip install greenlet --no-binary :all:

RUN DISABLE_SQLALCHEMY_CEXT=1 pip install sqlalchemy --no-binary :all:

RUN echo '\
from sqlalchemy import create_engine \n\
from sqlalchemy.orm import sessionmaker \n\
\n\
session = sessionmaker(bind=create_engine("sqlite://"))() \n\
result = session.execute("select 1, 2, 3").fetchall() \n\
row = result[0] \n\
\n\
print(dict(row)) \n\
' > script.py

RUN python script.py

[zzzeek]

we should also keep in mind that dict(row) is deprecated in any case. you can work around this now by using row._mapping.

@zzzeek We started using this a couple weeks ago when we upgraded to a recent version of SQLAlchemy and this stopped working dict(row.items()).

I could work around using row._asdict() which returns _mapping :)

If this is deprecated, what is the official way of translating a row to a dict?

_asdict() or _mapping

https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Row._mapping

[CaselIT]

@zzzeek we seem to have a bug in the documentation. _asdict is documented in the code but is not in the docs https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=row#sqlalchemy.engine.Row

[CaselIT]

@zzzeek the python implementation should match the c ones in this case right?

[zzzeek]

@zzzeek we seem to have a bug in the documentation. _asdict is documented in the code but is not in the docs https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=row#sqlalchemy.engine.Row

well it's part of namedtuple so...sure it should be a method that's doc'ed

[zzzeek]

@zzzeek the python implementation should match the c ones in this case right?

you mean the behavior? sure

[lindycoder]

So I see documentation says .keys() is deprecated, which seems to be what dict() is relying on, from what i can tell.

I'm okay with closing this issue, maybe that method could spit our warnings?

[zzzeek]

dict(row) for the legacy row should work so we should figure out the source of the issue. if C exts have a problem then this would not be the only problem

[CaselIT]

The issue in in the python code. The c code works with dict(row), python one's does not

[zzzeek]

oh. OK I saw all the "Cant reproduce" and assumed it was weirder than that.

reproduces right away w/ python code!

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

session = sessionmaker(bind=create_engine("sqlite://"))()
result = session.execute("select 1, 2, 3").fetchall()
row = result[0]
print(dict(row))

[zzzeek]

oh ok. OK. sorry. so what's happening here is that Session.execute() is returning the future version of Result in all cases. this actually was changed on purpose as session.execute() was trying to be consistent with how ORM results are returned, but at the same time I don't know if that was a good idea. it looks like I might not have documented this very well and also the C extensions have probably been covering this up, as they don't seem to be expressing the same limitations.

This is a hard one because if the given statement has ORM entities, you get the new style row back, and it's hard for people to tell between:

result = session.execute(select(User))

result = session.execute(select(user_table))

the idea is that if those two returned different kinds of Row objects that would really confuse people. So maybe the Python version of row should still have a fallback here, seeing that the C one seems to.

[zzzeek]

there's no 2.0 deprecation warning either. this is pretty broken

[zzzeek]

pretty broken, the C BaseRow uses BaseRow_subscript_mapping as the universal __getitem__ , it is not distinguishing for some reason

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Fix LegacyRow/Row index access https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2721

[zzzeek]

alright well i have somethign to work on for 1.4.7

[VijethKashyapTR]

Will upgrading to 1.4.7 fix this?

I am facing the exact issue today with 1.4.2 version. In AWS environment sqlalchemy deployed in AWS Lambda itself works fine if I unpack the CursorResult like {**row}

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

But same thing fails if I deploy sqlalchemy 1.4.2(same version) in Layers instead of Lambda directly. Is there any fix for this?

[CaselIT]

Is there any fix for this?

Have you tried updating sqlalchemy? 1.4.2 is quite old, and early versions of the 1.4 series had a few bugs that were fixed in later releases

[VijethKashyapTR]

Is there any fix for this?

Have you tried updating sqlalchemy? 1.4.2 is quite old, and early versions of the 1.4 series had a few bugs that were fixed in later releases

I'll try to update to 1.4.7 and retry, thank you!

[CaselIT]

Any reason for not using the last version on the 1.4 sereis?

[jvanasco]

For context on @CaselIT’s comment, 1.4.40 is the latest version in the 1.4 series.

[VijethKashyapTR]

Any reason for not using the las version on the 1.4 sereis?

Nothing particularly, will update it, thanks

[m00dawg]

Old issue, I apologize, but I just encountered this in a very surprising way wherein I tried to deploy my app to my webserver and it broke in horrible ways due to this error on the 2.0 branch. Reverting to 1.4 fixes this but I'm not sure what the proper fix should be? Is there an example of changes that need to be done between 1.4 and 2.0?

[lelit]

Maybe _asdict() helps?

I'd suggest reading the excellent migration notes, and in particular the Result section. There you can find an alternative to the above.