Re: [sqlalchemy] is there a more proper way to chain dynamic or clauses ?
Using sa.tuple_ looks like the nicest solution here, but back to your
original question about building or clauses, expressions built with or_
can be passed as arguments to or_ to build more complex expressions. One
simple iterative way to do this is:
clauses = or_() # empty starting value
for something in criteria:
clauses = or_(clauses, next_clause)
# equivalent to: clauses = clauses | next_clause
query = query.filter(clauses)
But that's just a long-winded way to express a reduce operation*, so for
your example you could also write:
import sqlalchemy as sa
criteria = (('male', 35), ('female', 35))
Useraccount = model.Useraccount
query = session.query(Useraccount)
ands = [sa.and_(Useraccount.gender == gender, Useraccount.age == age)
for
gender, age in criteria]
or_clauses = reduce(sa.or_, ands)
query = query.filter(or_clauses)
* Yes, I know in python 3 they recommend a for loop instead of reduce, but
using it here prevents you from having to create an empty starting value,
and this way you don't have to use an inner loop to mutate a value outside
the loop on each iteration. If you might be dealing with empty ands lists
then you'd need reduce(sa.or_, ands, sa.or_()), which does lose readability.
On Fri, Feb 22, 2013 at 11:58 AM, Conor conor.edward.da...@gmail.comwrote:
On 02/21/2013 07:31 PM, Jonathan Vanasco wrote:
basd on a bunch of error messages, this example works...
criteria = ( ('male',35),('female','35) )
query = session.query( model.Useraccount )
ands = []
for set_ in criteria :
ands.append(\
sqlalchemy.sql.expression.and_(\
model.Useraccoun.gender == set_[0] ,
model.Useraccoun.age == set_[1] ,
)
)
query = query.filter(\
sqlalchemy.sql.expression.or_( *ands )
)
results= query.all()
this seems really awkward though. is there a better way to build up a
set of dynamic or criteria ?
For this specific case, if your database supports it, you can use the
tuple_ construct:
criteria = (('male', 35), ('female', 35))
query = session.query(model.Useraccount)
query = query.filter(sa.tuple_(model.Useraccount.gender,
model.Useraccount.age).in_(criteria))
results = query.all()
It's cleaner and should give better index usage.
-Conor
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: DetachedInstanceError after transaction
Hi Jonathan, It's pyramid_tm -- it will clear the session on commit. It's counter-intuitive (or at least it was for me) if you've spent a lot of time with SQLAlchemy and using sessions directly, but you should try flush instead of commit: print userInstance.id DBSession.flush() print userInstance.id Then your view will still have access to the userInstance object after adding/updating it, but pyramid_tm to commit the transaction for you. I know there's been a lot of discussion about this and there are many other ways of handling it, but from the perspective of porting Pylons to Pyramid + pyramid_tm, swapping out commit() for flush() covers a lot of cases and still lets you handle IntegrityError and other exceptions. We should probably move this to the Pylons/Pyramid list if there are more questions though. Hope this helps! Eric On Tue, Feb 14, 2012 at 2:09 PM, Jonathan Vanasco jonat...@findmeon.comwrote: my stuff doesn't handle the transaction commit - that's purely transaction / pyramid_tm so i'll look into that code to see if its closing it. great lead, thanks. -- 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. -- 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] Re: DetachedInstanceError after transaction
Jonathan, You probably want transaction.savepoint() in that case. There's a pretty extensive discussion here showing savepoints (and rollbacks to savepoints) as a way of creating sub-transactions with pyramid_tm: https://groups.google.com/d/msg/pylons-discuss/5Mj4R3YMXhI/GVFj2Du33JAJ You can of course do all of that in SQLAlchemy without pyramid_tm doing it for you, so if the savepoints don't cover your use cases that's something to consider. -Eric On Tue, Feb 14, 2012 at 2:24 PM, Jonathan Vanasco jonat...@findmeon.comwrote: eric- thanks. I'll post a followup on the pylons list. i've already got a call to session.flush() the problem is that i need the transaction itself committed in this block. i have a series of database transactions that happen within the request. the first database transaction should error out on a fail and it's very important to save that information for perpetuity. subsequent database transactions could fail , and are just hidden. it sounds like the design of pyramid_tm is one transaction per request, which is really weird. -- 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. -- 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] a list as a named argument for an in clause
Another possible approach is using the sql module to build the query: from sqlalchemy import sql ids = [1,2,3] query = sql.select([Table.col1, Table.col2], Table.id.in_(ids)) session.execute(query) I'm not sure how that fits into the larger context of what you're doing, but it's flexible and pretty easy to build the queries dynamically. -Eric On Tue, Jan 24, 2012 at 7:40 AM, Conor conor.edward.da...@gmail.com wrote: On 01/22/2012 01:49 PM, alex bodnaru wrote: hello friends, i'm using sa at a quite low level, with session.execute(text, dict) is it possible to do something in the spirit of: session.execute(select * from tablename where id in (:ids), dict(ids=[1,2,3,4])) ? thanks in advance, alex I'm not aware of a general way to do this. If you are using PostgreSQL+psycopg2, you can use the = ANY(...) operator instead of the IN operator: session.execute(select * from tablename where id = ANY (:ids), dict(ids=[1,2,3,4])) -Conor -- 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. -- 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.
