Strange issue in SQLAlchemy

Question

Consider the following code which creates a very simple table (without using SQLAlchemy), then adds an entry to it using SQLAlchemy ORM and retrieves it:

import sqlite3
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DB_PATH = '/tmp/tst.db'

#create a DB
sqlite_conn = sqlite3.connect(DB_PATH)
sqlite_conn.execute('''CREATE TABLE tst (
    id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
    c0 INTEGER,
    c1 INTEGER
);''')
sqlite_conn.commit()

#intialize an SA engine/session/mapped class
engine = create_engine('sqlite:///{}'.format(DB_PATH))
Base = declarative_base()
Base.metadata.reflect(bind=engine)
Session = sessionmaker(bind=engine)

class Tst(Base):
    __table_name__ = 'tst'
    __table__ = Base.metadata.tables[__table_name__]
    columns = list(__table__.columns)
    field_names = [c.name for c in columns]

#add an entry to the table
session = Session()
inst = Tst()
session.add(inst)
session.commit()

#retrieve an entry from the table
session = Session()
inst = session.query(Tst).first()
print inst.c1

One may expect that the code above will just print 'None', as 'c1' was not assigned a value. Instead of it, I'm getting the following error message:

Traceback (most recent call last):
  File "...", line 39, in <module>
    print inst.c1
AttributeError: 'Tst' object has no attribute 'c1'

But if the following line will be removed/commented:

    field_names = [c.name for c in columns]

the output will be as expected.

In general, it looks like the iteration over Table.columns inside the class definition will cause the last column to be omitted from the class instances.

Following this answer, I actually changed the code to use Inspector, and it worked fine. However, AFAIK, accessing Table.columns is completely legitimate, so I wanted to understand whether it's buggy behavior or something wrong on my side.

P.S. tested with SQLAlchemy 1.1.9

P.P.S. the issue doesn't appear to be related to a specific DB dialect - reproduced with MySQL, sqlite.

score 2 · Accepted Answer · edited May 23 '17 at 12:17

This is more of a Python version issue than an SQLAlchemy issue. The root cause is the leaking of the name c from the list-comprehension in Python 2. It becomes part of the namespace of the constructed class, and so SQLAlchemy sees it as if you were explicitly naming the last column in the list columns in your class definition. Your class definition is equivalent to:

class Tst(Base):
    __table_name__ = 'tst'
    __table__ = Base.metadata.tables[__table_name__]
    columns = list(__table__.columns)
    ...
    c = columns[-1]  # The last column of __table__

If you change your print statement to:

print inst.c

you'll get None as you expected. If you must have your field_names, you could for example remove the name from the namespace:

class Tst(Base):
    __table_name__ = 'tst'
    __table__ = Base.metadata.tables[__table_name__]
    columns = list(__table__.columns)
    field_names = [c.name for c in columns]
    del c

but this is unportable (and ugly) between Python 2 and 3, since the name would not actually exist in 3. You could also work around the issue with attrgetter():

from operator import attrgetter

class Tst(Base):
    __table_name__ = 'tst'
    __table__ = Base.metadata.tables[__table_name__]
    columns = list(__table__.columns)
    field_names = list(map(attrgetter('name'), columns))

or use a generator expression:

    field_names = list(c.name for c in columns)

Strange issue in SQLAlchemy

1 Answers1