CODEWITHSUNDEEP
pythonsqlalchemy
urim
urim

Reputation: 591

Proper access to SQLAlchemy relationship

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

Upvotes: 0

Views: 1413

Answers (1)

van
van

Reputation: 76962

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

Upvotes: 1

Related Questions