Programming Your Home Mike Riley

Prev Next

5.6 Tweeting with Python

A number of different languages capable of accomplishing the tasks of monitoring and interpreting incoming serial console messages and transmitting outbound messages to the serial port exist. There are also a number of Twitter libraries for various programming languages.

Python was chosen for this and several other scripts in the book due to the language’s easy-to-follow syntax, its default inclusion in Linux and Mac OS X operating systems, and its “batteries included” approach to bundling a number of relevant libraries (such as SQLite) in its base distribution. To learn more about programming in Python, check out Learning Python [LA03].

For this project, the basic functionality we need the tweetingbirdfeeder.py Python script to accomplish is this:

Record the date and time when a bird lands and departs from the perch in the birdfeeding table in the tweetingbirdfeeder database.
Record the date and time when seed levels are depleted and replenished in the seedstatus table, which is also part of the tweetingbirdfeeder database.
Listen for inbound and transmit outbound serial messages via the FTDI cable-connected XBee radio and respond to events by storing the date and time of their occurrence and condition.
Connect to Twitter via OAuth authentication and post changes in bird feeding and seed level status.

The only additional Python libraries that need to be installed for this project are pyserial and python-twitter.

Beyond relaying tweets to a designated Twitter account, it would be helpful to visualize trends in the data we will be tweeting, such as the frequency and date/time of bird landings and the average time between seed refills. We can then see how these trends map out over an hour, a day, a month, and a year. To do this, we will need to capture the data in a structured format.

Configure the Database

Since Python 2.5 and higher supports the SQLite database out of the box, and because our data needs don’t require an overengineered standalone database server, SQLite is the ideal choice for the job. While we could have dumped these values to a plain-text comma separated value (CSV) file, organizing the data into a structured SQLite file affords us two benefits: First, we will be better prepared for future data analysis queries. Second, we will have greater flexibility to capture and manage other types of data events later on simply by adding new columns to the table.

In order to create a database in sqlite3 file format, we can use the sqlite3 command-line tool. This tool is already installed on Mac OS X. On Linux, it will most likely need to be retrieved from the distribution’s repository. In the case of Debian-based distributions like Ubuntu, issuing sudo apt-get install sqlite3 libsqlite3-dev should install the application. Windows users will need to download the sqlite3.exe utility from the SQLite website.[51]

Once installed, type sqlite3 within a terminal window. This will display something like the following:

	`SQLite version 3.7.6`
	`Enter ".help" for instructions`
	`Enter SQL statements terminated with a ";"`
	`sqlite>`

Your installation of SQLite may report a different version number.

Next, we will enter the SQL statement to create our new database. To do so, exit the sqlite command shell by typing .q followed by a carriage return at the sqlite> prompt. Then relaunch the sqlite3 tool, followed by the name of the database to be opened.

For this project, we will call the database tweetingbirdfeeder, with the filename tweetingbirdfeeder.sqlite. Because this database file does not yet exist, SQLite will automatically create the file for us. The database file will be created from the same directory that you launched the sqlite3 tool from. For example, if you launched sqlite3 from your home directory, the database file will be created there.

While the SQLite command-line tools provide all you need to create and manage SQLite databases, it’s sometimes easier to work with a graphic user interface. This is especially applicable if you need to scroll through rows of data in a single window. Several open source GUI-based SQLite database explorer-style apps exist. If you are a Mozilla Firefox web browser user, I recommend using the cross-platform SQLite Manager Firefox plug-in.[52]

Installing the plug-in is easy. From the Firefox’s Tools→Add-ons menu selection, search for SQLite Manager and click the Install button. Open SQLite Manager from Firefox’s Tools→SQLite Manager menu option. Creating a new database is as simple as clicking on the New Database icon in the SQLite Manager window toolbar. Saving and opening SQLite database files is just as easy.

We will create a new table in the tweetingbirdfeeder.sqlite database that we will call birdfeeding with the following structure:

Column Name	Data Type	Primary Key?	Autoinc?	Allow Null?	Unique?
id	INTEGER	YES	YES	NO	YES
time	DATETIME	NO	NO	NO	NO
event	TEXT	NO	NO	NO	NO

