How to get SQLAlchemy session.execute to not add single quotes to string when replacing parameters?-CodePudding

I have this piece of SQL inside a bigger statement where I need to replace two variables:

jsonb_pretty(jsonb_build_object('analysis_config', jsonb_agg(
            case value->>'name'
            when :old_name then value || '{"name": ":new_name"}'
            else value
            end
        ))

The problem is that when I do session.execute(statement, params={"old_name": old_name, "new_name": new_name}) new_name gets replaced surrounded by single quotes, which then doesn’t work. It needs to be explicitly surrounded by double quotes (which I’ve tried to do by adding them as above). fstring works perfectly, but we don’t wanna do that here. Any simple ways to do it? Thanks!

CodePudding user response：

One option is to pass the entire JSON string as the value. Instead of

when :old_name then value || '{"name": ":new_name"}'

use

when :old_name then value || :new_name

and pass an encoded string as the value:

import json

session.execute(
    statement,
    params={
        "old_name": old_name,
        "new_name": json.dumps({"name": new_name})
    })