On Tue, Jul 18, 2017 at 2:12 AM, <vin...@benchling.com> wrote:
> A related issue (that seems like a bug) happens when reading attribute
> values after a flush, but before a commit. In this scenario, I set the
> default=JSON.NULL in both columns
> class A(db.Model):
> __tablename__ = 'a'
>
> id = db.Column(db.Integer, primary_key=True)
> column1 = db.Column('column1', JSON, nullable=False, default=JSON.NULL)
> _column2 = db.Column('column2', JSON, nullable=False, default=JSON.NULL)
>
> a = A(column1=None, column2=None)
> db.session.add(a)
>
> # Expected
> print a.column1 # None
> print a._column2 # None
>
>
> # Unexpected differences
> db.session.flush()
> print a.column1 # None
> print a.column2 # symbol('JSON_NULL')
Above, you are looking at "a.column2", did you mean "a._column2"?
There should be no ".column2" attribute on the object.
>
> db.session.commit()
> print a.column1 # None
> print a.column2 # None
same
>
>
>
> I believe this is expected given the bug listed above. However, I have a
> question about how to handle this even when the original bug is fixed.
>
> With "default=JSON.NULL", it seems like the value of a.column1 can be None
> OR symbol('JSON_NULL') depending on if it is read 1) before flush, 2) after
> flush, and 3) after commit. Are there any suggested ways of dealing with
> this? (Or it's possible I'm doing something wrong here!) We have code that
> checks "if a.column1 is None", which is failing with the 1.1.11 upgrade.
the JSON column will translate "None" into JSON.NULL as it goes to the
database. The "default" here is a python side-default, so the python
side value will be maintained, so ideally we'd want to set
default=None, but that means, "there's no default" so we're in a
little bit of a pickle there. this issue was discussed in
https://bitbucket.org/zzzeek/sqlalchemy/issues/3870/client-side-column-default-value,
where the solution was a new bit of documentation added to the notes
for JSON. the solution is at
http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=json#sqlalchemy.types.JSON.NULL
and describes to set the default to a SQL expression that resolves to
JSON NULL; the ORM will fetch this default back from the database; the
flush process will expire the Python value so that this fetch occurs
prior to the usual expiration at commit time. Hopefully you're
using Postgresql in which case you can set eager_defaults=True on the
mapping and it will re-fetch the value inline with the INSERT using
RETURNING.
>
>
> On Monday, July 17, 2017 at 9:47:10 PM UTC-7, vin...@benchling.com wrote:
>>
>> Hello, I'd like to report a bug report regarding JSON nulls and
>> underscore-prefixed attributes as we're upgrading from 1.0.17 to 1.1.11.
>>
>> I read through the behavior at
>> http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#change-3514
>> and
>> http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#behavior-change-3514,
>> all of which makes sense to me.
>>
>> However, this does not seem to work for attributes which are prefixed with
>> an underscore. Example:
>>
>> class A(db.Model):
>> __tablename__ = 'a'
>>
>>
>> id = db.Column(db.Integer, primary_key=True)
>> column1 = db.Column('column1', JSON, nullable=False)
>> _column2 = db.Column('column2', JSON, nullable=False)
>>
>> # Succeeds
>> db.session.add(A(column1=[], _column2=[]))
>> db.session.flush()
>>
>> # Succeeds
>> db.session.add(A(column1=None, _column2=[]))
>> db.session.flush()
>>
>> # Fails with integrity error
>> db.session.add(A(column1=[], _column2=None))
>> db.session.flush()
>>
>>
>> (psycopg2.IntegrityError) null value in column "column2" violates not-null
>> constraint
>> DETAIL: Failing row contains (5, [], null).
>> [SQL: '-- manage.py:1242 in <module>\nINSERT INTO a (column1) VALUES
>> (%(column1)s) RETURNING a.id'] [parameters: {'column1': '[]'}]
>>
>> Using db.Column('column2', JSON, nullable=False, default=JSON.NULL) ends
>> up sending a JSON NULL, so basically it looks like it is ignoring explicitly
>> set None values for attributes that start with an underscore.
>>
>> This is not workflow blocking for us (I will just change our usages to
>> also use default=JSON.NULL), but wanted to file a bug report in case it's
>> something others run into as well!
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
