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!

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:

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:

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.


<p>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?</p>
<p>Hey there,</p><p>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):</p><p>while True:</p><p style="margin-left: 20.0px;"> try: </p><p style="margin-left: 40.0px;"> data = arduino.readline() #read the data from the arduino</p><p style="margin-left: 40.0px;"> if not data.strip(): #did we get an empty line?</p><p style="margin-left: 60.0px;"> #could put a sleep(1) or something if you wanted to wait before trying to read again</p><p style="margin-left: 60.0px;"> continue #start the loop over</p><p style="margin-left: 40.0px;"> pieces = data.split(&quot;\t&quot;) #split the data by the tab</p><p style="margin-left: 40.0px;"> #Here we are going to insert the data into the Database</p><p style="margin-left: 40.0px;"> try:</p><p style="margin-left: 60.0px;"> cursor.execute(&quot;INSERT INTO weatherData (humidity,tempC) VALUES (%s,%s)&quot;, (pieces[0],pieces[1]))</p><p style="margin-left: 60.0px;"> dbConn.commit() #commit the insert</p><p style="margin-left: 60.0px;"> cursor.close() #close the cursor</p><p style="margin-left: 60.0px;"> except MySQLdb.IntegrityError:</p><p style="margin-left: 60.0px;"> print &quot;failed to insert data&quot;</p><p style="margin-left: 40.0px;">finally:</p><p style="margin-left: 60.0px;"> cursor.close() #close just incase it failed</p><p style="margin-left: 40.0px;"> except:</p><p style="margin-left: 60.0px;"> print &quot;Failed to get data from Arduino!&quot;</p>
<p>your code didnt work for me.. can u check it sir?</p>
<p>hei dude, i just get error in except</p>
<p>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 :) </p><p>Here's a good guide on installing MySQLdb in Kali -Linux</p><p>http://blog.mysqlboy.com/2010/08/installing-mysqldb-python-module.html</p>
<p>@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</p>
<p>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</p>
<p>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 :)</p>
<p>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 :( </p>
<p>Worked well. I had to do a little fault finding but that might have been my typing. Just got to make it loop. Now.</p><p>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 ;) </p>
<p>Will only work under Python2 as it is now. It can be made to work under python3 by adding print(&quot;blah&quot;) and that will work with Python2 first (tbh I always use print </p><p>Print(&quot;&quot;) anyway. Habbit</p>
<p>umm.. bro, i'm python newbie. how to run the python program?</p>
<p>Used this as a way to learn some inital mysql and check a different sensor (optical interrupter) . Thanks! </p>
<p>why am i always failed to connect to usb port? i am using windows and arduino uno with serial cable attached to usb port COM3, it always printed that failed to get data from Arduino. as written in the codes on the last except section.</p><p>any help would be appreciated</p><p>thanks</p>
<p>Hi,</p><p>your tutorial is great!!!</p><p>But i still have a problem.</p><p>My Sql database is empty.</p><p>I&acute;m getting no error, only a warning:&quot;Warning: Out of range value for column 'tempC' at row 1&quot;</p><p>I only use one sensor: temerature sensor (DS18S20).</p><p>In my python code i only delete the humidity data</p><p> try:</p><p> cursor.execute(&quot;INSERT INTO weatherData (tempC) VALUE (%s)&quot;, (pieces[0]))</p><p> dbConn.commit() #commit the insert</p><p> cursor.close() #close the cursor</p><p>Nothing is happening in my Database.</p><p>What can be the reason.</p><p>Thanks for helping me</p>
<p>There is a site that make the MySqldb install a breeze. Literally a few button clicks.</p><p>http://www.codegood.com/</p>
Very useful! I set up my smoker to send it's data via wireless to my home server. Then using your 'ible it is logging it's data to a db. Last part is my internal webpage with live graphing I can access at work via VPN. I must say your tutorial gave me the exact starting point I needed to finish the project up. Thank you very much!
<p>I have to say, I find this tutorial very cool, but I'm having a couple of issues, hope you can help me.<br><br>I did everything you said with the difference that I'm using a RFID card reader to send data. The problem seems to be that my Python script never receives this data. I have tryed with a very simple code to send something from the Arduino to Python and I discovered that it just keeps looking for data (specificaly this line):<br><br>data = arduino.readline() #read the data from the arduino<br><br>but never goes forward and never prints the excepcion. Everything what is printed in the terminal is:<br><br>Trying... /dev/ttyXXX<br><br>And that is it. I don't know what is that due to. Could you help me?<br><br>Regards,<br>Estefan&iacute;a</p>
<p>Ok, I found the problem. This is what I saw in the documentation (http://pyserial.sourceforge.net/shortintro.html#opening-serial-ports):<br><br>&gt;&gt;&gt; ser = serial.Serial('/dev/ttyS1', 19200, timeout=1)<br>&gt;&gt;&gt; x = ser.read() # read one byte<br>&gt;&gt;&gt; s = ser.read(10) # read up to ten bytes (timeout)<br>&gt;&gt;&gt; line = ser.readline() # read a '\n' terminated line<br>&gt;&gt;&gt; ser.close()<br><br>as one can see, readline reads a '\n' terminated line and I was printing with Serial.print(val); then I changed it for Serial.println(val); and it worked :)</p><p>Regards,</p><p>Estefan&iacute;a</p>
Thank's man. This looks like a real good solution. Can't wait to try it.
I don't understand the following point: <br>device = '/dev/tty.usbmodem1411' #this will have to be changed to the serial port you are using <br> <br>I know what's mean /dev/ttyX on Linux but I don't understand in this point. <br>how can I know my serial port? <br> <br>Thanks
Why not run the mysql connector on the Arduino. No intermediary needed.<br> <br> <a href="http://arduinotronics.blogspot.com/2013/04/introducing-mysql-connectorarduino.html" rel="nofollow">http://arduinotronics.blogspot.com/2013/04/introducing-mysql-connectorarduino.html</a>
That's an other possibility but I'm working with Arduino YUN and all of its possibilities so I'm really interesting on the option of this post. <br> <br>Also the cost of a Arduino Yun is lower than a Wifi shield + Arduino board.
Hey do you know if there is any chance to use a wifiShield instead of EthernetShield?
Sure, no problem. TCP/IP is TCP/IP, no matter if cabled or wifi.
That's very cool! I hadn't come across that yet. That seems like a very good option with an ehternet shield. Thanks for the tip.
Take a bow :)
Useful! I'm using Python to interface mysql and a serial connected radio module at the moment.

About This Instructable


246 favorites


More by mangopeach: Interface Arduino to MySQL using Python
Add instructable to: