41

In SQLAlchemy, how do I populate or update a table from a SELECT statement?

joeforker
  • 40,459
  • 37
  • 151
  • 246

3 Answers3

76

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.

Community
  • 1
  • 1
nosklo
  • 217,122
  • 57
  • 293
  • 297
  • Would you suggest session.execute('INSERT INTO t1 (%s)' % str(sqlalchemy_select_expression))? – joeforker Dec 04 '09 at 20:35
  • Sure, why not - don't need the `str()` though, since `%s` already does that. – nosklo Dec 05 '09 at 14:00
  • @hadrien: now you have http://www.sqlalchemy.org/docs/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct where you could compile it. – nosklo Jan 20 '11 at 19:14
  • 5
    I love that you've updated this over time, but it'd be nice if this were broken into latest to oldest, with the sqlalchemy version called out instead of the years between edits ;) – Conrad.Dean Oct 08 '15 at 20:24
  • And if you don't want to `select *` but rather `select x, y` only (out of many columns), one way is to `from sqlalchemy import select` and then you can use this one like `select([t2,x, t2,y])` instead of `t2.select()`. – Mahdi Jan 09 '17 at 09:50
  • @nosklo I have a doubt regarding https://stackoverflow.com/questions/71266107/sqlalchemy-relationships-row-in-child-table-is-dependent-on-two-parent-tables. Would you mind answering it? – NewToCoding Feb 28 '22 at 09:23
33

As of 0.8.3, you can now do this directly in sqlalchemy: Insert.from_select:

sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5)
ins = table2.insert().from_select(['a', 'b'], sel)
Meow
  • 1,207
  • 15
  • 23
David Fraser
  • 6,475
  • 1
  • 40
  • 56
  • 1
    Thanks. I'll add that into the original answer. – nosklo Aug 05 '13 at 14:42
  • How about scopped session? – TheRealChx101 Nov 09 '19 at 03:02
  • Do I need to import insert()? If yes, how do I import ? I get `AttributeError: type object 'table2' has no attribute 'insert'` – NewToCoding Feb 23 '22 at 14:30
  • @VamshiPulluri it sounds like your `table2` is a class, not a table object for SQL expressions. See `nosklo`'s answer below on constructing table objects (under *Another EDIT*) or follow the SQLAlchemy tutorial here: https://docs.sqlalchemy.org/en/14/core/tutorial.html#define-and-create-tables – David Fraser Feb 26 '22 at 10:22
1

As Noslko pointed out in comment, you can now get rid of raw sql: http://www.sqlalchemy.org/docs/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct

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

class InsertFromSelect(Executable, ClauseElement):
    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)
    )

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)
Hadrien
  • 1,479
  • 2
  • 14
  • 18
  • 3
    Now you don't have to create your own ClauseElement. You can use the new `Insert.from_select` method! See my answer. – nosklo Sep 10 '13 at 12:22