Hi Mike! Thanks for answering. In fact i tried that same code as you posted and it did eager load of original (not my subquery) data. However, here <https://kite.com/docs/python;sqlalchemy.orm.contains_eager> i found a mention that contains_eager has an alias parameter, so doing s.query(A).join(all_b).options(contains_eager(A.b, alias=all_b)).all() helped.
Thanks for helping sort that out! Anatoly On Thursday, 30 November 2017 18:22:06 UTC+3, Mike Bayer wrote: > > On Thu, Nov 30, 2017 at 8:41 AM, Анатолий Иванов <dwh....@gmail.com > <javascript:>> wrote: > > Hello everyone! > > > > > > TL;DR: > > A mapping (class A) has a relationship(to class B). Lazy load is > specified. > > I want to > > > > manually select and filter data from B (the result lets call B') > > select everything from A > > outer join A with B' > > > > Problem: when doing > > > > all_b = session.query(B).filter(B.value == 1).subquery() > > all_a = session.query(A).join(all_b).all() > > > > it performs correct SQL request, although, when accessing all_a.b > performs > > an extra SQL request and selects all B (not B'). How do i make select > with > > the joined data from my subquery instead of selecting whole data from > the > > database? > > > you need to use contains_eager() for that: > > s.query(A).join(all_b).options(contains_eager(A.b)).all() > > docs at > http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=contains_eager#contains-eager > > > > > > > > > Long with working code: > > > > I do some kind of scanner management. Each IP can have several open > ports. > > > > import datetime > > from sqlalchemy import create_engine > > from sqlalchemy.orm import sessionmaker > > from sqlalchemy.pool import NullPool > > from sqlalchemy.orm import aliased, joinedload > > > > from sqlalchemy.orm import relationship > > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy import Column, String, ForeignKey, Table, Integer, > DateTime > > > > > > Base = declarative_base() > > > > association_table = Table( > > '_association', Base.metadata, > > Column('ip_id', Integer, ForeignKey('_ips.ip_id')), > > Column('host_id', Integer, ForeignKey('_hosts.host_id')) > > ) > > > > class Sessions(object): > > > > def __init__(self): > > self.engine = create_engine('postgresql://127.0.0.1/black', > > poolclass=NullPool, echo=True) > > self.session_builder = sessionmaker(bind=self.engine, > > expire_on_commit=False) > > > > def get_new_session(self): > > session = self.session_builder() > > > > return session > > > > def destroy_session(self, session): > > session.close() > > > > class IPDatabase(Base): > > __tablename__ = '_ips' > > > > ip_id = Column(Integer, primary_key=True, autoincrement=True) > > > > # The hostnames that point to this IP > > hostnames = relationship( > > "HostDatabase", > > secondary=association_table, > > back_populates="ip_addresses", > > lazy='select' > > ) > > > > # Open ports > > ports = relationship('PortDatabase', cascade="all, delete-orphan", > > lazy='select') > > date_added = Column(DateTime, default=datetime.datetime.utcnow) > > > > def __repr__(self): > > return """ IP=(id={}, ports={}) """.format(self.ip_id, > self.ports) > > > > > > class PortDatabase(Base): > > __tablename__ = '_ports' > > > > ports_id = Column(Integer, primary_key=True, autoincrement=True) > > > > data = Column(String) > > port_number = Column(Integer) > > > > date_added = Column(DateTime, default=datetime.datetime.utcnow) > > > > > > # The name of the related project > > target = Column( > > Integer, ForeignKey('_ips.ip_id', ondelete='CASCADE') > > ) > > > > > > def __repr__(self): > > return """ Port=(id={}, port={}) """.format(self.ports_id, > > self.port_number) > > > > > > def create(): > > sessions = Sessions() > > > > Base.metadata.drop_all(sessions.engine) > > Base.metadata.create_all(sessions.engine, checkfirst=True) > > > > session_spawner = Sessions() > > session = session_spawner.get_new_session() > > > > ip_1 = IPDatabase() > > ip_2 = IPDatabase() > > ip_3 = IPDatabase() > > > > port_1 = PortDatabase(port_number=80) > > port_2 = PortDatabase(port_number=80) > > port_3 = PortDatabase(port_number=443) > > > > ip_1.ports.append(port_1) > > ip_1.ports.append(port_2) > > ip_1.ports.append(port_3) > > > > session.add(ip_1) > > session.add(ip_2) > > session.add(ip_3) > > > > session.add(port_1) > > session.add(port_2) > > session.add(port_3) > > > > session.commit() > > > > session_spawner.destroy_session(session) > > > > def main(): > > session_spawner = Sessions() > > session = session_spawner.get_new_session() > > > > subq = session.query(PortDatabase).filter(PortDatabase.port_number > == > > 80).subquery() > > > > ips = session.query(IPDatabase).join(subq).all() > > > > session_spawner.destroy_session(session) > > > > print(ips) # This will fail, as 'ports' were not loaded due to > > lazyload='select' > > > > if __name__ == '__main__': > > main() > > > > > > Any help would be really great, as i have read tons of manuals and other > > resources, but still cannot find the solution. > > > > Thanks, > > Anatoly > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
