On Thu, Nov 30, 2017 at 8:41 AM, Анатолий Иванов <dwh.ia...@gmail.com> 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+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. -- 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.
