On Tue, Jul 18, 2017 at 2:13 PM, <vin...@benchling.com> 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
> 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
> <type 'NoneType'>
>
>
> 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 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.
