creating a temporary table from a query using sqlalchemy orm

Question

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?

Just curios.. would it work if you create definition of temp table with first approach? for example create table tablename (id primarykey, column1) Insert into tablename select id, column2 from existing table where... — AJP
– AJP, Commented Mar 7, 2012 at 0:57
@AJP Yes, I think this is the answer. Unfortunately, no SA ORM equivalent of INSERT INTO and no easy way for me to compile my relatively complicated query into a sql statement. — Paul
– Paul, Commented Mar 7, 2012 at 6:17

stephan · Accepted Answer · 2020-08-10 16:29:53Z

16

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)

edited Aug 10, 2020 at 16:29

answered Mar 7, 2012 at 7:31

stephan

10.3k1 gold badge55 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Paul Over a year ago

Unfortunately, I get an importerror when running this : ImportError: cannot import name UpdateBase. Similar import error using the InsertFromSelect example. I'm using SA 0.5.7

stephan Over a year ago

@Paul: ah, this functionality is only available since SA 0.6. I have tested it with 0.7.5

stephan Over a year ago

@Ranjith: for this case, I'd probably create my own custom SQL Construct SelectInto (similar to the InsertFromSelect above) that leverages the available syntax in most SQL dialects with dialect-specific compilation rules (e.g. SELECT * INTO NewTable FROM OldTable for SQL Server or CREATE TABLE NewTable AS SELECT * FROM OldTable in Oracle or SQLite). You could also look into materialized views (Oracle) or indexed views (SQL Server).

Kevin Kraft Over a year ago

What exactly makes this table temporay? Isn't it just a regular table? If you name the table '#temp', then it is a temp table.

stephan Over a year ago

@KevinKraft almost 10 years later, someone notices that I skipped the temporary aspect and focused on the cloning of (most of) the table structure as asked by the OP ;) While I haven't worked on T-SQL for years, I guess nothing prevents you from temp = cloneTable('#temp', t, m). And for other dialects, you could add return Table(name, metadata, *(cols + constraints), prefixes=['TEMPORARY']) or some such. You would have to put in some extra work to make it run cross-platform (which is why I skipped this, I guess).

|

Collectives™ on Stack Overflow

creating a temporary table from a query using sqlalchemy orm

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related