CODEWITHSUNDEEP
pythonjsonpostgresqlsqlalchemy
NathaneilCapital
NathaneilCapital

Reputation: 1469

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

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?

Upvotes: 16

Views: 9561

Answers (4)

Jerry Chen
Jerry Chen

Reputation: 389

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.

Upvotes: -1

Down the Stream
Down the Stream

Reputation: 697

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.

Upvotes: -1

Dima
Dima

Reputation: 2072

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.

Upvotes: 2

Martijn Pieters
Martijn Pieters

Reputation: 1123350

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.

Upvotes: 16

Related Questions