On 01/02/2017 11:14 AM, tvial wrote:
Hi and a happy new year to all :)
I am having trouble applying a WHERE clause to a chained SELECT
expression, by referencing the columns of the expression itself. Hard to
put in words, it's better to give an example:
|
fromsqlalchemy importcreate_engine
fromsqlalchemy importTable,Column,Integer,String,MetaData,ForeignKey
engine =create_engine('sqlite:///:memory:')
connection =engine.connect()
metadata =MetaData()
parts =Table('parts',metadata,
Column('id',Integer,primary_key=True),
Column('name',String),
Column('price',Integer),
)
metadata.create_all(engine)
expr =parts.select()
printexpr
# SELECT parts.id, parts.name, parts.price
# FROM parts
#
# -> this is expected
printexpr.c
# ['id', 'name', 'price']
#
# -> OK, I should be able to refer the columns of the result
printexpr.where(expr.c.id ==1)
# SELECT parts.id, parts.name, parts.price
# FROM parts, (SELECT parts.id AS id, parts.name AS name, parts.price AS
price
# FROM parts)
# WHERE id = :id_1
#
# -> Uh oh, the table is joined to itself, which is wrong, and the name `id`
# from the WHERE clause is ambiguous
|
In this simple case, of course it would be better to use:
|
printexpr.where(parts.c.id ==1)
|
and indeed it works as expected.
But in practice I want to be able to build complex expression, and
introduce filters anywhere up in the chain. I suspect I'm demanding too
much -- on the other hand, the expr object exposes the name of its
columns, so it's worth trying. Am I missing something?
the expr object exposes the names of its columns as the "outer" columns.
that is, if you have "SELECT x, y FROM table", that "selectable"
exposes "x" and "y" for subquery use, e.g. "SELECT x, y FROM (SELECT x,
y FROM table) AS foo".
For modifying the SELECT that you have, WHERE criteria is added in terms
of the selectables which that selectable is itself against. In this
case, the "parts" table.
Your concern about building up the chain raises the question, why would
"up the chain" know about "id" but not about "parts" ? These are both
arbitrary names. Building up the chain also usually means you need to
add joins and other things and you definitely need to know about the
tables and such being joined to or from. I can hack you up an easy way
to get "parts.id" out of "your_select / id" but it's not going to work
for any non-trivial case, like if your select is against two tables that
both have "id" and things like that.
Thanks!
Thomas
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
