4

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

Improve this question
9
  • 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. Commented Mar 22, 2019 at 2:08
  • Based on the mysql documentation (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) Commented Mar 22, 2019 at 2:13
  • The table record exists since i checked by the other statement already, what i concerned about the error maybe is the insert method Commented Mar 22, 2019 at 2:14
  • I'd try sql = "INSERT INTO record (temperature,humidity) VALUES (%s, %s)" Commented Mar 22, 2019 at 2:15
  • From the docs "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)." Commented Mar 22, 2019 at 2:16

3 Answers 3

Reset to default
5

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

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

4 Comments

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.
@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.
@JustinEzequiel, I'll make a note of that in an edit. You can also pass them as strings.
Can you take a look here? stackoverflow.com/questions/72684970/…
1

simply change insert method to 

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

then it works fine

Improve this answer

1 Comment

Can you take a look here? stackoverflow.com/questions/72684970/…
-1

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()
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.