1"""Single-table inheritance example.""" 2 3from sqlalchemy import MetaData, Table, Column, Integer, String, \ 4 ForeignKey, create_engine 5from sqlalchemy.orm import mapper, relationship, sessionmaker 6 7metadata = MetaData() 8 9# a table to store companies 10companies = Table('companies', metadata, 11 Column('company_id', Integer, primary_key=True), 12 Column('name', String(50))) 13 14employees_table = Table('employees', metadata, 15 Column('employee_id', Integer, primary_key=True), 16 Column('company_id', Integer, ForeignKey('companies.company_id')), 17 Column('name', String(50)), 18 Column('type', String(20)), 19 Column('status', String(20)), 20 Column('engineer_name', String(50)), 21 Column('primary_language', String(50)), 22 Column('manager_name', String(50)) 23) 24 25 26class Person(object): 27 def __init__(self, **kwargs): 28 for key, value in kwargs.items(): 29 setattr(self, key, value) 30 def __repr__(self): 31 return "Ordinary person %s" % self.name 32class Engineer(Person): 33 def __repr__(self): 34 return "Engineer %s, status %s, engineer_name %s, "\ 35 "primary_language %s" % \ 36 (self.name, self.status, 37 self.engineer_name, self.primary_language) 38class Manager(Person): 39 def __repr__(self): 40 return "Manager %s, status %s, manager_name %s" % \ 41 (self.name, self.status, self.manager_name) 42class Company(object): 43 def __init__(self, **kwargs): 44 for key, value in kwargs.items(): 45 setattr(self, key, value) 46 def __repr__(self): 47 return "Company %s" % self.name 48 49person_mapper = mapper(Person, employees_table, 50 polymorphic_on=employees_table.c.type, 51 polymorphic_identity='person') 52manager_mapper = mapper(Manager, inherits=person_mapper, 53 polymorphic_identity='manager') 54engineer_mapper = mapper(Engineer, inherits=person_mapper, 55 polymorphic_identity='engineer') 56 57mapper(Company, companies, properties={ 58 'employees': relationship(Person, lazy=True, backref='company') 59}) 60 61 62engine = create_engine('sqlite:///', echo=True) 63 64metadata.create_all(engine) 65 66session = sessionmaker(engine)() 67 68c = Company(name='company1') 69c.employees.append(Manager(name='pointy haired boss', status='AAB', 70 manager_name='manager1')) 71c.employees.append(Engineer(name='dilbert', status='BBA', 72 engineer_name='engineer1', primary_language='java')) 73c.employees.append(Person(name='joesmith', status='HHH')) 74c.employees.append(Engineer(name='wally', status='CGG', 75 engineer_name='engineer2', primary_language='python' 76 )) 77c.employees.append(Manager(name='jsmith', status='ABA', 78 manager_name='manager2')) 79session.add(c) 80session.commit() 81 82c = session.query(Company).get(1) 83for e in c.employees: 84 print(e, e.company) 85 86print("\n") 87 88dilbert = session.query(Person).filter_by(name='dilbert').one() 89dilbert2 = session.query(Engineer).filter_by(name='dilbert').one() 90assert dilbert is dilbert2 91 92dilbert.engineer_name = 'hes dibert!' 93 94session.flush() 95session.expunge_all() 96 97c = session.query(Company).get(1) 98for e in c.employees: 99 print(e) 100 101session.delete(c) 102session.commit() 103