On 03/09/2026 21:50, Peter J. Holzer wrote:
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?
Agree with @Chris, locals brings risk due to lack of
focus/scope/guard-rails.
No mention of particular RDBMS - am assuming a 'grown-up' choice
(because some of the smaller-brethren don't have 'advanced' features).
Somewhat disagree with the idea of t-strings though. When saw first
examples in pre-PEP proposals, thought the DB-i/f a weak justification
if not a re-invention of the wheel. Remember that the DB-people have
been dealing with this i/f for 25+years - separating SQL and data at the
call and/or using prepared-statements.
Whilst do use t-strings 'here' in a limited way, by-and-large don't,
because it provides a (very recent) language-level version of what
already exists. Worse, with psycopg there are 'rumblings in the camp'
that having adopted t-string support (v3.3) it may be dropped (?'safety').
A t-string contains literal-components and interpolations separately.
The execute() renders the t-string - at which time each interpolation is
converted into a real, bound, parameter (on the Python side). At this
time, psycopg2 (per example) will not accept an ordinary-string, per
type-safety.
There's an argument that building the string becomes 'local and visible'
- everything in-line instead of the SQL + data possibility of
disconnect. Hand-building.
However, this is weaker than it may sound. Firstly, type-hints work at
IDE-time, cf execution-time. Secondly, it is possible to inject/append
to the t-string, eg `sql + f"Little Bobby Tables"` because there is no
guard/it's 'silent' and there's no type-error. Hence the accusation of
t-strings being un-safe when compared to the 'traditional' alternatives
provided at the boundary.
NB can't predict how this pessimism might transpire as policy...
The DB-API is tightly constrained: two arguments. The second must be
sequence or mapping. Full stop!
NB different RDBMS-es offer/require different (combinations of)
"paramstyles", but that's somewhat irrelevant here.
NBB "mapping" allows for dict and named-tuple.
In the same way that many of us have a policy that when there are three
or more parameters to pass, we should prefer a (named) structure over
positioning (assumptions), eg a Python function-signature and
named-parameters - which is an implicit dict).
Similarly, when a group of data-items are often used together, they
might be better if formed into a collectively-named structure (with
named-attributes!) and passed en-masse. (exactly the intuition behind
using a dict, above - but also remembering that only need one 'entry' in
the dict for the example query above, whereas a sequence would require
the `horizon` value to be forwarded twice, quite aside from the issue of
relative-positioning).
Accordingly, consider using a dataclass, eg something like:
class feature_search:
service_type: str
login_id: str
horizon: datetime
instantiate by minor variation of existing code:
service_type = "login"
login_id = 12345
horizon = datetime.datetime.now()
code-references become, eg:
login_feature.login_id
which is more verbose (thank you for relief: IDE code-completion) but
also more explicit as to purpose.
then use asdict() at execute() call.
I'd like to think that such will improve error-identification, but can't
point to a good reason right now. Perhaps personal bias?
However, the dataclass will also give an opportunity to think
deliberately about the way data passes across the boundary and if the
Python version/view/format needs to be changed to be DB-compatible
@properties are our friend.
Earlier, embedded a 'hint' about named-tuples. Have not (yet) used in
call, but have been at the return. Now that can build a named-tuple in
the style of a traditional-class or dataclass, rather like the idea of
Python encouraging (my) preference of retaining an immutable copy of the
DB-row - and even if will be UPDATE-ing, still retain 'source' and build
a mutable 'changed' version.
Advanced/asides:
- executemany() bulk writes
- simplifications, eg sqlite
- gotchas with ANY() and IN()
- SQL injection is not as funny as the proverbial XKCD cartoon
- choose one approach and stick with it, ie "consistency"
- levels of 'sophistication' and/or abstraction:
driver-native/cursor (no typing)
-> hand-rolled forms (dataclass or named-tuple = generic, immutable,
deliberate coercion)
-> SQLalchemy and other ORMs (type-maps)
-> Pydantic models (advantages with un-trusted data, strict 'contracts')
- preference for 'self-documenting' contracts for boundaries
- validate at the boundary - they've already thought (hard) about it
- explicit > implicit also means: exclude the irrelevant - locals!
- tying a dataclass to a query (and v-v) may (not) help maintenance/change
- whereas, the DB-API input structure is a rigid one or two inputs,
output offers more variety/control, eg row-factories.
--
Regards,
=dn
--
https://mail.python.org/mailman3//lists/python-list.python.org
