11

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!

0atman
  • 3,298
  • 4
  • 30
  • 46
  • what is the purpose of the exercise? – van Jun 19 '10 at 19:15
  • Van, I'm attempting to do some validation on the `install` table, and only the subset that validates is being passed to the `install_archive` table. I'm sure this is a simple thing "move some records from one table to another, but not all" but I can't seem to find a way of doing it in the docs. I'll keep looking. – 0atman Jun 21 '10 at 10:14
  • are you sure you need to do it in python (using SA), and not directly in MySQL? Why? – van Jun 21 '10 at 12:41
  • Well, that's the thing, I thought it would be best to do it the sqlalchemy way? – 0atman Jun 22 '10 at 15:08
  • 1
    Well, I do not know your particular case, but unless there is a complex logic that filters what goes into the `archive` table and what does not, I would do it directly in the DB and avoid overhead. – van Jun 23 '10 at 20:45
  • I would say a (maybe not common) use case would be for expensive queries that you don't want to run twice, but want to store the results in another table. though you could argue that you insert the results into the 'cache' table and then query that table for the data to be used in your application – dangel Aug 06 '23 at 00:57

2 Answers2

12
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;
dtheodor
  • 4,894
  • 3
  • 22
  • 27
-2

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

INSERT INTO `table1` (SELECT * FROM `table2`);
Michael Mior
  • 28,107
  • 9
  • 89
  • 113
  • How would you do this without raw sql but with a sqlalchemy expression? – Hadrien Jan 20 '11 at 17:47
  • 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. – Michael Mior Jan 20 '11 at 18:27
  • I don't think this answers the question. – Andrei Sosnin Nov 28 '12 at 08:51
  • @AndreiSosnin Could you clarify how you think this fails to answer the question? – Michael Mior Nov 28 '12 at 13:07
  • 2
    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 Dec 03 '12 at 15:08
  • 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). – Andrei Sosnin Dec 03 '12 at 15:12
  • @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. – Michael Mior Dec 03 '12 at 16:44
  • @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). – Andrei Sosnin Dec 05 '12 at 20:32