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 [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.
