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

declarative, orm, python, sqlalchemy

Solution

You can do this with a `join` on the query object, no need to specify this attribute directly. So your model would look like:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relation
from 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 with filtering the 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 at 0x02DB3730>

Edit: As you can directly access the user from an address object, there is no need for directly referencing an attribute to the Address class:

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

Problem

I wish to create a mapped attribute of an object which is populated from another table. Using the SQLAlchemy documentation example, I wish to make a user_name field exist on the Address class such that it can be both easily queried and easily accessed (without a second round trip to the database) For example, I wish to be able to query and filter by `user_name` `Address.query.filter(Address.user_name == 'wcdolphin').first()` And also access the `user_name` attribute of all Address objects, without performance penalty, and have it properly persist writes as would be expected of an attribute in the `__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 is wrong ``` How do I do this? I found the documentation relatively difficult to understand, as it did not seem to conform to most examples, especially the Flask-SQLAlchemy examples.

Original source