why am I getting error when executing insert query

Question

On executing the following code I am getting the error

code:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="proteinsystem"
)

mycursor = mydb.cursor()
name = "test",
surname = "tester",
val1 = 0,
val2 = 0,
val3 = 332,
val4 = 62,
val5 = 181,
val6 = 0,
val7 = 125,
val8 = 490,
mycursor.execute('INSERT INTO ppi2 (name, surname, val1, val2, val3, val4, val5, val6, val7, val8) VALUES (%s,%s,%d,%d,%d,%d,%d,%d,%d,%d)', ('name', 'surname', 'val1', 'val2', 'val3', 'val4', 'val5', 'val6', 'val7', 'val8'))
mydb.commit()

print(mycursor.rowcount, "record inserted.")

error:

cursor.py", line 561, in execute "Not all parameters were used in the SQL statement") mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Does this answer your question? [Not all parameters were used in the SQL statement (Python, MySQL)](https://stackoverflow.com/questions/20818155/not-all-parameters-were-used-in-the-sql-statement-python-mysql) — Bash, Apr 14 '20 at 12:44

Bash · Answer 1 · 2020-04-13T13:15:00.877

1

You're passing in your variable names as strings instead of the variable names themselves. Furthermore, the parameter markers used must all be %s.

mycursor.execute(
    ('INSERT INTO ppi2 (name, surname, val1, val2, val3, val4, val5, val6, val7, val8)',
    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'),
    (name, surname, val1, val2, val3, val4, val5, val6, val7, val8)
)

You can refer to MySQLCursor.execute() for more information on how to parameterize your query.

edited Apr 13 '20 at 13:15

answered Apr 12 '20 at 14:23

Bash

628
6
19

From the link I provided you: `Specify variables using %s or %(name)s parameter style` – Bash Apr 13 '20 at 13:16

why am I getting error when executing insert query

1 Answers1