psycopg2 inserts null into a column when inserting None.
...
foo=json.dumps(insertObj.get('foo', None)),
...
db.session.add(s)
db.session.commit()
The value in the column is a string 'null' not the database type null which looks like <null> in the database manager. I thought it should insert database type null for None (see this question).
What should be inserted into a json column for an empty json value for best practice?
I would think NULL is best practice for missing data, regardless of data type.
Use NULL / None when you don't have a json document to store, and you can then use the same idiom to pick or omit rows with where your json is NULL as you do with columns of other types.
This does however mean that when you fetch records, you have to expect that the json column can sometimes be null, and that needs special handling, but again, this is no different than other columns that allow nulls.
update:
As the OP already realized and is evident from the code snippet provided, the issue is that None is being dumped by json.dumps as a string, which is then inserted into the database.
psycopg2 provides a json adapter for postgresql, which can be used here.
change
foo=json.dumps(insertObj.get('foo', None))
to
from psycopg2.extras import Json
foo= Json(insertObj.get('foo', None))
Just to add to this answer. If the variable is set and is null e.g.
from psycopg2.extras import Json
someVar = None
foo = Json(someVar)
then it will still add null as a string. To get around this do:
foo = Json(someVar) if someVar else None
