No effect on mysql database table by sending Arduino sensors data using python

Question

I am trying to upload Arduino sensors data(DHT11, BMP180, Rain sensor) to MySQL server using python. But There is no effect on table in mysql server. here is python code

import serial
import MySQLdb
import time
import mysql.connector

mydb = mysql.connector.connect(host = "localhost", user = "root", password = "", database = "climate predictive analysis") or die ("could not connect to database")

mycursor = mydb.cursor()
device = 'COM4'
try:
  print ("Trying...", device)
  arduino = serial.Serial(port = device, baudrate = 9600)
except:
  print ("Failed to connect on", device)    

while True:
    try:
      time.sleep(2)
      data = arduino.readline()
      print (data)
      pieces = data.split(" ")
      try:
        mycursor.execute("INSERT INTO sensors_data (Rain, Temperature, Humidity, Pressure) VALUES (%s, %s, %s, %s)", (pieces[0], pieces[1], pieces[2], pieces[3]))
        mydb.commit() #commit the insert
        mycursor.close()  #close the cursor
      except MySQLdb.IntegrityError:
        print ("failed to insert data")
##      finally:
##        cursor.close()  #close just incase it failed
    except:
      print ("Failed to get data from Arduino!")

sensors data is in this form

Rain Temp Hum Pres
0 31 60 985
0 31 60 985
0 31 60 985
0 31 60 985
0 31 61 985
1 31 61 985
0 31 61 985
0 31 61 985
0 31 61 985

python output This is output from python. In one line it is displaying the out in correct from but at other line it is saying that Failed to get data from Arduino. I don't know why?? Its output should in this form e.g (0 31 60 980) What are b at beginning and \r\n at the end of output??

Answer 1

The 'b' at the beginning means that it is a byte array - rather than a string. The \r\n are the characters to indicate the end of the line.

The arduino encodes and sends the strings in ASCII - where each byte is used to hold a single character. You need to convert this into a python unicode string (where more than one byte can represent a single character) before calling string operations.

It's failing on the data.split() call

Try changing your code to:

data = arduino.readline()
data = data.decode('ascii') 

and it should get to the mysql code.