3

Is there a way to dynamically use the SQLite IN command in Python without providing the exact number of placeholders?

For instance, let's say I'm trying to get:

SELECT
*
FROM mytable
WHERE somecol IN (1, 3, 4, 7, 9)

In Python, a placeholder is a tuple of at least length 1, but I'm not sure how (or even if it is possible) to use them with the IN command. I have tried pure tuples, stringed tuples and pure strings, both free and enclosed, all to no avail:

In [1]: import sqlite3
   ...: conn = sqlite3.connect(':memory:')
   ...: cur = conn.cursor()
   ...: l = [(i, chr(i+64)) for i in range(1, 11)]
   ...: cur.execute('CREATE TABLE mytable (somecol INTEGER, char TEXT)')
   ...: cur.executemany('INSERT INTO mytable VALUES(?, ?)', l)
   ...: conn.commit()

In [2]: tup = ((1, 3, 4, 7, 9),)
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tup)
OperationalError                          Traceback (most recent call last)
<ipython-input-2-195e99af7b4f> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tup).fetchall()

OperationalError: near "?": syntax error

In [3]: cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', tup).fetchall()
---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-2-a6c2d28cce18> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', tup).fetchall()

InterfaceError: Error binding parameter 0 - probably unsupported type.

In [4]: tups = tuple(str(i) for i in tup)
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tups)
OperationalError                          Traceback (most recent call last)
<ipython-input-3-195e99af7b4f> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN ?', tups).fetchall()

OperationalError: near "?": syntax error

In [5]: # Empty list due to trying to fetch a somecol string value of "(1, 3, 4, 7, 9)"
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', tups).fetchall()
Out[5]: []

In [6]: stup = (', '.join(str(i) for i in tup[0]),)
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN ?', stup)
OperationalError                          Traceback (most recent call last)
<ipython-input-5-195e99af7b4f> in <module>
----> 1 cur.execute('SELECT * FROM mytable WHERE somecol IN ?', stup).fetchall()

OperationalError: near "?": syntax error

In [7]: # Empty list due to trying to fetch a somecol string value of "1, 3, 4, 7, 9"
   ...: cur.execute('SELECT * FROM mytable WHERE somecol IN (?)', stup).fetchall()
Out[7]: []

I know that if I provide cur.execute('SELECT * FROM mytable WHERE somecol IN (?, ?, ?, ?, ?)', tup[0]).fetchall() I will get the desired result, but that's because I know the length of tup[0] beforehand and adjusted the cursor accordingly. However, this will break down on applications where I can't anticipate that.

I'm almost sure this is actually not feasbile in Python, but I'm wondering why it is and should be so?

Improve this question
2
  • 1
    I can only tell you the reason: Because the underlying C/C++ API doesn't support this, see sqlite.org/c3ref/bind_blob.html Commented Jan 24, 2021 at 22:29
  • tup = (1, 3, 4, 7, 9) ; cur.execute("SELECT * FROM mytable WHERE somecol IN " + str(tup)) Commented Jan 24, 2021 at 22:29

3 Answers 3

Reset to default
3

The sequence placeholder supported by other databases isn't supported by sqlite.

To get around this you just generate as many single placeholders in a sequence as needed. Via like: '(' + ','.join('?'*len(v)) + ')'

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

It's a simple solution, though it requires preparing the query string before executing. Still better than simply formatting the string altogether.
@manoelpqueiroz Evereyting is better than opening your application to SQL injection... cf. xkcd.com/327 ^^
2

Since the argument list can don't have the same length, you must use string formating to build the correct number of parameter marks check this answer here

cur.execute("SELECT * FROM mytable WHERE somecol IN ({seq})".format( seq=','.join(['?']*len(tup[0]))),tup[0]).fetchall()
Improve this answer

2 Comments

Formatting strings goes precisely against the developers' intended use of the module (docs.python.org/3/library/sqlite3.html).
The format will be "select * from mytable where somecol in (?,?,?,?,?)
1

You can join all the values in comma separated string:

tup = (1, 3, 4, 7, 9)
s = ",".join(str(i) for i in tup)

and then use the operator LIKE instead of IN:

sql = "SELECT * FROM mytable WHERE ',' || ? || ',' LIKE '%,' || somecol || ',%';"
cur.execute(sql, (s,)).fetchall()

This way you need only 1 placeholder.

Improve this answer

6 Comments

Not exactly a neat syntax, but hey, that's on SQLite for not having a CONCAT function ¯_(ツ)_/¯ thanks
|| is the concatenation operator for SQLite.
I know, I just find it counterproductive to use a verbose statement like SQLite's instead of a readily available function in other flavours.
Do you really consider this verbose? It is a very simple sql statement.
Just a tad bit, but at the same time it's very ingenious. Will probably mark it as the correct answer if no one comes forward with another plausible solution.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.