CODEWITHSUNDEEP
pythonsqlite
Berry Tsakala
Berry Tsakala

Reputation: 16620

sqlite + python using a WHERE ... IN as a parameter > 999 --- or just escape

consider this pseudo code:

items = ["aaa", "bbb", "cc%'`cc"]
sql = "select * from table where item IN (?)"
sqlite3.execute( sql, (items, ) )

this will fail:

InterfaceError: Error binding parameter 0 - probably unsupported type.

There are many Q&A about it here in StackOverflow.

Some answer that you can pre-generate a param-list as this '?,?,?...' by ','.join(len(items)*['?'].

besides being awkward and unreadable answer, it will fail if the list of items > 999.

  1. Is there a way around that limitation?
  2. If not, how can I escape ("quote") a string in pysqlite?

i'd expect to build something like:

escaped_items = map(sqlite3.escape, items)
sqlite3.execute( sql, (escaped_items, ) )

Upvotes: 0

Views: 218

Answers (1)

CL.
CL.

Reputation: 180162

Constructing such a large parameter list would be inefficient.

Just create a temporary table to hold the values:

sqlite3.execute("CREATE TEMPORARY TABLE items(x)")
sqlite3.executemany("INSERT INTO items VALUES (?)", items);
sqlite3.execute("SELECT ... WHERE item in items")
...
sqlite3.execute("DROP TABLE items")

Upvotes: 1

Related Questions