We can create this table by submitting the following SQL statement to the sqlite command-line tool:

	`[~]$ sqlite3 tweetingbirdfeeder.sqlite`
	`SQLite version 3.7.6`
	`Enter ".help" for instructions`
	`Enter SQL statements terminated with a ";"`
	`sqlite> CREATE TABLE "birdfeeding" ("id" INTEGER PRIMARY KEY NOT NULL UNIQUE,`
	`"time" DATETIME NOT NULL,"event" TEXT NOT NULL);`

With the birdfeeding table established, we need another table, one that has a similar structure in the same database and is called seedstatus:

Column Name	Data Type	Primary Key?	Autoinc?	Allow Null?	Unique?
id	INTEGER	YES	YES	NO	YES
time	DATETIME	NO	NO	NO	NO
event	TEXT	NO	NO	NO	NO

Just like the birdfeeding table, submitting the following SQL statement to the sqlite command-line tool will generate the desired structure for the seedstatus table:

	`[~]$ sqlite3 tweetingbirdfeeder.sqlite`
	`SQLite version 3.7.6`
	`Enter ".help" for instructions`
	`Enter SQL statements terminated with a ";"`
	`sqlite> CREATE TABLE "seedstatus" ("id" INTEGER PRIMARY KEY NOT NULL,`
	`"time" DATETIME NOT NULL ,"event" TEXT NOT NULL );`

Now that the database has been defined, we can work on the code to import the database and Serial and Twitter libraries, then listen for serial events being generated and timestamp and store these events to the appropriate database table.

We’ll conclude the event capture by posting a tweet of the situation. But before you can programmatically tweet to Twitter, you need to create a Twitter account and sign up for a Twitter API key and related OAuth credentials. Let’s go get ourselves an API key.

Twitter API Credentials

Before sending tweets to Twitter, you need a Twitter account to send them to. And before you send tweets to Twitter programmatically via a language or library that supports OAuth,[53] you need to create an application identifier for the intended Twitter account. While you could use an existing Twitter account, I prefer creating new accounts whenever a new project demands it. That way, followers of my existing account are not accosted by tweets of my latest experiments. It also offers a way to share your application’s tweets selectively. With these considerations in mind, create a new account and application ID specifically for the bird feeder project.

Using your new Twitter account credentials, visit http://dev.twitter.com and select the “Register an app” option. On the New Twitter Application page, enter a unique name for your application, a description at least ten characters long, and a valid website for the app. Enter a temporary one if you don’t have a permanent website from which you will offer your application for download. Then select Client under Application Type and select Read & Write under Default Access type. You can upload a custom icon if you like, but it’s not required. Then enter the CAPTCHA validation and click the Register Application button at the bottom of the screen. Read and accept the Twitter API Terms of Service to proceed.

Once your request has been approved, a unique API Key, OAuth Consumer key, and Consumer secret will be generated. Click the My Access Token menu item on the right side of the page to access your application’s all important Access Token (oauth_token) and super-secret Access Token Secret (oauth_token_secret). Copy these unique codes and store them in a safe, secure file. You will need these values to programmatically interact with your new Twitter account. Remember to keep these values a secret! You don’t want any unscrupulous individuals getting hold of your secret token and using it to spam your friends and raise the ire of the Twitter community.

With a Twitter account and a valid application API key in hand, you can use these credentials in our Python-based Tweeting Bird Feeder application.

The Python-Twitter Library

Even though we have API access to Twitter, we still need to talk to Twitter from Python. We can do so with help a little help from the Python-Twitter library.[54] To install both the Pyserial and Python-Twitter libraries, download the latest version and execute the standard sudo python setup.py install command. If you’re installing this library on Mac OS X 10.6 (Snow Leopard) or higher, the easy_install Python setup tool is preinstalled. However, due to quirks in the 64-bit libraries, you will need to precede the command with an i386 architecture flag to install the Python-Twitter library without errors. The complete command for this is sudo env ARCHFLAGS="-arch i386" easy_install python-twitter.

At last, we have all the accounts configured and dependencies installed, and we can complete the project by writing the Python script that will listen for messages on the receiving XBee radio serial port, timestamp the messages, save them in a database, and post the message to Twitter. Let’s write the Python script that will codify this process.

