How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?

by

SQLalchemy doesn’t build this construct for you. You can use the query from text.

session.execute('INSERT INTO t1 (SELECT * FROM t2)')

EDIT:

More than one year later, but now on sqlalchemy 0.6+ you can create it:

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

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

@compiler.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)
    )

insert = InsertFromSelect(t1, select([t1]).where(t1.c.x>5))
print insert

Produces:

"INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z FROM mytable WHERE mytable.x > :x_1)"

Another EDIT:

Now, 4 years later, the syntax is incorporated in SQLAlchemy 0.9, and backported to 0.8.3; You can create any select() and then use the new from_select() method of Insert objects:

>>> from sqlalchemy.sql import table, column
>>> t1 = table('t1', column('a'), column('b'))
>>> t2 = table('t2', column('x'), column('y'))
>>> print(t1.insert().from_select(['a', 'b'], t2.select().where(t2.c.y == 5)))
INSERT INTO t1 (a, b) SELECT t2.x, t2.y
FROM t2
WHERE t2.y = :y_1

More information in the docs.

More Related Contents:

  1. How to serialize SqlAlchemy result to JSON?
  2. How to execute raw SQL in Flask-SQLAlchemy app
  3. SQLAlchemy: print the actual query
  4. SQLAlchemy create_all() does not create tables
  5. Query for list of attribute instead of tuples in SQLAlchemy
  6. How to give column name dynamically from string variable in sql alchemy filter?
  7. SQL Alchemy ORM returning a single column, how to avoid common post processing
  8. How do I execute inserts and updates in an Alembic upgrade script?
  9. SQLAlchemy, get object not bound to a Session
  10. Bulk insert with SQLAlchemy ORM
  11. How can I verify Column data types in the SQLAlchemy ORM?
  12. Flask sqlalchemy many-to-many insert data
  13. How to delete a record by id in Flask-SQLAlchemy
  14. How to upsert pandas DataFrame to Microsoft SQL Server table?
  15. How to do an upsert with SqlAlchemy?
  16. SQLAlchemy printing raw SQL from create()
  17. SQLAlchemy: engine, connection and session difference
  18. SQLAlchemy: how to filter date field?
  19. method of iterating over sqlalchemy model’s defined columns?
  20. flake8 complains on boolean comparison “==” in filter clause
  21. How to execute “left outer join” in SqlAlchemy
  22. SQLAlchemy – Mapping self-referential relationship as one to many (declarative form)
  23. flask-sqlalchemy or sqlalchemy
  24. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  25. How to set connection timeout in SQLAlchemy
  26. SQLAlchemy – can you add custom methods to the query object?
  27. Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?
  28. SQLAlchemy classes across files
  29. How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?
  30. flask sqlalchemy query with keyword as variable

Leave a Comment