Convenient Iteration Over Query Results
From
Lawrence D'Oliveiro@21:1/5 to
All on Tue Apr 23 00:57:06 2024
Python is such a good fit for building database-centric applications.
For example, a generator function offers a convenient way of wrapping
up the all-too-common sequence of 1) create a cursor, 2) execute a
query on the cursor, then 3) iterate over the results from the query.
Here is a generator function that does the job for SQLite:
def db_iter(conn, cmd, values = (), mapfn = lambda x : x) :
"executes cmd on a new cursor from connection conn and yields" \
" the results in turn."
for item in conn.cursor().execute(cmd, values) :
yield mapfn(item)
#end for
#end db_iter
This one for MySQL/MariaDB:
def db_iter(conn, cmd, values = None, mapfn = None) :
"generator which executes cmd with values in a new cursor on conn," \
" yielding the rows one at a time, optionally mapped through function mapfn."
if mapfn == None :
mapfn = lambda x : x
#end if
cursor = conn.cursor()
cursor.execute(cmd, values)
while True :
next_row = cursor.fetchone()
if next_row == None :
cursor.close()
break
#end if
yield mapfn(next_row)
#end while
#end db_iter
What is the point of the mapfn? This lets you perform useful
transformations on the returned result sequence. For example,
extracting the single item from a sequence of one item:
for planet in db_iter \
(
db,
"select name from planets",
mapfn = lambda x : x[0]
) \
:
print("Another planet: ", planet)
#end for
Or how about extracting a set of fields, and turning the result
records into dicts with field values indexed by field names?
for planet in db_iter \
(
db,
"select planets.name, count(*) as nr_moons from planets inner join"
" moons on planets.id = moons.parent group by planets.name",
mapfn = lambda r : dict(zip(("planet", "nr_moons"), r))
) \
:
print \
(
"Planet name = %s, number of moons = %d"
%
(planet["name"], planet["nr_moons"])
)
#end for
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)