如何在 SQLAlchemy 中插入 t1 (SELECT * FROM t2)? [英] How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?

问题描述

在 SQLAlchemy 中，如何从 SELECT 语句填充或更新表?

解决方案

SQLalchemy 不会为您构建此结构.您可以使用文本查询.

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

---

一年多后，但现在在 sqlalchemy 0.6+ 你可以创建它:

from sqlalchemy.ext 导入编译器from sqlalchemy.sql.expression import Executable, ClauseElement类 InsertFromSelect(Executable, ClauseElement):def __init__(self, table, select):self.table = 表self.select = 选择@compiler.compiles(InsertFromSelect)def visit_insert_from_select(element, compiler, **kw):返回INSERT INTO %s (%s)"；% (compiler.process(element.table, asfrom=True),编译器过程(元素.选择))insert = InsertFromSelect(t1, select([t1]).where(t1.c.x>5))印刷插页

产生:

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

---

另一个

现在，4 年后，该语法被纳入 SQLAlchemy 0.9，并反向移植到 0.8.3；您可以创建任何 select()，然后使用 Insert 对象的新 from_select() 方法:

<预><代码>>>>从 sqlalchemy.sql 导入表，列>>>t1 = table('t1', column('a'), column('b'))>>>t2 = table('t2', column('x'), column('y'))>>>打印(t1.insert().from_select(['a', 'b'], t2.select().where(t2.c.y == 5)))插入 t1 (a, b) 选择 t2.x, t2.y从 t2哪里 t2.y = :y_1

文档中的更多信息.

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

解决方案

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.

这篇关于如何在 SQLAlchemy 中插入 t1 (SELECT * FROM t2)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！