并非所有参数都在 SQL 语句中使用（Python、MySQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20818155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Not all parameters were used in the SQL statement (Python, MySQL)
提问by Enthuziast
I get an error on the following Python code:
我在以下 Python 代码中收到错误消息：
import mysql.connector
cnx = mysql.connector.connect(user='root', password='',
host='127.0.0.1',
database='DB')
cursor = cnx.cursor()
Name = "James"
Department = "Finance"
StartYear = 2001
CurrentPos = 2001
Link = ""
add_user = ("INSERT INTO DB.tbluser "
"(username, department, startyear, currentpos, link) "
"VALUES (%s, %s, %d, %d, %s)")
data_user = (Name, Department, StartYear, CurrentPos, Link)
cursor.execute(add_user, data_user)
cnx.commit()
cursor.close()
cnx.close()
The error message is
错误信息是
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement
Do you understand why?
你明白为什么吗？
采纳答案by unutbu
The parameter marker is %snot %d.
参数标记%s不是%d。
add_user = """INSERT INTO DB.tbluser
(username, department, startyear, currentpos, link)
VALUES (%s, %s, %s, %s, %s)"""
Note that the parameter markersused by mysql.connectormay look the same as the %sused in Python string formatting but the relationship is only coincidental. Some database adapters like oursqland sqlite3use ?as the parameter marker instead of %s.
请注意，by 使用的参数标记mysql.connector可能看起来与%sPython 字符串格式中使用的相同，但这种关系只是巧合。一些数据库适配器喜欢oursql和sqlite3使用?的参数标记代替%s。
回答by Gaddafi adamu
use single quotations
使用单引号
sql = 'insert into Student (id, fname, lname, school) values(%s, %s, %s , %s)'
values = (4, "Gaddafi", "Adamu", "Informatic")
a.execute(sql, values)
mydb.commit()
print(a.rowcount, "record inserted.")
回答by Sujan Poudel
add_user = '''("INSERT INTO DB.tbluser " "(username, department, startyear, currentpos, link) " "VALUES (%s, %s, %s, %s, %s)")'''
add_user = '''("INSERT INTO DB.tbluser " "(username, Department, startyear, currentpos, link) " "VALUES (%s, %s, %s, %s, %s)")'''
=> you are using multi line statement so use triple single quotation marks here and use %s to represent passing value as string then that will works because %d is not supported by mysql to pass value
=> 您正在使用多行语句，因此在此处使用三重单引号并使用 %s 将传递的值表示为字符串，然后这将起作用，因为 mysql 不支持 %d 传递值
