Re: [sqlalchemy] Trying to apply result of raw sql to an 'in_' clause of mapped table

2012-11-21 Thread 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/


pgpsGOaXAu2Ds.pgp
Description: PGP signature

Re: [sqlalchemy] Trying to apply result of raw sql to an 'in_' clause of mapped table

2012-11-21 Thread Michael Bayer 

On Nov 21, 2012, at 7:05 AM, Ralph Heinkel wrote:

 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?

yeahits 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.

-- 
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.

Re: [sqlalchemy] Trying to apply result of raw sql to an 'in_' clause of mapped table

2012-11-21 Thread 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?


yeahits 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

--
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.

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

2012-11-19 Thread 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

-- 
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.

Re: [sqlalchemy] Trying to apply result of raw sql to an 'in_' clause of mapped table

2012-11-19 Thread Michael Bayer 

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.