[alembic] data migration and select expression

[Bruno Binet]

Hi,

I'm successfully using Alembic for my schema migration, but I fail
when it comes to data migration.
For example I would like to add new lines in an association table
which foreign key values would be set using a `select` expression.
Here is the kind of migration I want to be able to do:

```
# revision identifiers, used by Alembic.
revision = '17a6073d49ac'
down_revision = '22e1c34c8efd'

from alembic import op
import sqlalchemy as sa

metadata = sa.MetaData()
l = sa.Table('liste', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('type_liste', sa.Unicode(32)),
sa.Column('valeur', sa.Unicode(256)),
schema='app',
)
ll = sa.Table('liste_liste', metadata,
sa.Column('id_liste_parent', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
sa.Column('id_liste_child', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
schema='app')

def _listeid_select(liste, valeur):
return sa.select(
[l.c.id],
sa.and_(l.c.type_liste==liste, l.c.valeur==valeur)
).limit(1)

def upgrade():
op.bulk_insert(ll, [{
'id_liste_parent': _listeid_select('ListeNatureUE', 'poteau'),
'id_liste_child': _listeid_select('ListePlanUE', valeur)
} for valeur in [u'circulaire', u'ovale', u'rectangulaire'']])

def downgrade():
pass

```

But it does not work, I cannot use a `select` expression here.

How would you do such data migrations with alembic?
Ideally it would work with both online and offline mode.

Thanks,
Bruno

--
Bruno Binet

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex

Mail : bruno...@camptocamp.com
http://www.camptocamp.com

[Michael Bayer]

We don't have direct support for INSERT..SELECT yet, you can use this recipe :

http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct

[Bruno Binet]

Oh, I didn't mean doing INSERT..SELECT constructs, but my problem was
rather to select scalar values to be used in an insert statement.
SQLAlchemy already have support for this, so I finally managed to do
my migration script with the following:
```
#-*- coding: utf-8 -*-

# revision identifiers, used by Alembic.
revision = '17a6073d49ac'
down_revision = '22e1c34c8efd'

from alembic import op
import sqlalchemy as sa

metadata = sa.MetaData()
l = sa.Table('liste', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('type_liste', sa.Unicode(32)),
sa.Column('valeur', sa.Unicode(256)),
schema='app',
)
ll = sa.Table('liste_liste', metadata,
sa.Column('id_liste_parent', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
sa.Column('id_liste_child', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
schema='app')


def _listeid_select(liste, valeur):
return sa.select([l.c.id], sa.and_(

l.c.type_liste==op.inline_literal(liste),
l.c.valeur==op.inline_literal(valeur)))


def upgrade():
for valeur in [u'carré', u'circulaire', u'ovale', u'rectangulaire',
u'irrégulier']:
op.execute(ll.insert().values(
id_liste_parent=_listeid_select('ListeNatureUE', 'poteau'),
id_liste_child=_listeid_select('ListePlanUE', valeur)))

def downgrade():
for valeur in [u'carré', u'circulaire', u'ovale', u'rectangulaire',
u'irrégulier']:
op.execute(ll.delete().where(sa.and_(
ll.c.id_liste_parent==_listeid_select('ListeNatureUE', 'poteau'),
ll.c.id_liste_child==_listeid_select('ListePlanUE', valeur))))

```
The only minor problem that remains with the above implementation is a
UnicodeDecodeError which I reported on alembic bug tracker:
https://bitbucket.org/zzzeek/alembic/issue/90/special-unicode-chars-causes
(this can easily be worked around by asking alembic not to output sql
to sys.stdout).

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.