Mysql Python: Not all parameters were used in MySQL statement

Question

So I am trying to input data into my table as variables but I seem to keep on getting the error mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement, I have used the %s instead of the row names but I seem to keep on getting the same error. I suspect it is my syntax but I cannot seem to figure it out btw this is my first time using python and MySQL together

import mysql.connector


Sensor_ID = "test"

Location = "room"

Sensor_IP = "192.168.1.1"

Sensor_1 = "10"

Sensor_1_Unit = "*C"

Sensor_2 =""

Sensor_2_Unit = ""

Sensor_3 = ""

Sensor_3_Unit = ""

conn = mysql.connector.connect(user='******', password='********',    host='******', database='*****') #blanked my user n pass
mycursor = conn.cursor()
mycursor.execute('SHOW TABLES')
print(mycursor.fetchall())
print ""

mycursor.execute("SHOW VARIABLES LIKE '%version%'")
print "Version:",(mycursor.fetchall())
#works up to here

mycursor.execute("INSERT INTO iot_sensors VALUES (ID, Sensor_ID, Location, Sensor_IP, Sensor_1, Sensor_1_Unit, Sensor_2,Sensor_2_Unit, Sensor_3,   Sensor_3_Unit)",(Sensor_ID,Location,Sensor_IP,Sensor_1,Sensor_1_Unit,Sensor_2,Sensor_2_Unit,Sens or_3,Sensor_3_Unit))
conn.commit()

  #    Sensor_ID,Location,Sensor_IP,Sensor_1,Sensor_1_Unit,Sensor_2,Sensor_2_Unit,Sensor_3,Sensor_3_Unit

CREATE TABLE IoT_Sensors(

ID INT NOT NULL AUTO_INCREMENT,

Sensor_ID VARCHAR (15) NOT NULL,

Location VARCHAR (20) NOT NULL,

Sensor_IP VARCHAR (15) NOT NULL,

Sensor_1 VARCHAR (15) NOT NULL,

Sensor_1_Unit VARCHAR (15) NOT NULL,

Sensor_2 VARCHAR (15),

Sensor_2_Unit VARCHAR (15),

Sensor_3 VARCHAR (15),

Sensor_3_Unit VARCHAR (15),

Time_Stamp TIMESTAMP NOT NULL,

PRIMARY KEY (ID));

pjz · Accepted Answer · 2017-03-06 05:54:19Z

2

You need to put %s into your SQL statement.

mycursor.execute("INSERT INTO iot_sensors VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",(Sensor_ID,Location,Sensor_IP,Sensor_1,Sensor_1_Unit,Sensor_2,Sensor_2_Unit,Sens or_3,Sensor_3_Unit))

See the example in the docs.

answered Mar 6, 2017 at 5:54

pjz

43.4k6 gold badges54 silver badges60 bronze badges

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

Comments

BK435 · Accepted Answer · 2017-03-07 01:34:46Z

1

Looks like you are missing formatting your actual variables from the insert statement. Try formatting them using one of the known methods, %s or .format method. You are also not using the timestamp, last column, value in your table when inserting. If you just reference the table you will have a column mismatch. You will have to be explicit about what columns you are populating. You could use CURRENT_TIMESTAMP for that since it says NOT NULL for column description.

query = ("""Insert into iot_sensors (Sensor_ID, Location, Sensor_IP, Sensor_1, Sensor_1_Unit, Sensor_2, Sensor_2_Unit, Sensor_3, Sensor_3_Unit) values
                    ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');""".format(Sensor_ID,Location,Sensor_IP,Sensor_1,Sensor_1_Unit,Sensor_2,Sensor_2_Unit,Sensor_3,Sensor_3_Unit))
mycursor.execute(query)

or

query = ("""Insert into iot_sensors (Sensor_ID, Location, Sensor_IP, Sensor_1, Sensor_1_Unit, Sensor_2, Sensor_2_Unit, Sensor_3, Sensor_3_Unit) values
    (%s,%s,%s,%s,%s,%s,%s,%s,%s);""" % (Sensor_ID,Location,Sensor_IP,Sensor_1,Sensor_1_Unit,Sensor_2,Sensor_2_Unit,Sensor_3,Sensor_3_Unit))
mycursor.execute(query)

edited Mar 7, 2017 at 1:34

answered Mar 6, 2017 at 6:05

BK435

3,1863 gold badges22 silver badges27 bronze badges

2 Comments

moe.Patel Over a year ago

IndexError: tuple index out of range

BK435 Over a year ago

stackoverflow.com/questions/30059972/…

Collectives™ on Stack Overflow

Mysql Python: Not all parameters were used in MySQL statement

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related