SQLAlchemy has two general classes of SQL construct - the FromClause and the
ColumnElement. A FromClause is a thing that goes in the FROM list of a SELECT
statement, whereas a ColumnElement goes into the columns clause, WHERE, ORDER
BY, GROUP BY, and ON sections of a SELECT statement. A FromClause represents
a "set of rows" whereas ColumnElement represents "a column in a row".
So with the SELECT statement in SQL, we have a funny overlap of these two
behaviors. A SELECT statement normally is a "set of rows" - we can SELECT
from a SELECT, as in a JOIN:
SELECT * FROM
mytable JOIN (SELECT id from othertable WHERE foo=bar) AS mysubquery ON
mytable.id=mysubquery.id
Above, you can see "mysubquery" is what we often call a "derived table". It
acts just like a table in the statement but gets its data from an embedded
SELECT. So far so good.
But there's another class of "subquery" in SQL, which is when we use a SELECT
statement not in the FROM clause, but in the columns or WHERE clause:
SELECT mytable.id FROM
mytable WHERE mytable.current_other_id=(SELECT id from othertable WHERE
othertable.id=mytable.other_id)
When you run a query like that, the embedded SELECT is evaluated in a "scalar"
context - if that subquery returned more than one column or row, the database
itself raises an error.
So in SQLAlchemy, the difference between a select() that's used as a FromClause
and one as a ColumnElement often needs to be stated specifically. If you use
an expression like "somecolumn == someselect", it will figure out that
"someselect" should be evaluated in a scalar context. But there are some cases
where we accept FromClause and ColumnElement objects equally, namely the
select() function and the session.query() method:
s = select([sometable])
vs.
s = select([sometable.c.x, sometable.c.y])
Query has the same thing going on .
So if "sometable" is actually a select() object itself, it makes sense that by
default select() and Query() will interpret this FromClause as what it is, a
FromClause which should be expanded into its list of columns.
So when you pass a FromClause to select() or Query(), in order for it to be
treated as the less common scalar subquery, you need to explicitly cast it as a
ColumnElement, which is achieved via the as_scalar() or label() methods:
s = select([somestatement.as_scalar()])
or
s = select([somestatement.label("foo")])
I hope this clears up some of the rationale behind this particular API.
On Jul 24, 2012, at 5:46 AM, Alessandro wrote:
> Yes,I know, so simple... but it didn't work for me as far I didn't set the
> "label" for the internal select.
> I try it many times, but always without it; I didn't know it was mandatory.
>
> Thank you.
>
>
>
> first_id_row = s.query(Row.id_row).\
> filter(Row.id_head == Head.id_head).\
> order_by(Row.id_row).\
> limit(1).label("first_id_row")
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/sQWv1yGoC8gJ.
> 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.
--
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.
