date:20170718

Re: [sqlalchemy] session.add() neglecting some of my objects to be added

2017-07-18 Thread David Laredo Razo

Thanks Mike, indeed, that was the problem. I solved it using an "ad hoc" 
copy function. This is the solution that worked for me in case somebody 
else incurs in the same mistake I did

def copy_sqla_object(obj, omit_fk=True):
"""Given an SQLAlchemy object, creates a new object (FOR WHICH THE OBJECT
MUST SUPPORT CREATION USING __init__() WITH NO PARAMETERS), and copies
across all attributes, omitting PKs, FKs (by default), and relationship
attributes."""
cls = type(obj)
mapper = class_mapper(cls)
newobj = cls()  # not: cls.__new__(cls)
pk_keys = set([c.key for c in mapper.primary_key])
rel_keys = set([c.key for c in mapper.relationships])
prohibited = pk_keys | rel_keys
if omit_fk:
fk_keys = set([c.key for c in mapper.columns if c.foreign_keys])
prohibited = prohibited | fk_keys
for k in [p.key for p in mapper.iterate_properties if p.key not in 
prohibited]:
try:
value = getattr(obj, k)
setattr(newobj, k, value)
except AttributeError:
pass
return newobj



On Friday, July 14, 2017 at 2:47:45 PM UTC-5, Mike Bayer wrote:
>
> On Fri, Jul 14, 2017 at 1:24 AM, David Laredo Razo 
>  wrote: 
>
>
> this code is the problem: 
>
> > 
> > new_object = copy.copy(reading) 
>
> copy() will copy the _sa_instance_state and prevent the session from 
> tracking the object correctly. 
>
> Correct pattern should be: 
>
> new_object = ThermafuserReading(None, componentId) 
>
> Only when you call the constructor (e.g. ThermafuserReading.__init__) 
> do you get a new InstanceState object dedicated to that object. So 
> don't use copy(). 
>
> There *are* ways to use copy() here instead but they are non-obvious 
> and not necessary for a simple case like this. 
>
>
>
>
> > new_object.timestamp = timestamp 
> > 
> > readings.append(new_object) 
> > 
> > #print(new_object, mapper.identity_key_from_instance(new_object)) 
> > #session.add(new_object) 
> > 
> > row_format = "{:>15}" * (len(header) + 1) 
> > 
> > print("Before adding to the session") 
> > print(row_format.format("", *header)) 
> > for reading in readings: 
> > insp = inspect(reading) 
> > row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
> > insp.detached, insp.deleted, reading in session] 
> > print(row_format.format("", *row)) 
> > 
> > session.add_all(readings) 
> > 
> > print("\n#Elements in the session") 
> > print(session) 
> > for element in session: 
> > print(element) 
> > 
> > print("\nAfter adding to the session") 
> > print(row_format.format("", *header)) 
> > for reading in readings: 
> > insp = inspect(reading) 
> > row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
> > insp.detached, insp.deleted, reading in session] 
> > print(row_format.format("", *row)) 
> > 
> > These are some results I obtained by comparing wheter the objects in my 
> list 
> > are in the session or not 
> > 
> > 
> > 
> > 
> > As you can observe, according to the results above the objects are 
> indeed 
> > inside the session but for some reason when I try to print whats 
> contained 
> > in the session by doing 
> > 
> > for element in session: 
> >print(element) 
> > 
> > I just get a None, what am I doing wrong? I dont see anything wrong in 
> my 
> > code, I hope you can help me clarify this. Thanks in advance. 
> > 
> > I will attach both my code and the tests data in case you want to try it 
> by 
> > yourself. 
> > 
> > 
> > 
> > 
> > 
> > On Thursday, July 13, 2017 at 8:27:04 AM UTC-5, Mike Bayer wrote: 
> >> 
> >> On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo 
> >>  wrote: 
> >> > Hello, I am using SQLAlchemy version 1.2.0b1 
> >> > 
> >> > 
> >> > 
> >> > So far so go, the problem arises when I add readings to the session 
> via 
> >> > session.add_all(readings). I only get the last element in my list 
> added, 
> >> > e.g. 
> >> 
> >> there's no reason at all that would happen, other than what's in 
> >> "readings" is not what you'd expect. 
> >> 
> >> try iterating through every element in "readings" after the add_all(), 
> >> and do "obj in session". 
> >> 
> >> If some of these objects were from a different session, then they may 
> >> be "detached" as you put them in in which case they'd go into 
> >> session.identity_map, not session.new. 
> >> 
> >> 
> >> 
> >> 
> >> > 
> >> > for new in session.new: 
> >> >print(new, mapper.identity_key_from_instance(new_object)) 
> >> > 
> >> >  
> >> > (, (datetime.datetime(2017, 1, 
> 1, 
> >> > 0, 
> >> > 0), 1)) 
> >> > 
> >> > 
> >> > Why is this behavior? I have a test code and the test data in case 
> its 
> >> > needed to reproduce this behavior 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > 
> >> > -- 
> >> > SQLAlchemy - 
> >> > The Python SQL Toolkit and Object Relational Mapper 
> >> > 
> >> > http://www.sqlalchemy.org/ 
> >> > 
> >> > To post example code, please provide an MCVE: Minimal, Complete, and

Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads

