Re: [sqlalchemy] Bug report with JSON nulls in 1.1.x

[vineet]

>
> OK, this is great, it's likely not the "underscore" as much as that 
> the attribute is named differently from the column.  I will look to 
> confirm this and set up a complete bug report, thanks!
>
 
Ah, that makes a lot more sense. Thanks for looking into that!

Above, you are looking at "a.column2", did you mean "a._column2"? 
> There should be no ".column2" attribute on the object. 
>

Woops, typo. That's what I get for trying to clean up the code to make it 
look nicer without rerunning it. 

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. 
>

Got it, thanks. I think this will work well for us. We are using Postgres 
as well :)

JSON.NULL is this 
> totally special value that the Core default mechanism doesn't yet 
> fully accommodate so that's why setting default=text("'null'") for now 
> is a workaround. 
>

Makes sense, and a big thanks for the quick responses!
 

On Tuesday, July 18, 2017 at 8:02:56 AM UTC-7, Mike Bayer wrote:
>
> On Tue, Jul 18, 2017 at 10:31 AM, Mike Bayer  > wrote: 
> > On Tue, Jul 18, 2017 at 12:47 AM,   
> 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: 
> > 
> > OK, this is great, it's likely not the "underscore" as much as that 
> > the attribute is named differently from the column.  I will look to 
> > confirm this and set up a complete bug report, thanks! 
>
> set up w/ a patch at 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4031/eval_none-logic-isnt-resolving-col-to-prop.
>  
>
>
> > 
> >> 
> >> 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 \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 

Re: [sqlalchemy] Bug report with JSON nulls in 1.1.x

[Mike Bayer]

On Tue, Jul 18, 2017 at 10:31 AM, Mike Bayer  wrote:
> On Tue, Jul 18, 2017 at 12:47 AM,   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:
>
> OK, this is great, it's likely not the "underscore" as much as that
> the attribute is named differently from the column.  I will look to
> confirm this and set up a complete bug report, thanks!

set up w/ a patch at
https://bitbucket.org/zzzeek/sqlalchemy/issues/4031/eval_none-logic-isnt-resolving-col-to-prop.

>
>>
>> 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 \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.

Re: [sqlalchemy] Bug report with JSON nulls in 1.1.x

[Mike Bayer]

On Tue, Jul 18, 2017 at 12:47 AM,   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:

OK, this is great, it's likely not the "underscore" as much as that
the attribute is named differently from the column.  I will look to
confirm this and set up a complete bug report, thanks!

>
> 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 \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.

[sqlalchemy] Bug report with JSON nulls in 1.1.x

[vineet]

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 \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.