Python ProgrammingError: Not all parameters were used in the SQL statement

Question

I am coding a new python (version 3.8.2) project with a mysql(8.0.19) db.
This is the table creation code:

import mysql.connector

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "mypassword",
    database = "acme_db"
)

mycursor = mydb.cursor()

sql_formula = ("CREATE TABLE employee (employee_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                                        "first_name VARCHAR(255),"
                                        "last_name VARCHAR(255),"
                                        "email VARCHAR(255),"
                                        "phone_nr VARCHAR(255),"
                                        "hire_date DATE,"
                                        "job_id INTEGER,"
                                        "salary NUMERIC(8,2),"
                                        "commission_pct NUMERIC(8,2),"
                                        "manager_id INTEGER,"
                                        "department_id INTEGER)")
mycursor.execute(sql_formula)

mycursor.execute("CREATE TABLE jobs (job_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, job VARCHAR(255))")

mycursor.execute("CREATE TABLE managers (manager_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, employee_id INTEGER)")

mycursor.execute("CREATE TABLE departments (department_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, department_name VARCHAR(255))")

When I try to insert data:

import mysql.connector
from datetime import datetime

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "mypassword",
    database = "acme_db"
)

mycursor = mydb.cursor()

dptFormula = "INSERT INTO depatments (department_name) VALUES (%s)"
acme_departments = [("Accounting"),("R&D"),("Support")]
mycursor.executemany(dptFormula, acme_departments)

jobFormula = "INSERT INTO jobs (job) VALUES (%s)"
acme_jobs = [("Accountant"),("Developer"),("Team Leader"),("Support Specialist")]
mycursor.executemany(jobFormula, acme_jobs)

mngFormula = "INSERT INTO managers (employee_id) VALUES (%s)"
acme_managers = [(100),(101),(102)]
mycursor.executemany(mngFormula, acme_managers)
mydb.commit()

I got:

Traceback (most recent call last):
  File "c:/Users/Daniel/EmployeeProject/employee_mgt/insert_into.py", line 15, in <module>
    mycursor.executemany(dptFormula, acme_departments)
  File "C:\Program Files\Python38\lib\site-packages\mysql\connector\cursor.py", line 668, in executemany
    stmt = self._batch_insert(operation, seq_params)
  File "C:\Program Files\Python38\lib\site-packages\mysql\connector\cursor.py", line 613, in _batch_insert
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

I defined an auto increment primary key for all tables, so I insert only one parameter in each table with "%s". I don't know why it doesn't work, I was expecting trouble with the 3rd insert that has integers and i am not sure if I should insert like 100 or "100" not with the first insert which is a string.

I think you need to do it this way `dptFormula = "INSERT INTO depatments (department_name) VALUES (%s, %s, %s)"` — dcg, May 15 '20 at 13:10
why? I am trying to add 3 rows to a table that has only one column (beside the pk) — Daniel Ben-Shabtay, May 15 '20 at 13:12
Oh, I can see that, I thought it was three columns. I think what you want is to insert all rows as fast as possible? Then maybe [this](https://stackoverflow.com/a/46548796/4152153) can help. — dcg, May 15 '20 at 13:16
Insert into depatments should be insert into departments. Insert managers is missing — Denis Tsoi, May 15 '20 at 13:33
@Denis Tsoi You are correct, I corrected the typo, but it still doesn't work. Same error. What do you mean Insert managers is missing?, is the last insert. Insert employees is missing because I wanted to populate that table last, after I populate the other 3 tables — Daniel Ben-Shabtay, May 15 '20 at 13:47
Ill have a look later, need to sit in front of a computer to test on ipynb — Denis Tsoi, May 15 '20 at 14:08

score 0 · Answer 1 · answered May 15 '20 at 20:44

The solution is that I didn't remember that I needed to pass a list of tuples for the values, so I add a comma like this:

dptFormula = "INSERT INTO departments (department_name) VALUES (%s)"
acme_departments = [("Accounting",),("R&D",),("Support",)]
mycursor.executemany(dptFormula, acme_departments)

after each value I am passing. Now it works

Python ProgrammingError: Not all parameters were used in the SQL statement

1 Answers1