Using Perl to retrieve direct messages from Twitter, insert messages into a MySQL database and then delete the messages
September 18, 2015 Leave a comment
In two earlier posts, I gave some examples on how to use Perl to send tweets stored in a MySQL database to Twitter, and then how to automatically reply to your retweets with a “thanks”. In this post, I will show you how to automatically download your direct messages from Twitter, store the messages in a MySQL database, and then delete them.
I don’t like the way Twitter makes me read my direct messages. Granted, the majority of them are not real messages. The message is usually thanking me for following the sender, and then there is a personal website link or a link to a product they are selling. But if I want to delete a direct message, I have to click on the message, click the trash can, and then confirm I want to delete the message. This process is too cumbersome.
I wrote a Perl script that connects to Twitter, downloads your direct messages, inserts them into a MySQL database, and then deletes the direct message. I had a year of direct messages in my Inbox, and in a few minutes, they were gone. But I still had a copy in my MySQL database, in case I wanted to go back and read them or respond.
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, check your limits before you try downloading a large number of direct messages.
Just like the other two apps, we will be using the Net::Twitter module, as well as a few more modules which are listed in the beginning of the script.
There are two kinds of direct messages – the messages you sent and the messages you have received. So, we will be looking at two different Perl scripts to retrieve each kind of message. In the Net::Twitter module, there are a lot of different variables you can capture. In these examples, I only grabbed what I thought I needed. If there are other data variables you want, you will have to modify the tables and scripts.
First, you will need to create two databases to store your direct messages – one for the sent messages and one for the received messages. Here are the CREATE TABLE statements for both tables:
CREATE TABLE 'MESSAGES_SENT ' (
'id' int(10) NOT NULL AUTO_INCREMENT,
'creation_datetime' datetime DEFAULT NULL,
'message_id' bigint(20) DEFAULT NULL,
'sender_screen_name' varchar(16) DEFAULT NULL,
'recipient_screen_name' varchar(16) DEFAULT NULL,
'message_text' varchar(140) DEFAULT NULL,
'sender_friends_count' int(10) DEFAULT NULL,
'sender_time_zone' varchar(64) DEFAULT NULL,
'sender_description' varchar(160) DEFAULT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1
CREATE TABLE 'MESSAGES_RECEIVED' (
'id' int(10) NOT NULL AUTO_INCREMENT,
'creation_datetime' datetime DEFAULT NULL,
'message_id' bigint(20) DEFAULT NULL,
'sender_screen_name' varchar(16) DEFAULT NULL,
'recipient_screen_name' varchar(16) DEFAULT NULL,
'message_text' varchar(140) DEFAULT NULL,
'sender_friends_count' int(10) DEFAULT NULL,
'sender_time_zone' varchar(64) DEFAULT NULL,
'sender_description' varchar(160) DEFAULT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1
I am only storing a few of the variables. In each script, if you want to see all of the variables which are available, you will need to uncomment this line:
# print Dumper $statuses;
When using the Dumper command, be sure that you are only downloading a single message. Otherwise, the script will pull all of the variables and their values for all of the messages. You only need one message to see all of the variables. To only retrieve one message, be sure that the value $number_of_messages is equal to 1:
$number_of_messages = 1;
Afterwards, you can change this value to whatever you want – just be sure to watch your Twitter limits.
The print Dumper $statuses; line will display all of the possible variables for a single message. For the “sent” script, there are about 140 variables. For the “received” script, there are about 67 variables.
Here is the “Get direct messages I sent to other people” script, which uses the MESSAGES_SENT database:
#!/usr/bin/perl
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;
use Data::Dumper;
# ----------------------------------------------------------------------------
# get twitter direct messages sent
# ----------------------------------------------------------------------------
# you will need to fill in this information about your application and your twitter account
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
);
$number_of_messages = 1;
eval {
my $statuses = $nt->sent_direct_messages({ count => $number_of_messages });
# uncomment this line and set the count above to 1 (one) to see all of the variables that are available
# print Dumper $statuses;
for my $status ( @$statuses ) {
$creation_date = $status->{created_at};
# convert $creation_date to MySQL datetime format
# 0 1 2 3 4 5
# Fri Sep 04 07:32:05 +0000 2015
@creation_date_array = split(" ",$creation_date);
$creation_date_month = $creation_date_array[1];
if ($creation_date_month =~ "Jan") { $creation_date_month = "01"}
if ($creation_date_month =~ "Feb") { $creation_date_month = "02"}
if ($creation_date_month =~ "Mar") { $creation_date_month = "03"}
if ($creation_date_month =~ "Apr") { $creation_date_month = "04"}
if ($creation_date_month =~ "May") { $creation_date_month = "05"}
if ($creation_date_month =~ "Jun") { $creation_date_month = "06"}
if ($creation_date_month =~ "Jul") { $creation_date_month = "07"}
if ($creation_date_month =~ "Aug") { $creation_date_month = "08"}
if ($creation_date_month =~ "Sep") { $creation_date_month = "09"}
if ($creation_date_month =~ "Oct") { $creation_date_month = "10"}
if ($creation_date_month =~ "Nov") { $creation_date_month = "11"}
if ($creation_date_month =~ "Dec") { $creation_date_month = "12"}
$creation_date_day_of_month = $creation_date_array[2];
$creation_date_year = $creation_date_array[5];
$creation_date_time = $creation_date_array[3];
$creation_datetime = "$creation_date_year-$creation_date_month-$creation_date_day_of_month $creation_date_time";
$message_id = $status->{id};
$sender_screen_name = $status->{sender}{screen_name};
$sender_screen_name =~ s/\'/\~/g;
$recipient_screen_name = $status->{recipient_screen_name};
$recipient_screen_name =~ s/\'/\~/g;
$message_text = $status->{text};
# remove carriage returns and single tick marks
$message_text =~ s/\n/ /g;
$message_text =~ s/\'/\~/g;
$sender_friends_count = $status->{recipient}{followers_count};
$sender_time_zone = $status->{sender}{time_zone};
$sender_description = $status->{sender}{description};
$sender_description =~ s/\n/ /g;
$sender_description =~ s/\'/\~/g;
# uncomment this line if you want to print
# print "$creation_date_day_of_month - $creation_datetime - $message_id - $sender_screen_name - $recipient_screen_name - $message_text - $sender_friends_count - $sender_time_zone - $sender_description\n";
$dbh = ConnectToMySql($Database);
$query = "insert into messages_sent (creation_datetime,message_id,sender_screen_name,recipient_screen_name,message_text,sender_friends_count,sender_time_zone,sender_description) values ('$creation_datetime','$message_id','$sender_screen_name','$recipient_screen_name','$message_text','$sender_friends_count','$sender_time_zone','$sender_description')";
#print "\nquery $query\n";
$sth = $dbh->prepare($query);
$sth->execute();
# stop the program if we have an error with the database
if ( $sth->err )
{
die "ERROR! return code:" . $sth->err . " error msg: " . $sth->errstr . "\n";
}
else
{
my $destroy_id = eval { $nt->destroy_direct_message("$message_id") };
}
# you can change this so you don't go over your twitter connection limits
sleep 5;
# end for my $status
}
# end eval
};
#----------------------------------------------------------------------
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;
}
Here is the “Get direct messages I received from other people” script, which uses the MESSAGES_RECEIVED database:
#!/usr/bin/perl
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;
use Data::Dumper;
# ----------------------------------------------------------------------------
# get twitter direct messages received
# ----------------------------------------------------------------------------
# you will need to fill in this information about your application and your twitter account
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
);
$number_of_messages = 1;
eval {
my $statuses = $nt->direct_messages({ count => $number_of_messages });
# uncomment this line and set the $number_of_messages variable above to 1 (one) to see all of the variables that are available
# print Dumper $statuses;
for my $status ( @$statuses ) {
$creation_date = $status->{created_at};
# convert $creation_date to MySQL datetime format
# 0 1 2 3 4 5
# Fri Sep 04 07:32:05 +0000 2015
@creation_date_array = split(" ",$creation_date);
$creation_date_month = $creation_date_array[1];
if ($creation_date_month =~ "Jan") { $creation_date_month = "01"}
if ($creation_date_month =~ "Feb") { $creation_date_month = "02"}
if ($creation_date_month =~ "Mar") { $creation_date_month = "03"}
if ($creation_date_month =~ "Apr") { $creation_date_month = "04"}
if ($creation_date_month =~ "May") { $creation_date_month = "05"}
if ($creation_date_month =~ "Jun") { $creation_date_month = "06"}
if ($creation_date_month =~ "Jul") { $creation_date_month = "07"}
if ($creation_date_month =~ "Aug") { $creation_date_month = "08"}
if ($creation_date_month =~ "Sep") { $creation_date_month = "09"}
if ($creation_date_month =~ "Oct") { $creation_date_month = "10"}
if ($creation_date_month =~ "Nov") { $creation_date_month = "11"}
if ($creation_date_month =~ "Dec") { $creation_date_month = "12"}
$creation_date_day_of_month = $creation_date_array[2];
$creation_date_year = $creation_date_array[5];
$creation_date_time = $creation_date_array[3];
$creation_datetime = "$creation_date_year-$creation_date_month-$creation_date_day_of_month $creation_date_time";
$message_id = $status->{id};
$sender_screen_name = $status->{sender}{screen_name};
$sender_screen_name =~ s/\'/\~/g;
$message_text = $status->{text};
# remove carriage returns and single tick marks
$message_text =~ s/\n/ /g;
$message_text =~ s/\'/\~/g;
$sender_friends_count = $status->{sender}{friends_count};
$sender_time_zone = $status->{sender}{time_zone};
$sender_description = $status->{sender}{description};
$sender_description =~ s/\n/ /g;
$sender_description =~ s/\'/\~/g;
$recipient_screen_name = $status->{recipient_screen_name};
$recipient_screen_name =~ s/\'/\~/g;
# uncomment this line if you want to see the output
# print "$creation_datetime - $message_id - $sender_screen_name - $recipient_screen_name - $message_text - $sender_friends_count - $sender_time_zone - $sender_description\n";
$dbh = ConnectToMySql($Database);
$query = "insert into messages_received (creation_datetime,message_id,sender_screen_name,recipient_screen_name,message_text,sender_friends_count,sender_time_zone,sender_description) values ('$creation_datetime','$message_id','$sender_screen_name','$recipient_screen_name','$message_text','$sender_friends_count','$sender_time_zone','$sender_description')";
#print "\nquery $query\n";
$sth = $dbh->prepare($query);
$sth->execute();
# stop the program if we have an error with the database
if ( $sth->err )
{
die "ERROR! return code:" . $sth->err . " error msg: " . $sth->errstr . "\n";
}
else
{
my $destroy_id = eval { $nt->destroy_direct_message("$message_id") };
}
# you can change this so you don't go over your twitter connection limits
sleep 5;
# end for my $status
}
# end eval
};
#----------------------------------------------------------------------
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
You may hardcode these values into the script if you don’t want to mess with storing them in a file.
I tested these scripts on two twitter accounts, and everything worked for me – but I ran out of messages quickly. 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 may be 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 