SQLAlchemy declarative property from join (separate attribute, not the entire object)

Question

I want to create a mapped attribute on an object that is populated from another table.

Using the SQLAlchemy documentation example, I want to create a user_name field in the Address class so that it can be easily requested and easily accessed (without a second round trip to the database)

For example, I want to be able to query and filter using user_name Address.query.filter(Address.user_name == 'wcdolphin').first() And also get access to the user_name attribute for all Address objects, without performance penalty, and have it stores the records correctly, as expected from the 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 is wrong 

How to do it?

I found that the documentation is relatively difficult to understand, since it does not seem to fit most of the examples, especially Flask-SQLAlchemy examples.

Answer 1

You can do this using the join of the request object, you do not need to specify this attribute directly. Thus, your model will look like this:

 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) 

The request after the addresses with filtering the user name is as follows:

 >>> session = Session() >>> session.add(Address(user=User(name='test'))) >>> session.query(Address).join(User).filter(User.name == 'test').first() <__main__.Address object at 0x02DB3730> 

Change Since you can directly access a user from an address object, there is no need to directly refer to the attribute of the Address class:

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

Answer 2

If you really want the address to have a version with the SQL code "User.name" enabled without the need for explicit participation, you need to use a correlated subquery. This will work in all cases, but tends to be inefficient on the database side (particularly with MySQL), so there may be a performance penalty on the SQL side compared to using a regular JOIN. Performing some EXPLAIN tests may help analyze how much of the effect might be.

Another example of correlated property_ column () is at http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-column-property .

For the set event, the correlated subquery is a read-only attribute, but the event can be used to intercept changes and apply them to the user's parent line. Two approaches to this are given below: one uses regular mechanics of identification cards, which will bear the load of the user's string, if it is not already present, the other, which emits direct UPDATE to the string:

 from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base= declarative_base() 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) user_id = Column(Integer, ForeignKey('users.id')) email = Column(String(50)) Address.user_name = column_property(select([User.name]).where(User.id==Address.id)) from sqlalchemy import event @event.listens_for(Address.user_name, "set") def _set_address_user_name(target, value, oldvalue, initiator): # use ORM identity map + flush target.user.name = value # use direct UPDATE #object_session(target).query(User).with_parent(target).update({'name':value}) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) s.add_all([ User(name='u1', addresses=[Address(email='e1'), Address(email='e2')]) ]) s.commit() a1 = s.query(Address).filter(Address.user_name=="u1").first() assert a1.user_name == "u1" a1.user_name = 'u2' s.commit() a1 = s.query(Address).filter(Address.user_name=="u2").first() assert a1.user_name == "u2"