Groups | Search | Server Info | Login | Register

Groups > comp.databases > #18898

Convenient Iteration Over Query Results

Show key headers only | View raw

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

Back to comp.databases | Previous | Next | Find similar

Thread

Convenient Iteration Over Query Results Lawrence D'Oliveiro <ldo@nz.invalid> - 2024-04-23 00:57 +0000

csiph-web