How to populate CTE values from a list?

user_2363712 0
Viewed 1153

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.

python-3.5 common-table-expression python-3.x sqlite
2 Answers

I don't actually know if you can use CTE syntax this way from the Python API. Assuming you can, then the problem you are facing is that you are trying to bind a collection to a single placeholder in the query. Instead, you may try binding the collection to a prepared statement which has the correct number of ? placeholders.

vals = (1, 2, 3)
vals_clause = '(?)' + ',(?)'*(len(vals)-1)
sql = 'WITH cte(k) AS (VALUES ' + vals_clause + ') '
sql = sql + 'SELECT * FROM t INNER JOIN cte USING(k)'
for row in conn.execute(SQL_QUERY, vals):
    print(row)

The SQL query generated by the above script is:

WITH cte(k) AS (VALUES (?),(?),(?))
SELECT * FROM t INNER JOIN cte USING(k)

You can see that we generated however many ? placeholders are needed to cover the tuple you pass to the call to execute().

If you can't use the above solution, then it could be perhaps that the API you are using doesn't allow for CTE. We can still rephrase your query as this:

SELECT *
FROM t INNER JOIN
(
    SELECT 1 AS k UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) cte USING(k);

In this case, we are just using a formal subquery to inline the values in a makeshift table. The scripting approach you would use to generate the above for a dynamic tuple of literal values is identical to what I already presented above.

user_18632290

One solution, while not a very elegant one, would be to dynamically create the list of placeholders:

SQL_QUERY_HEAD="""
WITH cte(k) AS (VALUES
"""

SQL_QUERY_TAIL="""
) SELECT * FROM t INNER JOIN cte USING(k);
"""

[...]

values=(1,2,3)

SQL_QUERY=(
    SQL_QUERY_HEAD +
    ",".join(len(values)*("(?)",)) +
    SQL_QUERY_TAIL
)
user_23637120
Related