Andor in queries

How to use AND or OR in queries

In SQL, you'd probably do something like this:

*Example1*

SELECT mode from route WHERE mode='bicycle' AND motive='commuting';

In SQLObject, you firstly need to define objects for your database table. Let's assume you have a Route object defined already, and it contains 'mode' and 'motive' attributes. To make the above query, you do this:

*Example2*

query = Route.select(AND(Route.q.mode=="bicycle",Route.q.motive=="commuting"))
*Note* you need to import sqlobject.sqlbuilder in order to use AND and OR.

Now for a slightly more complicated example. Suppose you want to query the database a number of times, to create a report of the number of entries for various different modes of transportation, i.e. you have a list of 'mode' values. You could do something like this:

*Example3*
 
 modeList = ['bicycle','car','boat']
 for m in modeList:
     query = objects.Route.select( eval('objects.Route.q.%mode=="%s"' % (,values[0])) )
     query = Route.select(AND(Route.q.mode==m,Route.q.motive=="commuting"))
     count = query.count()
     print "There were %i routes travelled by %s" % (count, m,)

Now a more complete example showing how to use this concept in a CherryPy app:

*Example4*

#objects.py
  
from sqlobject import *
from MySQLdb import *
from bikeparams import *
from enumlist import *

conn = 'mysql://%s:%s@%s/%s' % (bikeuser, bikepass, bikeseat, bikedb)

motiveList = ['commuting','exercise','errands','leisure']
modeList   = ['bicycle','motorbike','automobile','transit']

class Route(SQLObject):
    """This corresponds to the 'route' table in MySQL.
    Note the link to the 'person' table so we can do joins"""
    _connection = conn
    #id         = IntCol()    #not required, as SQLObject creates this field automatically
    motive      = EnumCol(enumValues = motiveList)
    mode        = EnumCol(enumValues = modeList)

----------
#rowroutes.py

import objects, enumlist, string   #Route is defined in module objects.py
from sqlobject.sqlbuilder import * #AND and OR are defined in this module

class routeDataCount:
    """get the route count by various criteria. 
        you can specify more than one criterion, 
        and whether the criteria should be ANDed or ORed."""

    def __init__(self):
        pass

    def getCount(self, *args, **kwargs):
        """find out how many routes there are for a given criterion"""
        #print "There are %i args and %i kwargs" % (len(args), len(kwargs,))
        #for arg in args: print "arg: %s" % arg
        if len(args) > 0:
            #will be using AND or OR to join criteria?
            andOr = args[0]
            #in case of a typo, use OR
            if string.lower(andOr) not in ['and','or']:
                andOr = 'or'
        else:
            andOr = ''
        if len(kwargs) > 0:
            keys = kwargs.keys()
            values = kwargs.values()
            if len(kwargs) == 1:
                query = objects.Route.select( eval('objects.Route.q.%s=="%s"' % (keys[0],values[0])) )
            else:
                evalList = []
                for kw in kwargs.items():
                    #assemble all criteria into a big string to be evaluated
                    key, val = kw
                    evalList.append('objects.Route.q.%s=="%s"' % (key, val))
                critString = ','.join(evalList)
                evalString = 'objects.Route.select(%s(%s))' % (andOr, critString)
                query = eval(evalString)
        else:
            query = objects.Route.select()
        return query.count()
        
if __name__ == "__main__":
    print "TESTING rowroutes.py\n"
    print "\nMOTIVES\n"
    r = routeDataCount()
    for lis in enumlist.motiveList:
        num = r.getCount('AND', motive=lis, mode='bicycle')
        if num > 0:
            print "routes recorded by %s - %s: %s" % (mode, lis, num)
    print "\nFinished testing."

----------    
#index.py (excerpt)

class StatsPage:
"""this is the page that provides statistics on the database contents"""
@cherrypy.expose
def index(self):
    return renderTemplate(file="static/stats.html")

cherrypy.root.stats = StatsPage()


----------
#stats.html

<py-include="static/head.html"><!-- this just includes initial html tags -->
<link rel="stylesheet" type="text/css" href="/static/bikeroots.css">

<div id="heading"><!-- this draws the header for each page -->
<py-include="static/header.html">
</div>

<div id="content"><!-- this draws the main content of the page -->
<py-code="
from objects import *
import rowroutes

msg = ""

numRoutes = Route.select().count()

# --- Page title --- 

pageTitle = "<h3>Statistics for %s</h3>" % sitename

# --- Trip motives --- 

r = rowroutes.routeDataCount()
msg = ""
vallist = enumlist.motiveList
vallist.sort()
for lis in vallist:
    num = r.getCount('AND', motive=lis)
    if num > 0:
        pct = num*100.0/numRoutes
        msg = msg + " %s: %0.1f%%," % (lis,pct,)
if msg[-1] == ',':
    msg = msg[:-1]
s1left  = "<td class='bold'>Trip<br/>Motives</td>"
s1right = "<td class='stats'>Routes were travelled for these reasons: %s</td>" % msg
s3row   = "<tr>%s%s</tr>" % (s1left,s1right,)

# --- Assemble entire page string --- 

msg = "%s<p><table border=0>%s</table></p>" % (pageTitle, s3row)
">

<!-- now print out our content to the page -->
<py-eval="'%s\n' % msg">
<p>&nbsp;</p>
<p><a href="../../">Back to index</a>.</p>
</div>

<div id="footing"><!-- this draws the page's footer -->
<py-include="static/footer.html">
</div>
<py-include="static/foot.html"><!-- this is just the final html tags -->