I have a simple use case as follows:
Given an employee table containing names and salaries, I want to retrieve
the highest and lowest salaries *AND* the corresponding employee info.
Ideally I'd like to do this in a single database request since in the real
world we like to minimize the number of round trips to the database and
associated db connection requests.
(BTW: getting a db connection can incur significant overhead unless some
form of connection pooling is provided).
My solution (see test code below) creates a raw sql query that includes a
'UNION' clause to combine 2 separate min/max queries and does in fact
require just one db connection.
I'm wondering if it is possible to build this type of query using the DAL
but I suspect it may not be possible (AFAIK the DAL and other ORM's do not
support UNION's). I see 2 issues here that make this difficult with the DAL:
1) In addition to the min/max values I also want the corrsponding record
info in the result set
2) At best the DAL may require 2 separate db requests
Any DAL guru's out there care to comment on this and provide an alternative
DAL solution here.
TIA,
BrendanC
***************************************** DAL Sample Code
***************************************
'''
Example: Find min & max emp salaries and related name using SQL and Web2py
DAL
'''
import os, sys
sdir = '/home/web2py'
sys.path.append(sdir)
try:
from gluon import DAL, Field
except ImportError as err:
print('gluon path not found')
# target db - sqlite
db = DAL("sqlite://emptest.db")
# define emp table
db.define_table('emp',
Field('name', type='string'),
Field('salary', type='double'),
migrate=True)
# define dict with test data
empdict = {'adams': 10, 'gates': 5, 'jobs': 20, 'david': 12, 'williams': 35}
def populate_table():
# insert recs from dict into db
rcnt = 0
for (k,v) in empdict.iteritems():
ddict ={}
ddict['name'] = k
ddict['salary'] = v
print '>>> Inserting New Rec <<<'
try:
db.emp.insert(**ddict)
db.commit()
rcnt += 1
except:
#raise RuntimeError, 'Script error'
print '**** Insert Failed ****'
print sys.exc_info()
db.rollback()
print '\nAdded %s recs to emp table' %rcnt
if __name__ == "__main__":
import datetime
dtstart = datetime.datetime.now()
print '\n>>>>Starting at: %s <<<<\n' %dtstart.ctime()
print '\n>>>>DB Trace Info: Db uri: %s; DB Name: %s\n' %(db._uri,
db._dbname)
#check rec count
cnt = db(db.emp.id>0).count()
#print '>>> Emp Rec Count: %s' %cnt
#reload table
if cnt>0:
# deleting previous records
db(db.emp.id>0).delete()
populate_table()
#select min emp, salary
strsql_min = 'select name, salary from (select min(salary) as minsal
from emp) as x ' + \
'inner join emp as t on t.salary = x.minsal'
strsql_max = 'select name, salary from (select max(salary) as maxsal
from emp) as x ' + \
'inner join emp as t on t.salary = x.maxsal'
# Build a Union Query; returns a pair of dicts with min&max values
strsql = strsql_min + ' UNION ' + strsql_max
rows = db.executesql(strsql, as_dict=True)
mindict = rows[0]
print '\n**** Lowest Salary: Name: %s; \t Salary: %s'
%(mindict['name'], mindict['salary'])
maxdict = rows[1]
print '**** Highest Salary: Name: %s; \t Salary: %s\n'
%(maxdict['name'], maxdict['salary'])
dtstop = datetime.datetime.now()
print '\n>>>>Stopping at: %s <<<<\n' %dtstop.ctime()
