CODEWITHSUNDEEP
pythonmysqlpython-3.x
B.JOE
B.JOE

Reputation: 87

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

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

Upvotes: 4

Views: 12596

Answers (3)

ASH
ASH

Reputation: 20302

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()

Upvotes: -1

B.JOE
B.JOE

Reputation: 87

simply change insert method to 

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

then it works fine

Upvotes: 1

Lord Elrond
Lord Elrond

Reputation: 16032

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

Upvotes: 5

Related Questions