SQLAlchemy：将查询结果插入另一个表中

So I have some results which I've got from the install table, like so:

所以我从安装表中得到了一些结果,如下所示:

install = metadata.tables['install']  
results = session.query(install) #<sqlalchemy.orm.query.Query object>

I'd like to insert these same results into the install_archive table.

我想将这些相同的结果插入install_archive表中。

I'm not entirely sure how to do this, because I don't want to duplicate the schema by defining an install-archive object and then parsing the results into that. I believe I'm not using the ORM, because I'm just reflecting (is that the right term?) the tables and querying them.

我不完全确定如何执行此操作,因为我不想通过定义安装 - 归档对象然后将结果解析为该模式来复制模式。我相信我没有使用ORM,因为我只是反思(这是正确的术语吗?)表格并查询它们。

All the tutorials I can see use the ORM.

我能看到的所有教程都使用ORM。

A slow way of doing it, in psudocode, would be:

在psudocode中,这样做的一种缓慢方法是:

for id in result.all():
    install_archive.insert(install(id))

Thanks in advance!

提前致谢!

2 个解决方案

#1

install_archive \
.insert() \
.from_select(names=['col1', 'col2'], # array of column names that your query returns
             select=session.query(install)) # your query or other select() object

This results in (PostgreSQL dialect)

这导致(PostgreSQL方言)

INSERT INTO install_archive (col1, col2)
SELECT install.col1, install.col2
FROM install;

#2

You can do something like this (changing the SELECT to suit your needs). Just make sure the two tables have the same structure.

你可以这样做(改变SELECT以满足你的需要)。只需确保两个表具有相同的结构。

INSERT INTO `table1` (SELECT * FROM `table2`);

#1

install_archive \
.insert() \
.from_select(names=['col1', 'col2'], # array of column names that your query returns
             select=session.query(install)) # your query or other select() object