Cannot insert JSON null via bulk_insert_mappings
Issue #3514
resolved
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"'
...
Comments (7)
-
repo owner -
repo owner 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.
-
repo owner - changed status to resolved
- 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 ofFalse
, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM object is set to the valueNone
or when the valueNone
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>>
-
repo owner - changed status to open
this is still in a branch so will have to be merged.
-
repo owner -
repo owner - changed status to resolved
- 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 ofFalse
, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM object is set to the valueNone
or when the valueNone
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>>
-
repo owner - 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 ofFalse
, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM object is set to the valueNone
or when the valueNone
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>>
- Log in to comment
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 ofNone
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: