I am using PyQt5 and a PostgreSQL database.
https://doc.qt.io/qt-5/qsqlquery.html#approaches-to-binding-values
If I use positional placeholders in my query and not bind values to them, query execution will fail:
query.prepare("INSERT INTO t (id) VALUES (?);")
# missing bind values here
query.exec()
print(query.lastError().text())
->
ERROR: wrong number of parameters for prepared statement "qpsqlpstmt_1"
DETAIL: Expected 1 parameters but got 0.
(42601) QPSQL: Unable to create query
This is good as it catches mistakes. I want to use named placeholders though.
If I use named placeholders in my query and not bind values to them, query execution will not fail. Instead a NULL value will be used.
query.prepare("INSERT INTO t (id) VALUES (:foo);")
# missing bind values here
query.exec()
| id |
|---|
NULL |
I do not want that. I want to be able to catch such a misprepared query and tell the user.
How can I know if my query was set up properly, with a value bounded for each of its named placeholders?
query.boundValues() is a dictionary of the placeholders/parameters, all None before you actually bind values. E.g.: {":parameter1": None, ":parameter2": None, ...}
My API users now have to pass a dictionary of the same structure with their wanted values, e.g.: {":parameter1": 42, ":parameter2": None, ...}
The binding is done in a function of my API and it checks for matching parameters between the query's placeholders and the passed data using set comparisons:
missing_values = set(query.boundValues().keys()).difference(values_to_bind.keys())
if missing_values:
raise ValueError(f"Missing input for named placeholder(s): {missing_values}")
missing_placeholders = set(values_to_bind.keys()).difference(query.boundValues().keys())
if missing_placeholders:
raise ValueError(f"Missing placeholder(s) for passed value(s): {missing_placeholders}")
This way I can be sure that there is a 100% match between the named placeholders in the query and the actually bound values.