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, Mar 07 '12 at 00: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, Mar 07 '12 at 06:17

stephan · Accepted Answer · 2020-08-10T16:29:53.510

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 '20 at 16:29

answered Mar 07 '12 at 07:31

stephan

10,104
1
51
64

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 – Paul Mar 07 '12 at 14:49
@Paul: ah, this functionality is only available since SA 0.6. I have tested it with 0.7.5 – stephan Mar 07 '12 at 14:51
Very nice answer. I really needed this solution. I have this question to you. http://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy Is your method the best way to go here. As in create table first and then do the insert from select? – Ranjith Ramachandra Jun 01 '15 at 14:10
1

@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](http://docs.sqlalchemy.org/en/latest/core/compiler.html#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). – stephan Jun 02 '15 at 05:01
Thanks for the inputs Stephan – Ranjith Ramachandra Jun 02 '15 at 06:17
How does `cloneTable` compare to the function `s_create_table_as` in http://stackoverflow.com/a/19054719/656833 ? – Conrad.Dean Dec 10 '15 at 12:52
@Conrad.Dean: a `CREATE TABLE AS` copies the data and some parts of the table structure (e.g. types), but usually not the constraints (e.g. primary keys), see the OP problem. The approach above first copies (some of) the table structure and then fills in the data. It is more like two SQL statements `CREATE TABLE LIKE` followed by `INSERT INTO SELECT` – stephan Dec 11 '15 at 09:50
@Conrad.Dean: also see my comment to @Ranjith's comment above (and the answer to his linked question). If you don't need the complete table structure (e.g. the primary key) like the OP, `CREATE TABLE AS` might be more straightforward. – stephan Dec 11 '15 at 10:23
Can someone help me with this question?https://stackoverflow.com/questions/50846075/how-to-clone-selected-constraints-in-sqlalchemy-table – mad_ Jun 14 '18 at 12:44
1

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. – Kevin Kraft Aug 07 '20 at 16:49
@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). – stephan Aug 10 '20 at 16:22

creating a temporary table from a query using sqlalchemy orm

1 Answers1

Linked