CODEWITHSUNDEEP
pythonsqlite
manoelpqueiroz
manoelpqueiroz

Reputation: 637

How to safely use the IN command with Python SQLite?

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?

Upvotes: 3

Views: 122

Answers (3)

Belhadjer Samir
Belhadjer Samir

Reputation: 1659

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()

Upvotes: 2

forpas
forpas

Reputation: 164139

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.

Upvotes: 1

Dan D.
Dan D.

Reputation: 74655

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)) + ')'

Upvotes: 3

Related Questions