association_table is not needed here, forgot to remove it, when minimizing
the code, sorry.
On Thursday, 30 November 2017 16:41:05 UTC+3, Анатолий Иванов wrote:
>
> Hello everyone!
>
>
> *TL;DR:*
> A mapping (class A) has a relationship(to class B). Lazy load is
> specified. I want to
>
> 1. manually select and filter data from B (the result lets call B')
> 2. select everything from A
> 3. 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?
>
> *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.