2017-07-18 Thread vineet


>
> however, I can't think of any reason public use of .subquery() or 
> .exists() would ever *want* eagerloaded options to take place since by 
> definition .subquery() and .exists() are never used to load objects. 
> So in 1.2 I'll propose to make this automatic
>

Yep, I agree with this! Thanks, I'll use .enable_eagerloads(False) to fix 
it for now.

On Tuesday, July 18, 2017 at 11:56:08 AM UTC-7, Mike Bayer wrote:
>
> On Tue, Jul 18, 2017 at 2:13 PM,   
> wrote: 
> > Hello, I'm running into some difference in behavior for .exists() in 
> > SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads. 
> > 
> > 
> > class A(db.Model): 
> > __tablename__ = 'a' 
> > 
> > id = db.Column(db.Integer, primary_key=True) 
> > b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False) 
> > b = db.relationship('B', lazy='subquery') 
> > 
> > 
> > class B(db.Model): 
> > __tablename__ = 'b' 
> > 
> > id = db.Column(db.Integer, primary_key=True) 
> > 
> > 
> > In SQLAlchemy 1.0.17, I can do all of the following: 
> > 
> > A.query.exists()  # Note that A.b has lazy='subquery' 
> > A.query.options(subqueryload(A.b)).exists() 
> > 
> > # We usually use this in the context of: 
> > db.session.query(A.query.exists()).scalar() 
>
> There is a bug here in add_column() / subqueryload added as 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4033/dont-crash-for-add_columns-when.
>  
>
>
> however if you go into your 1.0 version and instead do lazy="joined" 
> you'll see your EXISTS query rendering out the LEFT OUTER JOIN 
> entirely unnecessarily. 
>
> Current API when you're turning the query into a subquery or exists is 
> to call enable_eagerloads(False): 
>
> print s.query(A).enable_eagerloads(False).exists() 
>
>
> the method is documented as such: 
>
>
> https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=enable_eagerloads#sqlalchemy.orm.query.Query.enable_eagerloads
>  
>
> however, I can't think of any reason public use of .subquery() or 
> .exists() would ever *want* eagerloaded options to take place since by 
> definition .subquery() and .exists() are never used to load objects. 
> So in 1.2 I'll propose to make this automatic, that's 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4032/enable_eagerloads-false-automatically-for.
>  
>
>
>
>
>
>
>
> > 
> > In SQLALchemy 1.1.11, we run into the following error: 
> > A.query.exists()  # Note that A.b has lazy='subquery' 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self) 
> >3046 # .with_only_columns() after we have a core select() so 
> that 
> >3047 # we get just "SELECT 1" without any entities. 
> > -> 3048 return sql.exists(self.add_columns('1').with_labels(). 
> >3049   statement.with_only_columns([1])) 
> >3050 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in 
> statement(self) 
> > 465 """ 
> > 466 
> > --> 467 stmt = self._compile_context(labels=self._with_labels).\ 
> > 468 statement 
> > 469 if self._params: 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in 
> > _compile_context(self, labels) 
> >3363 
> >3364 for entity in self._entities: 
> > -> 3365 entity.setup_context(self, context) 
> >3366 
> >3367 for rec in context.create_eager_joins: 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in 
> > setup_context(self, query, context) 
> >3725 with_polymorphic=self._with_polymorphic, 
> >3726 only_load_props=query._only_load_props, 
> > -> 3727 
> > polymorphic_discriminator=self._polymorphic_discriminator) 
> >3728 
> >3729 def __str__(self): 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in 
> > _setup_entity_query(context, mapper, query_entity, path, adapter, 
> > column_collection, with_polymorphic, only_load_props, 
> > polymorphic_discriminator, **kw) 
> > 257 column_collection=column_collection, 
> > 258 memoized_populators=quick_populators, 
> > --> 259 **kw 
> > 260 ) 
> > 261 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in 
> setup(self, 
> > context, entity, path, adapter, **kwargs) 
> > 515 else: 
> > 516 strat = self.strategy 
> > --> 517 strat.setup_query(context, entity, path, loader, 
> adapter, 
> > **kwargs) 
> > 518 
> > 519 def create_row_processor( 
> > 
> > .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in 
> > setup_query(self, context, entity, path, loadopt, adapter, 
> > column_collection, parentmapper, **kwargs) 
> > 786 orig_query, leftmost_mapper, 
> > 787 leftmost_attr, leftmost_relationship, 
> > --> 788 entity.entity_zero 
> > 789 ) 
> > 790

Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads

2017-07-18 Thread Mike Bayer

On Tue, Jul 18, 2017 at 2:13 PM,   wrote:
> Hello, I'm running into some difference in behavior for .exists() in
> SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads.
>
>
> class A(db.Model):
> __tablename__ = 'a'
>
> id = db.Column(db.Integer, primary_key=True)
> b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False)
> b = db.relationship('B', lazy='subquery')
>
>
> class B(db.Model):
> __tablename__ = 'b'
>
> id = db.Column(db.Integer, primary_key=True)
>
>
> In SQLAlchemy 1.0.17, I can do all of the following:
>
> A.query.exists()  # Note that A.b has lazy='subquery'
> A.query.options(subqueryload(A.b)).exists()
>
> # We usually use this in the context of:
> db.session.query(A.query.exists()).scalar()

There is a bug here in add_column() / subqueryload added as
https://bitbucket.org/zzzeek/sqlalchemy/issues/4033/dont-crash-for-add_columns-when.

however if you go into your 1.0 version and instead do lazy="joined"
you'll see your EXISTS query rendering out the LEFT OUTER JOIN
entirely unnecessarily.

Current API when you're turning the query into a subquery or exists is
to call enable_eagerloads(False):

print s.query(A).enable_eagerloads(False).exists()


the method is documented as such:

https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=enable_eagerloads#sqlalchemy.orm.query.Query.enable_eagerloads

however, I can't think of any reason public use of .subquery() or
.exists() would ever *want* eagerloaded options to take place since by
definition .subquery() and .exists() are never used to load objects.
So in 1.2 I'll propose to make this automatic, that's
https://bitbucket.org/zzzeek/sqlalchemy/issues/4032/enable_eagerloads-false-automatically-for.






>
> In SQLALchemy 1.1.11, we run into the following error:
> A.query.exists()  # Note that A.b has lazy='subquery'
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self)
>3046 # .with_only_columns() after we have a core select() so that
>3047 # we get just "SELECT 1" without any entities.
> -> 3048 return sql.exists(self.add_columns('1').with_labels().
>3049   statement.with_only_columns([1]))
>3050
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in statement(self)
> 465 """
> 466
> --> 467 stmt = self._compile_context(labels=self._with_labels).\
> 468 statement
> 469 if self._params:
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in
> _compile_context(self, labels)
>3363
>3364 for entity in self._entities:
> -> 3365 entity.setup_context(self, context)
>3366
>3367 for rec in context.create_eager_joins:
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in
> setup_context(self, query, context)
>3725 with_polymorphic=self._with_polymorphic,
>3726 only_load_props=query._only_load_props,
> -> 3727
> polymorphic_discriminator=self._polymorphic_discriminator)
>3728
>3729 def __str__(self):
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in
> _setup_entity_query(context, mapper, query_entity, path, adapter,
> column_collection, with_polymorphic, only_load_props,
> polymorphic_discriminator, **kw)
> 257 column_collection=column_collection,
> 258 memoized_populators=quick_populators,
> --> 259 **kw
> 260 )
> 261
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in setup(self,
> context, entity, path, adapter, **kwargs)
> 515 else:
> 516 strat = self.strategy
> --> 517 strat.setup_query(context, entity, path, loader, adapter,
> **kwargs)
> 518
> 519 def create_row_processor(
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in
> setup_query(self, context, entity, path, loadopt, adapter,
> column_collection, parentmapper, **kwargs)
> 786 orig_query, leftmost_mapper,
> 787 leftmost_attr, leftmost_relationship,
> --> 788 entity.entity_zero
> 789 )
> 790
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in
> _generate_from_original_query(self, orig_query, leftmost_mapper,
> leftmost_attr, leftmost_relationship, orig_entity)
> 859 ent['entity'] for ent in
> orig_query.column_descriptions
> 860 ])),
> --> 861 False
> 862 )
> 863
>
> .../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in
> _set_select_from(self, obj, set_base_alias)
> 191
> 192 for from_obj in obj:
> --> 193 info = inspect(from_obj)
> 194 if hasattr(info, 'mapper') and \
> 195 (info.is_mapper or info.is_aliased_class):
>
> .../lib/python2.7/site-packages/sqlalchemy/inspection.pyc in
>

