Simple Automated Twitter Updates

There are several Twitter accounts, which offer a regular stream of Quotes on a variety of topics. Examples are @iwisenet and @tweetsayings. Using a few tools available on my Linux distribution I’ve created a very simple automated setup for regularly sending tweets from a Twitter account. There are likely a myriad of software products available for doing this a bit more professionally but I though some of you might be interested in something you can do from own PC, assuming you have the appropriate tools and like playing around.

There are only three requirements:

  • A means of storing and retrieving the tweets
  • A means of automated processing
  • A means of posting the tweets to your Twitter account

I used the following tools which are avalalble in most Linux distributions but other tools could be used instead.

  • For storing the tweets – MySql
  • For automated processing – Cron and Bash Shell
  • For posting the tweets – the Twitter Command Line API and curl

Step One – Setup a MySql Table

CREATE TABLE texts
lastused timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
usagecount int(11) NOT NULL default '0',
saying varchar(140) collate utf8_unicode_ci NOT NULL COMMENT 'Twitter Status Text',
KEY usagecount (usagecount),
KEY lastused (lastused)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This table stores the tweets. The column ‘lastused’ is automatically updated by the DB upon an update operation. This gives you the ability to cycle through the tweets when they are retrieved with the appropriate query (see below)

Step Two – The Cron Job

30 14 * * * /twitterupdate.sh

This job updates the twitter status once per day. Vary the frequency by adjusting the crontable parameters.

Step Three – The Shell Script

#!/bin/bash
##
## Bash cron job for updating a twitter status using curl, the twitter API and MySql Table
##
## Requirements:
## - cron daemon
## - bash shell
## - curl
## - MySql
##
## (Note: Twitter Status refers to the actual 'tweet' that is displayed for the account)
##
## Operational Description: The MySql Table contains a listing of Status Updates for the twitter
## account. The frequency of the updates is determined by the settings for the cron job, which
## must call this shell script. This script performs the following tasks
## - read one Status Update Text from the DB. The status text which has not been used
## for the longest time is selected.
## - An update is performed on the DB for this record incrementing the usage count and last used
## timestamp.
## - The text is sent to the twitter API using curl to perform an update of the account status.
##
## ------------------------------------------------------------------------------------------------
##
## Variables
twitterUsername="<twitter user name>"
twitterPassword="<twitter password>"
mysqlDbName="<MySql DB Name>"
mysqlUsername="<MySql User>"
mysqlPassword="<MySql Password>"
selectQuery="select saying from texts order by lastused, usagecount limit 1;"
updateQuery="update texts set usagecount = usagecount + 1 order by lastused, usagecount limit 1;"
##
## Retrieve Next Text from the DB
## ------------------------------
echo $selectQuery > query.sql
statusText=`/usr/bin/mysql -s --user="$mysqlUsername" --password="$mysqlPassword" "$mysqlDbName" < query.sql`
rm query.sql
##
## Update Text usage counter
## -------------------------
echo $updateQuery > query.sql
/usr/bin/mysql -s --user="$mysqlUsername" --password="$mysqlPassword" "$mysqlDbName" < query.sql
rm query.sql
##
## Update Twitter Status
## ---------------------
/usr/bin/curl --basic --user $twitterUsername:$twitterPassword --data status="$statusText" http://twitter.com/statuses/update.xml >/dev/null 2>&1

This script performs all the steps necessary. With a little tweaking other tools could be used. It would certainly be possible to do this with a plain text file and eliminate the need for MySql but that would require another script and would be a bit more involved. Another command line URL utility besides curl is also conceivable but that is what Twitter recommended and I have curl so I didn’t bother looking for alternatives.

Lot’s of fun …

Share

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>