On 06/04/2010 03:56 PM, Kent Bower wrote:
> Nice. That might come in very useful, thanks.
>
> However, I can't quite get the second approach to work:
>
> exq=DBSession.query(Inventory).join(Location).filter(Location.siteid=='03').correlate(Product).subquery()
> DBSession.query(Product).filter(exists(exq)).all()
>
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File
> "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
> line 512, in exists
> return _Exists(*args, **kwargs)
> File
> "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
> line 2806, in __init__
> s = select(*args, **kwargs).as_scalar().self_group()
> File
> "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
> line 237, in select
> return Select(columns, whereclause=whereclause, from_obj=from_obj,
> **kwargs)
> File
> "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
> line 3637, in __init__
> raise exc.ArgumentError("columns argument to select() must "
> ArgumentError: columns argument to select() must be a Python list or
> other iterable
>
>
>
> print exq
> SELECT inventory.productid, inventory.siteid, inventory.locationid,
> inventory.receiptdate, inventory.qty, inventory.lastmovedate
> FROM inventory JOIN locations ON locations.siteid = inventory.siteid
> AND locations.locationid = inventory.locationid
> WHERE locations.siteid = :siteid_1
>
> Any idea what I am doing wrong?
>
1. Oops, looks like exists() does not detect the subquery as a select
statement and tries to create its own. Try replacing .subquery()
with .statement.
2. exq is missing a WHERE clause that relates Inventory to Product.
exq=DBSession.query(Inventory).join(Location).filter(Location.siteid=='03').filter(Location.productid==Product.productid).correlate(Product).statement
DBSession.query(Product).filter(exists(exq)).all()
another alternative is to construct the subquery outside of the ORM.
This at least gives you the nice SELECT 1 output:
exq = select([1], from_obj=orm.join(Inventory, Location))
exq = exq.where(Inventory.productid == Product.productid)
exq = exq.where(Location.siteid == '03')
exq = exq.correlate(Product.__table__)
DBSession.query(Product).filter(exists(exq)).all()
-Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
