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.
