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

[Michal Petrucha]

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 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