Re: [sqlalchemy] How to make a SQLAlchemy query using exists with a correlated subquery containing joins?
Hello
I tried the use of where(e) but it fires the following error:
AttributeError: 'Query' object has no attribute 'where'
I tried with filter(e) instead and the query does not fire any error but
the result is not exactly what is expected. Here is the query:
e = session.query(Plant).\
join(Article, Plant.articles).\
join(Catalogitem, Article.catalogitems).\
filter(Catalogitem.marketingseason == marketingseason).\
exists()
taxon = session.query(Taxon).filter(e)
Here is the SQL output:
SELECT botany.taxon.id AS botany_taxon_id
FROM botany.taxon
WHERE EXISTS (SELECT 1
FROM botany.plant
JOIN product.article ON botany.plant.id = product.article.plant_id
JOIN catalog.catalogitem ON product.article.plant_id =
catalog.catalogitem.plant_id AND product.article.article_id =
catalog.catalogitem.article_id
WHERE :param_1 = catalog.catalogitem.marketingseason_id
)
As we can see, the subquery is not correlated to the enclosing query via
the relationship Taxon.plant (the AND botany.plant.taxon_id =
botany.taxon.id is missing in the subquery).
Is it possible to do that?
I join a new file (example2.py) with the new query.
Le mercredi 7 août 2013 20:56:43 UTC+2, Michael Bayer a écrit :
On Aug 7, 2013, at 11:58 AM, Etienne Rouxel
rouxel@gmail.comjavascript:
wrote:
Hello
Long story cut short, I would like to know if it is possible to generate a
query with SQLAlchemy ORM such as the one below and how.
SELECT botany.taxon.id AS botany_taxon_id
FROM botany.taxon
WHERE EXISTS (SELECT 1
FROM botany.plant
JOIN product.article ON botany.plant.id = product.article.plant_id
JOIN catalog.catalogitem ON product.article.plant_id =
catalog.catalogitem.plant_id AND product.article.article_id =
catalog.catalogitem.article_id
WHERE :param_1 = catalog.catalogitem.marketingseason_id
AND botany.plant.taxon_id = botany.taxon.id
)
Put differently, it is like the regular use of EXISTS (
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-exists) but
with a more complex subquery that contains JOINs.
Is it possible to do such a query?
Query has an exists() method that will turn the SELECT you've constructed
into an EXISTS:
http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.exists#sqlalchemy.orm.query.Query.exists
So you say e = query(Plant).join(..).join(..).filter(...).exists();
query(Taxon).where(e).
before we had that method you also could construct the joins using
orm.join() and then use sqlalchemy.exists() to produce a select, but the
Query method is a shortcut on that.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_plant_table = Table('plant', Base.metadata,
Column('id', Integer, primary_key=True),
Column('taxon_id', Integer),
ForeignKeyConstraint(['taxon_id'], ['botany.taxon.id']),
schema='botany'
)
_article_table = Table('article', Base.metadata,
Column('plant_id', Integer, primary_key=True),
Column('article_id', Integer, primary_key=True),
ForeignKeyConstraint(['plant_id'], ['botany.plant.id']),
schema='product'
)
_marketingseason_table = Table('marketingseason', Base.metadata,
Column('id', Integer, primary_key=True),
schema='catalog'
)
_catalogitem_table = Table('catalogitem', Base.metadata,
Column('plant_id', Integer, primary_key=True),
Column('article_id', Integer, primary_key=True),
Column('marketingseason_id', Integer, primary_key=True),
ForeignKeyConstraint(
['plant_id', 'article_id'],
['product.article.plant_id', 'product.article.article_id']),
ForeignKeyConstraint(['marketingseason_id'], ['catalog.marketingseason.id']),
schema='catalog'
)
class Taxon(Base):
__table__ = _taxon_table
class Plant(Base):
__table__ = _plant_table
taxon = relationship('Taxon',
innerjoin=True,
backref=backref('plant',
uselist=False))
class Article(Base):
__table__ = _article_table
plant = relationship('Plant',
innerjoin=True,
backref=backref('articles',
cascade='all, delete-orphan'))
class Marketingseason(Base
Re: [sqlalchemy] How to make a SQLAlchemy query using exists with a correlated subquery containing joins?
I have managed to add the missing SQL part with the following query: e = session.query(Plant).\ join(Article, Plant.articles).\ join(Catalogitem, Article.catalogitems).\ filter( Catalogitem.marketingseason == marketingseason, Plant.taxon_id == Taxon.id ).\ exists() taxon = session.query(Taxon).filter(e) However, I would like (if possible) to fully take advantage of SQLAlchemy and avoid writing the test with the columns explicitly. Indeed, I have composite primary keys with 4 columns in some of my other real case scenario so that would be great if I could say something like: Plant.taxon == Taxon of the enclosing query. Le jeudi 8 août 2013 09:02:34 UTC+2, Etienne Rouxel a écrit : Hello I tried the use of where(e) but it fires the following error: AttributeError: 'Query' object has no attribute 'where' I tried with filter(e) instead and the query does not fire any error but the result is not exactly what is expected. Here is the query: e = session.query(Plant).\ join(Article, Plant.articles).\ join(Catalogitem, Article.catalogitems).\ filter(Catalogitem.marketingseason == marketingseason).\ exists() taxon = session.query(Taxon).filter(e) Here is the SQL output: SELECT botany.taxon.id AS botany_taxon_id FROM botany.taxon WHERE EXISTS (SELECT 1 FROM botany.plant JOIN product.article ON botany.plant.id = product.article.plant_id JOIN catalog.catalogitem ON product.article.plant_id = catalog.catalogitem.plant_id AND product.article.article_id = catalog.catalogitem.article_id WHERE :param_1 = catalog.catalogitem.marketingseason_id ) As we can see, the subquery is not correlated to the enclosing query via the relationship Taxon.plant (the AND botany.plant.taxon_id = botany.taxon.id is missing in the subquery). Is it possible to do that? I join a new file (example2.py) with the new query. Le mercredi 7 août 2013 20:56:43 UTC+2, Michael Bayer a écrit : On Aug 7, 2013, at 11:58 AM, Etienne Rouxel rouxel@gmail.com wrote: Hello Long story cut short, I would like to know if it is possible to generate a query with SQLAlchemy ORM such as the one below and how. SELECT botany.taxon.id AS botany_taxon_id FROM botany.taxon WHERE EXISTS (SELECT 1 FROM botany.plant JOIN product.article ON botany.plant.id = product.article.plant_id JOIN catalog.catalogitem ON product.article.plant_id = catalog.catalogitem.plant_id AND product.article.article_id = catalog.catalogitem.article_id WHERE :param_1 = catalog.catalogitem.marketingseason_id AND botany.plant.taxon_id = botany.taxon.id ) Put differently, it is like the regular use of EXISTS ( http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-exists) but with a more complex subquery that contains JOINs. Is it possible to do such a query? Query has an exists() method that will turn the SELECT you've constructed into an EXISTS: http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.exists#sqlalchemy.orm.query.Query.exists So you say e = query(Plant).join(..).join(..).filter(...).exists(); query(Taxon).where(e). before we had that method you also could construct the joins using orm.join() and then use sqlalchemy.exists() to produce a select, but the Query method is a shortcut on that. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How to make a SQLAlchemy query using exists with a correlated subquery containing joins?
Ok, thank you very much, one more time! And thank you to Ladislav Lenart as well. Le jeudi 8 août 2013 15:33:58 UTC+2, Michael Bayer a écrit : On Aug 8, 2013, at 2:02 AM, Etienne Rouxel rouxel@gmail.comjavascript: wrote: Hello I tried the use of where(e) but it fires the following error: AttributeError: 'Query' object has no attribute 'where' sorry, I meant filter() I tried with filter(e) instead and the query does not fire any error but the result is not exactly what is expected. Here is the query: e = session.query(Plant).\ join(Article, Plant.articles).\ join(Catalogitem, Article.catalogitems).\ filter(Catalogitem.marketingseason == marketingseason).\ exists() taxon = session.query(Taxon).filter(e) Here is the SQL output: SELECT botany.taxon.id AS botany_taxon_id FROM botany.taxon WHERE EXISTS (SELECT 1 FROM botany.plant JOIN product.article ON botany.plant.id = product.article.plant_id JOIN catalog.catalogitem ON product.article.plant_id = catalog.catalogitem.plant_id AND product.article.article_id = catalog.catalogitem.article_id WHERE :param_1 = catalog.catalogitem.marketingseason_id ) As we can see, the subquery is not correlated to the enclosing query via the relationship Taxon.plant (the AND botany.plant.taxon_id = botany.taxon.id is missing in the subquery). Is it possible to do that? the solution you have where you've named Plant.taxon_id == Taxon.id is the right approach. When your exists() is embedded into an enclosing query, Taxon.id doesn't add Taxon to the local FROM clause and instead knows to correlate to the enclosing query. However, I would like (if possible) to fully take advantage of SQLAlchemy and avoid writing the test with the columns explicitly. Indeed, I have composite primary keys with 4 columns in some of my other real case scenario so that would be great if I could say something like: Plant.taxon == Taxon of the enclosing query. I think that syntax should be added as a supported feature, however you can get that now using this notation, since you just want the join condition of Plant.taxon: e = session.query(Plant).\ join(Article, Plant.articles).\ join(Catalogitem, Article.catalogitems).\ filter(Catalogitem.marketingseason == marketingseason).\ filter(Plant.taxon.expression).\ exists() I join a new file (example2.py) with the new query. Le mercredi 7 août 2013 20:56:43 UTC+2, Michael Bayer a écrit : On Aug 7, 2013, at 11:58 AM, Etienne Rouxel rouxel@gmail.com wrote: Hello Long story cut short, I would like to know if it is possible to generate a query with SQLAlchemy ORM such as the one below and how. SELECT botany.taxon.id AS botany_taxon_id FROM botany.taxon WHERE EXISTS (SELECT 1 FROM botany.plant JOIN product.article ON botany.plant.id = product.article.plant_id JOIN catalog.catalogitem ON product.article.plant_id = catalog.catalogitem.plant_id AND product.article.article_id = catalog.catalogitem.article_id WHERE :param_1 = catalog.catalogitem.marketingseason_id AND botany.plant.taxon_id = botany.taxon.id ) Put differently, it is like the regular use of EXISTS ( http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-exists) but with a more complex subquery that contains JOINs. Is it possible to do such a query? Query has an exists() method that will turn the SELECT you've constructed into an EXISTS: http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.exists#sqlalchemy.orm.query.Query.exists So you say e = query(Plant).join(..).join(..).filter(...).exists(); query(Taxon).where(e). before we had that method you also could construct the joins using orm.join() and then use sqlalchemy.exists() to produce a select, but the Query method is a shortcut on that. -- 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.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. example2.py -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] How to make a SQLAlchemy query using exists with a correlated subquery containing joins?
Hello
Long story cut short, I would like to know if it is possible to generate
a query with SQLAlchemy ORM such as the one below and how.
SELECT botany.taxon.id AS botany_taxon_id
FROM botany.taxon
WHERE EXISTS (SELECT 1
FROM botany.plant
JOIN product.article ON botany.plant.id = product.article.plant_id
JOIN catalog.catalogitem ON product.article.plant_id =
catalog.catalogitem.plant_id AND product.article.article_id =
catalog.catalogitem.article_id
WHERE :param_1 = catalog.catalogitem.marketingseason_id
AND botany.plant.taxon_id = botany.taxon.id
)
Put differently, it is like the regular use of EXISTS
(http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-exists)
but with a more complex subquery that contains JOINs.
Is it possible to do such a query?
If it can help, I have joined a file (example.py) with all the mapped
classes used for the example above and also with a different query that
should returns the same results as the one I would like to build here.
--
Etienne Rouxel
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_plant_table = Table('plant', Base.metadata,
Column('id', Integer, primary_key=True),
Column('taxon_id', Integer),
ForeignKeyConstraint(['taxon_id'], ['botany.taxon.id']),
schema='botany'
)
_article_table = Table('article', Base.metadata,
Column('plant_id', Integer, primary_key=True),
Column('article_id', Integer, primary_key=True),
ForeignKeyConstraint(['plant_id'], ['botany.plant.id']),
schema='product'
)
_marketingseason_table = Table('marketingseason', Base.metadata,
Column('id', Integer, primary_key=True),
schema='catalog'
)
_catalogitem_table = Table('catalogitem', Base.metadata,
Column('plant_id', Integer, primary_key=True),
Column('article_id', Integer, primary_key=True),
Column('marketingseason_id', Integer, primary_key=True),
ForeignKeyConstraint(
['plant_id', 'article_id'],
['product.article.plant_id', 'product.article.article_id']),
ForeignKeyConstraint(['marketingseason_id'], ['catalog.marketingseason.id']),
schema='catalog'
)
class Taxon(Base):
__table__ = _taxon_table
class Plant(Base):
__table__ = _plant_table
taxon = relationship('Taxon',
innerjoin=True,
backref=backref('plant',
uselist=False))
class Article(Base):
__table__ = _article_table
plant = relationship('Plant',
innerjoin=True,
backref=backref('articles',
cascade='all, delete-orphan'))
class Marketingseason(Base):
__table__ = _marketingseason_table
class Catalogitem(Base):
__table__ = _catalogitem_table
article = relationship('Article',
innerjoin=True,
backref=backref('catalogitems',
cascade='all, delete-orphan'))
marketingseason = relationship('Marketingseason',
innerjoin=True,
backref=backref('catalogitems'))
if __name__ == '__main__':
# Initialize database models
engine = create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
Session = sessionmaker(bind=engine)
session = Session()
marketingseason = Marketingseason(id= -2147483648)
taxon = session.query(Taxon).\
filter(
Taxon.plant.has(
Plant.articles.any(
Article.catalogitems.any(
Catalogitem.marketingseason == marketingseason
print taxon
# SQL output:
#
# SELECT botany.taxon.id AS botany_taxon_id
# FROM botany.taxon
# WHERE EXISTS (SELECT 1
# FROM botany.plant
# WHERE botany.taxon.id = botany.plant.taxon_id AND (EXISTS (SELECT 1
# FROM product.article
# WHERE botany.plant.id = product.article.plant_id AND (EXISTS (SELECT 1
# FROM catalog.catalogitem
# WHERE product.article.plant_id = catalog.catalogitem.plant_id AND product.article.article_id = catalog.catalogitem.article_id AND :param_1 = catalog.catalogitem.marketingseason_id)
signature.asc
Description: OpenPGP digital signature
[sqlalchemy] Problems with eager loading instances of a class which is part of an inheritance hierarchy where polymorphic_identity has been set dynamically.
Hello
Better than a long speech, here are two files (eagerloading1.py,
eagerloading2.py) which, I though, were supposed to do the same thing.
Indeed, the difference is that in the first file, the polymorphic_identity
is hard coded and in the second file, it is set dynamically afterward (like
explained
in
http://stackoverflow.com/questions/15112340/how-can-i-set-polymorphic-identity-after-a-class-is-defined).
The result is that both files trigger the query to get all the taxa.
However, the second file also triggers a new query each time there is an
access to the referenced_taxon, which is supposed to be loaded already and
of course, that query in a loop ruins the performances.
So my question is, how can set the polymorphic_identity dynamically and
still avoid the new queries in the loop?
I also provided some the SLQ commands to quickly reproduce the results
(test-data.sql).
Tests have been performed with SQLAlchemy==0.8.2
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_rank_table = Table('rank', Base.metadata,
Column('id', Integer, primary_key=True),
Column('label', String)
)
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer, server_default=FetchedValue()),
Column('rank_id', Integer),
ForeignKeyConstraint(['rank_id'], ['rank.id'])
)
_relationshiptype_table = Table('relationshiptype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('label', String)
)
_taxon_relationship_table = Table('taxon_relationship', Base.metadata,
Column('taxon_id', Integer, primary_key=True),
Column('referenced_taxon_id', Integer, primary_key=True),
Column('relationshiptype_id', Integer, primary_key=True),
ForeignKeyConstraint(['taxon_id'], ['taxon.id']),
ForeignKeyConstraint(['referenced_taxon_id'], ['taxon.id']),
ForeignKeyConstraint(['relationshiptype_id'], ['relationshiptype.id'])
)
class Rank(Base):
__table__ = _rank_table
class Taxon(Base):
__table__ = _taxon_table
__mapper_args__ = {
'polymorphic_on': 'rank_id',
}
rank = relationship('Rank',
innerjoin=True,
backref=backref('taxa'))
class Family(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483640,
}
class Genus(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483636,
}
class Species(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483630,
}
class Subspecies(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483629,
}
class Variety(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483628,
}
class Form(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483626,
}
class Cultivar(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483624,
}
class Group(Taxon):
__mapper_args__ = {
'polymorphic_identity':-2147483623,
}
class Relationshiptype(Base):
__table__ = _relationshiptype_table
class TaxonRelationship(Base):
__table__ = _taxon_relationship_table
relationship_type = relationship('Relationshiptype',
innerjoin=True,
backref=backref('relationships'))
taxon = relationship('Taxon',
innerjoin=True,
primaryjoin='Taxon.id==TaxonRelationship.taxon_id',
backref=backref('relationships',
cascade='all, delete-orphan',
passive_deletes=True))
referenced_taxon = relationship('Taxon',
innerjoin=True,
primaryjoin='Taxon.id==TaxonRelationship.referenced_taxon_id',
backref=backref('referenced_relationships'))
if __name__ == '__main__':
# Initialization
engine = create_engine('postgresql://nurseryacme@localhost:5432/testdatabase')
Session = sessionmaker(bind=engine)
session = Session()
# Load instances
taxa = session.query(Taxon).\
options(joinedload(Taxon.relationships)).\
order_by(Taxon.refno).\
all()
# Try to see if load has worked
for taxon in taxa:
print Taxon (refno)=(%d) % taxon.refno
for relationship in taxon.relationships:
# The below line does not fire any other query
print \tlinked taxon (refno)=(%d) %
[sqlalchemy] Re: Problems with eager loading instances of a class which is part of an inheritance hierarchy where polymorphic_identity has been set dynamically.
Hello Michael Thank you very much for your quick answer. I did provide the file test-data.sql that generates data. Didn't you see it or maybe would you like something different next time? Thank you Le mercredi 31 juillet 2013 10:09:54 UTC+2, Etienne Rouxel a écrit : Hello Better than a long speech, here are two files (eagerloading1.py, eagerloading2.py) which, I though, were supposed to do the same thing. Indeed, the difference is that in the first file, the polymorphic_identity is hard coded and in the second file, it is set dynamically afterward (like explained in http://stackoverflow.com/questions/15112340/how-can-i-set-polymorphic-identity-after-a-class-is-defined ). The result is that both files trigger the query to get all the taxa. However, the second file also triggers a new query each time there is an access to the referenced_taxon, which is supposed to be loaded already and of course, that query in a loop ruins the performances. So my question is, how can set the polymorphic_identity dynamically and still avoid the new queries in the loop? I also provided some the SLQ commands to quickly reproduce the results (test-data.sql). Tests have been performed with SQLAlchemy==0.8.2 -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Changing a value linked with a one-to-one relationship
Hello Michael
Sorry for the late reply.
So, below is my full stack trace:
Traceback (most recent call last):
File
/Users/foobar/Developpement/nursery_project/applications/nurserydb/utils_scripts/test2.py,
line 64, in module
plant.taxon = taxon_new # triggers an IntegrityError
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 303, in __set__
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 804, in set
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 824, in fire_replace_event
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 1131, in emit_backref_from_scalar_set_event
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 638, in append
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 788, in set
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 613, in get
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/strategies.py,
line 524, in _load_for_state
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/strategies.py,
line 585, in _emit_lazyload
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/query.py, line
2104, in all
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/query.py, line
2215, in __iter__
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1138, in _autoflush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1817, in flush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1935, in _flush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/util/langhelpers.py,
line 58, in __exit__
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1899, in _flush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/unitofwork.py,
line 372, in execute
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/unitofwork.py,
line 525, in execute
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/persistence.py,
line 58, in save_obj
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/persistence.py,
line 491, in _emit_update_statements
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
662, in execute
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
761, in _execute_clauseelement
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
874, in _execute_context
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
1024, in _handle_dbapi_exception
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/util/compat.py, line
163, in raise_from_cause
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
867, in _execute_context
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/default.py,
line 324, in do_execute
sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
taxon_id violates not-null constraint
'UPDATE botany.plant SET taxon_id=%(taxon_id)s WHERE botany.plant.id =
%(botany_plant_id)s' {'taxon_id': None, 'botany_plant_id': -2147483643}
I guess it correspond to what you mentioned.
I have tried to use what you told :
with session.no_autoflush:
plant.taxon = taxon_new
...and it works fine.
But the thing is that I don't need it in case I comment out the line :
taxon_old = session.query(Taxon).get(-2147483634)
and the test:
print plant.taxon is taxon_old # True
It seems that the problems occurs because the taxon_old is already loaded
in the identity map so the taxon.plant of the already loaded taxon_old must
be changed to reflect the change made by the below line:
plant.taxon = taxon_new # triggers an IntegrityError
This is totally understandable.
However, I get confused by SQLAlchemy trying to set taxon_id to NULL or
even worse, by SQLAlchemy trying to delete the plant if I configure a
cascade='all, delete-orphan' on the backref side of the relationship. I
understand that it might not be easy for SQLAlchemy to understand what I
am trying to do, but the fact that I end up with two different results
depending on whether or not taxon_old is previously loaded is confusing.
Anyway, SQLAlchemy is still very impressive and never ceases to amaze me
every day I discover new features. Thank you for that.
Le mardi 4 juin 2013 16:38:30 UTC+2, Etienne Rouxel a écrit :
Hello
I would like to change a value in a one-to-one relationship but I cannot
because of some actions that SQLAlchemy try to do, and I don't know why.
Here is my simplified code :
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_foliagetype_table = Table('foliagetype', Base.metadata,
Column
[sqlalchemy] Changing a value linked with a one-to-one relationship
Hello
I would like to change a value in a one-to-one relationship but I cannot
because of some actions that SQLAlchemy try to do, and I don't know why.
Here is my simplified code :
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_foliagetype_table = Table('foliagetype', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_plant_table = Table('plant', Base.metadata,
Column('id', Integer, primary_key=True),
Column('taxon_id', Integer),
Column('foliagetype_id', Integer),
ForeignKeyConstraint(['taxon_id'], ['botany.taxon.id']),
ForeignKeyConstraint(['foliagetype_id'], ['botany.foliagetype.id']),
schema='botany'
)
class Taxon(Base):
__table__ = _taxon_table
class Foliagetype(Base):
__table__ = _foliagetype_table
class Plant(Base):
__table__ = _plant_table
taxon = relationship('Taxon',
backref=backref('plant',
uselist=False))
foliagetype = relationship('Foliagetype',
backref=backref('plants'))
if __name__ == '__main__':
engine = create_engine('postgresql://xxx@localhost:5432/xxx')
Session = sessionmaker(bind=engine)
session = Session()
taxon_old = session.query(Taxon).get(-2147483634)
taxon_new = session.query(Taxon).get(-2147483645)
foliagetype_old = session.query(Foliagetype).get(-2147483646)
foliagetype_new = session.query(Foliagetype).get(-2147483645)
plant = session.query(Plant).get(-2147483643)
print -- Change foliagetype --
print plant.foliagetype is foliagetype_old # True
plant.foliagetype = foliagetype_new
print plant.foliagetype is foliagetype_new # True
print -- Change taxon --
print plant.taxon is taxon_old # True
plant.taxon = taxon_new # triggers an IntegrityError
print plant.taxon is taxon_new
So a plant must have one and exactly one foliagetype and same with the the
taxon : a plant must have one and exactly one taxon. The difference
however, is that a foliagetype can be linked to several plants while a
taxon can only be linked to a single plant.
In my code above the change of foliagetype leads SQLAlchemy to output the
expected SQL :
UPDATE botany.plant SET foliagetype_id= -2147483645 WHERE botany.plant.id =
-2147483643
However, concerning the change of taxon, I don't understand what SQLAlchemy
is doing. It first output:
SELECT botany.plant.id AS botany_plant_id, botany.plant.taxon_id AS
botany_plant_taxon_id, botany.plant.foliagetype_id AS
botany_plant_foliagetype_id
FROM botany.plant
WHERE -2147483634 = botany.plant.taxon_id
and then:
UPDATE botany.plant SET taxon_id=NULL WHERE botany.plant.id = -2147483643
which fails because NULL is not allowed for taxon_id. So, why do not
SQLAlchemy just output this instead?
UPDATE botany.plant SET taxon_id= -2147483645 WHERE botany.plant.id =
-2147483643
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Are consecutive query.get calls supposed to send a single SELECT query?
@Michael Bayer:
I successfully ran your script and like my previous tests using variables
to stored the results of the queries : only a single SQL query was sent.
Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :
Hello
In my program, I was trying to guess why so many SQL queries were sent
while some could have been avoided with the help of the identity map.
So, I reduced my program to what is below and wrote 3 times the same
query.get call and 3 SQL queries were sent to the database server.
Why does SQLAlchemy send 3 times the SQL while it could have done it only
1 time for the first call and use the identity map for the 2 last calls?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_descriptiontype_table = Table('descriptiontype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer),
Column('sortindex', Integer),
Column('designation', String),
schema='botany'
)
class Descriptiontype(Base):
__table__ = _descriptiontype_table
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Are consecutive query.get calls supposed to send a single SELECT query?
My real application seems to share the cause for why the identity map is
not used. Indeed, I have a method that encapsulate the building of a form
using queries. Once the execution goes out of this method, the instances
are cleared out from the identity map.
I guess this is a very common concern, is there any appropriate design
pattern to fully take advantage of the identity map?
Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :
Hello
In my program, I was trying to guess why so many SQL queries were sent
while some could have been avoided with the help of the identity map.
So, I reduced my program to what is below and wrote 3 times the same
query.get call and 3 SQL queries were sent to the database server.
Why does SQLAlchemy send 3 times the SQL while it could have done it only
1 time for the first call and use the identity map for the 2 last calls?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_descriptiontype_table = Table('descriptiontype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer),
Column('sortindex', Integer),
Column('designation', String),
schema='botany'
)
class Descriptiontype(Base):
__table__ = _descriptiontype_table
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Are consecutive query.get calls supposed to send a single SELECT query?
I would like that no garbage collection occurs within a session, is there a
way to configure that behavior?
Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :
Hello
In my program, I was trying to guess why so many SQL queries were sent
while some could have been avoided with the help of the identity map.
So, I reduced my program to what is below and wrote 3 times the same
query.get call and 3 SQL queries were sent to the database server.
Why does SQLAlchemy send 3 times the SQL while it could have done it only
1 time for the first call and use the identity map for the 2 last calls?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_descriptiontype_table = Table('descriptiontype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer),
Column('sortindex', Integer),
Column('designation', String),
schema='botany'
)
class Descriptiontype(Base):
__table__ = _descriptiontype_table
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Are consecutive query.get calls supposed to send a single SELECT query?
Hello
In my program, I was trying to guess why so many SQL queries were sent
while some could have been avoided with the help of the identity map.
So, I reduced my program to what is below and wrote 3 times the same
query.get call and 3 SQL queries were sent to the database server.
Why does SQLAlchemy send 3 times the SQL while it could have done it only 1
time for the first call and use the identity map for the 2 last calls?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_descriptiontype_table = Table('descriptiontype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer),
Column('sortindex', Integer),
Column('designation', String),
schema='botany'
)
class Descriptiontype(Base):
__table__ = _descriptiontype_table
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Are consecutive query.get calls supposed to send a single SELECT query?
@Charlie Clark:
In the documentation
(http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions
see Is the Session a cache ?), it is mentioned that it does not
necessarily performs a query.
@Simon King:
I guess you're the King indeed, I tried:
1. to assign the result of the 3 queries to 3 different variables
2. to assign the result of the 3 queries to the same variable
3. to assign the result of the first query to a variable and not to assign
the result of the 2 last queries
In all these cases, only 1 SQL query were sent so your suspicion looks
correct.
Thank you both.
Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :
Hello
In my program, I was trying to guess why so many SQL queries were sent
while some could have been avoided with the help of the identity map.
So, I reduced my program to what is below and wrote 3 times the same
query.get call and 3 SQL queries were sent to the database server.
Why does SQLAlchemy send 3 times the SQL while it could have done it only
1 time for the first call and use the identity map for the 2 last calls?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_descriptiontype_table = Table('descriptiontype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer),
Column('sortindex', Integer),
Column('designation', String),
schema='botany'
)
class Descriptiontype(Base):
__table__ = _descriptiontype_table
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Are consecutive query.get calls supposed to send a single SELECT query?
@Michael Bayer:
The database type used is integer with a range from -2147483648 to
+2147483647 according to the documentation
(http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html).
I just started at the beginning of the range, that is why I have negative
numbers.
I performed all the tests mentioned earlier by using positive numbers and I
have the same results.
When you say that it is seems a bit suspect, are you talking about my
problem in particular or about using negative number in general?
Le mardi 21 mai 2013 17:31:10 UTC+2, Etienne Rouxel a écrit :
Hello
In my program, I was trying to guess why so many SQL queries were sent
while some could have been avoided with the help of the identity map.
So, I reduced my program to what is below and wrote 3 times the same
query.get call and 3 SQL queries were sent to the database server.
Why does SQLAlchemy send 3 times the SQL while it could have done it only
1 time for the first call and use the identity map for the 2 last calls?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_descriptiontype_table = Table('descriptiontype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer),
Column('sortindex', Integer),
Column('designation', String),
schema='botany'
)
class Descriptiontype(Base):
__table__ = _descriptiontype_table
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] backref relationship not set as soon as declared
Hello
I don't understand why the backref 'relation_b' of relationship
'relation_a' is not properly set when declaring 'relation_a'. Below is an
example that highlight this situation.
I must probably miss the philosophy being relation backref. What should I
do to get backref working from the beginning?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_relation_a_table = Table('relation_a', Base.metadata,
Column('id', Integer, primary_key=True)
)
_relation_b_table = Table('relation_b', Base.metadata,
Column('id', Integer, primary_key=True),
ForeignKeyConstraint(['id'], ['relation_a.id'])
)
class RelationA(Base):
__table__ = _relation_a_table
class RelationB(Base):
__table__ = _relation_b_table
relation_a = relationship('RelationA', backref='relation_b')
if __name__ == '__main__':
engine = create_engine('postgresql://@localhost:5432/')
Session = sessionmaker(bind=engine)
session = Session()
print RelationA.__dict__.has_key('relation_b')
q1 = session.query(RelationA).outerjoin(RelationA.relation_b)
print RelationA.__dict__.has_key('relation_b')
q2 = session.query(RelationA.id).outerjoin(RelationA.relation_b)
# Output:
# False
# True
# Output when q1 (line 31) is commented out:
# False
# False
# Traceback (most recent call last):
# File /my_path/dummy.py, line 35, in module
#q2 = session.query(RelationA.id).outerjoin(RelationA.relation_b)
# AttributeError: type object 'RelationA' has no attribute 'relation_b'
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Unexpected SQL output for query with contains_eager and innerjoin
Hello Michael, thank you for your answer.
It is written in the documentation
(http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#sqlalchemy.orm.relationship)
:
*innerjoin=False* –
when True, joined eager loads will use an inner join to join against
related tables instead of an outer join. The purpose of this option is
generally one of performance, as inner joins generally perform better than
outer joins. Another reason can be the use of with_lockmode, which does
not support outer joins.
This flag can be set to True when the relationship references an object
via many-to-one using local foreign keys that are not nullable, *or when
the reference is one-to-one or a collection that is guaranteed to have one
or at least one entry*.
So here I am not trying to confuse the query, it is just that there is at
least one entry for RelationB.relation_c. So I thought that SQLAlchemy
would have inferred that the only solution here was to use a LEFT OUTER
JOIN.
I have the same result if I remove the lazy=joined and if I use the query
:
q3 = session.query(RelationA).\
outerjoin(RelationA.relation_b).\
options(contains_eager(RelationA.relation_b)).\
options(joinedload(RelationA.relation_b, RelationB.relation_c))
So, if I understand well, there is nothing wrong with my mapping, right?
The wrong part is just the query and I should fix it as you previously
mentioned, right?
Le mardi 9 avril 2013 16:25:52 UTC+2, Michael Bayer a écrit :
oh and also, make the join an outer by adding the option
joinedload(RelationB.relation_c, innerjoin=False)
On Apr 9, 2013, at 10:16 AM, Michael Bayer
mik...@zzzcomputing.comjavascript:
wrote:
On Apr 9, 2013, at 8:12 AM, Etienne Rouxel
rouxel@gmail.comjavascript:
wrote:
Hello
I am wondering why the outputs q1 and q2 below are not the same. Is it a
bug?
its not a bug. You have lazy='joined' and innerjoin=true on
RelationB.relation_c, and automatic joined loading isn't automated to the
degree that it detects that RelationB is actually the target of an
explicit outer join.If you try to confuse Query like that it will
gladly go along :).
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_relation_a_table = Table('relation_a', Base.metadata,
Column('id', Integer, primary_key=True),
Column('refno', Integer)
)
_relation_b_table = Table('relation_b', Base.metadata,
Column('id', Integer, primary_key=True),
ForeignKeyConstraint(['id'], ['relation_a.id'])
)
_relation_c_table = Table('relation_c', Base.metadata,
Column('id', Integer, primary_key=True),
ForeignKeyConstraint(['id'], ['relation_b.id'])
)
class RelationA(Base):
__table__ = _relation_a_table
class RelationB(Base):
__table__ = _relation_b_table
relation_a = relationship('RelationA',
innerjoin=True,
backref=backref('relation_b'))
class RelationC(Base):
__table__ = _relation_c_table
relation_b = relationship('RelationB',
innerjoin=True,
backref=backref('relation_c',
innerjoin=True,
lazy='joined'))
if __name__ == '__main__':
# Initialize database models
engine = create_engine('postgresql://xxx@localhost:5432/xxx')
Session = sessionmaker(bind=engine)
session = Session()
q1 = session.query(RelationA).\
options(joinedload(RelationA.relation_b))
q2 = session.query(RelationA).\
outerjoin(RelationA.relation_b).\
options(contains_eager(RelationA.relation_b))
print q1
print q2
#Actual and expected SQL output for q1:
#SELECT
#relation_a.id AS relation_a_id,
#relation_a.refno AS relation_a_refno,
#relation_b_1.id AS relation_b_1_id,
#relation_c_1.id AS relation_c_1_id
#FROM relation_a
#LEFT OUTER JOIN relation_b AS relation_b_1 ON relation_a.id =
relation_b_1.id
#LEFT OUTER JOIN relation_c AS relation_c_1 ON relation_b_1.id =
relation_c_1.id
#Actual SQL output for q2 (with JOIN):
#SELECT
#relation_a.id AS relation_a_id,
#relation_a.refno AS relation_a_refno,
#relation_b.id AS relation_b_id,
#relation_c_1.id AS relation_c_1_id
#FROM relation_a
#LEFT OUTER JOIN relation_b ON relation_a.id = relation_b.id
#JOIN relation_c AS relation_c_1 ON relation_b.id = relation_c_1.id
#Expected SQL output for q2 (with LEFT OUTER JOIN):
#SELECT
#relation_a.id AS relation_a_id,
#relation_a.refno AS relation_a_refno,
#relation_b.id AS relation_b_id,
#relation_c_1.id AS relation_c_1_id
#FROM relation_a
#LEFT OUTER JOIN relation_b ON relation_a.id = relation_b.id
#LEFT OUTER JOIN relation_c AS relation_c_1
Re: [sqlalchemy] Unexpected SQL output for query with contains_eager and innerjoin
All right, thank you very much. Le mardi 9 avril 2013 17:27:38 UTC+2, Michael Bayer a écrit : On Apr 9, 2013, at 11:21 AM, Etienne Rouxel rouxel@gmail.comjavascript: wrote: Hello Michael, thank you for your answer. It is written in the documentation ( http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#sqlalchemy.orm.relationship) : *innerjoin=False* – when True, joined eager loads will use an inner join to join against related tables instead of an outer join. The purpose of this option is generally one of performance, as inner joins generally perform better than outer joins. Another reason can be the use of with_lockmode, which does not support outer joins. This flag can be set to True when the relationship references an object via many-to-one using local foreign keys that are not nullable, *or when the reference is one-to-one or a collection that is guaranteed to have one or at least one entry*. So here I am not trying to confuse the query, it is just that there is at least one entry for RelationB.relation_c. So I thought that SQLAlchemy would have inferred that the only solution here was to use a LEFT OUTER JOIN. it only infers that when it follows along a chain of generations from joinedload(). An outerjoin() applied to the enclosing query is not detected. I have the same result if I remove the lazy=joined and if I use the query : q3 = session.query(RelationA).\ outerjoin(RelationA.relation_b).\ options(contains_eager(RelationA.relation_b)).\ options(joinedload(RelationA.relation_b, RelationB.relation_c)) So, if I understand well, there is nothing wrong with my mapping, right? The wrong part is just the query and I should fix it as you previously mentioned, right? yeah, just send along more options that correct its behavior in this case. -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
