Python - 删除 SQLITE3 行根据 CSV 数据

Question

Python。我需要根据两个csv文件中的数据从sqlite3数据库中追加和删除行。至于附加它的全部罚款，我有问题删除。你知道吗

我有csv文件要删除，如下所示：

hostname,shortname,env
lx1234.pl.net,lx1234,tst
sp2444445.net,sp2444445,prd

python如下所示：

import sqlite3
import csv

conn = sqlite3.connect("C:\db.sqlite3")
cursor = conn.cursor()
[...]

######---APPEND to DB---######
cursor.execute("CREATE TABLE IF NOT EXISTS itpassed_host (hostname, shortname, env);")
with open('C:\csv\insert.csv','rt') as fin:
    dr = csv.DictReader(fin)
    to_db = [(i['hostname'], i['shortname'], i['env']) for i in dr]

cursor.executemany("INSERT INTO itpassed_host (hostname, shortname, env) VALUES (?, ?, ?);", to_db)
conn.commit()

######---DELETE from DB---######
with open('C:\csv\decom.csv','rt') as fin:
    dr = csv.DictReader(fin)
    del_db = [i['shortname'] for i in dr]
    #print(del_db)

    cursor.executemany("DELETE FROM itpassed_host WHERE shortname = ?;", (del_db,))
    conn.commit()

conn.close()

print（delu db）提供输出：

['lx1234', 'sp2444445']

所以它的字典

它给了我一个错误：

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

如何根据for循环创建的字典删除这些行？你知道吗

Answer 1

您正在传递一个列表的元组，而您应该传递一个元组列表。你知道吗

with open('C:\csv\decom.csv','rt') as fin:
    dr = csv.DictReader(fin)
    del_db = [i['cftshortname'] for i in dr]
    #print(del_db)

    del_db = [(d,) for d in del_db]
    cursor.executemany("DELETE FROM itpassed_host WHERE shortname = ?;", del_db)
    conn.commit()