On Wed, Jan 23, 2013 at 11:30 AM, Jose Soares
<[email protected]> wrote:
> Hi all,
> I'm trying to compile a query to avoid Oracle limit of 1000 in IN():
>
> def chunks(l, n):
> """ Yield successive n-sized chunks from l. """
> for i in xrange(0, len(l), n):
> yield l[i:i+n]
>
>
> qry=session.query(Azienda).fiter(Azienda.c.cap=='1111')
>
> val=[1,3,3,4,3,23,2,4,5,6,7,8,9,90,34,2]
>
> for jj in list(chunks(val, 5)):
> qry = qry.filter(sa.or_(Azienda.c.id.in_( jj )))
>
>
> I expected a query like this one:
>
> SELECT * FROM azienda
> WHERE cap = '1111'
> AND (
> azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s,
> %(id_5)s)
> OR azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s)
> OR azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s, %(id_15)s)
> OR azienda.id IN (%(id_16)s)
> )
>
> instead I got this one:
>
> SELECT * FROM azienda
> WHERE azienda.cap = %(cap)s
> AND azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s)
> AND azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s)
> AND azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s,
> %(id_15)s)
> AND azienda.id IN (%(id_16)s)
>
> How can I do this in the right way?
> thanks for your help.
> j
>
Successive calls to query.filter() always use AND. Each call to
query.filter() further restricts the results that would be returned
from the query.
The "or_" function is meant to take multiple conditions and "OR" them
together, so something like this should do what you want:
conditions = []
for jj in list(chunks(val, 5)):
conditions.append(Azienda.c.id.in_( jj ))
qry = qry.filter(sa.or_(*conditions))
Hope that helps,
Simon
--
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.
