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.
