Using specific column values in non-select clauses in SQLAlchemy 2.0/Mapped Classes ORM

I tried to do the following in SQLAlchemy 2.0 using a MappedClass:

async with sessionmanager.session() as db_session:
    await db_session.execute(update(MyMappedClass).values(slug=slugify(MyMappedClass.c.title)))

(Ignore the fact that this is async, 99% sure it's immaterial.)

The part that didn't work as MyMappedClass.c.title which (I gleaned from the error) returned a Comparator object rather than a string. I thought this would work because it looks like this syntax works with SQLAlchemy tables (docs). Is there some way to do a similar thing with mapped classes? Please let me know if I got something else wrong.

Solution

A values call like .values(slug=<some_expression>) only works if <some_expression> is either pure Python, like 'a' + 'b' or 1 + 2, or can be converted by SQLAlchemy into an SQL expression. For example, in the example in the docs "Username: " + user_table.c.name can be converted to 'Username: ' || name at the SQL level, because SQLAlchemy maps the + operator for string data types to ||.

However slugify(MyMappedClass.title) cannot be mapped in this way, because SQLAlchemy doesn't know how to map slugify to an SQL expression.

You could possibly provide an expression yourself. If slugify was defined like this:

def slugify(s):
    return s.lower().replace(' ', '-')

then your update statement would be

from sqlalchemy import func
...
update(MyMappedClass).values(slug=func.replace(func.lower(MyMappedClass.title), ' ', '-'))

If slugify is not expressible in this way then you would need to retrieve the rows for update and call slugify in the Python layer:

from sqlalchemy import bindparam,select, update
...
with Session.begin() as s:
    mms = s.scalars(select(MyMappedClass))
    values = [{'id': mm.id, 'slug': slugify(mm.title)} for mm in mms]
    stmt = update(MyMappedClass).values(slug=bindparam('slug'))
    s.execute(stmt, values)