Mapping a 'fake' object in SQLAlchemy

Question

I'm not sure what this is called since it is new to me, but here is what I want to do:

I have two tables in my database: TableA and TableB. TableA has pk a_id and another field called a_code. TableB has pk b_id and another field called b_code.

I have these tables mapped in my sqlalchemy code and they work fine. I want to create a third object called TableC that doesn't actually exist in my database, but that contains combinations of a_code and b_code, something like this:

class TableC:
  a_code = String
  b_code = String

Then I'd like to query TableC like:

TableC.query.filter(and_(
        TableC.a_code == x,
        TableC.b_code == y)).all()

Question 1) Does this type of thing have a name? 2) How do I do the mapping (using declarative would be nice)?

Answer 1

I don't really have a complete understanding of the query you are trying to express, weather it's a union or a join or some third thing, but that aside, it certainly is possible to map an arbitrary selectable (anything you can pass to a database that returns rows).

I'll start with the assumption that you want some kind of union of TableA and TableB, which would be all of the rows in A, and also all of the rows in B. This is easy enough to change to a different concept if you reveal more information about the shape of the data you are expressing.

We'll start by setting up the real tables, and classes to map them, in the declarative style.

from sqlalchemy import *
import sqlalchemy.ext.declarative

Base = sqlalchemy.ext.declarative.declarative_base()

class TableA(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    a_code = Column(String)

class TableB(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    b_code = Column(String)

Since we've used declarative, we don't actually have table instances to work from, which is neccesary for the next part. There are many ways to access the tables, but the way I prefer is to use sqlalchemy mapping introspection methods, since that will work no matter how the class was mapped.

from sqlalchemy.orm.attributes import manager_of_class
a_table = manager_of_class(TableA).mapper.mapped_table
b_table = manager_of_class(TableB).mapper.mapped_table

Next, we need an actual sql expression that represents the data we are interested in. This is a union, which results in columns that look the same as the columns defined in the first class, id and a_code. We could rename it, but that's not a very important part of the example.

ab_view_sel = sqlalchemy.alias(a_table.select().union(b_table.select()))

Finally, we map a class to this. It is possible to use declarative for this, but it's actually more code to do it that way instead of classic mapping style, not less. Notice that the class inherits from object, not base

class ViewAB(object):
    pass

sqlalchemy.orm.mapper(ViewAB, ab_view_sel)

And that's pretty much it. Of course there are some limitations with this; the most obvious being there's no (trivial) way to save instances of ViewAB back to the database.