python - SQLAlchemy declarative property from join (single attribute, not whole object) -

i wish create mapped attribute of object populated table.

using sqlalchemy documentation example, wish make user_name field exist on address class such can both queried , accessed (without second round trip database)

for example, wish able query , filter user_name address.query.filter(address.user_name == 'wcdolphin').first() , access user_name attribute of address objects, without performance penalty, , have persist writes expected of attribute in __tablename__

class user(base):     __tablename__ = 'users'      id = column(integer, primary_key=true)     name = column(string(50))     addresses = relation("address", backref="user")  class address(base):     __tablename__ = 'addresses'      id = column(integer, primary_key=true)     email = column(string(50))     user_name = column(integer, foreignkey('users.name'))#this line wrong 

how do this?

i found documentation relatively difficult understand, did not seem conform examples, flask-sqlalchemy examples.

you can join on query object, no need specify attribute directly. model like:

from sqlalchemy import create_engine, column, integer, string, foreignkey sqlalchemy.orm import sessionmaker, relation sqlalchemy.ext.declarative import declarative_base  base = declarative_base() engine = create_engine('sqlite:///') session = sessionmaker(bind=engine)  class user(base):     __tablename__ = 'users'     id = column(integer, primary_key=true)     name = column(string(50))     addresses = relation("address", backref="user")  class address(base):     __tablename__ = 'addresses'     id = column(integer, primary_key=true)     email = column(string(50))     user_id = column(integer, foreignkey("users.id"))   base.metadata.create_all(engine) 

a query after addresses filtering username looks like:

>>> session = session() >>> session.add(address(user=user(name='test'))) >>> session.query(address).join(user).filter(user.name == 'test').first() <__main__.address object @ 0x02db3730> 

edit: can directly access user address object, there no need directly referencing attribute address class:

>>> = session.query(address).join(user).filter(user.name == 'test').first() >>> a.user.name 'test'