Not all parameters were used in the SQL statement when using python and mysql

Question

hi I am doing the python mysql at this project, I initial the database and try to create the table record, but it seems cannot load data to the table, can anyone here can help me out with this

import mysql.connector
mydb = mysql.connector.connect( host="localhost",user="root",password="asd619248636",database="mydatabase")
mycursor = mydb.cursor()
mycursor.excute=("CREATE TABLE record (temperature FLOAT(20) , humidity FLOAT(20))")
sql = "INSERT INTO record (temperature,humidity) VALUES (%d, %d)"
val = (2.3,4.5)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

and the error shows "Not all parameters were used in the SQL statement") mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

You're creating the table every time (or attempting to)? Does the table already exist? If not, that would presumably be where it is failing. — Anthony, Mar 22 '19 at 02:08
Based on the mysql documentation (https://pynative.com/python-mysql-insert-data-into-database-table/), you should be initializing the cursor to be a prepared statement, like `mycursor = mydb.cursor(prepared=True)` — Anthony, Mar 22 '19 at 02:13
The table record exists since i checked by the other statement already, what i concerned about the error maybe is the insert method — B.JOE, Mar 22 '19 at 02:14
I'd try `sql = "INSERT INTO record (temperature,humidity) VALUES (%s, %s)"` — , Mar 22 '19 at 02:15
From [the docs](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) "The parameters found in the tuple or dictionary params are bound to the variables in the operation. Specify variables using %s or %(name)s parameter style (that is, using format or pyformat style)." — , Mar 22 '19 at 02:16
yeah that works but i dont understand why i need to set value as string , the input should be floating number — B.JOE, Mar 22 '19 at 02:17
I'd ask the developers of the library why. Oh and `%d` is for integers, `%f` is for floats in python string formatting. — , Mar 22 '19 at 02:19
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) — snakecharmerb, Aug 07 '21 at 10:24
%s is used because the operation is parameter substitution as specified in PEP-249, the DB-API spec, not string formatting. The similarity is unfortunate. — snakecharmerb, Aug 07 '21 at 10:25

Lord Elrond · Answer 1 · 2019-03-22T03:13:26.490

4

Changing the following should fix your problem:

sql = "INSERT INTO record (temperature,humidity) VALUES (%s, %s)"
val = ("2.3","4.5") # You can also use (2.3, 4.5)
mycursor.execute(sql,val)

The database API takes strings as arguments, and later converts them to the appropriate datatype. Your code is throwing an error because it isn't expecting %d or %f (int or float) datatypes.

For more info on this you can look here

edited Mar 22 '19 at 03:13

answered Mar 22 '19 at 02:33

Lord Elrond

13,430
7
40
80

Can you edit to explain *why* that change fixes the problem? You're not teaching anyone anything without providing details about why that change is needed. – Ken White Mar 22 '19 at 02:34
@Jane, the `val = (2.3, 4.5)` as floats will work just fine. The library being used only supports `%s` or `%(name)s` for parameter placeholders thus the SQL should use `%s`. See my comment above. – Mar 22 '19 at 02:50
@JustinEzequiel, I'll make a note of that in an edit. You can also pass them as strings. – Lord Elrond Mar 22 '19 at 03:13
Can you take a look here? https://stackoverflow.com/questions/72684970/execution-failed-on-sql-select-name-from-sqlite-master-where-type-table-and-n – Jnl Jun 20 '22 at 11:19

score 1 · Answer 2 · answered Mar 22 '19 at 03:41

1

simply change insert method to

sql = "INSERT INTO record (temperature,humidity) VALUES (%s, %s)"

then it works fine

answered Mar 22 '19 at 03:41

B.JOE

77
1
1
8

Can you take a look here? https://stackoverflow.com/questions/72684970/execution-failed-on-sql-select-name-from-sqlite-master-where-type-table-and-n – Jnl Jun 20 '22 at 11:19

score -1 · Answer 3 · answered Jun 18 '19 at 02:43

This works for me.

# Insert from dataframe to table in SQL Server
import time
import pandas as pd
import pyodbc

# create timer
start_time = time.time()
from sqlalchemy import create_engine


df = pd.read_csv("C:\\your_path_here\\CSV1.csv")

conn_str = (
    r'DRIVER={SQL Server Native Client 11.0};'
    r'SERVER=Excel-Your_Server_Name;'
    r'DATABASE=NORTHWND;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

cursor = cnxn.cursor()

for index,row in df.iterrows():
    cursor.execute('INSERT INTO dbo.Table_1([Name],[Address],[Age],[Work]) values (?,?,?,?)', 
                    row['Name'], 
                    row['Address'], 
                    row['Age'],
                    row['Work'])
    cnxn.commit()
cursor.close()
cnxn.close()

Not all parameters were used in the SQL statement when using python and mysql

3 Answers3