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
> <[email protected]<javascript:>>
> 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 [email protected].
To post to this group, send email to [email protected].
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):
__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__':
engine = create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
Session = sessionmaker(bind=engine)
session = Session()
marketingseason = Marketingseason(id= -2147483648)
e = session.query(Plant).\
join(Article, Plant.articles).\
join(Catalogitem, Article.catalogitems).\
filter(Catalogitem.marketingseason == marketingseason).\
exists()
# The use of where(e) fires the following error
# AttributeError: 'Query' object has no attribute 'where'
# taxon = session.query(Taxon).where(e)
# The use of filter(e) instead is fine but the result is not exactly what is
# expected. Indeed, the subquery is not correlated via the relationship Taxon.plant.
taxon = session.query(Taxon).filter(e)
print taxon
# 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
# )
