insert to mysql variable from python script

Question

Basically, I am trying to send result from ultrasonic sensor to mysql db. I am able to send data I manually type into INSERT. however, I am not able to send the variable result.

This is the script for getting the number:

elapsed = stop - start    
# Distance pulse travelled in that time is time
# multiplied by the speed of sound (cm/s)
distance = elapsed * speedSound

# That was the distance there and back so halve the value
distance = distance / 2
distance = '{:f}'.format(distance)
print(distance)

Result looks like 6.110798.

Then, I try to send it do DB:

# Open database connection
db = MySQLdb.connect("192.168.2.3","DB","**********","Ultrasonic_IoT" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO Pi (distance) VALUES (%distance)"

try:

    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()

except:
    # Rollback in case there is any error
    db.rollback()

    # disconnect from server
    db.close()

I tried different combinations in VALUE, however I am still stuck.

Read more on string substitutions: http://stackoverflow.com/questions/5082452/python-string-formatting-vs-format/6335836#6335836 — Mohammad Yusuf, Dec 03 '16 at 15:02
still not working - tried to change %s to %d - ValueError: incomplete format .... is the string correct format when my DB is expecting in the row Decimal?? — Viktor Kubica, Dec 03 '16 at 15:52
String is to only substitute the value inside the query string. When it is passed over to the sql engine it will be treated as int. Sql engine will treat only values in " " as strings. — Mohammad Yusuf, Dec 03 '16 at 15:58
Did you check if you have properly described the type of distance as Floating number in your table? If it's an int it will fail. — Mohammad Yusuf, Dec 03 '16 at 16:02

score 0 · Answer 1 · answered Dec 04 '16 at 15:26

0

Ok, so this did the trick

sql = "INSERT INTO UltraSonic (distance) VALUES (%s)"

try: # Execute the SQL command cursor.execute(sql, (distance))

answered Dec 04 '16 at 15:26

Viktor Kubica

1

insert to mysql variable from python script

1 Answers1