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
– dcg, Commented May 15, 2020 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
– Daniel Ben-Shabtay, Commented May 15, 2020 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 can help. — dcg
– dcg, Commented May 15, 2020 at 13:16
Insert into depatments should be insert into departments. Insert managers is missing — Denis Tsoi
– Denis Tsoi, Commented May 15, 2020 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
– Daniel Ben-Shabtay, Commented May 15, 2020 at 13:47

Daniel Ben-Shabtay · Accepted Answer · 2020-05-15 20:44:41Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related