Use of locals() with SQL placeholders

"Peter J. Holzer" <[email protected]> Thu, 3 Sep 2026 11:50:24 +0200

Newsgroups	gmane.comp.python.general
Message-ID	<upvyatibrtuhm2ikp7kyxc4eelxdrxsc4zkurciisiarutbtbq@uqnozvw5mmh4>

The Python database API allows the use of a dict to specify the values
for the placeholders in the SQL statement.

For example:

    csr = conn.cursor()
    ...
    csr.execute(
        """
        select feature as name, description as label
        from service s join permission p on s.id = p.service
        where type = %(service_type)s
            and p.login = %(login_id)s
            and p.start_date <= %(horizon)s
            and %(horizon)s < coalesce(p.end_date, 'infinity')
        order by sort, description, feature
        """,
        {
            "service_type": "login",
            "login_id": 12345,
            "horizon": datetime.datetime.now(),
        }
    )

Quite often the values already exist in local variables:

    service_type = "login"
    login_id = 12345
    horizon = datetime.datetime.now()

Then the dict often becomes a bit repeitive:

    csr.execute(
        """ ... omitted for brevity ... """,
        {
            "service_type": service_type,
            "login_id": login_id,
            "horizon": horizon,
        }
    )

Therefore I have recently started to use locals() for the dict:

    csr.execute(
        """ ... omitted for brevity ... """,
        locals()
    )

Pros:

 * it looks very clean
 * it makes the code shorter
 * The names of the placeholders are always in sync with the names of
   local variables

Cons:

 * Less explicit, so it may not be as obvious what the parameters are
 * All local variables are exposed to execute, not just those it needs

What do you guys think?

        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (application/pgp-signature, 833 B)