插入失败“操作错误：没有这样的列”

Question

我试图用我试图修复的姓名和电话创建一个数据库，但它随时会重播给我：

File "exm0.py", line 14, in <module>
    cursor.execute("""INSERT INTO Employee VALUES({0}, {1})""".format(k, v))
sqlite3.OperationalError: no such column: dani

代码：

from user_names import *
import sqlite3

# user_names.py
names = ["dani", "eric", "dinna", "tamir", "edan", "daniel", "tomer", 
"noa", "shalev", "tom", "shir"]
phones = ["0432", "5415166255", "61467254", "6146758", "5889482"]

connection = sqlite3.connect("")
cursor = connection.cursor()


cursor.execute("""CREATE TABLE Employee(Name text, Phone int)""")
acc = {}

for k, v in zip(names, phones):
    #acc[k] = v
    cursor.execute("""INSERT INTO Employee VALUES({0}, 
{1})""".format(k, v))

Answer 1

我不熟悉你用的那种事先准备好的陈述方式。此外，最佳实践是始终指定插入的目标列名。尝试此版本：

for k, v in zip(names, phones):
    #acc[k] = v
    cursor.execute("INSERT INTO Employee (Name, Phone) VALUES(?,?)", (k, v))

Answer 2

您正在将值格式化为语句文本，而不引用它们。因此，数据库将它们解释为列名，并因此失败，出现您看到的错误。相反，您可以只传递一个参数数组作为execute函数的第二个参数，并让驱动程序库为您完成繁重的工作：

cursor.execute("INSERT INTO Employee VALUES(?, ?)", [k, v])

Answer 3

不要设置字符串格式，使用带有占位符的准备语句。你知道吗

for k, v in zip(names, phones):
    #acc[k] = v
    cursor.execute("INSERT INTO Employee VALUES(?, ?)", (k, v))

您甚至不需要循环，可以使用executemany方法进行迭代：

cursor.executemany("INSERT INTO Employee VALUES(?, ?)", zip(names, phones))