SQLAlchemy query to find and replace a substring if it exists in a column

Question

I am trying to write a query in SqlAlchemy to find and replace a substring with another string if it exists in a column. What is the best way to achieve this?

I am trying to use regexp_replace. I am unable to figure out how to use it. What I have tried so far is:

regexp_statement = table.select(sqlalchemy.func.regexp_replace(*regexp))

But this translates into wrong sql query. Select * from table_name where regexp_replace(string, search_string, replace_string)

How to properly use regexp_replace in sqlalchemy?

Answer 1

UPDATE

You need to use update, not select. Try to use this:

replace_func = sqlalchemy.func.regexp_replace(
    table.c.column,
    your_string_to_replace,
    your_string_to_replace_with
)
table.update(values={table.c.column: replace_func})

OLD

You can use raw sql for that:

sql_expr = (
    "update {table} set {field} = " +
    "replace({field}, {str_to_repl}, {str_to_repl_with});"
).format(
    table=your_table_name,
    field=your_field_name,
    str_to_repl=your_string_to_replace,
    str_to_repl_with=your_string_to_replace_with,
)
session.execute(sql_expr)
session.commit()