Python 和 SQL：从 csv 获取行会导致错误："There are more columns in the INSERT statement than values specified in

Question

我有一个csv文件，其中包含一些记录，我正试图通过Python脚本将这些记录导入SQL表。我的csv文件(现在减少为(只有一行1。以下是我正在尝试做的事情(在成功连接到数据库等之后(：

def add_records():
with open('C:/testing/myCSV.csv') as csvFile:
for row in csvFile:
cursor.execute(
"INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
"VALUES (?)", row
)

无论我如何格式化csv中的数据(现在都是1(，我都会得到错误：

INSERT语句中的列比values子句中指定的值多

Answer 1

您需要在values子句中为要插入的每一列指定一个值(或?占位符(：

cursor.execute(
"INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
"VALUES (?, ?, ?, ?, ?)", row
)

EDIT:
row只是从CSV文件中读取的一行。您可能应该使用csv阅读器将其分解为单独的组件：

with open('C:/testing/myCSV.csv') as csvFile:
csvReader = csv.reader(csvFile)
for row in csvReader:
cursor.execute(
"INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
"VALUES (?, ?, ?, ?, ?)", row
)

Answer 2

with open('C:/testing/myCSV.csv') as csvFile:
factories = csv.reader(csvFile, delimiter=' ')
for row in factories:
cursor.execute(
"INSERT INTO MY_Table (thing1, thing2, thing3, thing4, thing5)"
"VALUES (?, ?, ?, ?, ?)", (''.join(row[0])), (''.join(row[1])), (''.join(row[2])), (''.join(row[3])), (''.join(row[4]))
)