Database Reference
In-Depth Information
( name , name_map [ name ][ 0 ], name_map [ name ][ 1 ]))
cur_name = name
print ( " date: %s , trip length: %d " % ( trav_date , miles ))
cursor . close ()
An alternative implementation performs summary calculations within the program,
which reduces the number of queries required. If you iterate through the trip list and
calculate the per-driver day counts and mileage totals yourself, a single query suffices:
# get list of trips for the drivers
cursor = conn . cursor ()
cursor . execute ( '''
SELECT name, trav_date, miles FROM driver_log
ORDER BY name, trav_date
''' )
# fetch rows into data structure because we
# must iterate through them multiple times
rows = cursor . fetchall ()
cursor . close ()
# iterate through rows once to construct a dictionary that
# maps each driver name to days on the road and miles driven
# (the dictionary entries are lists rather than tuples because
# we need mutable values that can be modified in the loop)
name_map = {}
for ( name , trav_date , miles ) in rows :
if not name_map . has_key ( name ): # initialize entry if nonexistent
name_map [ name ] = [ 0 , 0 ]
name_map [ name ][ 0 ] += 1 # count days
name_map [ name ][ 1 ] += miles # sum miles
# iterate through rows again to print the report, displaying the
# summary entry for each driver prior to the list of trips
cur_name = ""
for ( name , trav_date , miles ) in rows :
if cur_name != name : # new driver; print driver's summary info
print ( "Name: %s ; days on road: %d ; miles driven: %d " %
( name , name_map [ name ][ 0 ], name_map [ name ][ 1 ]))
cur_name = name
print ( " date: %s , trip length: %d " % ( trav_date , miles ))
Should you require more levels of summary information, this type of problem gets more
difficult. For example, you might want to precede the report that shows driver summa‐
ries and trip logs with a line that shows the total miles for all drivers:
Total miles driven by all drivers combined: 2166
Name: Ben; days on road: 3; miles driven: 362
date: 2014-07-29, trip length: 131
date: 2014-07-30, trip length: 152
date: 2014-08-02, trip length: 79
Name: Henry; days on road: 5; miles driven: 911
date: 2014-07-26, trip length: 115
Search WWH ::




Custom Search