Oops forgot to tell I'm using SQLAlchemy 1.1.0 or 1.0.9 (tried both).
Le lundi 2 janvier 2017 17:14:42 UTC+1, tvial a écrit :
>
> 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:
>
> from sqlalchemy import create_engine
> from sqlalchemy import Table, 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()
> print expr
> # SELECT parts.id, parts.name, parts.price
> # FROM parts
> #
> # -> this is expected
>
> print expr.c
> # ['id', 'name', 'price']
> #
> # -> OK, I should be able to refer the columns of the result
>
> print expr.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:
> print expr.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?
>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
