CODEWITHSUNDEEP
mysqlsqlpython-3.x
Alan Cai
Alan Cai

Reputation: 21

Not all parameters were used in the SQL statement when inserting list to mysql

    mycursor.execute("CREATE TABLE test_table3(lane smallint UNSIGNED NOT NULL, previousT int NOT NULL, averageT int NOT NULL, detectionT int NOT NULL, carID int PRIMARY KEY AUTO_INCREMENT)")

                mysql_lane1_output = [1, 28, 28, datetime.datetime(2020, 6, 7, 13, 24, 45, 297050)]
                print(mysql_lane1_output)
                lane_1_param = ','.join('?' * len(mysql_lane1_output))
                mysql1 = 'INSERT INTO test_table3 VALUES(%s);'% lane_1_param
                mycursor.execute(mysql1,mysql_lane1_output)
                mydb.commit()

I have created a table and want to insert list items into it and keep getting the error 'Not all parameters were used in the SQL statement'. I followed the answer on Stackoverflow for this code regarding inserting list to mysql.

Upvotes: 1

Views: 51

Answers (1)

nbk
nbk

Reputation: 49375

This runs in python 3.8.2

With 

import mysql.connector
import datetime

The following code is changed

  • create table now is detectionT DATETIME
  • mysql_lane1_output has now 5 elements
  • lane_1_param is changed to use %s instead of ?

code

 mycursor.execute("CREATE TABLE IF NOT exists test_table3(lane smallint UNSIGNED NOT NULL, previousT int NOT NULL, averageT int NOT NULL, detectionT DATETIME NOT NULL, carID int PRIMARY KEY AUTO_INCREMENT)")
 mysql_lane1_output = [1, 28, 28, datetime.datetime(2020, 6, 7, 13, 24, 45, 297050),None]
 print(mysql_lane1_output)
 lane_1_param = ','.join(len(mysql_lane1_output)*('%s',))
 mysql1 = 'INSERT INTO test_table3 VALUES(%s);'% lane_1_param
 mycursor.execute(mysql1,mysql_lane1_output)
 mydb.commit()

Upvotes: 1

Related Questions