A solution to SQLAlchemy temporary table pain?

Question

It seems like the biggest drawback with SQLAlchemy is that it takes several steps backwards when it comes to working with temporary tables. A very common use case, for example, is to create a temporary table that is very specific to one task, throw some data in it, then join against it.

For starters, declaring a temporary table is verbose, and limited. Note that in this example I had to edit it because my classes actually inherit a base class, so what I give here may be slightly incorrect.

@as_declarative(metaclass=MetaBase)
class MyTempTable(object):

    __tablename__ = "temp"
    __table_args__ = {'prefixes': ['TEMPORARY']}

    id = Column(Integer(), primary_key=True)
    person_id = Column(BigInteger())
    a_string = Column(String(100))

Creating it is unintuitive:

MyTempTable.__table__.create(session.bind)

I also have to remember to explictly drop it unless I do something creative to get it to render with ON COMMIT DROP:

MyTempTable.__table__.drop(session.bind)

Also, what I just gave doesn't even work unless the temporary table is done "top level". I still haven't fully figured this out (for lack of wanting to spend time investigating why it doesn't work), but basically I tried creating a temp table in this manner inside of a nested transaction using session.begin_nested() and you end up with an error saying the relation does not exist. However, I have several cases where I create a temporary table inside of a nested transaction for unit testing purposes and they work just fine. Checking the echo output, it appears the difference is that one renders before the BEGIN statement, while the other renders after it. This is using Postgresql.

What does work inside of a nested transaction, and quite frankly saves you a bunch of time, is to just type out the damned sql and execute it using session.execute.

        session.execute(text(
            "CREATE TEMPORARY TABLE temp ("
            "  id SERIAL,"
            "  person_id BIGINT,"
            "  a_string TEXT"
            ") ON COMMIT DROP;"
        ))

Of course, if you do this, you still need a corresponding table model to make use of ORM functionality, or have to stick to using raw sql queries, which defeats the purpose of SQLAlchemy in the first place.

I'm wondering if maybe I'm missing something here or if someone has come up with a solution that is a bit more elegant.

Answer 1

I use ORM with Core. ORM is reserved for higher-level operations. For large volumes of data and for temp tables Core is more handy. Example:

temptbl_name = 'temp_del_dup_pk_{}'.format(datestamp)
temptbl = Table(temptbl_name, metadata, Column('col1', Integer, index=True),..., extend_existing=True)
temptbl.create(engine)

Update Here is a simple function that can generate temp table ORM definition on the fly:

def temp_table(name, cols):
    args = dict(col1=Column(Integer, index=True),...)
    args['__tablename__'] = name
    args['__table_args__'] = dict(extend_existing=True)
    return type(name, (Base,), args)

It can be useful to mirror columns of an existing table:

def temp_table(name, base_table):
    args = {c.name:c.copy() for c in base_table.__table__.c}
    ...