Trying to apply result of raw sql to an 'in_' clause of mapped table

[Ralph Heinkel]

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?

Any help would be very much appreciated.

Thanks,

Ralph

[Michael Bayer]

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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Ralph Heinkel]

Hi Michael,

thanks for your reply.

> 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')
>

The latter was my favorite, but this seems to fail with

[...]
File
"/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py",
line 1390, in in_
return self._in_impl(operators.in_op, operators.notin_op, other)
File
"/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py",
line 1409, in _in_impl
for o in seq_or_selectable:
TypeError: '_TextClause' object is not iterable

Any idea how to fix this?

Thanks,

Ralph

[Audrius Kažukauskas]

On Wed, 2012-11-21 at 13:05:22 +0100, Ralph Heinkel wrote:
> The latter was my favorite, but this seems to fail with
>
> [...]
> File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py",
> line 1390, in in_
> return self._in_impl(operators.in_op, operators.notin_op, other)
> File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py",
> line 1409, in _in_impl
> for o in seq_or_selectable:
> TypeError: '_TextClause' object is not iterable
>
> Any idea how to fix this?

My first guess is that you're using too old version of SQLAlchemy
(0.5.8). I suggest to try out the latest version 0.7.9.

--
Audrius Kažukauskas
http://neutrino.lt/

[Michael Bayer]

yeah....its that "0.5.8" that's your problem on this one :). That version is three years old. text() inside of in_() started working in the 0.6 series.

[Ralph Heinkel]

>>
>> The latter was my favorite, but this seems to fail with
>>
>> [...]
>> File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py", line 1390, in in_
>> return self._in_impl(operators.in_op, operators.notin_op, other)
>> File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.5.8-py2.7.egg/sqlalchemy/sql/expression.py", line 1409, in _in_impl
>> for o in seq_or_selectable:
>> TypeError: '_TextClause' object is not iterable
>>
>> Any idea how to fix this?
>
> yeah....its that "0.5.8" that's your problem on this one :). That version is three years old. text() inside of in_() started working in the 0.6 series.
>

oh yes, I see, we are far behind ... seems like we definitely have to
migrate ...
Thanks for your help anyway, the other solution with creating
temporary table finally worked even in my antique version of SA!

Thanks a lot,

Ralph