On Oct 3, 5:40 pm, Conor <conor.edward.da...@gmail.com> wrote: > On 10/01/2010 10:00 PM, Edward Williams wrote:
> > I'm trying to wrap my head around building the WHERE clause. I find > > that when a function returns a condition (age > 18 for example) it > > comes with brackets around it. However, when I enter it into the where > > function directly it doesn't unless it uses an SQLA method like > > column.in_(stuff) . > > > I have some test code I've been playing with below: > > > def condition(age,b): > > return age > b > > > name = tableObjects['users'].c['name'] > > age = tableObjects['users'].c['age'] > > password = tableObjects['users'].c['password'] > > q = select([tableObjects['users']]) > > names = ('Mark','Joel') > > #the query construction > > a = (name.in_(names)) > > > # I enter it into the where method directly: > > q = q.where(name.in_(names)+age>18+~password.like('v')) > > #Results in: > > #WHERE (users.name IN (?, ?)) + users.age > ? + (users.password NOT > > LIKE ?) > > This query doesn't make any sense. Notice that you are using + (as in > "add" or sometimes "concatenate") instead of AND. To use AND, you need a > query like this: > > q = q.where(and_(name.in_(names), > age > 18, > ~password.like('v'))) > > or: > > q = q.where(name.in_(names) > & (age > 18) > & ~password.like('v')) > > > # If functions build the term: > > expr = name.in_(names) + condition(age,18) > > q = q.where(expr) # If placed in where(expr) it results in WHERE > > (users.name IN (?, ?)) + (users.age > ?) > > # Note extra brackets > > result = dataConnections['sqlite:///tutorial.db'].execute(q) > > for row in result: > > print row > > Again, you are adding when you want to be ANDing: > > expr = and_(name.in_(names), condition(age, 18)) > > or: > > expr = name.in_(names) & condition(age, 18) > > > Would the brackets make any difference to the performance? (They > > wouldn't make any meaningful difference to the result as far as I can > > see) > > > I've recently noticed the serializer for SQLA queries. How easy would > > it be to save them with IDs? Also, is it easy to manipulate and adjust > > a specific piece of a query? Like, the second element of a nested > > where clause? If it is, my separate class system may be unnecessary > > overhead (unless I decide the ability to port to other libraries is > > really important...). > > AFAIK there is no public API for modifying clause elements in the way > that you are talking about, so you are probably better off regenerating > them every time. > > -Conor Thanks Conor, that was a real help. That was a pretty bad screwup of mine :/ On the second thing - I'm sticking with my classes; I'd looked at the docs and not really seen it, so it was good to have the API clarified. Edward -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.