On Nov 19, 2012, at 2:44 PM, Ralph Heinkel wrote:
> Hi everybody,
>
> I've banging my head against this for hours ... maybe someone can help?
>
> For a mapped class called 'Person' I'm trying to do something like:
>
> query(Person).filter(Person.status_id.in_('select status_id from
> myschema.mytable where statusname=:n1', n1='hired'))
>
> Obviously this does not work but shows what I want to do. There is no mapping
> or SA table available for 'myschema.mytable' and I cannot provide one. So I
> have to apply raw SQL in the subselect, and I need a binding variable.
> Can anyone help how to wrap the raw SQL into something that works in the
> 'in_'-clause?
first off, there's always a "Table" available, since you can just make one ad
hoc, using either Table with a throwaway MetaData, or you can use the lowercase
versions (sqlalchemy.sql.table, sqlalchemy.sql.column) for something even more
transitory. You only need those names that the SQL statement needs. Since
you have a schema name here, probably need to use Table:
from sqlalchemy import Table, Column, Integer, String, MetaData
mytable = Table('mytable', MetaData(), Column('status_id', Integer),
Column('statusname', String), schema="myschema")
Person.status_id.in_(select([mytable.c.status_id]).where(statusname='hired'))
but with that aside, you can use text():
from sqlalchemy import text, bindparam
Person.status_id.in_(text("select status_id ...",
bindparams=[bindparam('n1', 'hired')]))
and the bind values you can add later too:
from sqlalchemy import text
Person.status_id.in_(text("select status_id ...")).params(n1='hired')
>
> Any help would be very much appreciated.
>
> Thanks,
>
> Ralph
>
>
> --
> 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/-/iMQWfRnwrOAJ.
> 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.
--
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.
