How to create a new table from select statement in sqlalchemy?

Question

I am using sqlalchemy's core features to write some abstraction layer. The layer itself needs to be able to create tables from select statements.

Sample code:

metadata = MetaData(bind=engine)
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
s = select(table).where(table.c.column_1 > 10)

Now what I want to be able to do is create a new table from the select statement above. How can I do it?

note: This question has also been asked by me at https://groups.google.com/forum/#!topic/sqlalchemy/lKDkX68fOqI. Sorry for cross-posting

Only way I can think of is to compile the above select statement with parameters bound to it. The construct SQL manually and then do the create table

For example:

sql = str(s.compile(dialect=postgresql.dialect(),
                    compile_kwargs={"literal_binds": True}
                    )
          )

create_tbl_sql = "CREATE TABLE {0} AS {2}".format(new_table_name, sql)

connection.execute(create_tbl_sql)

Also the literal bind compile is a pain when datetime objects are involved in the where condition. Pretty much whenever something that can not be serialised exists in the query. I will have to work around that.

It does not seem clean to take this approach. So I am asking the community at large if they know something better.

Answer 1

Normally we'd subclass DDLElement, but as you'd like to use bound parameters, we'll stay within the SQL element system:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement, Executable


class CreateTableAs(Executable, ClauseElement):

    def __init__(self, name, query):
        self.name = name
        self.query = query


@compiles(CreateTableAs, "postgresql")
def _create_table_as(element, compiler, **kw):
    return "CREATE TABLE %s AS %s" % (
        element.name,
        compiler.process(element.query)
    )

if __name__ == '__main__':
    from sqlalchemy import Table, Column, Integer, \
        MetaData, create_engine, select

    m = MetaData()
    t = Table('t', m, Column('x', Integer), Column('y', Integer))

    e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
    c = e.connect()
    trans = c.begin()

    t.create(c)
    c.execute(
        t.insert(),
        [{"x": 1, "y": 2}, {"x": 11, "y": 3}]
    )

    s = select([t]).where(t.c.x > 10)

    c.execute(CreateTableAs('t2', s))

    assert c.execute("select x, y from t2").fetchall() == [(11, 3)]
    trans.rollback()

output:

2015-06-01 11:42:55,982 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-01 11:42:55,982 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE t (
    x INTEGER, 
    y INTEGER
)


2015-06-01 11:42:55,982 INFO sqlalchemy.engine.base.Engine {}
2015-06-01 11:42:55,985 INFO sqlalchemy.engine.base.Engine INSERT INTO t (x, y) VALUES (%(x)s, %(y)s)
2015-06-01 11:42:55,986 INFO sqlalchemy.engine.base.Engine ({'y': 2, 'x': 1}, {'y': 3, 'x': 11})
2015-06-01 11:42:55,988 INFO sqlalchemy.engine.base.Engine CREATE TABLE t2 AS SELECT t.x, t.y 
FROM t 
WHERE t.x > %(x_1)s
2015-06-01 11:42:55,988 INFO sqlalchemy.engine.base.Engine {'x_1': 10}
2015-06-01 11:42:55,996 INFO sqlalchemy.engine.base.Engine select x, y from t2
2015-06-01 11:42:55,996 INFO sqlalchemy.engine.base.Engine {}
2015-06-01 11:42:55,997 INFO sqlalchemy.engine.base.Engine ROLLBACK