Sheevaplug + Python Daemon + MySQL = RS232 Logging Made Easy
Intro: Sheevaplug + Python Daemon + MySQL = RS232 Logging Made Easy
So, you've got some kinda RS232 device and you want to log a bunch of data from it. No problem. What you need is a little Sheevaplug. The Sheevaplug is a 5 watt full blown solid state Linux box running at 1.2 GHz. Now, how cool is that?
Here is a link for a bunch more info on the Sheevaplug:
http://en.wikipedia.org/wiki/SheevaPlug
I think the Sheevaplug is just so darn neat. All that juice in the palm of your hand.
Just recently I re-flashed my old Sheevaplug with the new Ubuntu 9.04 Arm build. After the re-flash the USB/RS232 cables started working for the first time. Seems there was some problem with the old Debian build.
OK, so here is the deal, I'm playing with a bunch of Zigbee radios that generate a few RS232 messages every second. I want to log all those messages so I can analyze them later. Just shoving all those strings into a text file would not be difficult. But, that makes it hard to handle. Plus, it would make it hard to look at the data while the data being collected. Also consider, I'm collecting data for weeks and weeks. Just how big that text file would grow is anybodies guess.
The solution to large amounts of data is a MySQL database. And the solution to pumping data into a MySQL database is Python.
Read on to understand all the plumbing involved in getting your Sheevaplug to pack all that data into a database for you.
Can you tell I'm rather smitten with the Sheevaplug? Disclaimer, although it sounds like I'm a salesman for the Sheevaplug, I'm not. I have no personal interest in the plug other than I think it is cool.
Here is a link for a bunch more info on the Sheevaplug:
http://en.wikipedia.org/wiki/SheevaPlug
I think the Sheevaplug is just so darn neat. All that juice in the palm of your hand.
Just recently I re-flashed my old Sheevaplug with the new Ubuntu 9.04 Arm build. After the re-flash the USB/RS232 cables started working for the first time. Seems there was some problem with the old Debian build.
OK, so here is the deal, I'm playing with a bunch of Zigbee radios that generate a few RS232 messages every second. I want to log all those messages so I can analyze them later. Just shoving all those strings into a text file would not be difficult. But, that makes it hard to handle. Plus, it would make it hard to look at the data while the data being collected. Also consider, I'm collecting data for weeks and weeks. Just how big that text file would grow is anybodies guess.
The solution to large amounts of data is a MySQL database. And the solution to pumping data into a MySQL database is Python.
Read on to understand all the plumbing involved in getting your Sheevaplug to pack all that data into a database for you.
Can you tell I'm rather smitten with the Sheevaplug? Disclaimer, although it sounds like I'm a salesman for the Sheevaplug, I'm not. I have no personal interest in the plug other than I think it is cool.
STEP 1: Add Flash Memory to the Sheevaplug.
I wanted my data stored using the SD-Card slot on the plug. I think that just adds to the "coolness" factor. With an extra 4Gig SD-Card on hand I used the following couple of commands to format and mount the new SD-Card into the file system.
Use this command to delete the FAT partition / make a partition new: "fdisk /dev/mmcblk0"
Use this to format the new partition: "mkfs -t ext3 /dev/mmcblk0p1"
Note, the format command takes a minute, or more, to execute. I had a hard time waiting.
Add this string to /etc/fstab: "/dev/mmcblk0p1 /usr2 ext3 noatime,commit=600,data=ordered,barrier=1 0 3"
Use this command to mount the new file system: "mount -a"
Your new SD-Card should now be mounted on /usr2
You can now test your new file system. I tested by copying some files onto, and off of, "/usr2" as a test.
I learned a lot of really good stuff from this English bloke's web site: http://www.earth.org.uk/note-on-SheevaPlug-setup.html This guy uses the Sheevaplug to log/control his home's solar panel installation. Seems to really know his stuff - very good info.
Use this command to delete the FAT partition / make a partition new: "fdisk /dev/mmcblk0"
Use this to format the new partition: "mkfs -t ext3 /dev/mmcblk0p1"
Note, the format command takes a minute, or more, to execute. I had a hard time waiting.
Add this string to /etc/fstab: "/dev/mmcblk0p1 /usr2 ext3 noatime,commit=600,data=ordered,barrier=1 0 3"
Use this command to mount the new file system: "mount -a"
Your new SD-Card should now be mounted on /usr2
You can now test your new file system. I tested by copying some files onto, and off of, "/usr2" as a test.
I learned a lot of really good stuff from this English bloke's web site: http://www.earth.org.uk/note-on-SheevaPlug-setup.html This guy uses the Sheevaplug to log/control his home's solar panel installation. Seems to really know his stuff - very good info.
STEP 2: Install: Python Serial Module / MySQL
The Sheevaplug comes with Python already installed. However, to read and write to the serial ports (USB serial ports included) an extra python module needs to be installed. Use the following apt-get command: "sudo apt-get install python-serial". The database stuff is just as easy: "sudo apt-get install mysql-common".
With these two added packages you now have almost all the secret sauce needed. All that remains is a little Python code to tie the serial port messages to the MySQL database.
The following python code opens a serial port and a database connection:
ser = serial.Serial( '/dev/ttyUSB0', 115200, timeout=1 )
conn = MySQLdb.connect( host='localhost', db='vam', user='james', passwd='passwd' )
curs = conn.cursor()
Then, use following line to read lines of text from the serial port. If the length of "s" is greater than zero then you know there is new info in the string.
s = ser.readline().strip("\r\n") # Either return a string, or timeout.
Now, parse your string into fields you want to pump into the database.
The next couple of commands do the heavy lifting of getting the data into the database.
t = datetime.datetime.now()
curs.execute( 'insert into RouterDat (MAC, udt, PMAC, RSSI, Temp, Voltage, Battery) \
values (%s, %s, %s, %s, %s, %s, %s)', (mac, t, pmac, rssi, temp, volt, batt))
conn.commit()
Note, the Sheevaplug has a NTP (network time protocol) daemon running in the background. So, all the data is time-stamped with really accurate time values. Just more coolness.
With these two added packages you now have almost all the secret sauce needed. All that remains is a little Python code to tie the serial port messages to the MySQL database.
The following python code opens a serial port and a database connection:
ser = serial.Serial( '/dev/ttyUSB0', 115200, timeout=1 )
conn = MySQLdb.connect( host='localhost', db='vam', user='james', passwd='passwd' )
curs = conn.cursor()
Then, use following line to read lines of text from the serial port. If the length of "s" is greater than zero then you know there is new info in the string.
s = ser.readline().strip("\r\n") # Either return a string, or timeout.
Now, parse your string into fields you want to pump into the database.
The next couple of commands do the heavy lifting of getting the data into the database.
t = datetime.datetime.now()
curs.execute( 'insert into RouterDat (MAC, udt, PMAC, RSSI, Temp, Voltage, Battery) \
values (%s, %s, %s, %s, %s, %s, %s)', (mac, t, pmac, rssi, temp, volt, batt))
conn.commit()
Note, the Sheevaplug has a NTP (network time protocol) daemon running in the background. So, all the data is time-stamped with really accurate time values. Just more coolness.
STEP 3: Getting MySQL to Use SD-Card
By default, the MySQL database will not get stored onto the external SD-Card. This is simple to change, edit the file using the following command "sudo vi /etc/mysql/my.cnf". Look for the line starting with "datadir" and change it to "datadir = /usr2/lib/mysql".
Then, make an exact copy of "/var/lib/mysql" to "/usr2/lib/mysql". It's important to get all the directory and file ownership permissions set correctly. If not set correctly you will get error messages when you restart the mysql daemon.
If your ready, restart the mysql daemon with "sudo /etc/init.d/mysql restart". If all goes well, your all set to create a new database on the SD-Card. It took me half a dozen attempts before I got all the permissions and ownerships set correctly. I'm sure there is some swift Linux command that preserves the owner and group permissions. But, I messed it up with my simple "cp" command. A lot of "sudo chown" commands got it all sorted out in the end.
One other quick note, you can also have remote machines log data into your new MySQL database. However, there is a trick that took me a long time to figure out. To fix the problem, find the line "bind-address = 127.0.0.1" in "/etc/mysql/my.cnf" and comment it out by prefixing the line with a "#". Another restart on the daemon and your all set.
Then, make an exact copy of "/var/lib/mysql" to "/usr2/lib/mysql". It's important to get all the directory and file ownership permissions set correctly. If not set correctly you will get error messages when you restart the mysql daemon.
If your ready, restart the mysql daemon with "sudo /etc/init.d/mysql restart". If all goes well, your all set to create a new database on the SD-Card. It took me half a dozen attempts before I got all the permissions and ownerships set correctly. I'm sure there is some swift Linux command that preserves the owner and group permissions. But, I messed it up with my simple "cp" command. A lot of "sudo chown" commands got it all sorted out in the end.
One other quick note, you can also have remote machines log data into your new MySQL database. However, there is a trick that took me a long time to figure out. To fix the problem, find the line "bind-address = 127.0.0.1" in "/etc/mysql/my.cnf" and comment it out by prefixing the line with a "#". Another restart on the daemon and your all set.
STEP 4: SQL Tools
There is one other tool your going to need to. The folks over at MySQL have a free open-source tool that runs on Linux/Mac/Windows. Here is a link: http://dev.mysql.com/downloads/gui-tools/5.0.html
I find the Query Browser tool really handy. With the Query Browser tool you can create databases, tables, and views. Very handy. The following query pulls all the data for one router out of my database. The data is sorted, filtered, and reduced. If you look closely, the query reduces the returned dataset to only those samples that fall at the top of the hour. Otherwise, with samples recorded every minute, I would get flooded with returning data.
The following example defines a "view" that is also stored in the database.
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`james`@`%` SQL SECURITY DEFINER VIEW `RouterDatView` AS select hex(`RouterDat`.`MAC`) AS `0xMAC`,hex(`RouterDat`.`PMAC`) AS `0xPMAC`,`RouterDat`.`udt` AS `udt`,`RouterDat`.`RSSI` AS `RSSI`,format((`RouterDat`.`Temp` / 10.0),1) AS `Temp degC`,format((`RouterDat`.`Voltage` / 100.0),2) AS `Voltage`,format((`RouterDat`.`Battery` / 100.0),2) AS `Battery`,`NodeInfo`.`Notes` AS `Notes` from (`RouterDat` left join `NodeInfo` on((`RouterDat`.`MAC` = `NodeInfo`.`MAC`))) where `NodeInfo`.`Notes`="Pole Mounted Solar Router" and extract( MINUTE from udt ) = "00" order by `RouterDat`.`udt` desc
I find the Query Browser tool really handy. With the Query Browser tool you can create databases, tables, and views. Very handy. The following query pulls all the data for one router out of my database. The data is sorted, filtered, and reduced. If you look closely, the query reduces the returned dataset to only those samples that fall at the top of the hour. Otherwise, with samples recorded every minute, I would get flooded with returning data.
The following example defines a "view" that is also stored in the database.
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`james`@`%` SQL SECURITY DEFINER VIEW `RouterDatView` AS select hex(`RouterDat`.`MAC`) AS `0xMAC`,hex(`RouterDat`.`PMAC`) AS `0xPMAC`,`RouterDat`.`udt` AS `udt`,`RouterDat`.`RSSI` AS `RSSI`,format((`RouterDat`.`Temp` / 10.0),1) AS `Temp degC`,format((`RouterDat`.`Voltage` / 100.0),2) AS `Voltage`,format((`RouterDat`.`Battery` / 100.0),2) AS `Battery`,`NodeInfo`.`Notes` AS `Notes` from (`RouterDat` left join `NodeInfo` on((`RouterDat`.`MAC` = `NodeInfo`.`MAC`))) where `NodeInfo`.`Notes`="Pole Mounted Solar Router" and extract( MINUTE from udt ) = "00" order by `RouterDat`.`udt` desc
STEP 5: Final Thoughts
Just today I saw a new device come across Hack-A-Day called the GuruPlug.
Here is a link to the next generation of Sheevaplug called a GuruPlug.
http://www.globalscaletechnologies.com/p-32-guruplug-server-plus.aspx
With the new plug, you can have WiFi & Bluetooth build right in - still only 5 watts. The price did get bumped up a bit to $130 bucks. Still, to get WiFi and Bluetooth for only an extra $30 bucks, the price still sounds good to me.
I hope this got you motivated to play with the GuruPlug or SheevaPlug. Logging all your serial data into a database is not all that hard. Once you've got your logging working, using SQL to get just the right data back is really cool.
I'll also post some other info on my web site: "ph-elec.com". I'll put up some extra source code that takes your Python code and turns it into a daemon that runs in the background. So, once kicked off, the Python daemon just simply handles anything coming in on the serial port.
Now, how cool is that!
Here is a link to the next generation of Sheevaplug called a GuruPlug.
http://www.globalscaletechnologies.com/p-32-guruplug-server-plus.aspx
With the new plug, you can have WiFi & Bluetooth build right in - still only 5 watts. The price did get bumped up a bit to $130 bucks. Still, to get WiFi and Bluetooth for only an extra $30 bucks, the price still sounds good to me.
I hope this got you motivated to play with the GuruPlug or SheevaPlug. Logging all your serial data into a database is not all that hard. Once you've got your logging working, using SQL to get just the right data back is really cool.
I'll also post some other info on my web site: "ph-elec.com". I'll put up some extra source code that takes your Python code and turns it into a daemon that runs in the background. So, once kicked off, the Python daemon just simply handles anything coming in on the serial port.
Now, how cool is that!
13 Comments
storm017 11 years ago
I'm trying your script but directly reading to Mysql. This is my first python script. I'm having an issue loading the data into the db. Here is the code:
import serial
import MySQLdb
ser = serial.Serial( '/dev/ttyUSB1', 9600, timeout=1 )
conn = MySQLdb.connect( host='localhost', db='Weatherboard', user='rich', passwd='wordpass' )
curs = conn.cursor()
s = ser.readline().strip("\r\n")
curs.execute( 'insert into weatherdata (humiditycol, sht15tempcol, scp1000tempccol, scp1000presscol, scp1000tempfcol, temt6000lightcol, batterycol) \
values (%s, %s, %s, %s, %s, %s, %s)', (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
conn.commit()
The error I'm receiving is the Name humidity is not defined.
Do you see a issue with the code.
grazercam 9 years ago
Hey Jim - I know this is two years on, but I struggled with this too. I figured my method might help someone else.
Not really sure what I did wrong, but here is what I did.
All my data comes in over serial, once a minute as CSV, with values like: 1,17,12.19,55,18,36,936,19
import serial
import MySQLdb
ser = serial.Serial( '/dev/ttyS0', 9600, timeout=60 )
conn = MySQLdb.connect( host='SERVER', db='DATABASE', user='USER', passwd='PASSWORD' )
curs = conn.cursor()
s = ser.readline().strip("\r\n") # Either return a string, or timeout.
judas = """INSERT into Logging (MSG, Light, BVolt, IntC, IntH, ExtC, ExtH, RawhPa, BtC) VALUES (""" + s +""")"""
curs.execute(judas)
conn.commit()
It ends up creating a nice SQL Insert command like this:
INSERT into TABLE (MSG, Light, BVolt, IntC, IntH, ExtC, ExtH, RawhPa, BtC) VALUES ( 1,18,12.13,22.10,55.20,18.00,37.00,935.85,18.50 )
Also, I used the timestamp on the SQL server as opposed to on the RaspberryPi/Sheevaplug. In my setup, the SQL Insert is done directly to an internet mysql server as opposed to storing locally first. Best of luck everyone.
jimk3038 11 years ago
Forgive me, it's been a long time since I used any of this stuff. I'm going to be rusty with this stuff.
I don't see anything directly wrong with your source code.
Here are a couple of things I would check. Use a "print" statement just before the "curs.execute" to make sure "humidity", as well as all the variables, are defined. Also, make sure all the database table column names are defined and spelled correctly. Awful easy to get the name just slight wrong. Lastly, check your database permissions to make sure user "rich" with "wordpress" password has permission to write to the database. You can use the SQL Tools to verify database table names. You can also run your "insert into" query in SQL Tools to make sure it works. Just form the insert query with some dummy data to test with.
The problem with this stuff is everything must be formatted perfectly for any of it to work. However, it is sweet when it does all work!
Good luck,
Jim
MY 13 years ago
dukechem 14 years ago
Seems there may be commands to switch the FT2232DF between rs232 and usb mode?
Can you provide any details on the cable connecting to your Zigbee(s), and are you using the rs232 or usb port of the radio(s)? I want to gather data from old instruments that may use rs232-signals with voltages higher than TTL (plus or minus 5 volts max), typically +12 or -12 volts I think. Currently these connect to laptop com1 port via 9-pin-D connectors.
The spec-sheet for the FT2232DF chip seems to say you need a TTL-to-RS232 voltage-level converter if you were to connect to a older devices with higher-voltage rs232 signals.
You say "The following python code opens a serial port...:
ser = serial.Serial( '/dev/ttyUSB0', 115200, timeout=1 )"
I assume this must be the usb-connector as 232 is not mentioned at:
http://www.globalscaletechnologies.com/t-sheevaplugdetails.aspx#hw_block
I guess what I'd like to know is what are the pin-outs and voltages of the connector named "JTAG UART RS232" at:
www.globalscaletechnologies.com/t-guruplugdetails.aspx#extern
Then I could figure out if I needed a voltage-clipper to use it to talk to my instruments.
dukechem 14 years ago
http://plugcomputer.org/plugforum/index.php?topic=1070.0
I suspect this would be needed for any "kinda rs232 device", right? And you were talking to your devices with a cable similar to the usb cable as used with a printer or camera?
jimk3038 14 years ago
The cable I'm using level shifts down to 3.3V that my Zigbee board expects.
However, I think most any USB to RS232 cable that is based on the FTDI chip will work. Plugging in a USB / RS232 cable makes the port appear in /dev/ttyUSBx on the Plug computer. The Plug does not know, or care, what actual voltage level is being used on the other end of the cable.
So, to answer your question (at least I hope I'm answering it), no, there is no switch on the Plug. The Plug has a mini USB port that allows many different devices to be plugged in. The Plug automatically detects the device type and loads the device driver automatically. Its that automatic load feature that makes the RS232 cable appear as /dev/ttyUSBx.
In fact, the Plug support a USB hub attached to that mini USB port. With a hub attached, the Plug could handle bunches of simultaneous comm ports.
As an example, logging into the Plug using telnet, I just executed the command "lsusb" to get a list of all connected devices. The response on my Plug was:
Bus 001 Device 002: ID 0403:6001 Future Technology Devices International, Ltd FT232 USB-Serial (UART) IC
Bus 001 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
So, there is an internal root hub and the FTDI cable on my system.
If I haven't answer your question just shoot me another reply to this email.
Hope this helps & Good Luck,
Jim
frollard 14 years ago
I've been itching for a reason to get one - but really, how often does the end user need to log ...anything. I'd maybe want one as a firewall...
I just can't see using one for virtually anything though :S
jimk3038 14 years ago
You gotta be kidding me. I can think of a thousand, and one, uses for one of these babies. It's kinda like saying, “What would people use an embedded Linux box for, anyway?” Holy Cow!
Note, I miss read the Hack-A-Day announcement. Looks like even the base $100 dollar GuruPlug has both WiFi and Bluetooth. So, here is a 5 watt wall wart that has two radios, two USB ports, a eSATA port, and a MicroSD slot.
So many goodies I just don't know where to begin.
frollard 14 years ago
Short of a very very full featured router or data logger I can't think of much it would excel at. It has no visual interface by default, so it's relegated to the 'automate' field of computing, which is sadly already taken up with modules in a larger computer system for most people.
For me, I guess it's like trying to come up with 'how to use an arduino' all by itself. You can blink leds, log data, make an autonomous robot...etc - but until it's been fuufuu dusted with the magic of applications that do the actual cool stuff, it's just a piece of hardware. Don't get me wrong; I'm super techhy and love gadgety stuff - I just can't comprehend a use for this other than 'server'.
jimk3038 14 years ago
In the past I would dedicate an old laptop to this kind of data logging task. It would run for months. I'd check on it from time-to-time to make sure the kids didn't fool with. With old worn out batteries I would have to make sure power outages didn't mess it up either. I would always feel guilty with the amount of heat rolling off the laptop too. For simple tasks - the laptop is so inefficient. The Sheevaplug has made me not feel guilty any more.
I'm also thinking of giving the Sheevaplug other tasks too. I've got a bunch of external USB drives that the Plug can turn into shared drives. It can do this while also logging my data. No extra anything required - just plug them in.
For an interface, I use simply use "ssh plug -l username" where "plug" is in my hosts file. I can open as many terminals as I want with each one running over Ethernet at a 100 Mega bits. Right out of the box, the Plug also supports shared files so the Plug shows up in file explorer. Very handy.
The new Guruplug sounds cool too. Having both WiFi and Bluetooth is just begging for something. How about controlling X10 with a Bluetooth phone? I hear folks are jacking in USB cameras for home security too. The Plug takes a picture once per minute and saves it off. At the end of the day, all the snapshots are assembled into an mpeg movie. All done on the Plug.
I also agree with you that the "fuufuu dust" is a hassle. However, that is what separates us from the rest. If you haven't played much with Python, give it a try. There are so many modules already written - it makes app development so much easier. The Python serial module is an example. Takes 10 seconds to install with the apt-get command. Takes just three, or four, lines of code to open and read strings. Now, how cool it that. Try opening a serial port using .Net - I'm not even sure you can any more using the latest release.
Anyway, sorry for the rant, I'm just so smitten with my little Plug,
Jim
frollard 14 years ago
Serial in .net is possible, but it's abstracted through so many layers and controls that you can barely use it for terminal ASCII data. Try bit-banging one. BAH.
I digress. Cool tech. The video points out using hundreds of these in places you wouldn't expect - an automated milk-buying fridge, etc. It just seems like too much power for most small tasks, and not enough power to be a standalone computer. Five watts is unbeatable though.
jimk3038 14 years ago
Have a look at the attached image. The top shelf is my "computer room". The two laptops on the left are my Drupal / Apache web servers. I have a primary and a backup - only the primary is powered. If you hit my web site at ph-elec.com the pages come off that top laptop.
Anyway, I count over a dozen "electronics" devices on that shelf. Guess which one is the most powerful. Yep, the little Sheevaplug in the middle.
Your right, for small tasks it might be too much.
But for stand-alone it seems fine to me. We're talking 1.2GHz here! Plus, no stupid GUI to slow it down either. It's just like being back at Purdue in the late 80's. We logged into a super computer using serial terminals (I think running at 9600 baud). The little Sheeva is probably fast than those old Super Computers who hosted hundreds of students simultaneously. Just amazes me.
I don't want to sound argumentative, but, if 1.2GHz it's enough, well then, I guess we'll just have to wait another 18 months for Moore's law to kick and double the performance again. I love techno advancement.
Just my two cents,
Jim