[sqlalchemy] query.filter AND ( ... OR ... OR ) how to?
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=='') 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 = '' 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 -- 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] query.filter AND ( ... OR ... OR ) how to?
On Wed, Jan 23, 2013 at 11:30 AM, Jose Soares jose.soa...@sferacarta.com 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=='') 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 = '' 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 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] query.filter AND ( ... OR ... OR ) how to?
It works, thanks Simon. j On 01/23/2013 12:53 PM, Simon King wrote: On Wed, Jan 23, 2013 at 11:30 AM, Jose Soares jose.soa...@sferacarta.com 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=='') 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 = '' 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 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.
