Python - SQLite For Loop OperationalError

Question

我已经给了一个凌乱的Excel文件，我正试图转移到整理SQL表。我正在构建一个Python程序来完成转换。有一个名单，有些名字是重复的。我已经将包含所有名称的列放入元组列表中，如:

[(John Christopher, ), (Elizabeth Smith, ), (etc, )]

我已经建立了一个名为Players的SQL表，并有一个名为"id"的空列。我试图通过这个列表迭代，并分配一个唯一的id，每个球员在表中，然后也删除重复的名字在我的表。

但是我一直得到这个错误:

cursor.execute("UPDATE Players SET id = "+str(id)+" WHERE name = "+str(item[0]))
sqlite3.OperationalError: near "Christopher": syntax error

我的问题是什么?

代码如下:

import sqlite3
player_list_start = cursor.execute("SELECT name FROM Players")
saved_list = player_list_start.fetchall()
# number id should start on
id = 1
# list of players to keep track if they are already in the table
names = []
for item in saved_list:
if item[0] not in names:
cursor.execute("UPDATE Players SET id = "+str(id)+" WHERE name = "+str(item[0]))
connection.commit()
names.append(item[0])
id += 1
else:
cursor.execute("DELETE FROM Players WHERE name = "+str(item[0])+" AND id = NULL")
connection.commit()

Answer 1

肯定是sqlite3.OperationalError: near "Christopher": syntax error

下面一行将生成语法错误的SQL语句，因为它没有引用字符串(特别是包括空格)。

cursor.execute("DELETE FROM Players WHERE name = "+str(item[0])+" AND id = NULL")

使用

cursor.execute("DELETE FROM Players WHERE name = '"+str(item[0])+"' AND id = NULL")

或

cursor.execute("DELETE FROM Players WHERE name = ? AND id = NULL", (str(item[0]),))