Python SQLite IN With a List: Generate Placeholders and Bind Each Value

Generate one placeholder per value and bind the values separately. Keep SQL structure under your control; do not interpolate the values into the statement.

Check the failure with a small fixture

Binding a Python list to one question mark does not expand it into an SQL IN list.

import sqlite3
con = sqlite3.connect(":memory:")
try:
    con.execute("create table item (id integer, label text)")
    con.executemany("insert into item values (?, ?)", [(1, "a"), (2, "b"), (3, "c")])
    def select_ids(ids):
        if not ids:
            return []
        slots = ",".join("?" for _ in ids)
        sql = "select id from item where id in (" + slots + ") order by id"
        return [r[0] for r in con.execute(sql, ids)]
    assert select_ids([3, 1]) == [1, 3]
    assert select_ids([]) == []
    assert select_ids([1, 1]) == [1]
finally:
    con.close()

What the result means

Only question marks are composed into the SQL string. The data remains in the parameter sequence, so a value is handled as a value rather than parsed as SQL text. The returned order comes from ORDER BY, not from the order of the input list. Repeated requested IDs do not cause repeated rows by themselves.

Choose the correction for your caller

Define empty-input behavior explicitly before composing the query. If the caller expects an empty selection, return it directly as shown. If empty input means no filter, make that a distinct branch with an intentional query. For one placeholder, pass a one-element tuple or list; a plain string is a sequence of characters. For large sets, respect the connection variable limit and consider an owned temporary table with a defined transaction and cleanup lifecycle instead of an unlimited placeholder string.

Limits and the next check

Placeholders bind values, not table names, column names or sort directions. Those require fixed choices or a separate identifier policy. This in-memory example does not establish application transaction, authorization or database performance behavior.

Related: Why the connection context does not close a connection.

The embedded Python example was executed as an isolated local script on November 10, 2026 (KST), using Python 3.11.6. It demonstrates the stated boundary; it is not a test of your application or a promise of compatibility with every Python release.