diff options
Diffstat (limited to 'examples/inheritance/joined.py')
| -rw-r--r-- | examples/inheritance/joined.py | 111 |
1 files changed, 65 insertions, 46 deletions
diff --git a/examples/inheritance/joined.py b/examples/inheritance/joined.py index 6e0205e04..f9322158e 100644 --- a/examples/inheritance/joined.py +++ b/examples/inheritance/joined.py @@ -1,66 +1,79 @@ """Joined-table (table-per-subclass) inheritance example.""" -from sqlalchemy import Table, Column, Integer, String, \ +from sqlalchemy import Column, Integer, String, \ ForeignKey, create_engine, inspect, or_ from sqlalchemy.orm import relationship, Session, with_polymorphic from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() + class Company(Base): __tablename__ = 'company' id = Column(Integer, primary_key=True) name = Column(String(50)) - employees = relationship("Person", - backref='company', - cascade='all, delete-orphan') + employees = relationship( + "Person", + back_populates='company', + cascade='all, delete-orphan') def __repr__(self): return "Company %s" % self.name + class Person(Base): __tablename__ = 'person' id = Column(Integer, primary_key=True) - company_id = Column(Integer, ForeignKey('company.id')) + company_id = Column(ForeignKey('company.id')) name = Column(String(50)) type = Column(String(50)) + company = relationship("Company", back_populates="employees") + __mapper_args__ = { - 'polymorphic_identity':'person', - 'polymorphic_on':type + 'polymorphic_identity': 'person', + 'polymorphic_on': type } + def __repr__(self): return "Ordinary person %s" % self.name + class Engineer(Person): __tablename__ = 'engineer' - id = Column(Integer, ForeignKey('person.id'), primary_key=True) + id = Column(ForeignKey('person.id'), primary_key=True) status = Column(String(30)) engineer_name = Column(String(30)) primary_language = Column(String(30)) __mapper_args__ = { - 'polymorphic_identity':'engineer', + 'polymorphic_identity': 'engineer', } + def __repr__(self): - return "Engineer %s, status %s, engineer_name %s, "\ - "primary_language %s" % \ - (self.name, self.status, - self.engineer_name, self.primary_language) + return ( + "Engineer %s, status %s, engineer_name %s, " + "primary_language %s" % + ( + self.name, self.status, + self.engineer_name, self.primary_language) + ) + class Manager(Person): __tablename__ = 'manager' - id = Column(Integer, ForeignKey('person.id'), primary_key=True) + id = Column(ForeignKey('person.id'), primary_key=True) status = Column(String(30)) manager_name = Column(String(30)) __mapper_args__ = { - 'polymorphic_identity':'manager', + 'polymorphic_identity': 'manager', } + def __repr__(self): - return "Manager %s, status %s, manager_name %s" % \ - (self.name, self.status, self.manager_name) + return "Manager %s, status %s, manager_name %s" % ( + self.name, self.status, self.manager_name) engine = create_engine('sqlite://', echo=True) @@ -73,18 +86,21 @@ c = Company(name='company1', employees=[ name='pointy haired boss', status='AAB', manager_name='manager1'), - Engineer(name='dilbert', + Engineer( + name='dilbert', status='BBA', engineer_name='engineer1', primary_language='java'), Person(name='joesmith'), - Engineer(name='wally', - status='CGG', - engineer_name='engineer2', - primary_language='python'), - Manager(name='jsmith', - status='ABA', - manager_name='manager2') + Engineer( + name='wally', + status='CGG', + engineer_name='engineer2', + primary_language='python'), + Manager( + name='jsmith', + status='ABA', + manager_name='manager2') ]) session.add(c) @@ -93,8 +109,8 @@ session.commit() c = session.query(Company).get(1) for e in c.employees: print(e, inspect(e).key, e.company) -assert set([e.name for e in c.employees]) == set(['pointy haired boss', - 'dilbert', 'joesmith', 'wally', 'jsmith']) +assert set([e.name for e in c.employees]) == set( + ['pointy haired boss', 'dilbert', 'joesmith', 'wally', 'jsmith']) print("\n") dilbert = session.query(Person).filter_by(name='dilbert').one() @@ -110,27 +126,30 @@ for e in c.employees: print(e) # query using with_polymorphic. -eng_manager = with_polymorphic(Person, [Engineer, Manager], aliased=True) -print(session.query(eng_manager).\ - filter( - or_(eng_manager.Engineer.engineer_name=='engineer1', - eng_manager.Manager.manager_name=='manager2' - ) - ).all()) - -# illustrate join from Company, -# We use aliased=True -# to help when the selectable is used as the target of a join. -eng_manager = with_polymorphic(Person, [Engineer, Manager], aliased=True) -print(session.query(Company).\ +eng_manager = with_polymorphic(Person, [Engineer, Manager]) +print( + session.query(eng_manager). + filter( + or_( + eng_manager.Engineer.engineer_name == 'engineer1', + eng_manager.Manager.manager_name == 'manager2' + ) + ).all() +) + +# illustrate join from Company. +# flat=True means the tables inside the "polymorphic join" will be aliased. +# not strictly necessary in this example but helpful for the more general +# case of joins involving inheritance hierarchies as well as joined eager +# loading. +eng_manager = with_polymorphic(Person, [Engineer, Manager], flat=True) +print( + session.query(Company). join( - eng_manager, - Company.employees + Company.employees.of_type(eng_manager) ).filter( - or_(eng_manager.Engineer.engineer_name=='engineer1', - eng_manager.Manager.manager_name=='manager2') + or_(eng_manager.Engineer.engineer_name == 'engineer1', + eng_manager.Manager.manager_name == 'manager2') ).all()) session.commit() - - |