Mapping a 'fake' object in SQLAlchemy

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 ma开发者_运维知识库pping (using declarative would be nice)?

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.

There isn't really a concept of 'virtual tables', but it is possible to send a single query that 'joins' the data from multiple tables. This is probably as close as you can get to what you want.

For example, one way to do this in sqlalchemy/elixir would be (and this isn't far off from what you've shown, we're just not querying a 'virtual' table):

result = session.query(TableA, TableB).filter(TableA.a_code==x).filter(TableB.b_code==y).all()

This is similar to an SQL inner join, with some qualifying conditions in the filter statements. This isn't going to give you an sqlalchemy table object, but will give you a list of objects from each real table.

It looks like SQLAlchemy allows you to map an arbitrary query to a class. e.g. From SQLAlchemy: one classes – two tables:

usersaddresses = sql.join(t_users, t_addresses,
                           t_users.c.id == t_addresses.c.user_id)
class UserAddress(object):
    def __repr__(self):
        return "<FullUser(%s,%s,%s)" % (self.id, self.name, self.address)

mapper(UserAddress, usersaddresses, properties={
        'id': [t_users.c.id, t_addresses.c.user_id],
        })

f = session.query(UserAddress).filter_by(name='Hagar').one()