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
Description: PGP signature
-- https://mail.python.org/mailman3//lists/python-list.python.org
