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 apps.twitter.com, 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 post — tweets
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
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
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] ScriptingMySQL.com and on LinkedIn. |