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