Properly parameterize sqlite query with IN operator

Question

I want to delete records from a table where the record IDs match a python list.

Example data:

dbfile = ':memory:'
conn = sqlite3.connect(dbfile)
cur = conn.cursor()
cur.executescript('''BEGIN TRANSACTION;
CREATE TABLE `strat` (
    `Id`    INTEGER NOT NULL,
    `thing1`    TEXT NOT NULL,
    `thing2`    NUMERIC NOT NULL,
    PRIMARY KEY(`Id`)
);
INSERT INTO `strat` (Id,thing1,thing2) 
VALUES (1,'0','delete'),
       (2,'34','delete'),
       (3,'456','delete'),
       (4,'458','keep'),
       (5,'998','keep'),
       (6,'1000','delete'),
       (7,'2001','delete');
COMMIT;''')

cur.execute('''select * from strat'''); cur.fetchall()
#Out[32]: 
#[(1, '0', 'delete'),
 #(2, '34', 'delete'),
 #(3, '456', 'delete'),
 #(4, '458', 'keep'),
 #(5, '998', 'keep'),
 #(6, '1000', 'delete'),
 #(7, '2001', 'delete')]

I find the IDs of records I want to delete from processing elsewhere. Here I want to delete records with IDs of 1,2,3,6,7

delete_list = [1,2,3,6,7]

Desired output:

[(4, '458', 'keep'),
(5, '998', 'keep')]

This doesn't work:

cur.execute('''DELETE FROM strat WEHRE Id IN ?''', delete_list)

But the equivalent does work in the sqlite shell:

DELETE FROM strat WHERE Id IN (1,2,3,6,7)

I have resorted to a loop, which works, but it feels like defeat:

   for id in delete_list:
    cur.execute('''delete from strat where Id = ?''', (id,))
    conn.commit()

cur.execute('''select * from strat'''); cur.fetchall()
#Out[50]: [(4, '458', 'keep'), (5, '998', 'keep')]

lol @ "feels like defeat" ;-) – mechanical_meat Feb 14 '17 at 17:50 — mechanical_meat, Feb 14 '17 at 17:50

mechanical_meat · Accepted Answer · 2017-02-14T17:59:59.020

2

Provided your delete_list isn't overly long you can do something like:

place_holders = ",".join("?"*len(delete_list))
query_string = '''delete from strat where Id in ({})'''.format(place_holders)
cur.execute(query_string, delete_list)

edited Feb 14 '17 at 17:59

answered Feb 14 '17 at 17:52

mechanical_meat

163,903
24
228
223

1

I'm glad that I had a dig about for that answer though, I'm not sure how long it would have taken me to find out the cap of 999 items for an `IN` statement without reading further down that page! – roganjosh Feb 14 '17 at 18:01
1

Ya that's definitely a gotcha in the making... – mechanical_meat Feb 14 '17 at 18:03

Properly parameterize sqlite query with IN operator

1 Answers1