MySQL Blog Archive
For the latest blogs go to
Using Perl and MySQL to Automatically Respond to Retweets on Twitter

In my previous post, I showed you a way to store tweets in MySQL, and then use Perl to automatically publish them on Twitter.

In this post, we will look at automatically sending a “thank you” to people who retweet your tweets — and we will be using Perl and MySQL again.

Just like in the first post, you will need to register your application with Twitter via, and obtain the following:

One caveat: Twitter has a rate limit on how often you may connect with your application — depending upon what you are trying to do. See the Rate Limiting and Rate Limits API docs for more information. What this means is that if you are going to put this into a cron job (or use some other automated scheduler), I wouldn’t run it more than once every 15 minutes or so.

We will also be using the same tables we created in the first posttweets and history — as well as a new table named retweets. The retweets table will contain all of the user names and tweet ID’s for those retweets we have discovered and for which we’ve already sent a “thank you” tweet response.

The Perl script will connect to your tweet history table and retrieve a set of your tweet ID’s, with the most recent tweet first. The script will then connect to Twitter and check to see if there are any retweets for each given ID. If an existing retweet is found, then the script will check your retweets table to see if you have already thanked the user for their retweet. If this is a new retweet, then the script will connect to Twitter, send the “thank you” message to that user, and finally insert the user name and tweet ID combination into the retweets table. This will ensure that you do not send repeat “thank you” responses.

Here is a flow chart to help explain what the script does:

We will be using the retweets(id) API call to see if a tweet ID was retweeted, and then we will send the “thank you” tweet via the update API call. You can find more information about the Perl Twitter module at Net::Twitter::Lite::WithAPIv1_1.

First we will need to create the retweets table, where we will store the information about our tweets that were retweeted. Here is the CREATE TABLE statement for the retweets table:

Then you will need to edit the below script and insert the consumer_key, consumer_secret, access_token, and access_token_secret values for your application (which you get when registering your app with Twitter), and edit the accessTweets file (more on that shortly) used by the ConnectToMySql subroutine. (You may also want to comment-out the debug style “print” calls).

In the ConnectToMySql subroutine, I store the MySQL login credentials in a text file one directory below where my Perl script is located. This accessTweets file should contain the following information:

I tested this on two Twitter accounts and everything worked well for me, but please let me know if you have any problems. Please keep in mind, however, that I am not the best Perl programmer, nor am I an expert on the Twitter API, so there are likely better/easier way to do this.

That’s it for now. I hope that this was interesting and may prove helpful. THANK YOU for using MySQL!


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] and on LinkedIn.