creating a temporary table from a query using sqlalchemy orm

python, sql, sqlalchemy

Solution

It's not exactly ORM, but to create the table initially, I'd clone the table structure (see `cloneTable` in the example below). For copying the data, I then would use the InsertFromSelect example.

Edit: Since version 0.8.3, SqlAlchemy supports Insert.from_select() out of the box. Hence the InsertFromSelect class and the respective visitor in the example below can be directly replaced and are no longer needed. I leave the original example unchanged for historic reasons.

Here is a working example

from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import UpdateBase

class InsertFromSelect(UpdateBase):
    def __init__(self, table, select):
        self.table = table
        self.select = select

@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
    return "INSERT INTO %s %s" % (
        compiler.process(element.table, asfrom=True),
        compiler.process(element.select)
    )

def cloneTable(name, table, metadata):
    cols = [c.copy() for c in table.columns]
    constraints = [c.copy() for c in table.constraints]
    return Table(name, metadata, *(cols + constraints))

# test data
from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine
e = create_engine('sqlite://')
m = MetaData(e)
t = Table('t', m, Column('id', Integer, primary_key=True),
          Column('number', Integer))
t.create()
e.execute(t.insert().values(id=1, number=3))
e.execute(t.insert().values(id=9, number=-3))

# create temp table
temp = cloneTable('temp', t, m)
temp.create()

# copy data
ins = InsertFromSelect(temp, t.select().where(t.c.id>5))
e.execute(ins)

# print result
for r in e.execute(temp.select()):
    print(r)

Problem

I can create a temporary table this way: ``` session.execute("CREATE TABLE temptable SELECT existingtable.id, " "existingtable.column2 FROM existingtable WHERE existingtable.id<100000") ``` but the new table is unreadable because it says it has no primary key. `existingtable.id` is the primary key of exisitingtable, so I expected it to get the same treatment in the temp table. However, I would rather find some ORM way of doing this anyway. Given: ``` temp_table = Table('temptable', metadata, Column('id', Integer, primary_key=True), Column('column2', Integer), useexisting=True ) class TempTable(object): pass mapper(TempTable, temp_table) temp_table.create(bind=session.bind, checkfirst=True) if session.query(TempTable).delete(): #make sure it's empty session.commit() ``` How can I populate `temp_table` with some selected contents of `existingtable` without doing 100000 `session.query.add(TempTable(...))` commands? Or is there a way of creating the table from a query similar to the plain SQL version above?

Original source