Question: Python: Writing data from python to mysql on rasb pi

Question

Python: Writing data from python to mysql on rasb pi

Answers 1
Added at 2017-01-01 08:01
Tags
Question

I am new to python as well as to coding on Raspberry Pi. I am reading data from a sensor and trying to store it in MySQL. I managed to set up MySQL and create a table and used INSERT INTO command manually to test if data is being pushed and properly and it seems to be working fine. I have set two columns in the table both FLOAT.

My issue is when I write the python program to do the same it only stores numerical value instead of the value in a variable which actually contains live sensor data. For example if I wrote in python:

sql = "INSERT INTO temp VALUES(12,temperature)"
cursor.execute(sql)
db.commit()

In the MySQL it would show:

+-------------------+
|   12  |    NULL   |
+-------------------+

As you can see the value 12 is stored but the value in the variable temperature is showing as NULL. I tried to printed the value in the temperature variable to see the data and it shows just fine so I'm sure the variable temperature has proper values. I really don't understand why my python script wont send the data of a variable to MySQL.

Also rather than typing 12 for the first column, I tried to create a variable I=I+1 so it would increment every time the loop ran, I get the same NULL value in both columns then. Here is the full code:

import time
import os
import MySQLdb
# Connect to MySQL
db=MySQLdb.connect("localhost","zikmir","gforce","temprecords")
cursor=db.cursor()
i=0
while True:
    # Open the file that we viewed earlier so that python can see what is in it. Replace the serial number as before. 
    tfile = open("/sys/bus/w1/devices/10-000802824e58/w1_slave") 
    # Read all of the text in the file. 
    text = tfile.read() 
    # Close the file now that the text has been read. 
    tfile.close() 
    # Split the text with new lines (\n) and select the second line. 
    secondline = text.split("\n")[1] 
    # Split the line into words, referring to the spaces, and select the 10th word (counting from 0). 
    temperaturedata = secondline.split(" ")[9] 
    # The first two characters are "t=", so get rid of those and convert the temperature from a string to a number. 
    temperature = float(temperaturedata[2:]) 
    # Put the decimal point in the right place and display it. 
    temperature = temperature / 1000   
    # Display Iteration and Temperature
    print i, temperature
    i=i+1
    # Push data into SQL
    sql = "INSERT INTO temp VALUES (i,temperature)"
    cursor.execute (sql)
    db.commit()
    # Wait 5 seconds  
    time.sleep(5)

Any help would be truly appreciated!

Answers
nr: #1 dodano: 2017-01-01 08:01

While this can be done with string concatenation as in the other answer or with string formatting, the same way is this:

temperature = temperature / 1000   
sql = "INSERT INTO temp VALUES (i,%s)"
cursor.execute(sql, (temperature,))

This is to avoid a phenomenom known as SQL injection. For additional information refer: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

Source Show
◀ Wstecz