Proper access to SQLAlchemy relationship

Question

The following sample code (based on SQLAlchemy ORM tutorial):

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func

engine = create_engine('sqlite:///:memory:')

Session = sessionmaker(bind=engine)

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", backref=backref('addresses', order_by=id))    

Base.metadata.create_all(engine)


if __name__ == "__main__":

    session = Session()
    query = session.query(func.count(User.id)).join(User.addresses)

runs well on SQLAlchemy version prior to 0.8. But for version >= 0.8, it produces the following error:

AttributeError: type object 'User' has no attribute 'addresses'

This will not happen in case the last line is:

    query = session.query(User).join(User.addresses)

However, the usage of the func expression is the preferred way to issue 'count' queries according to SQLAlchemy documentation (and see also zzzeek's answer here). And it really matters in case of large tables.

So, I understand that this problem happens because of the new way relationships work since version 0.8. The question is, what should be the proper (and the most efficient) way to 'enforce' backref initialization.

Note: After the first query issued with the mapped class as an argument, the backrefs will be initialized, so that all other queries will not face the problem described above. The point is, that when issuing the func.count query, I don't know whether the backrefs were already initialized or not (i.e. this is the first time a query is issued).

score 1 · Answer 1 · edited Feb 07 '19 at 17:26

Your example is exactly the same as that in the Linking Relationships with Backref section of SQLAlchemy documentation. Second code block shows the equivalent version using back_populates argument. But in this case, both sides of the relationship are initialized and your query should work. With order_by your model should look like:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    addresses = relationship("Address", order_by="Address.id", back_populates="user")


class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship( "User", back_populates="addresses")

Thanks. I can also declare the relationship on the 'User' class (this way it will be *exactly* the same as in the docs) and it will do the job. Just wondering if this is the way it should work, after all in the previous versions it worked out of the box without all these tweaks... — urim, Sep 24 '15 at 06:42

Proper access to SQLAlchemy relationship

1 Answers1