python mysql insert float variables error

Question

Here is the code:

import MySQLdb
from socket import *
import time

address = ( '192.168.1.177', 5000) 
client_socket = socket(AF_INET, SOCK_DGRAM) #Set Up the Socket
client_socket.settimeout(30) # wait 30 seconds for a response

while(1): #Main Loop
    data = "send" #Set data to Blue Command
    client_socket.sendto(data, address) #send command to micro
    rec_data, addr = client_socket.recvfrom(1024) #Read response from micro
    print rec_data #Print the response from micro
    sdata = rec_data.split(',')     #csv string split

    Vrms = 123.1 #float(sdata[0])          #
    Irms =  213.45 #float(sdata[1])
    RP =  10.1 #float(sdata[2])
    AP = 1121.6 #float(sdata[3])
    PF =  1.02 #float(sdata[4])
    CPS =  60.41 #float(sdata[5])
    UP =  1154.1 #float(sdata[6])
    BV =  27.22 #float(sdata[7])
    BI = 78.66 #float(sdata[8])
    WI =  50.87 #float(sdata[9])
    EI =  23.83 #float(sdata[10])
    temp_BP =  78.2 #float(sdata[11])
    gridOK = 1 #int(sdata[12])
    genON = 0 #int(sdata[13])
    genOK =  0 #int(sdata[14])

    db = MySQLdb.connect("localhost","charlie","chab1541","PowerData")
    c = db.cursor ()

    c.execute("""INSERT INTO data
           VALUES (%f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %d, %d,        %d )""",
          (Vrms, Irms, RP, AP, PF, CPS, UP,  BV, BI, WI, EI, temp_BP, gridOK, genON, genOK))

    db.commit ()
    c.close ()
    db.close ()

Here is the error:

Traceback (most recent call last): File "C:/Users/Charlie/PycharmProjects/Read Store Power Data/PowerParser.py", line 41, in ( Vrms, Irms, RP, AP, PF, CPS, UP, BV, BI, WI, EI, temp_BP, gridOK, genON, genOK)) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 183, in execute query = query % db.literal(args) TypeError: float argument required, not str

I can't seem to get rid of the float error. I commented out the parsed list of float conversions, thinking that was the problem-no joy. Of course, taking the var names out and hard-coding floats works.

I am missing something! Now have mind-mush... Please show me the error of my ways!

score 7 · Answer 1 · answered Oct 02 '17 at 00:28

FIXED!

One needs to always use %s, no matter the actual type:

Changed code:

    c.execute("""INSERT INTO data (Vrms, Irms, RP, AP, PF, CPS, UP, BV, BI, WI, EI, temp_BP, gridOK, genON, genOK)
               VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )""",
              (Vrms, Irms, RP, AP, PF, CPS, UP,  BV, BI, WI, EI, temp_BP, gridOK, genON, genOK))

Found answer way down on Python MySQL Parameterized Queries

Thanks to all!

Thanks nat5142! Took me a long time to find it!! – Charlie Oct 02 '17 at 00:29 — Charlie, Oct 02 '17 at 00:29

score 4 · Answer 2 · answered Oct 01 '17 at 22:59

4

Use "%s" instead of "%f" and "%d" in your insert statement. MySQL knows the data type that corresponds to each table column when you create the table - that datatype specific to each field does not need to match the variable type passed in your insert statement. MySQL likes string inserts and will convert your inserts to the corresponding data type for the respective field.

answered Oct 01 '17 at 22:59

nat5142

485
9
21

MySQL will also gleefully convert what should be strings to `0.0` and cause rampant confusion if you're not careful. Getting the bindings right *should* fix the problem, but it depends on having clean data. Garbage in, garbage out. – tadman Oct 01 '17 at 23:15

python mysql insert float variables error

2 Answers2

Linked