Cannot insert JSON null via bulk_insert_mappings

[Adrian]

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'test_a'

    id = Column(Integer, primary_key=True)
    data = Column(JSON(none_as_null=False), nullable=False)


# e = create_engine('sqlite:///:memory:', echo=True)
e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.bulk_insert_mappings(A, [{'data': 'null'}])
s.bulk_insert_mappings(A, [{'data': None}])
s.commit()

output (only relevant parts)

2015-08-17 18:34:06,882 INFO sqlalchemy.engine.base.Engine INSERT INTO test_a (data) VALUES (%(data)s) RETURNING test_a.id
2015-08-17 18:34:06,882 INFO sqlalchemy.engine.base.Engine {'data': '"null"'}
2015-08-17 18:34:06,883 INFO sqlalchemy.engine.base.Engine INSERT INTO test_a DEFAULT VALUES RETURNING test_a.id
2015-08-17 18:34:06,883 INFO sqlalchemy.engine.base.Engine {}

So the None is never converted to a 'null' but the 'null' string is converted to '"null"'...

[Mike Bayer]

None is the default value of an unset attribute and in this case the traditional flush only works by accident, due to the fact that we send None in explicitly for columns without a default. that is, if i fixed that line to work for bulk, it still would fail if you sent in None and the column had a default of something else on it. There is no system in the ORM's flush/insert process right now that distinguishes between a column value of None that was set vs. one that was just never set; however, thanks to #3061, this is possible in theory but would need to be tested extremely carefully and still might have backwards-incompatible effects, unless we added a flag to the type itself so that this behavior could be very limited.

workaround for now is to set a default of None:

data = Column(JSON(none_as_null=False), default=lambda: None, nullable=False)

[Mike Bayer]

this is the logic change:

diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py
index 0bfee2e..ca24760 100644
--- a/lib/sqlalchemy/orm/persistence.py
+++ b/lib/sqlalchemy/orm/persistence.py
@@ -375,12 +375,15 @@ def _collect_insert_commands(

         propkey_to_col = mapper._propkey_to_col[table]

-        for propkey in set(propkey_to_col).intersection(state_dict):
+        keys = set(propkey_to_col).intersection(state_dict)
+        if not bulk:
+            keys = keys.intersection(state.committed_state)
+        for propkey in keys:
             value = state_dict[propkey]
             col = propkey_to_col[propkey]
-            if value is None:
-                continue
-            elif not bulk and isinstance(value, sql.ClauseElement):
+            #if value is None:
+            #    continue
+            if not bulk and isinstance(value, sql.ClauseElement):
                 value_params[col.key] = value
             else:
                 params[col.key] = value

and it fails for composites, because they have None values set which still need to mean, "please use the column default" and that breaks here. we can fix composites but the expectation that even if you set "obj.a = None", a default still fires off, is in conflict with the request here that None is now a "value".

seems like the easiest answer is to admit none_as_null is a failure, deprecate it, and require the special value JSON.NONE.

[Mike Bayer]

• Additional fixes have been made regarding the value of None in conjunction with the Postgresql :class:.JSON type. When the :paramref:.JSON.none_as_null flag is left at its default value of False, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM object is set to the value None or when the value None is used with :meth:.Session.bulk_insert_mappings, including if the column has a default or server default on it. fixes #3514
• Added a new constant :attr:.postgresql.JSON.NULL, indicating that the JSON NULL value should be used for a value regardless of other settings.

→ <<cset 5215d96d2886>>

[Mike Bayer]

this is still in a branch so will have to be merged.

[Mike Bayer]

• merge of ticket_3514 None-handling branch
• Fixes to the ORM and to the postgresql JSON type regarding the None constant in conjunction with the Postgresql :class:.JSON type. When the :paramref:.JSON.none_as_null flag is left at its default value of False, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM object is set to the value None or when the value None is used with :meth:.Session.bulk_insert_mappings, including if the column has a default or server default on it. This makes use of a new type-level flag "evaluates_none" which is implemented by the JSON type based on the none_as_null flag. fixes #3514
• Added a new constant :attr:.postgresql.JSON.NULL, indicating that the JSON NULL value should be used for a value regardless of other settings. part of fixes #3514

→ <<cset d14a4b480c3b>>

[Mike Bayer]

• merge of ticket_3514 None-handling branch
• Fixes to the ORM and to the postgresql JSON type regarding the None constant in conjunction with the Postgresql :class:.JSON type. When the :paramref:.JSON.none_as_null flag is left at its default value of False, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM object is set to the value None or when the value None is used with :meth:.Session.bulk_insert_mappings, including if the column has a default or server default on it. This makes use of a new type-level flag "evaluates_none" which is implemented by the JSON type based on the none_as_null flag. fixes #3514
• Added a new constant :attr:.postgresql.JSON.NULL, indicating that the JSON NULL value should be used for a value regardless of other settings. part of fixes #3514

→ <<cset d14a4b480c3b>>