Skip to content
Advertisement

Python SqlAlchemy adding escape characters to json string while inserting in postgresql json column, so queries not working

I am adding JSON string to Postgres jsonb column in python using the sqlalchemy library.

Code to create json string is

Code to add to postgresql database is:

My JSON is printed properly in logs

But when it goes to database the json is enclosed in a string and an escape character is added in front of every double quote.

Due to which none of the below queries are working:

My requirement is to be able to query JSON column by matching the key value. Please help

SOLVED: I edited the code to pass the dictionary directly to sqlalchemy table object to add the row. It automatically converted the dictionary to JSON.

Thanks @snakecharmerb

Advertisement

Answer

By default, SQLAlchemy will automatically cll json.dumps on values assigned to a JSON or JSONB column, so it isn’t necessary to call it yourself – in fact this will lead to double-encoded values as seen in the question.

The functions used to encode and decode JSON can be managed by setting the json_serializer and json_deserializer arguments to create_engine

If you don’t want SQLAlchemy to serialise automatically, pass an identity function like lambda x: x.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement