Why sqlalchemy add \ to " for a perfect JSON string to postgresql json field?

Question

SQLAlchemy 0.9 added built-in support for the JSON data type of PostgreSQL. But when I defined an object mapper which has a JSON field and set its value to a perfect JSON string:

json = '{"HotCold":"Cold,"Value":"10C"}'

The database gets the data in the form:

"{\"HotCold\":\"Cold\",\"Value":\"10C\"}"

All internal double quotes are backslashed, but if I set JSON from a python dict:

json = {"HotCold": "Cold, "Value": "10C"}

I get the JSON data in the database as:

{"HotCold":"Cold,"Value":"10C"}

Why is that? Do I have to pass the data in dict form to make it compatible with SQLAlchemy JSON support?

Answer 1

The short answer: Yes, you have to.

The JSON type in SQLAlchemy is used to store a Python structure as JSON. It effectively does:

database_value = json.dumps(python_value)

on store, and uses

python_value = json.loads(database_value)

You stored a string, and that was turned into a JSON value. The fact that the string itself contained JSON was just a coincidence. Don't store JSON strings, store Python values that are JSON-serializable.

A quick demo to illustrate:

>>> print json.dumps({'foo': 'bar'})
{"foo": "bar"}
>>> print json.dumps('This is a "string" with quotes!')
"This is a \"string\" with quotes!"

Note how the second example has the exact same quoting applied.

Use the JSON SQLAlchemy type to store extra structured data on an object; PostgreSQL gives you access to the contents in SQL expressions on the server side, and SQLAlchemy gives you full access to the contents as Python values on the Python side.

Take into account you should always set the whole value anew on an object. Don't mutate a value inside of it and expect that SQLAlchemy detects the change automatically for you; see the PostgreSQL JSON type documentation.