On Nov 5, 1:39 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> Rodney wrote:
>
> > In Oracle, I can write a query like this:
> > SELECT ...
> > FROM ...
> > WHERE id IN (SELECT f_id FROM f ('param'))
> > to constrain the returned rows to those that have an id in the set
> > returned by the table function f.
>
> > I would like to generate such a query with sqlalchemy like this:
> > q = session.query (...)....filter (id.in_ (sq))
> > but I cannot figure out how to define sq.
>
> > I've tried something like this:
> > sq = session.query ('F_ID').from_statement ('SELECT column_value FROM
> > TABLE (f (:p1))').params(p1 = 'param')
> > but I get:
> > TypeError: 'Annotated_TextClause' object is not iterable
>
> > Any suggestions?
>
> not getting that error:
>
> >>> from sqlalchemy.orm import create_session
> >>> s = create_session()
> >>> q = s.query ('F_ID').from_statement ('SELECT column_value FROM TABLE
>
> (f (:p1))').params(p1 = 'param')>>> print q
>
> SELECT column_value FROM TABLE (f (:p1))
Thanks for the quick response, Mike.
Sorry, I wasn't explicit enough. I got the TypeError when using sq in
my full query, not when creating sq.
> although the "subquery" you're trying to do here, if you intend to stay as
> a straight string, I would just render as is within
> Session.execute("select * from (select x, y, z from ...)"). Query doesn't
> really have the fluency with completely textual expressions you're looking
> for here.
My full query is much more complex and includes various dynamically-
determined constraints, so I'd rather not convert the whole thing to
text.
I'm using text for the subquery merely because I don't know any other
way to generate the query for the table function. (I'd appreciate
pointers to something else.)
I working through an attempt to map a class onto that table function
select, but I haven't done that sort of mapping before and I'm not
sure what's going to happen to the function's parameter since I need
it to be a bind variable.
Rodney
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
