我试着把所有的信息从excel放到mysql中,同时处理它有这些问题。
努力解决它!
算上所有的%s,似乎没有遗漏任何一个。
query = """INSERT INTO sanction (id, organization_type, organization, date, decision_number, penalty_type, penalty_way
penalty, violation, execution_period, article, note, type_npa, department, uploaded_date)
VALUES(null, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
for r in range(1, sheet.nrows):
organization_type = sheet.cell(r,1).value
organization = sheet.cell(r,2).value
date = sheet.cell(r,3).value
decision_number = sheet.cell(r,4).value
penalty_type = sheet.cell(r,5).value
penalty_way = sheet.cell(r,6).value
penalty = sheet.cell(r,7).value
violation = sheet.cell(r,8).value
execution_period = sheet.cell(r,9).value
article =sheet.cell(r,10).value
note =sheet.cell(r,11).value
type_npa =sheet.cell(r,12).value
department =sheet.cell(r,13).value
uploaded_date =datetime.now().strftime("%Y-%m-%d %H:%M")
values = (organization_type, organization, date, decision_number, penalty_type,
penalty_way,penalty, violation, execution_period,article, note, type_npa, department,uploaded_date)
mycursor.execute(query, [values])
最佳答案
我注意到可能导致此错误的两件事:
你的变量values
已经是一个tuple
了,所以你不需要把它包装在一个新的list
里面。
也就是说,改变这条线
mycursor.execute(query, [values])
到
mycursor.execute(query, values)
查询中列出目标列名的部分也缺少逗号,介于
penalty_way
和penalty
之间。如果有这么多的争论,我建议重新构造代码,这样您就可以更容易地看到是否遗漏了什么。
例如,这里有一个版本,它将1-3-3-3-3-2格式中的15个参数分为3部分:查询的第一部分、查询的第二部分以及构建
values
元组时。query = """
INSERT INTO sanction (
id,
organization_type, organization, date,
decision_number, penalty_type, penalty_way,
penalty, violation, execution_period,
article, note, type_npa,
department, uploaded_date)
VALUES (
null,
%s, %s, %s,
%s, %s, %s,
%s, %s, %s,
%s, %s, %s,
%s, %s)
"""
for r in range(1, sheet.nrows):
organization_type = sheet.cell(r, 1).value
organization = sheet.cell(r, 2).value
date = sheet.cell(r, 3).value
decision_number = sheet.cell(r, 4).value
penalty_type = sheet.cell(r, 5).value
penalty_way = sheet.cell(r, 6).value
penalty = sheet.cell(r, 7).value
violation = sheet.cell(r, 8).value
execution_period = sheet.cell(r, 9).value
article = sheet.cell(r, 10).value
note = sheet.cell(r, 11).value
type_npa = sheet.cell(r, 12).value
department = sheet.cell(r, 13).value
uploaded_date = datetime.now().strftime("%Y-%m-%d %H:%M")
values = (
# the first value of the INSERT statement will be NULL
organization_type, organization, date, # 3 elements
decision_number, penalty_type, penalty_way, # 3 elements
penalty, violation, execution_period, # 3 elements
article, note, type_npa, # 3 elements
department, uploaded_date, # 2 elements
)
mycursor.execute(query, values)
关于python - 从excel到mysql的格式字符串参数不足,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54771822/