Delete all entries except identifier available in python list

Question

I want to delete all records in mysql db except the record identifier that I have in the list. The length of this list can vary and can easily contain 2000+ id, ...

I am currently converting my list to a string so that it fits into something like this: cursor.execute ("" remove from the table where id is not in (% s) "" ", (list)) Which does not seem to be correct , and I have no idea how long the list is resolved, ....

What is the most efficient way to do this from python?

Changing the structure of the table with an additional field for marking / unchecking for deletion would be great, but not an option. If a dedicated table storing an identifier would really be useful, then this can only be done using an SQL query ... but I would really like to avoid these parameters, if possible.

Thank,

Answer 1

If the db table is not too large, just read all the identifiers and create a list of the ones you want to delete:

keep_ids=[...]
cursor.execute('SELECT id FROM table')
delete_ids=[]
for (row_id,) in cursor:
    if row_id not in keep_ids:
        delete_ids.append(row_id)
cursor.executemany('DELETE FROM table WHERE id = %s',delete_ids)

If the db table is huge, recreate the table:

keep_ids=[...]
cursor.execute('CREATE TABLE IF NOT EXISTS temp_table LIKE table')
cursor.executemany('INSERT INTO temp_table (SELECT * FROM table WHERE id = %s)',keep_ids)
cursor.execute('DROP TABLE table')
cursor.execute('ALTER TABLE temp_table RENAME table')

Answer 2

SQL? , , , . , , SQL, , :

','.join(str(int(x)) for x in ids)

, , , . , NOT IN (...), , .

Answer 3

"todelete tinyint (1) not null default 1", 0 , , delete from table where todelete;. , .

, , . .

Answer 4

. , , DBM, . :

CREATE TABLE words (id integer primary key not null, word string);
CREATE TEMPORARY TABLE exclusion (word string);
INSERT INTO words VALUES ... # 100,000 of these
INSERT INTO exclusion VALUES ... # 1000 of these
DELETE FROM words WHERE words.word NOT IN (SELECT word FROM exclusion);
# 99,000 records now in words, table exclusion evaporates when the session is over

-, SQL, , . , - . MySQL , CREATE/DROP, , overlong.

, Python , . , .