[sqlalchemy] Potential bug with .exists() queries and subqueryloads

2017-07-18 Thread vineet

Hello, I'm running into some difference in behavior for .exists() in 
SQLAlchemy 1.0.17 and 1.1.11, related to subqueryloads.


class A(db.Model):
__tablename__ = 'a'

id = db.Column(db.Integer, primary_key=True)
b_id = db.Column(db.Integer, db.ForeignKey('b.id'), nullable=False)
b = db.relationship('B', lazy='subquery')


class B(db.Model):
__tablename__ = 'b'

id = db.Column(db.Integer, primary_key=True)


In SQLAlchemy 1.0.17, I can do all of the following:

A.query.exists()  # Note that A.b has lazy='subquery'
A.query.options(subqueryload(A.b)).exists()

# We usually use this in the context of:
db.session.query(A.query.exists()).scalar()

In SQLALchemy 1.1.11, we run into the following error:
A.query.exists()  # Note that A.b has lazy='subquery'

.../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in exists(self)
   3046 # .with_only_columns() after we have a core select() so that
   3047 # we get just "SELECT 1" without any entities.
-> 3048 return sql.exists(self.add_columns('1').with_labels().
   3049   statement.with_only_columns([1]))
   3050

.../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in statement(self)
465 """
466
--> 467 stmt = self._compile_context(labels=self._with_labels).\
468 statement
469 if self._params:

.../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in 
_compile_context(self, labels)
   3363
   3364 for entity in self._entities:
-> 3365 entity.setup_context(self, context)
   3366
   3367 for rec in context.create_eager_joins:

.../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in 
setup_context(self, query, context)
   3725 with_polymorphic=self._with_polymorphic,
   3726 only_load_props=query._only_load_props,
-> 3727 
polymorphic_discriminator=self._polymorphic_discriminator)
   3728
   3729 def __str__(self):

.../lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in 
_setup_entity_query(context, mapper, query_entity, path, adapter, 
column_collection, with_polymorphic, only_load_props, 
polymorphic_discriminator, **kw)
257 column_collection=column_collection,
258 memoized_populators=quick_populators,
--> 259 **kw
260 )
261

.../lib/python2.7/site-packages/sqlalchemy/orm/interfaces.pyc in 
setup(self, context, entity, path, adapter, **kwargs)
515 else:
516 strat = self.strategy
--> 517 strat.setup_query(context, entity, path, loader, adapter, 
**kwargs)
518
519 def create_row_processor(

.../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in 
setup_query(self, context, entity, path, loadopt, adapter, 
column_collection, parentmapper, **kwargs)
786 orig_query, leftmost_mapper,
787 leftmost_attr, leftmost_relationship,
--> 788 entity.entity_zero
789 )
790

.../lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc in 
_generate_from_original_query(self, orig_query, leftmost_mapper, 
leftmost_attr, leftmost_relationship, orig_entity)
859 ent['entity'] for ent in 
orig_query.column_descriptions
860 ])),
--> 861 False
862 )
863

.../lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in 
_set_select_from(self, obj, set_base_alias)
191
192 for from_obj in obj:
--> 193 info = inspect(from_obj)
194 if hasattr(info, 'mapper') and \
195 (info.is_mapper or info.is_aliased_class):

.../lib/python2.7/site-packages/sqlalchemy/inspection.pyc in 
inspect(subject, raiseerr)
 73 "No inspection system is "
 74 "available for object of type %s" %
---> 75 type_)
 76 return ret
 77

NoInspectionAvailable: No inspection system is available for object of type 



In particular, a subqueried relationship (either through lazy='subquery' or 
subqueryload(A.b)) causes .exists() to fail. We obviously don't call 
subqueryload(A.b) directly with exists queries, but we have models using 
lazy='subquery' that we use .exists() queries for.

My current workaround is A.query.options(lazyload('*')).exists(). Does this 
seem like a bug introduced in 1.1, or were there any intentional changes 
regarding use of .exists() queries?

Thanks!

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

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

2017-07-18 Thread 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

2017-07-18 Thread 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] Re: Bug report with JSON nulls in 1.1.x

2017-07-18 Thread Mike Bayer

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

this actually depends on if you explicitly set the value in Python.
In your example, you're setting the value to None so that None is what
you'll see in Python.   It's only if you omit the "None" from Python
that we need the "default" to do the translation.   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.


>
>
> 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 \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] Re: Bug report with JSON nulls in 1.1.x

2017-07-18 Thread Mike Bayer

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

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

2017-07-18 Thread 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] Re: Bug report with JSON nulls in 1.1.x

2017-07-18 Thread vineet

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')

db.session.commit()
print a.column1  # None
print a.column2  # None



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.

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

Re: [sqlalchemy] session.add() neglecting some of my objects to be added

Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads

Re: [sqlalchemy] Potential bug with .exists() queries and subqueryloads

[sqlalchemy] Potential bug with .exists() queries and subqueryloads

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

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

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

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

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

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

10 matches

Site Navigation

Mail list logo

Footer information