Bulk update with subquery using SQLAlchemy

Question

I'm trying to implement the following MySQL query using SQLAlchemy. The table in question is nested set hierarchy.

UPDATE category
JOIN
    (
    SELECT
        node.cat_id,
        (COUNT(parent.cat_id) - 1) AS depth
    FROM category AS node, category AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    GROUP BY node.cat_id
    ) AS depths
ON category.cat_id = depths.cat_id
SET category.depth = depths.depth

This works just fine.

This is where I start pulling my hair out:

from sqlalchemy.orm import aliased
from sqlalchemy import func

from myapp.db import db

node = aliased(Category)
parent = aliased(Category)
stmt = db.session.query(node.cat_id,
                        func.count(parent.cat_id).label('depth_'))\
    .filter(node.lft.between(parent.lft, parent.rgt))\
    .group_by(node.cat_id).subquery()

db.session.query(Category,
                 stmt.c.cat_id,
                 stmt.c.depth_)\
    .outerjoin(stmt, 
               Category.cat_id == stmt.c.cat_id)\
    .update({Category.depth: stmt.c.depth_},
             synchronize_session='fetch')

...and I get InvalidRequestError: This operation requires only one Table or entity be specified as the target. It seems to me that Category.depth adequately specifies the target, but of course SQLAlchemy trumps whatever I may think.

Stumped. Any suggestions? Thanks.

Could you print out `stmt` without the `.subquery()`? – astex Dec 10 '13 at 23:05 — astex, Dec 10 '13 at 23:05
Did you figure it out? – Antony Hatchkins Feb 10 '17 at 16:22 — Antony Hatchkins, Feb 10 '17 at 16:22

score 4 · Answer 1 · answered May 05 '19 at 21:20

I know this question is five years old, but I stumbled upon it today. My answer might be useful to someone else. I understand that my solution is not the perfect one, but I don't have a better way of doing this.

I had to change only the last line to:

db.session.query(Category)\
    .outerjoin(stmt, 
               Category.cat_id == stmt.c.cat_id)\
    .update({Category.depth: stmt.c.depth_},
             synchronize_session='fetch')

Then, you have to commit the changes:

db.session.commit()

This gives the following warning:

SAWarning: Evaluating non-mapped column expression '...' onto ORM instances; this is a deprecated use case. Please make use of the actual mapped columns in ORM-evaluated UPDATE / DELETE expressions.
"UPDATE / DELETE expressions." % clause

To get rid of it, I used the solution in this post: Turn off a warning in sqlalchemy

Note: For some reason, aliases don't work in SQLAlchemy update statements.

Bulk update with subquery using SQLAlchemy

1 Answers1