Path: csiph.com!news.swapon.de!eternal-september.org!feeder3.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Lawrence D'Oliveiro <ldo@nz.invalid>
Newsgroups: comp.databases
Subject: Convenient Iteration Over Query Results
Date: Tue, 23 Apr 2024 00:57:06 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 67
Message-ID: <v07112$17r2v$6@dont-email.me>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 23 Apr 2024 02:57:07 +0200 (CEST)
Injection-Info: dont-email.me; posting-host="e762e53ec9e8808879f7618c3ddfda81"; logging-data="1305695"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+LqvAeSnk94FwL6SRAiGWo"
User-Agent: Pan/0.155 (Kherson; fc5a80b8)
Cancel-Lock: sha1:uQQGCOduc6uJSTmxdt+hrbHxNFQ=
Xref: csiph.com comp.databases:18898

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