In the following example, I would like to dynamically populate the values of the CTE in the query from a list, using the standard Python SQLite3 API:
-- test schema
CREATE TABLE t AS SELECT "" AS k FROM (VALUES (1),(3));
-- query to build
WITH cte(k) AS (VALUES
(1),
(2),
(3)
) SELECT * FROM t INNER JOIN cte USING(k);
This doesn't work:
import sqlite3
SQL_INIT="""
CREATE TABLE t AS SELECT "" AS k FROM (VALUES (1),(3));
"""
SQL_QUERY="""
WITH cte(k) AS (VALUES
(:values)
) SELECT * FROM t INNER JOIN cte USING(k);
"""
conn = sqlite3.connect(':memory:')
conn.executescript(SQL_INIT)
params = dict(
values=(1,2,3)
)
for row in conn.execute(SQL_QUERY, params):
print(row)
So is there a way to use placeholders to populate the CTE values from the Python code? If no, what would be a good workaround? Do I really have to resort to plain string manipulation and manual values sanitization?
For the sake of simplicity, the example above populates the CTE with integer values. But the solution should work with numbers and strings.