I'm using alembic to manage my database migrations. In my current migration I need also to populate a column based on a SELECT statement (basically copying a column from a different table).
With plain SQL I can do:
UPDATE foo_table
SET bar_id=
(SELECT bar_table.id FROM bar_table
WHERE bar_table.foo_id = foo_table.id);
However can't figure out how to do that with alembic:
execute(
foo_table.update().\
values({
u'bar_id': ???
})
)
I tried to use plain SQLAlchemy expressions for the '???':
select([bar_table.columns['id']],
bar_table.columns[u'foo_id'] == foo_table.columns[u'id'])
But that only generates bad SQL and a ProgrammingError during execution:
'UPDATE foo_table SET ' {}
Actually it works exactly as I described above.
My problem was that the table definition for 'foo_table' in my alembic script did not include the 'bar_id' column so SQLALchemy did not use that to generate the SQL...