> > 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 > > <https://www.google.com/url?q=https%3A%2F%2Fbitbucket.org%2Fzzzeek%2Fsqlalchemy%2Fissues%2F3870%2Fclient-side-column-default-value&sa=D&sntz=1&usg=AFQjCNGcfSXo8bd43Vu3-lZcsGTg0MnqXQ> > , > 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 <mik...@zzzcomputing.com > <javascript:>> wrote: > > On Tue, Jul 18, 2017 at 12:47 AM, <vin...@benchling.com <javascript:>> > 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 <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+...@googlegroups.com <javascript:>. > >> To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > >> 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.