mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Question

Following is my python script:

#!/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()

I am getting the following error and need help in getting rid of this error:

/usr/bin/python ./test.py
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, EVISTA_INST_ID, EVISTA_AMI_ID, 
EVISTA_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 , %(EVISTA_INST_ID)s , %(EVISTA_AMI_ID)s , %(EVISTA_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)
Traceback (most recent call last):
  File "./test.py", line 18, in <module>
    cursor.execute(insert_sql, insert_data)
  File "/usr/lib/python2.6/site-packages/mysql/connector/cursor.py", line 498, in execute
    "Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

There's way too many for me to count, but it looks like your values list has an extra value, or that your column name list is missing one. — dursk
– dursk, Commented Dec 29, 2014 at 21:22
To use "named placeholders" in mysql connector, as you're doing, the second argument to execute should be a dictionary having those names as its keys, not a tuple (as you're using) or list. Alternatively, use unnamed placeholders such as '?'. — Alex Martelli
– Alex Martelli, Commented Dec 29, 2014 at 21:22
@AlexMartelli You should post this comment as an answer. However, I think unnamed placeholders should be %s rather than ?. — augurar
– augurar, Commented Dec 30, 2014 at 1:46
@augurar, done. You're right, it seems the qmark stile is not accepted -- I wonder why I thought otherwise. — Alex Martelli
– Alex Martelli, Commented Dec 30, 2014 at 2:05

Alex Martelli · Accepted Answer · 2014-12-30 02:02:19Z

3

To use "named placeholders" in mysql connector, as you're doing, the second argument to execute should be a dictionary having those names as its keys, not a tuple (as you're using) or list. Alternatively, use the "unnamed placeholder" '%s'.

answered Dec 30, 2014 at 2:02

Alex Martelli

887k175 gold badges1.3k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Obsidian · Accepted Answer · 2019-06-22 14:13:32Z

1

This problem is because of parenthesis. here is my code. Check this out. This is absolute.

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")

edited Jun 22, 2019 at 14:13

Obsidian

3,9328 gold badges20 silver badges33 bronze badges

answered Jun 22, 2019 at 12:14

Kazi

191 bronze badge

1 Comment

tripulse Over a year ago

This is not how a person answers on StackOverflow.

Collectives™ on Stack Overflow

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related