Search code examples
pythonsqlduckdb

DuckDB prepared statement list

I wanted a parameterized query using which I wanted to fetch persons dynamically:

import duckdb
query = "SELECT * FROM person WHERE id IN(?)"
duckdb.execute(query, [[4,5,6]])

This is raising the exception

ConversionException: Conversion Error: Unimplemented type for cast (BIGINT -> INTEGER[])

How to go about it?

Solution

  • The correct way to do this currently is to use IN(SELECT UNNEST(?))

    import duckdb
lst = [1,2,3,4]
res = duckdb.execute("select * from ( VALUES (5),(3),(6),(2),(7) ) t(i) where i IN(SELECT UNNEST(?))", [lst]).fetchall()
print(res)
#[(3,), (2,)]

    This might be improved in the future however