Using Perl and MySQL to automatically respond to retweets on twitter
May 15, 2015 Leave a comment
In an earlier post titled Using Perl to send tweets stored in a MySQL database to twitter, I showed you a way to use MySQL to store tweets, and then use Perl to automatically send your tweets to 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:
consumer_key consumer_secret access_token access_token_secret
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 Rate Limiting and Rate Limits for more information. So, if you are going to put this into a cron job, I wouldn’t run it more than once every 15 minutes.
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 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 ID. If a retweet is found, the script will check your retweets table to see if you have already thanked the tweeter for the retweet. If this is a new retweet, the script will connect to twitter and send a “thank-you” message to that user, and then insert the user name and tweet ID into the retweets table. This will ensure that you do not send a thank-you response more than one time.
Here is a flow chart that will attempt to explain what the script does:
We will be using the API call/method retweets(id) to see if a tweet ID was retweeted, and then we will send the thank-you tweet via the update call. More information about the Perl twitter API may be found 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:
CREATE TABLE `retweets` ( `id` int(8) NOT NULL AUTO_INCREMENT, `tweet_id` bigint(24) DEFAULT NULL, `user_name` varchar(24) DEFAULT NULL, `retweet_update` varchar(36) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
All you need to do is to use edit this script with your own consumer_key, consumer_secret, access_token and access_token_secret for your application, and edit the accessTweets file used by the subroutine ConnectToMySql. You may also comment-out the “print” commands.
#!/usr/bin/perl
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;
my $Database = "tweets";
# Credentials for your twitter application
# you will need to subsitute your own application information for these four variables
my $nt = Net::Twitter::Lite::WithAPIv1_1->new(
traits => [qw/API::RESTv1_1/],
consumer_key => "$consumer_key",
consumer_secret => "$consumer_secret",
access_token => "$access_token",
access_token_secret => "$access_token_secret",
ssl => 1
);
# Grab the last X number of tweets to check for retweets
# - determined by the number after "limit"
$dbh = ConnectToMySql($Database);
$query = "select tweet_id, tweet_update FROM history order by tweet_update desc, id limit 10";
$sth = $dbh->prepare($query);
$sth->execute();
# loop through our results - one tweet at a time
while (@data = $sth->fetchrow_array()) {
$tweet_id = $data[0];
$tweet_update = $data[1];
print "----------------------------------------------------------------------\n";
print "Checking: $tweet_id $tweet_update\n";
print "----------------------------------------------------------------------\n";
# Connect to twitter and see if anyone retweeted this tweet
my $results = eval { $nt->retweets($tweet_id)};
for my $status ( @$results ) {
$user_name = "$status->{user}{screen_name}";
$retweet_update = "$status->{created_at}";
# see if this person has retweeted this before, and we already
# have a record of the retweet in our database
$dbh2 = ConnectToMySql($Database);
$query2 = "select tweet_id, user_name FROM retweets where tweet_id = '$tweet_id' and user_name = '$user_name' limit 1";
$sth2 = $dbh2->prepare($query2);
$sth2->execute();
@data2 = $sth2->fetchrow_array();
# Uncomment if you want to see it in action
# print "Query: $query\n";
# Check to see if we had any results, and if not, then insert
# tweet into database and send them a "thank you" tweet
if (length($data2[0]) prepare($query3);
$sth3->execute();
# Uncomment if you want to see it in action
# print "Query2: $query2\n";
# ----------------------------------------------------------------------------
# send tweet
# ----------------------------------------------------------------------------
# This pause is just to slow down the action - you can remove this line if you want
sleep 5;
my $nt = Net::Twitter::Lite::WithAPIv1_1->new(
traits => [qw/API::RESTv1_1/],
consumer_key => "$consumer_key",
consumer_secret => "$consumer_secret",
access_token => "$access_token",
access_token_secret => "$access_token_secret",
ssl => 1
);
# Here is the message you want to send -
# the thank-you to the user who sent the retweet
$tweet = "\@$user_name thanks for the retweet!";
# send thank-you tweet
my $results = eval { $nt->update("$tweet") };
undef @data2;
undef @data3;
}
else
{
# we have already thanked this user - as their name and this tweet-id was found in the database
print "----- Found tweet: $tweet_id\n";
while (@data2) {
print "----------------------------------------------------------------------\n";
print "Checking retweet by $user_name for $tweet_id\n";
print "Found retweet: $tweet_id $user_name $retweet_update \n";
$tweet_id = $data2[0];
$user_name = $data2[1];
print "***** Retweet by $user_name already in database \n";
print "----------------------------------------------------------------------\n";
#exit;
undef @data2;
undef @data3;
# This pause is just to slow down the action - you can remove this line if you want
sleep 5;
# end while
}
# end else
}
# end for my $status ( @$results ) {
}
# end while
}
exit;
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
open(PW, "<..\/accessTweets") || die "Can't access login credentials";
my $db= ;
my $host= ;
my $userid= ;
my $passwd= ;
chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);
my $connectionInfo="dbi:mysql:$db;$host:3306";
close(PW);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
In the subroutine ConnectToMySql, I store the MySQL login credentials in a text file one directory below where my Perl script is located. This file – named accessTweets contains this information:
database_name hostname or IP MySQL user name password
I tested this on two twitter accounts, and everything worked for me – but let me know if you have problems. I am not the best Perl programmer, nor am I an expert at the twitter API, so there is probably a better/easier way to do this.
 |
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. |
|
Tony is the author of Twenty Forty-Four: The League of Patriots
Visit http://2044thebook.com for more information. |
info [at] Scripting
Scripting MySQL 