Hi folks, I'm trying to write a data migration for Alembic, and I can't seem to figure out the correct chain of calls to express it. The tables are as follows::
users = sa.table('users',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('is_admin', sa.Boolean(), default=False, nullable=False),
)
roles = sa.table('roles',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('name', sa.Unicode(length=50), unique=True),
)
user_roles = sa.table('user_roles',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'),
nullable=True),
sa.Column('role_id', sa.Integer(), sa.ForeignKey('roles.id'),
nullable=True),
)
Now, the query I'm trying to express in terms of the SQLAlchemy API is
something like::
UPDATE users
SET is_admin = 1
WHERE EXISTS (
SELECT *
FROM user_roles INNER JOIN roles ON (user_roles.role_id = roles.id)
WHERE roles.name = "administrator"
)
My first stab was this::
users.update().where(
sa.exists(
sa.select([user_roles.c.id]).\
join(roles, user_roles.c.role_id == roles.c.id).\
where(sa.and_(
user_roles.c.user_id == users.c.id,
roles.c.name == 'administrator'
))
)
).values({'is_admin': True})
...but the result of join() does not have a where method.
Am I at least going in the right direction? Am I better off just
dumping a chunk of literal SQL into the migration?
Next up, I'll try to do do the opposite migration, too, i.e. something
like::
INSERT INTO user_roles (user_id, role_id)
SELECT id, ?
FROM user
WHERE is_admin = 1
...where the second column in that SELECT would be a bound parameter.
I'm not even sure where to begin on this one.
Thanks for any nudge in the right direction.
Regards,
Michal
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: Digital signature
