SQLAlchemy: Inserting the results of a query into another table

Question

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.

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.

All the tutorials I can see use the ORM.

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

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

Thanks in advance!

Solution

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)

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

Comments

[Hadrien]

How would you do this without raw sql but with a sqlalchemy expression?

[Michael Mior]

I don't believe you can do something quite like this with SQLAlchemy. You could select all the data, then loop through and insert into the other table, but this would be horribly inefficient. You don't really gain anything from doing it the "SQLAlchemy way". A SQL statement like this is portable enough.

[Andrei Sosnin]

I don't think this answers the question.

[Michael Mior]

@AndreiSosnin Could you clarify how you think this fails to answer the question?

[Andrei Sosnin]

I mean, generally it sure does, but it fails to answer it in the narrow topic of SQLAlchemy. Since the question is not really about helping out @Oatman with solving the problem he has by avoiding to solve it altogether, but to find out, if it's possible to solve it using specifically SQLAlchemy. This question is more about SQLAlchemy and MySQL, than the general issue of moving data from one table to another.

[Andrei Sosnin]

That's why I came to this question, too. The very reason for me to use SQLAlchemy is to abstract away the database specifics and concentrate on the general issue of manipulating data. Also I'm pretty sure, that SQLAlchemy allows creating such SQL constructs using its (quite comprehensive) API. The problem is that its documentation is quite diverse and sometimes a bit too terse on details, which is why I, for one, came here to research it a little before trying to solve it myself (by digging deeply into the docs & code).

[Michael Mior]

@AndreiSosnin I understand your point, but to me something like this is outside the scope of an ORM. I would venture to say that something like this shouldn't be part of regular use of your application. Not that it doesn't have it's uses, but I don't have a problem writing raw SQL for things like this.

[Andrei Sosnin]

@MichaelMior, one use case, for instance, is database migration or version control. ORM has a lot to do with that as it is supposed to simplify the task (by hopefully making it possible to automate it altogether).