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?

@Martijin, your edit is fast! I just found the format need to change and in less than 10sec after submitting and it is already edited to the right format! — NathaneilCapital
– NathaneilCapital, Commented Jan 10, 2014 at 8:14
@Martijin, Regarding escape codes in database, I will double check, but with older version of sqlalchemy, I give a string form of json and it is passed into database without adding the backslash. It is treated as native json string just like now passing the dict form. — NathaneilCapital
– NathaneilCapital, Commented Jan 10, 2014 at 8:21

Martijn Pieters · Accepted Answer · 2014-01-10 08:24:23Z

17

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.

edited Jan 10, 2014 at 8:24

answered Jan 10, 2014 at 8:18

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

Yep, and thus the string '{"HotCold":"Cold,"Value":"10C"}' is stored as a Python string object: >>> print json.dumps('{"HotCold":"Cold,"Value":"10C"}') produces "{\"HotCold\":\"Cold,\"Value\":\"10C\"}"

Dima · Accepted Answer · 2016-08-04 02:26:40Z

2

Meh, but I don't want to do three round trips as in json.loads(), to pass to SQLAlchemy, which would then do json.dumps(), and then Postgres would do unmarshaling again.

So, instead I created a Metadata Table which specified the jsonb column type as Text. Now I take my json strings, and SQLALchemy passes them through and Postgres stores them as jsonb objects.

import sqlalchemy as sa
metadata = sa.MetaData()
rawlog = sa.Table('rawlog', metadata, sa.Column('document', sa.Text)
with create_engine("postgresql:///mydb") as engine:
    with engine.acquire() as conn:
        conn.execute(rawlog.insert().values(document=document)

Where document is a string, rather than a python object.

answered Aug 4, 2016 at 2:26

Dima

2,0922 gold badges17 silver badges23 bronze badges

Comments

Down the Stream · Accepted Answer · 2017-07-25 19:01:21Z

-1

I ran into a similar scenario today:

after inserting new row with a JSONB field via SQLAlchemy, I checked PostgreSQL DB:
"jsonb_fld"
"""{\""addr\"": \""66 RIVERSIDE DR\"", \""state\"": \""CA\"", ...

Reviewing Python code, it sets JSONB field value like so:

 row[some_jsonb_field] = json.dumps(some_dict)

after I took out the json.dumps(...) and simply do:

 row[some_jsonb_field] = some_dict

everything looks better in DB: no more extra \ or ".

Once again I realized that Python and SQLAlchemy, in the case, already take care of the minute details, such as json.dumps. Less code, more satisfaction.

answered Jul 25, 2017 at 19:01

Down the Stream

7379 silver badges11 bronze badges

Comments

Jerry Chen · Accepted Answer · 2021-09-30 19:36:37Z

-1

I ran into the same problem! It seems that SQLAlchemy does its own json.dumps() internally, so this is what is happening:

>>> x={"a": '1'}
>>> json.dumps(x) [YOUR CODE]
 '{"a": "1"}'
>>> json.dumps(json.dumps(x)) [SQLAlchemy applies json.dumps again]
 '"{\\"a\\": \\"1\\"}"' [OUTPUT]

Instead, take out the json.dumps() from your code and you'll load the JSON you want.

answered Sep 30, 2021 at 19:36

Jerry Chen

3993 silver badges4 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related