python 如何在 SQLAlchemy 中插入 t1 (SELECT * FROM t2)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1849375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?
提问by joeforker
In SQLAlchemy, how do I populate or update a table from a SELECTstatement?
在 SQLAlchemy 中，如何从SELECT语句填充或更新表？
回答by nosklo
SQLalchemy doesn't build this construct for you. You can use the query from text.
SQLalchemy 不会为您构建此构造。您可以使用文本查询。
session.execute('INSERT INTO t1 (SELECT * FROM t2)')
EDIT:
编辑：
More than one year later, but now on sqlalchemy 0.6+ you can create it:
一年多后，但现在在 sqlalchemy 0.6+ 上您可以创建它：
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 Insertobjects:
现在，4 年后，该语法被纳入 SQLAlchemy 0.9，并反向移植到 0.8.3；您可以创建 anyselect()然后使用对象的新from_select()方法Insert：
>>> 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
回答by David Fraser
As of 0.8.3, you can now do this directly in sqlalchemy: Insert.from_select:
从 0.8.3 开始，您现在可以直接在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)
回答by Hadrien
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
正如 Noslko 在评论中指出的那样，您现在可以摆脱原始 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)
