mysql.connector.errors.ProgrammingError:并非所有参数都在SQL语句中使用

Question

下面是我的python脚本：

#!/usr/bin/env python  

import mysql.connector
cnx = mysql.connector.connect(user='qdb1', password='qdb1', host='170.19.17.9', database='qdb1')
cursor = cnx.cursor()

insert_sql = ("insert into qdb1.amis"
              " (CUSTOMER_NAME, AWS_ACCOUNT, AMI_START, CRT_REGION_PRIMARY, CRT_REGION_DR1, CRT_REGION_DR2, DBAPP_INST_ID, DBAPP_AMI_ID, DBAPP_AMI_NAME, VISTA_INST_ID, VISTA_AMI_ID, VISTA_AMI_NAME, WS_INST_ID, WS_AMI_ID, WS_AMI_NAME, DEL_REGION_PRIMARY, DEL_REGION_DR1, DEL_REGION_DR2, DELETED_AMI_PRIMARY, DELETED_SNAP_PRIMARY, DELETED_AMI_DR1, DELETED_SNAP_DR1, DELETED_AMI_DR2, DELETED_SNAP_DR2, SUCCESSFUL) "
              "values ( %(CUSTOMER_NAME)s , %(AWS_ACCOUNT)s , %(AMI_START)s , %(CRT_REGION_PRIMARY)s , %(CRT_REGION_DR1)s , %(CRT_REGION_DR2)s , %(DBAPP_INST_ID)s , %(DBAPP_AMI_ID)s , %(DBAPP_AMI_NAME)s , %(VISTA_INST_ID)s , %(VISTA_AMI_ID)s , %(VISTA_AMI_NAME)s , %(WS_INST_ID)s , %(WS_AMI_ID)s , %(WS_AMI_NAME)s , %(DEL_REGION_PRIMARY)s , %(DEL_REGION_DR1)s , %(DEL_REGION_DR2)s , %(DELETED_AMI_PRIMARY)s , %(DELETED_SNAP_PRIMARY)s , %(DELETED_AMI_DR1)s , %(DELETED_SNAP_DR1)s , %(DELETED_AMI_DR2)s , %(DELETED_SNAP_DR2)s , %(SUCCESSFUL)s)")

print insert_sql

insert_data = ('SERVER1', '68687687876','2014-12-29 13:27:46', 'us-west-9', 'None', 'None', 'i-gtsuid43', 'ami-9jsh222f', 'DBAPP-SERVER', 'i-4wj333e3', 'ami-73eee351', 'VISTA-SERVER', 'i-5464ssse', 'ami-4ddd2853', 'WS-QSERVER', 'none', 'none', 'none', 'none', 'none', 'none', 'none', 'none', 'none', 1)

cursor.execute(insert_sql, insert_data)
cnx.commit()
cursor.close()

我遇到以下错误，需要帮助以消除此错误：

^{pr2}$

Answer 1

这个问题是因为括号。这是我的密码。看看这个。这是绝对的。在

import mysql.connector

my_database = mysql.connector.connect(host="localhost", user="root", passwd="kazinaim", database = "naim")

my_cursor = my_database.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val_1 = [('kazi' , 'jatrafbari'),
    ('kazi' , 'jatrafbari'),
    ('kazi' , 'jatrafbari'), ('kazi', 'jatrafbari'),('kazi' , 'jatrafbari')]



my_cursor.executemany(sql, val_1)

my_database.commit()

print(my_cursor.rowcount, "data inserted")

Answer 2

要在mysql connector中使用“named placeholders”，正如您所做的那样，要执行的第二个参数应该是一个以这些名称作为键的字典，而不是一个元组（如您所使用的那样）或list。或者，使用“unnamed placeholder”'%s'。在