Guest User

SQLAlchemy related attributes not filtered

a guest
Jan 26th, 2021
92
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 1.63 KB | None | 0 0
  1. #!/usr/bin/env python
  2.  
  3. from sqlalchemy import (
  4.     Boolean, Column,
  5.     ForeignKey, Integer,
  6.     String, create_engine,
  7. )
  8. from sqlalchemy.ext.declarative import declarative_base
  9. from sqlalchemy.orm import backref, joinedload, relationship, sessionmaker
  10.  
  11. engine = create_engine('sqlite:///:memory:', echo=True)
  12. Base = declarative_base()
  13. Session = sessionmaker(bind=engine)
  14.  
  15.  
  16. class Company(Base):
  17.     __tablename__ = 'companies'
  18.  
  19.     id = Column(Integer, primary_key=True)
  20.     name = Column(String)
  21.  
  22.  
  23. class User(Base):
  24.     __tablename__ = 'users'
  25.  
  26.     id = Column(Integer, primary_key=True)
  27.     name = Column(String)
  28.     company_id = Column(Integer, ForeignKey(Company.id))
  29.     is_active = Column(Boolean)
  30.  
  31.     company = relationship('Company', backref=backref('users', cascade='all'))
  32.  
  33.  
  34. if __name__ == '__main__':
  35.     Base.metadata.create_all(engine)
  36.     session = Session()
  37.  
  38.     acme = Company(name='ACME')
  39.     bob = User(name='Bob', is_active=True, company=acme)
  40.     alice = User(name='Alice', is_active=False, company=acme)
  41.  
  42.     session.add(bob)
  43.     session.add(alice)
  44.     session.flush()
  45.     session.expire_all()
  46.  
  47.     company_with_active: Company = (
  48.         session.query(Company)
  49.             .join(User)
  50.             .options(joinedload(Company.users))
  51.             .filter(
  52.                 Company.name == 'ACME',
  53.                 User.is_active.is_(True),
  54.             )
  55.             .one()
  56.     )
  57.  
  58.     print('\n'*3)
  59.     print('Company with active users:')
  60.     print('\n'*3)
  61.     print(company_with_active.name)
  62.     for user in company_with_active.users:
  63.         user: User
  64.         print(user.name)
  65.  
Advertisement
Add Comment
Please, Sign In to add comment