Re: [sqlalchemy] Update containing a JOIN inside an EXISTS condition in its WHERE clause

[Michal Petrucha]

Phew, took me some time, but I figured it out eventually. I'll post my
solution just in case someone else finds this thread while struggling
with a similar problem.

On Mon, Feb 08, 2016 at 11:23:54AM +0100, Michal Petrucha wrote:
> 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"
>     )

This seems to produce the right query::

    op.execute(users.update().where(
        sa.exists(
            sa.select([user_roles.c.id]).\
            select_from(
                user_roles.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}))


> 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

Here, I came up with the following::

    op.execute(
        sa.insert(user_roles).\
            from_select(
                ['user_id', 'role_id'],
                sa.select([users.c.id, sa.literal(47)],
                          whereclause=users.c.is_admin == True),
            )
    )

Have a nice day,

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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
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