Menu
In SQLAlchemy, how do I populate or update a table from a SELECT statement?
491
To select data from a table via SQLAlchemy, you need to build a representation of that table within SQLAlchemy. If Jupyter Notebook's response speed is any indication, that representation isn't filled in (with data from your existing database) until the query is executed. You need Table to build a table.
Return the first result of this Query or None if the result doesn't contain any row. first() applies a limit of one within the generated SQL, so that only one primary entity row is generated on the server side (note this may consist of multiple result rows if join-loaded collections are present).
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
SQLalchemy doesn't build this construct for you. You can use the query from text.
session.execute('INSERT INTO t1 (SELECT * FROM t2)') 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)" 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.
101
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
