Interface Arduino to MySQL Using Python
Intro: Interface Arduino to MySQL Using Python
Here's a brief tutorial that should get you up and running interfacing your Adruino with a MySQL database. For the sake of this tutorial, I am assuming you know how to set up and use MySQL. This tutorial does not require much Python experience, but you will be required to install two Python libraries for this project.
Glad we're on the same page, let's get to it!
Glad we're on the same page, let's get to it!
STEP 1: Downloading and Installing the Python Libraries
First I'll point you in the right direction for installing the required Python libraries. First you'll need to install the pySerial library. Simply put, the pySerial library allows your Python script to talk with the serial port in which the Arduino is connected. I.e. you can kind of think of it as a stream connecting the Arduino code to the Python code (insert other silly analogies here).
1. You can download the pySerial library here:
https://pypi.python.org/pypi/pyserial
2. For mac or linux users, download the file and extract it. Open terminal and cd into the extracted folder and run the following command:
python setup.py install
This will install the pySerial package. (screen shot below)
Next, we will install the library to allow Python to talk with MySQL called MySQLdb.
I just want to note, this step can be very annoying, but very rewarding once completed. I have included a guide for you to follow, but I recommend you have MySQL, python, and XCode(or the latest GCC) installed before you try and install MySQLdb.
1. download the library from source forge:
http://sourceforge.net/projects/mysql-python/?source=dlp
2. If you're lucky enough, you should just be able to download it, extract it, open Terminal, cd into the folder and run python setup.py install, just as you did before. If this works, you're awesome and you should awesome, but if not, this guide should help. Note, I had to do step 6 before step 3.
http://stackoverflow.com/questions/1448429/how-to-install-mysqldb-python-data-access-library-to-mysql-on-mac-os-x
1. You can download the pySerial library here:
https://pypi.python.org/pypi/pyserial
2. For mac or linux users, download the file and extract it. Open terminal and cd into the extracted folder and run the following command:
python setup.py install
This will install the pySerial package. (screen shot below)
Next, we will install the library to allow Python to talk with MySQL called MySQLdb.
I just want to note, this step can be very annoying, but very rewarding once completed. I have included a guide for you to follow, but I recommend you have MySQL, python, and XCode(or the latest GCC) installed before you try and install MySQLdb.
1. download the library from source forge:
http://sourceforge.net/projects/mysql-python/?source=dlp
2. If you're lucky enough, you should just be able to download it, extract it, open Terminal, cd into the folder and run python setup.py install, just as you did before. If this works, you're awesome and you should awesome, but if not, this guide should help. Note, I had to do step 6 before step 3.
http://stackoverflow.com/questions/1448429/how-to-install-mysqldb-python-data-access-library-to-mysql-on-mac-os-x
STEP 2: Fewf, Now Let's Set Up Our Arduino!
All right. Now that we've gotten all of the annoying steps out of the way, let's get to the fun parts!
For the sake of getting you up and running, I'll keep this short and concise.
1. Let's get our Arduino sending some output.
What we're going to do is essentially send data from our Arduino for our Python code to process, so let's first get our Arduino to send some data.
I have a temperature/humidity sensor lying around, so I'm going to take the readings from this and send them to my Python code.
Here's the sample code:
//you can ignore this part, just for the temperature sensor
#include "DHT.h"
#define DHTPIN 2
#define DHTTYPE DHT22
DHT dht(DHTPIN, DHTTYPE);
void setup() {
Serial.begin(9600);
dht.begin(); //start the temp reading (agian only for temperature sensor
}
void loop() {
//read the temperature and humidity (temperature sensor specific code)
float h = dht.readHumidity(); //read humidity
float t = dht.readTemperature(); //read temperature (C)
// check if returns are valid
if (isnan(t) || isnan(h)) {
Serial.println("Failed to read from DHT");
} else { //if it read correctly
Serial.print(h); //humidity
Serial.print(" \t"); //tab
Serial.println(t); //temperature (C)
}
}
It should be pretty straight forward. Again, I'm using a temperature/humidity sensor to get some data to send to the Python, but this could obviously be substituted with anything other data; it's just used as an example!
Note: the Serial.print lines are the data that is being sent to the serial port that the Python code will be grabbing and doing all the wonderful things with it.
For the sake of getting you up and running, I'll keep this short and concise.
1. Let's get our Arduino sending some output.
What we're going to do is essentially send data from our Arduino for our Python code to process, so let's first get our Arduino to send some data.
I have a temperature/humidity sensor lying around, so I'm going to take the readings from this and send them to my Python code.
Here's the sample code:
//you can ignore this part, just for the temperature sensor
#include "DHT.h"
#define DHTPIN 2
#define DHTTYPE DHT22
DHT dht(DHTPIN, DHTTYPE);
void setup() {
Serial.begin(9600);
dht.begin(); //start the temp reading (agian only for temperature sensor
}
void loop() {
//read the temperature and humidity (temperature sensor specific code)
float h = dht.readHumidity(); //read humidity
float t = dht.readTemperature(); //read temperature (C)
// check if returns are valid
if (isnan(t) || isnan(h)) {
Serial.println("Failed to read from DHT");
} else { //if it read correctly
Serial.print(h); //humidity
Serial.print(" \t"); //tab
Serial.println(t); //temperature (C)
}
}
It should be pretty straight forward. Again, I'm using a temperature/humidity sensor to get some data to send to the Python, but this could obviously be substituted with anything other data; it's just used as an example!
Note: the Serial.print lines are the data that is being sent to the serial port that the Python code will be grabbing and doing all the wonderful things with it.
STEP 3: Let's Go Ahead and Set Up Our MySQL
Now that we have the code running on our Arduino, we need some Python code to talk to it, but first we need a MySQL database and table to store this data.
Our Arduino is reading the temp/humidity data every second and writing it with Serial.print(). So we're going to write some Python to grab this data and insert it into some MySQL.
First, I'll create a simple MySQL table to store this data.
create table weatherData (
weatherDataID int(11) AUTO_INCREMENT NOT NULL,
humidity decimal(4,2) NOT NULL,
tempC decimal(4,2) NOT NULL,
constraint weatherData_PK primary key (weatherDataID)
);
This table is simple enough, just going to store the humidity and temperature reading that I'm getting from the Arduino.
Attached is a screen shot of me setting up this database using the mysql command line. Here's a wonderful guide to refresh your memory on the process if need be (I know I reference it monthly).
http://www.debuntu.org/how-to-create-a-mysql-database-and-set-privileges-to-a-user/
Our Arduino is reading the temp/humidity data every second and writing it with Serial.print(). So we're going to write some Python to grab this data and insert it into some MySQL.
First, I'll create a simple MySQL table to store this data.
create table weatherData (
weatherDataID int(11) AUTO_INCREMENT NOT NULL,
humidity decimal(4,2) NOT NULL,
tempC decimal(4,2) NOT NULL,
constraint weatherData_PK primary key (weatherDataID)
);
This table is simple enough, just going to store the humidity and temperature reading that I'm getting from the Arduino.
Attached is a screen shot of me setting up this database using the mysql command line. Here's a wonderful guide to refresh your memory on the process if need be (I know I reference it monthly).
http://www.debuntu.org/how-to-create-a-mysql-database-and-set-privileges-to-a-user/
STEP 4: Python TIEM
Alright, fewf, now we've got our Arduino ready and a database all prepared for our data. Last step is to write the Python to get this data and insert it into our database.
#!/usr/bin/python
import serial
import MySQLdb
#establish connection to MySQL. You'll have to change this for your database.
dbConn = MySQLdb.connect("localhost","database_username","password","database_name") or die ("could not connect to database")
#open a cursor to the database
cursor = dbConn.cursor()
device = '/dev/tty.usbmodem1411' #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:
data = arduino.readline() #read the data from the arduino
pieces = data.split("\t") #split the data by the tab
#Here we are going to insert the data into the Database
try:
cursor.execute("INSERT INTO weatherData (humidity,tempC) VALUES (%s,%s)", (pieces[0],pieces[1]))
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!"
Okay, so hopefully this is relatively understandable from the comments. The real important parts to note are to make sure you configure the connection to be specific to your data for your database (i.e. username/password/database name). Secondly, you're going to want to change the device='' line to point to the usb serial port that you are using.
Once you configure this script as you needed, you should see the data being populated in your MySQL table when you run the script. Here's an example below of what mine is populating like (see image).
Well that's about it! Hopefully you're all set up an good to go now. You should be able to do a number of cool things now with this basis, and I hope you have some fun with it. Go put this data on your website or do whatever your heart desires!
Thanks for reading, and please please please feel free to let me know if you have any suggestions to improve this tutorial, or have suggestions for any tutorials you'd like to see in the future.
Best,
Tom
#!/usr/bin/python
import serial
import MySQLdb
#establish connection to MySQL. You'll have to change this for your database.
dbConn = MySQLdb.connect("localhost","database_username","password","database_name") or die ("could not connect to database")
#open a cursor to the database
cursor = dbConn.cursor()
device = '/dev/tty.usbmodem1411' #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:
data = arduino.readline() #read the data from the arduino
pieces = data.split("\t") #split the data by the tab
#Here we are going to insert the data into the Database
try:
cursor.execute("INSERT INTO weatherData (humidity,tempC) VALUES (%s,%s)", (pieces[0],pieces[1]))
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!"
Okay, so hopefully this is relatively understandable from the comments. The real important parts to note are to make sure you configure the connection to be specific to your data for your database (i.e. username/password/database name). Secondly, you're going to want to change the device='' line to point to the usb serial port that you are using.
Once you configure this script as you needed, you should see the data being populated in your MySQL table when you run the script. Here's an example below of what mine is populating like (see image).
Well that's about it! Hopefully you're all set up an good to go now. You should be able to do a number of cool things now with this basis, and I hope you have some fun with it. Go put this data on your website or do whatever your heart desires!
Thanks for reading, and please please please feel free to let me know if you have any suggestions to improve this tutorial, or have suggestions for any tutorials you'd like to see in the future.
Best,
Tom
37 Comments
yousefo1991 1 year ago
mangopeach 1 year ago
liongdes 3 years ago
import serial
import MySQLdb
import time
# ct stores current time
#establish connection to MySQL. You'll have to change this for your database.
dbConn = MySQLdb.connect(host="localhost",
user="root",
password="",
database="projectakhir") or die ("could not connect to database")
#open a cursor to the database
cursor = dbConn.cursor()
device = 'COM3' #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)
while True:
time.sleep(1)
try:
data=arduino.readline()
print(data)
pieces=data.split(" ")
try:
print(ct)
cursor.execute("INSERT INTO 'phtemp' ('Ph', 'temperature') VALUES ('%s','%s')", (pieces[0],pieces[1]))
dbConn.autocommit(True)
cursor.close()
except MySQLdb.IntegrityError:
print ("failed to insert data")
finally:
cursor.close()
except:
print ("Processing")
ivanokeeffe 4 years ago
No idea if you're still seeing these questions, but is using Python to insert sensor data into MySQL seen as good practice in general? Is it better for instance to use PHP to send the data?
I am only asking because I have read many other methods that use PHP to send the data to the database.
Thanks,
Ivan
jamcry 6 years ago
Hello, thanks for this helpful tutorial. Is it possible to reverse this process? What I mean is can arduino read data from the database ?
mangopeach 6 years ago
hiraa.arooj96 6 years ago
Hello, i followed this instructable but i am getting an error
This is my python code
import serial
import pgdb
import psycopg2
#establish connection to MySQL. You'll have to change this for your database.
dbConn = psycopg2.connect(database="SensorData", user="postgres", password="postgres") or die ("could not connect to database")
#open a cursor to the database
cursor = dbConn.cursor()
#device = '/dev/tty.usbmodem1411' #this will have to be changed to the serial port you are using
arduino = serial.Serial('COM8', 9600, timeout=.1)
data = arduino.readline()
pieces = data.split("\t")
cursor.execute("INSERT INTO DHT22_data (humidity,temperature) VALUES (%s,%s)", (pieces[0],pieces[1]))
dbConn.commit() #commit the insert
cursor.close()
"Error i am getting is:
Traceback (most recent call last):
File "F:/Final Year Project/Python files/sendToDatabase.py", line 15, in <module>
cursor.execute("INSERT INTO DHT22_data (humidity,temperature) VALUES (%s,%s)", (pieces[0],pieces[1]))
IndexError: list index out of range
>>>
Kindly help me in this, its urgent
mangopeach 6 years ago
At first glance I would imagine it has to do with not reading successfully from the USB port. Try to log what the value of the line you read from the Arduino is to quickly check if it's actually getting data. I'd guess the index error comes from the pieces array at your insert line. You could simply check your split line result was an array with two array elements before trying the insert. I'm not familiar with the postgres library so I can't confirm it's not potentially something there. Hopefully that helps, let me know what you find.
Best,
Tom
russ_hensel 8 years ago
I love the combination of Python and the Arduino. So I have created a collection about it. I have added your instructable, you can see the collection at: >> https://www.instructables.com/id/Arduino-and-Pytho...
falexis19 8 years ago
goooood
armorer243 10 years ago
A little help, if you would be so kind. I am a python noob so please bear with me. This script seems to insert the data once and then exit. Is there a way for this to run as a loop over and over, inserting data every time it receives a new string?
mangopeach 10 years ago
Hey there,
I haven't testing this, so just a warning, but a straightforward solution would be to put the the insertion in a loop and do an insert each time some data was read. Something along the lines of (just restart the loop if you don't have any input, so you don't try and insert null values into your database):
while True:
try:
data = arduino.readline() #read the data from the arduino
if not data.strip(): #did we get an empty line?
#could put a sleep(1) or something if you wanted to wait before trying to read again
continue #start the loop over
pieces = data.split("\t") #split the data by the tab
#Here we are going to insert the data into the Database
try:
cursor.execute("INSERT INTO weatherData (humidity,tempC) VALUES (%s,%s)", (pieces[0],pieces[1]))
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!"
ArdsH 8 years ago
your code didnt work for me.. can u check it sir?
gembong.wijang 9 years ago
hei dude, i just get error in except
Gombakka 8 years ago
I made this one through lot of other referencing .But I have to say this tutorial was my major reference.Now I am finding a way to insert data continuously to my database.This script runs only once.Thank you so very much for sharing :)
Here's a good guide on installing MySQLdb in Kali -Linux
http://blog.mysqlboy.com/2010/08/installing-mysqldb-python-module.html
ArdsH 8 years ago
@Gombakka please send me an example code on how you manage go get it looping.. i tried but mine didn't work out.. please message here on send to my email kukhoni@yahoo.com thanks
khios78 8 years ago
Did you manage to get it to loop? I'm not sure of the code right now but before it starts the write to database you could add while loop to keep repeating until a condition.. Eg space bar or whatever. I'm going to set a loop that it will never exit from as I need it running In the background to keep inserting data every 5 mins
Gombakka 8 years ago
Yes I did,I am sending data every 3 hours to my database. Even though you add a 'while true' loop data insertion to the table will stop at some point unless you open the cursor to the database inside that loop :)
purehektik 8 years ago
Thanks for this tutorial! But you forget to explain the important last step and therefor I am not able to complete this tutorial. In step 4 you just paste the python script. But what should I do with it? It needs somehow to be executed, or not? This isn't explained anywhere :(
khios78 8 years ago
Worked well. I had to do a little fault finding but that might have been my typing. Just got to make it loop. Now.
Would you mind if I used your code as my base (with the right thanks and links back to you of cause) building a automated hydro setup that I can view from online but rather code it myself as least I know what's in it etc ;)