MySQL string replace using SQLAlchemy

Question

Is there a way in SQLAlchemy to perform a MySQL string replace. I'd like to find a way to do the following in SQLAlchemy core.

UPDATE your_table
SET your_field = REPLACE(your_field, 'from_str', 'to_str')
WHERE your_field LIKE '%from_str%'

Answer 1

Yes, read more about it on Column Elements and Expressions, but the key point is to use func.MY_FUNCTION:

func.REPLACE(YourTable.your_field, 'from_str', 'to_str')

If you perform update from query, it might look like this:

updated_rows = (
    session.query(YourTable)
    .filter(YourTable.your_field.like('%est%'))
    .update({YourTable.your_field: func.replace(YourTable.your_field, 'from_str', 'to_str')},
            synchronize_session=False)
    )
print("Updated {} rows".format(updated_rows))