On 10/01/2010 10:00 PM, Edward Williams wrote: > Hi, > > I'm working on an application where queries are created completely by > the user. (The goal is to make it easy for the user). Because of this > I have to support dynamic query creation and manipulation. My current > model has a query class with my own created specification for queries > which is manipulated by the user via the GUI. This is then turned into > an SQLA query when it's run. Well, it will be turned into one. > > 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 -- 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.