CODEWITHSUNDEEP
pythonsqlsqlalchemypyramid
Spencer Cooley
Spencer Cooley

Reputation: 8851

How do I write a query to get sqlalchemy objects from relationship?

I am learning python and using the framework pyramid with sqlalchemy as the orm. I can not figure out how relationships work. I have 2 tables, offices and users. the foreign key is on the users table 'offices_id'. I am trying to do a query that will return to me what office a user is a part of.

This is how I have my models set up. 

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(255), unique=True, nullable=False)
    trec_number = Column(Unicode(255), unique=True, nullable=False)
    office_id = Column(Integer, ForeignKey('offices.id'))

class Office(Base):
    __tablename__ = 'offices'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(255), unique=True, nullable=False)
    address = Column(Unicode(255), unique=True, nullable=False)
    members = relationship("User", backref='offices')

In my view how would I write a query that would return the the office information for a given user?

I am trying this: 

for user in DBSession.query(User).join(Office).all():
    print user.address

but I think I am misunderstanding how the queries work because I keep getting errors

AttributeError: 'User' object has no attribute 'address'

when I do this: 

 for user in DBSession.query(User).join(Office).all():
    print user.name

it prints out the users name fine since name is an attribute of the User class.

I also can not get the inverse to work

for offices in DBSession.query(Office).join(User).all():
    print offices.users.name

Upvotes: 1

Views: 2316

Answers (1)

Matti John
Matti John

Reputation: 20477

You need to use the name you used in the backref argument to access the Office model. Try user.offices.address

Upvotes: 3

Related Questions