TweetingBirdFeeder/tweetingbirdfeeder.py
	`# import DateTime, Serial port, SQLite3 and Twitter python libraries`
	`from datetime import datetime`
	`import serial`
	`import sqlite3`
	`import twitter`

	`# import the os module to clear the terminal window at start of the program`
	`# windows uses "cls" while Linux and OS X use the "clear" command`
	`import os`
	`if sys.platform == "win32":`
	`os.system("cls")`
	`else:`
	`os.system("clear")`

	`# Connect to the serial port, replacing YOUR_SERIAL_DEVICE with the`
	`# name of the serial port of the FTDI cable-attached XBee adapter`
	`XBeePort = serial.Serial('/dev/tty.YOUR_SERIAL_DEVICE', \`
	`baudrate = 9600, timeout = 1)`

	`# Connect to SQLite database file`
	`sqlconnection = sqlite3.connect("tweetingbirdfeeder.sqlite3")`

	`# create database cursor`
	`sqlcursor = sqlconnection.cursor()`

	`# Initialize Twitter API object`
	`api = twitter.Api('Your_OAuth_Consumer_Key', 'Your_OAuth_Consumer_Secret', \`
	`'Your_OAuth_Access_Token', 'Your_OAuth_Access_Token_Secret')`

	`def transmit(msg):`
	`# Get current date and time and format it accordingly`
	`timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")`

	`# Determine message and assign response parameters`
	`if msg == "arrived":`
	`tweet = "A bird has landed on the perch!"`
	`table = "birdfeeding"`
	`if msg == "departed":`
	`tweet = "A bird has left the perch!"`
	`table = "birdfeeding"`
	`if msg == "refill":`
	`tweet = "The feeder is empty."`
	`table = "seedstatus"`
	`if msg == "seedOK":`
	`tweet = "The feeder has been refilled with seed."`
	`table = "seedstatus"`

	`print "%s - %s" % (timestamp.strftime("%Y-%m-%d %H:%M:%S"), tweet)`

	`# Store the event in the SQLite database file`
	`try:`
	`sqlstatement = "INSERT INTO %s (id, time, event) \`
	`VALUES(NULL, \"%s\", \"%s\")" % (table, timestamp, msg)`
	`sqlcursor.execute(sqlstatement)`
	`sqlconnection.commit()`
	`except:`
	`print "Could not store event to the database."`
	`pass`

	`# Post message to Twitter`
	`try:`
	`status = api.PostUpdate(msg)`
	`except:`
	`print "Could not post Tweet to Twitter"`
	`pass`

	`# Main program loop`
	`try:`
	`while 1:`
	`# listen for inbound characters from the feeder-mounted XBee radio`
	`message = XBeePort.readline()`

	`# Depending on the type of message is received,`
	`# log and tweet it accordingly`
	`if "arrived" in message:`
	`transmit("arrived")`

	`if "departed" in message:`
	`transmit("departed")`

	`if "refill" in message:`
	`transmit("refill")`

	`if "seedOK" in message:`
	`transmit("seedOK")`

	`except KeyboardInterrupt:`
	`# Exit the program when the Control-C keyboard interrupt been detected`
	`print("\nQuitting the Tweeting Bird Feeder Listener Program.\n")`
	`sqlcursor.close()`
	`pass`

Once the necessary datetime, serial, sqlite, and twitter libraries are loaded, we clear the terminal window (sending a cls for Windows and a clear for any other operating system), connect to the receiving XBee radio serial port (the XBee that is attached to the computer via the FTDI cable). Then we connect to the tweetingbirdfeeder.sqlite3 database file we created earlier and run an infinite while loop until the Control-C keyboard combination is triggered so we can gracefully exit the program. If the attached XBee radio receives a message it recognizes, we call the def transmit(msg) function that parses the msg variable, adds descriptive text to the event, saves the message to the database, and posts it to Twitter.

With the Arduino running and the XBee radios paired and powered, test the threshold detections by touching the perch sensor and photocell enough times to trigger several event transmissions. Assuming no errors were reported in the terminal window of the running script, open the tweetingbirdfeeder.sqlite3 file in the SQLite Manager’s Browse and Search window and verify that entries for both sensors were timestamped when the related events were triggered. If everything checks out, log into the Twitter account that you used to post the events and verify that the events appear on the timeline.

We’re almost done. Just a few more hardware assembly steps remain.

Prev Next