i want to continuously insert arduino serial Data in MySQL using Python. I´ve already wrote a code. But only the first value is writing in mysql, by the second value, i get an error:“failed to get data from arduino”. I hope somebody can help me and tell me what i´m doing wrong.
Here is the code:
import serial
import time
import MySQLdb as mdb
import sys
#establish connection to MySQL. You'll have to change this for your database.
dbConn = mdb.connect("localhost","root","aaaaaa","aaaaaaa") or die ("could not connect to database")
#open a cursor to the database
cursor = dbConn.cursor()
device = 'COM7' #this will have to be changed to the serial port you are using
try:
print "Trying...",device
arduino = serial.Serial(device, 9600)
except:
print "Failed to connect on",device
try:
while 2:
time.sleep(2)
data = arduino.readline() #read the data from the arduino
pieces = data.split("\t") #split the data by the tab
print pieces
#Here we are going to insert the data into the Database
try:
#print 56 #verify the execution in the Try-loop
cursor.execute ("INSERT INTO kammer_a (VOCWert) VALUES (%s)", (pieces[0]))
print 5 #verify the execution in the Try-loop
dbConn.commit() #commit the insert
cursor.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!"
I´m using Python2.7. The Arduino is connected to a Xbee module through Xbee shield (coordinator in AT) and the other xbee is the router in AT mode
I always want to read two serial data (from 2 sensor) and insert it in Mysql using Python.
I do not understand Python-speak, or MySQL, so I cannot give suggestions on if the code is somehow faulty. But Perhaps the issue is hardware related. Eitherway, reduction of complexity might show light on the issue.
Have you tried to make the Arduino send out testdata (from a potentiometer for example) to the pc directly, and have the pc store this into the MySQL database without the Xbee link? Isolate the cause by eliminating potential problem areas.
Which arduino model is this? It might be important what code this executes, like sending serial data aswel. So please show this also.
What kind of Xbee shield is this? From Sparkfun? If so, on it is a switch that selects to send the serial data to the regular serial pins of the Arduino (D0 and D1), or a different set of general purpose IO pins (D2 and D3). If pointed to the latter than a software serial library might be needed, but this would also need solderjumpers to be made on the shield for it to work.
If the switch is set so the XBee connects to the Arduino serial pins D0 and D1, then it is also likely sent to the serial port of the pc (atleast if it is an Uno). If both Xbee and Arduino transmit data at the same time then the serial signal might get corrupted and ignored by the pc (or serial to USB interface chip on the Arduino board). But this really depends on what Arduino model this is, and which model/brand Xbeeshield.
Also, to get the picture complete: is the Arduino on the PC side of the Xbee link, or on the remote side of the link? In other words, is the Arduino the mediator/interface between the PC and Xbee link. Or is it measuring the data from whatever sensors, and sending it to the Xbee to be transmitted wireless?
Have you even attempted to debug this on your own? At the very least, you should be able to narrow down the failure to the line that throws the exception.
Valen:
I do not understand Python-speak, or MySQL, so I cannot give suggestions on if the code is somehow faulty. But Perhaps the issue is hardware related. Eitherway, reduction of complexity might show light on the issue.
Have you tried to make the Arduino send out testdata (from a potentiometer for example) to the pc directly, and have the pc store this into the MySQL database without the Xbee link? Isolate the cause by eliminating potential problem areas.
Which arduino model is this? It might be important what code this executes, like sending serial data aswel. So please show this also.
What kind of Xbee shield is this? From Sparkfun? If so, on it is a switch that selects to send the serial data to the regular serial pins of the Arduino (D0 and D1), or a different set of general purpose IO pins (D2 and D3). If pointed to the latter than a software serial library might be needed, but this would also need solderjumpers to be made on the shield for it to work.
If the switch is set so the XBee connects to the Arduino serial pins D0 and D1, then it is also likely sent to the serial port of the pc (atleast if it is an Uno). If both Xbee and Arduino transmit data at the same time then the serial signal might get corrupted and ignored by the pc (or serial to USB interface chip on the Arduino board). But this really depends on what Arduino model this is, and which model/brand Xbeeshield.
Also, to get the picture complete: is the Arduino on the PC side of the Xbee link, or on the remote side of the link? In other words, is the Arduino the mediator/interface between the PC and Xbee link. Or is it measuring the data from whatever sensors, and sending it to the Xbee to be transmitted wireless?
Hi Valen,
my sensor (connected to arduino Uno R3 serial pins A0 ) send some data through XBee (Router AT) to another XBee (coordinator AT connected to PC).
This serialdata from coordinator are inserting into a created table from MySql (from phpMyadmin) with Python. My Python code works very well, without the While-loop. When i use
while 1:
time.sleep(1)
the first value is insert correctly into MySql Table, the second value is not insert and i get the error “Failed to get data from XBee!” (the last exeption from code).
i hope you understand a little bit what problem i have.
lyndon:
Have you even attempted to debug this on your own? At the very least, you should be able to narrow down the failure to the line that throws the exception.
Hi lyndon,
yes i debug it my own, but i still not understand why the second value is not inserting into my table in MySql.
Well, the second value isn’t being inserted obviously because you have a communication fault with the Arduino! Does the variable “pieces” even print the second time?
What is most likely happening is that you’re not waiting long enough for the second set of data to be received (I notice you are not checking receive data available) and are timing out. The runtime will give you so much information you are not using. You’re not even bothering to see what the exception is!! That’s why I asked if you’ve even tried to debug this yourself.
As I said before, I cannot help with that Python code. Usually I can understand unfamiliar programming languages, but I haven’t got a clue how that try-ing and exception stuff works.
As you get the error:“failed to get data from arduino” I would think that removing the Xbee from the setup should help narrowing down where the problem lies. If the error does not happen when the Arduino is directly connected to the pc then it must have been caused by the interaction of the Xbees, and you can focus your fault analysis on those (settings).
lyndon:
Well, the second value isn’t being inserted obviously because you have a communication fault with the Arduino! Does the variable “pieces” even print the second time?
What is most likely happening is that you’re not waiting long enough for the second set of data to be received (I notice you are not checking receive data available) and are timing out. The runtime will give you so much information you are not using. You’re not even bothering to see what the exception is!! That’s why I asked if you’ve even tried to debug this yourself.
Hi Lyndon,
the second serialdata is correctly reading and printing in python´s console, but it is not insert in mysql.