Thanks to both of you for the pointers. Unfortunately, the way this is
meant to work is not really an option for me.
At current, I do
sel2 = sel.where(literal("name = '%s'" % thing))
to get around using a column reference. This works for now, but I was
hoping for a better solution. I might have a look at `_raw_columns` as
suggested to at least verify the existence of the columns to do something
like
def do_query(**keyvalues):
sel = create_complicated_query_with_lots_of_joins_and_labels()
a = and_()
for k, v in keyvalues.items():
column = make_column(k, sel) # use _raw_columns in here to extract the
column reference of `k`?
a.add(column == v)
return sel.where(a)
On Wednesday, May 21, 2014 6:45:21 AM UTC+10, Michael Bayer wrote:
>
>
> On May 19, 2014, at 9:12 PM, gbr <[email protected] <javascript:>>
> wrote:
>
> > 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’)
>
> the error here is that you’re trying to add things to the SELECT in terms
> of the SELECT itself, which would mean that you want to SELECT FROM that
> SELECT.
>
> Consider this statement:
>
> SELECT a, b FROM t1
>
> if we want to add a WHERE, we want it in terms of “a, b”, columns from
> “t1”:
>
> SELECT a, b FROM t1 WHERE a > b
>
> that is, when we say "WHERE a > b”, the parent table of a and b is t1.
>
> if on the other hand, we wanted our SELECT to be a subquery, then we can
> imagine it like this:
>
> (SELECT a, b FROM t1) AS subq
>
> above, “subq” also has columns named “a” and “b”. These correspond to the
> columns t1.a and t1.b, but they are not the same. if we want to SELECT
> from subq and specify a WHERE, it is apparent that we should talk about
> “subq” and the columns there:
>
> SELECT subq.a, subq.b FROM (SELECT a, b FROM t1) AS subq WHERE subq.a >
> subq.b
>
> on the other hand, if we wanted to select from subq and t1 at the same
> time, we refer to these columns distinctly as related to their FROM, or
> what SQLAlchemy calls the “selectable”:
>
> SELECT subq.a, subq.b, t1.a, t1.b FROM t1, (SELECT a, b FROM t1) AS subq
> WHERE subq.a > subq.b AND t1.a > t1.b
>
> the point is, when specifying columns in a WHERE clause or anything else,
> it is essential that the correct parent selectable is used. From a WHERE
> column or similar, the parent selectable of that column will be added to
> the FROM list of the SELECT.
>
> In this case, to add a WHERE clause to “sel” in terms of “product”, we
> must refer to those columns directly:
>
> sel2 = sel.where(product_table.c.name == 'water')
>
> I’d recommend the Core Tutorial at
> http://docs.sqlalchemy.org/en/latest/core/tutorial.html which has lots
> and lots of examples of building up SELECT objects.
>
>
>
>
--
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.
