I'm trying to modify a select statement which is fairly complex and which
is created in a function that I don't want to modify. It is returned from
this function and I'd like to add more WHERE clauses by just referencing
the returned select statement. How can I do this without causing SQLA
wrapping the returned select statement in another select?
See code below for demonstration. `sel` is the complex select statement (in
my application returned from the function) and by `sel2 = sel.where()` I
try to add another clause.
>>> from sqlalchemy import *
>>> metadata = MetaData()
>>> product_table = Table('product', metadata, Column('id', Integer),
Column('name', String(32)))
>>> sel = select(columns=[product_table.c.id.label('product_id'),
product_table.c.name.label('product_name'),
order_table.c.id.label('order_id'),
order_table.c.name.label('order_name')],
from_obj=product_table.join(order_table,
order_table.c.product_id==product_table.c.id))
>>> # Fine
>>> print sel
SELECT product.id AS product_id, product.name AS product_name, "order".id
AS order_id, "order".name AS order_name
FROM product JOIN "order" ON "order".product_id = product.id
>>> # Trying to add a where condition to sel
>>> sel2 = sel.where(sel.c.product_name=='water')
>>> # Which unfortunately wraps the select in another select. Any way of
adding the WHERE to `sel` post construction of `select()`?
>>> print sel2
SELECT product.id AS product_id, product.name AS product_name, "order".id
AS order_id, "order".name AS order_name
FROM (SELECT product.id AS product_id, product.name AS product_name,
"order".id AS order_id, "order".name AS order_name
FROM product JOIN "order" ON "order".product_id = product.id), product JOIN
"order" ON "order".product_id = product.id
WHERE product_name = :product_name_1
>>> # I would have expected:
>>> SELECT product.id AS product_id, product.name AS product_name,
"order".id AS order_id, "order".name AS order_name FROM product JOIN
"order" ON "order".product_id = product.id WHERE product_name =
:product_name_1
--
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/d/optout.
