MySQL :: 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 apps.twitter.com, and obtain the following:

consumer_key
consumer_secret
access_token
access_token_secret

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 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 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:

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

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

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).

#!/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, "connect($connectionInfo,$userid,$passwd);
   return $l_dbh;

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

#!/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, "connect($connectionInfo,$userid,$passwd);

return $l_dbh;

}

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:

<mysql database name>
<mysql server hostname or ip>
<mysql user name>
<mysql password>

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.