SQLAlchemy mapping a table union to a class

Question

I'm using SQLAlchemy to query a number of similar tables, and union the results. The tables are rows of customer information, but our current database structures it so that different groups of customers are in their own tables e.g. client_group1, client_group2, client_group3:

client_group1:
| id |     name    |        email        |
| 1  |  john       | johnsmith@gmail.com |
| 2  |  greg       | gregjones@gmail.com |

Each of the other tables have identical columns. If I'm using SQLAlchemy declarative_base, I can have a class for client_group1 like the following:

def ClientGroup1(Base):
    __tablename__ = 'client_group1'
    __table_args__ = {u'schema': 'clients'}

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    email = Column(String(32))

Then I can do queries such as:

session.query(ClientGroup1.name)

However, if I use union_all to combine a bunch of client tables into a viewport, such as:

query1 = session.query(ClientGroup1.name)
query2 = session.query(ClientGroup2.name)
viewport = union_all(query1, query2)

then I'm not sure how to map a viewport to an object, and instead I have to access viewport columns using:

viewport.c.name

Is there any way to map the viewport to a specific table structure? Especially considering the fact that each class points to a different __table_name__

Will you be only reading/querying data or also creating/updating? — van, Mar 23 '15 at 18:01
Just reading/querying, no plans to use the table for creating/updating purposes. — J3Y, Mar 23 '15 at 19:55

score 3 · Accepted Answer · edited May 23 '17 at 11:44

Read Concrete Table Inheritance documentation for the idea how this can be done. The code below is a running example of how this can be done:

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker, configure_mappers
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import AbstractConcreteBase

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(engine)


class ClientGroupBase(AbstractConcreteBase, Base):
    pass


class ClientGroup1(ClientGroupBase):
    __tablename__ = 'client_group1'
    # __table_args__ = {'schema': 'clients'}
    __mapper_args__ = {
        'polymorphic_identity': 'client_group1',
        'concrete': True,
    }

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    email = Column(String(32))


class ClientGroup2(ClientGroupBase):
    __tablename__ = 'client_group2'
    # __table_args__ = {'schema': 'clients'}
    __mapper_args__ = {
        'polymorphic_identity': 'client_group2',
        'concrete': True,
    }

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    email = Column(String(32))


def _test_model():
    # generate classes for all tables
    Base.metadata.create_all()
    print('-'*80)

    # configure mappers (see documentation)
    configure_mappers()
    print('-'*80)

    # add some test data
    session.add(ClientGroup1(name="name1"))
    session.add(ClientGroup1(name="name1"))
    session.add(ClientGroup2(name="name1"))
    session.add(ClientGroup2(name="name1"))
    session.commit()
    print('-'*80)

    # perform a query
    q = session.query(ClientGroupBase).all()
    for r in q:
        print(r)


if __name__ == '__main__':
    _test_model()

The above example has an added benefit that you can also create new objects, as well as query only some tables.

You could do it mapping an SQL VIEW to a class, but you need to specify a primary key explicitly (see Is possible to mapping view with class using mapper in SqlAlchemy?). In you case, I am afraid, this might not work because of the same PK value in multiple tables, and using a multi-column PK might not be the best idea.

SQLAlchemy mapping a table union to a class

1 Answers1