Add RSS feeds to your Twitter stream using MySQL and Perl
October 27, 2015 2 Comments
Adding good content to Twitter can be a pain. I can’t do it during working hours, and I don’t have much time at night. But, the more content you have, the more followers you can gain, and the more your original tweets can be seen (hopefully). I have written several posts about using the latest Perl-Twitter API – Net::Twitter::Lite::WithAPIv1_1, so you might want to check these out as well.
– Use MySQL and Perl to automatically find, follow and unfollow twitter users
– Using Perl to retrieve direct messages from Twitter, insert messages into a MySQL database and then delete the messages
– Using Perl and MySQL to automatically respond to retweets on twitter
– Using Perl to send tweets stored in a MySQL database to twitter
However, finding good supplemental content is easy. There are plenty of companies and web sites which offer RSS (Rich Site Summary or Really Simple Syndication) feeds that you can use on Twitter. In this post, I will show you how to capture the RSS feed from a web site and tweet the links with your Twitter account.
One warning – Twitter has strict guidelines for using API’s on their site. The rate limits may be found at https://dev.twitter.com/rest/public/rate-limiting. In my previous post, I included a Perl script (rate_limit.pl) that prints out the API rate limits for your application. You can use this script to monitor your usage. The key with using the Twitter API is to not be too aggressive, or your app will be banned by Twitter. For example, Twitter does not allow bulk follows and unfollows – so having patience is important. And if you use these scripts to send SPAM, your app and Twitter account will be banned.
For this post, I created two Perl scripts. The first grabs the RSS information and inserts it into a MySQL database. The second script pulls the information from the MySQL database and sends a tweet one record at a time. You can set up a cron job to do both of these scripts, but remember the RSS script needs to run first.
The key when parsing RSS feeds is to find a unique number/identifier which you can use for each feed item. Some RSS feeds will include a <guid> tag, but often you have to parse the URL link to get a unique number/identifier. The script uses this unique identifier to check and see if this RSS feed item is already in the database, so you don’t have duplicate RSS items. I tested these scripts on a dozen different RSS feeds, and it works really well.
Before you attempt to use these scripts, 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
We will need to create two databases. One to hold the RSS feed information, and another to hold the tweet history. I use the tweet history to delete older tweets with another Perl script I wrote. Here is the SQL to create the databases:
CREATE TABLE 'rss_oracle' (
'id' int(9) NOT NULL AUTO_INCREMENT,
'id_post' bigint(14) NOT NULL DEFAULT '0',
'post_title' varchar(256) DEFAULT NULL,
'post_url' varchar(256) DEFAULT NULL,
'post_author' varchar(48) DEFAULT NULL,
'post_date' datetime DEFAULT NULL,
'tweet_sent' varchar(3) DEFAULT NULL,
'tweet_sent_date' datetime DEFAULT NULL,
PRIMARY KEY ('id','id_post')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
CREATE TABLE `history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tweet` char(140) DEFAULT NULL,
`tweet_id` varchar(30) DEFAULT NULL,
`tweet_update` datetime DEFAULT NULL,
`error` char(3) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
For our RSS feed, we will be grabbing the RSS from Oracle‘s web site. You will need to change the $source variable to whatever RSS feed you want to use. As you grab the data, you will need to test your script to see what non-ASCII characters are included in the title. This example script should translate most characters to ASCII ones, but you will want to check the output before actually tweeting the posts. When you find strange characters, you can simply truncate the RSS database table and re-run the script, or you can uncomment the print statements to see what is being returned.
In the subroutine ConnectToMySql used in the Perl scripts, 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
The scripts are also available on GitHub – https://github.com/ScriptingMySQL/PerlFiles. (For some reason, WordPress likes to change the code.)
rss_oracle.pl
#!/usr/bin/perl -w
use LWP::Simple 'get';
use Encode;
use utf8;
use Text::Unidecode;
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;
use XML::FeedPP;
my $source = 'https://www.oracle.com/corporate/press/rss/rss-pr.xml';
my $feed = XML::FeedPP->new( $source );
foreach my $item ( $feed->get_item() ) {
$post_id = $item->guid();
$title = $item->title();
# this is where you have to replace non-ASCII characters
# each RSS feed will use different non-ASCII characters
$title = decode_utf8( $title );
$title =~ s/’/\^/g;
$title =~ s/\&\#8217;/^/g;
$title =~ s/\&\#8216;/^/g;
$title =~ s/\&\#8212;/-/g;
$title =~ s/\&\#8230;/-/g;
$title =~ s/'/\^/g;
$title =~ s/‘/\^/g;
$title =~ s/’/^/g;
$title =~ s/…/.../g;
$title =~ s/—/-/g;
$title =~ s/-/-/g;
$title =~ s/–/-/g;
$title =~ s/ 8212 /-/g;
$title =~ s/ 8230 /-/g;
$title =~ s/<em>//g;
$title =~ s/</em>//g;
$title =~ s/[^a-zA-Z0-9 ~,._*:?\$^-]//g;
$link = $item->link();
# uncomment this line to test
#print "$post_id | $title | $link\n";
# see if we already have this post in the RSS database
$dbh = ConnectToMySql($Database);
$query = "select id_post FROM rss_oracle where id_post = '$post_id' limit 1";
$sth = $dbh->prepare($query);
$sth->execute();
#print "\n$query\n\n";
# loop through our results - one user at a time
while (@data = $sth->fetchrow_array()) {
$id_post_found = $data[0];
# end - while
}
#print "id_post_found $id_post_found \n";
if (length($id_post_found) > 1)
{
#print "Found $id_post_found...\n";
$id_post_found = "";
}
else
{
$dbh2 = ConnectToMySql($Database);
$query2 = "insert into rss_oracle (id_post, post_title, post_url) values ('$post_id', '$title', '$link')";
$sth2 = $dbh2->prepare($query2);
# during testing, comment this next line to prevent the data from being inserted into the database
$sth2->execute();
#print "$query2\n";
$title = "";
$link = "";
$id_post_found = "";
}
# foreach my $item
}
exit;
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
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;
}
And here is the script to do the tweeting. You will need to add your Twitter Name to the $My_Twitter_User variable.
tweet-oracle.pl
#!/usr/bin/perl
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;
$My_Twitter_User = "YourTwitterNameHere";
# ----------------------------------------------------------------------------------
# this has to be near the top - as other parts of the script rely on these figures
# ----------------------------------------------------------------------------------
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time;
$year = $year + 1900;
$mon = $mon + 1;
# add a zero if the value is less than 10
if ($sec < 10) { $sec = "0$sec"; }
if ($min < 10) { $min = "0$min"; }
if ($hour < 10) { $hour = "0$hour"; }
if ($mday < 10) { $mday = "0$mday"; }
if ($mon < 10) { $mon = "0$mon"; }
if ($year < 10) { $year = "0$year"; }
if ($wday < 10) { $wday = "0$wday"; }
if ($yday < 10) { $yday = "0$yday"; }
if ($isdst prepare($query);
$sth->execute();
#print "$query \n";
while (@data = $sth->fetchrow_array()) {
$id_post = $data[0];
$post_title = $data[1];
$post_url = $data[2];
$post_title =~s/ / /g;
$post_title =~s/ ampamp / and /g;
# while (@data = $sth->fetchrow_array()) {
}
if (length($post_title) 100)
{
$title_trimmed = substr($post_title,0,105);
$title_trimmed = "$title_trimmed...";
#print "TRIM $title_trimmed\n";
if ($title_trimmed =~ " Oracle ")
{
$title_trimmed =~ s/ Oracle / \#Oracle /;
}
else
{
$add_Hashtag = " \#Oracle ";
}
}
else
{
$title_trimmed = "$post_title";
$title_trimmed =~ s/ Oracle / \#Oracle /;
#print "x $title_trimmed\n";
}
$tweet = "$title_trimmed $add_Hashtag \n\n$post_url";
$tweet =~ s/ / /g;
$tweet =~ s/ / /g;
$add_Hashtag = "";
#print "$tweet \n";
#exit;
# ----------------------------------------------------------------------------
# find carats and substitue for single quote
# ----------------------------------------------------------------------------
$tweet =~ s/\^/\'/g;
# ----------------------------------------------------------------------------
# send tweet
# ----------------------------------------------------------------------------
# Credentials for your twitter application
# You will need to substitute your own values for these 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",
apiurl => 'http://api.twitter.com/1.1',
ssl => 1
);
# send the tweet
my $results = eval { $nt->update("$tweet") };
# print "---DUMPER START---\n";
# print Dumper $results;
# print "---DUMPER END---\n\n";
# ----------------------------------------------------------------------------
# update mysql with new date for last_tweet date/time
# ----------------------------------------------------------------------------
$dbh = ConnectToMySql($Database);
$query = "UPDATE rss_oracle SET tweet_sent_date = '$DateTime' , tweet_sent = 'yes' where id_post = '$id_post'";
$sth = $dbh->prepare($query);
$sth->execute();
# ----------------------------------------------------------------------------
# get the status id of the last tweet
# ----------------------------------------------------------------------------
my $statuses = $nt->user_timeline({ user => "$My_Twitter_User", count=> 1 });
for my $status ( @$statuses ) {
$tweet_id = "$status->{id}";
# print "Tweet ID $tweet_id\n";
}
# ----------------------------------------------------------------------------
# replace special characters
# ----------------------------------------------------------------------------
$tweet =~ s/\\\n/~/g;
$tweet =~ s/\n/~/g;
$tweet =~ s/\'/^/g;
# update mysql with new date for last_tweet date/time
$dbh = ConnectToMySql($Database);
$query = "insert into history (tweet,tweet_id,tweet_update) values ('$tweet','$tweet_id','$DateTime')";
$sth = $dbh->prepare($query);
$sth->execute();
#----------------------------------------------------------------------
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;
}
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. Good luck with the scripts and let me know how they work for you. And follow me on Twitter at ScriptingMySQL and TonyDarnell.
 |
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 
Hi Tony,
One small piece of advice you might be interested in. I see you writing a lot of code similar to this:
$query2 = “insert into rss_oracle (id_post, post_title, post_url) values (‘$post_id’, ‘$title’, ‘$link’)”;
$sth2 = $dbh2->prepare($query2);
$sth2->execute();
Pretty basic stuff. Interpolate the variables into a string. Compile the string and execute it on the database. The problem is that if the variables you’re interpolating into the string come from user input (like, say, a web form) then this opens you up to an SQL injection attack (see http://bobby-tables.com/ for more details).
I know that in most of your code, you’re using data that isn’t from user input, but people will cut and paste your code and could potentially leave themselves open to attack.
In Perl, the easiest way to get round this is to use bind variables in your SQL statement. It’s a simple change to your existing code.
# Put bind point markerts in your SQL (note, you don’t need the quotes anymore either).
$query2 = “insert into rss_oracle (id_post, post_title, post_url) values (?, ?, ?)”;
# Compile as before
$sth2 = $dbh2->prepare($query2);
# Pass the actual values you want as arguments to execute()
$sth2->execute($post_id, $title, $link);
This easily protects your code (and your readers’ code!) from SQL injection attacks.
Thanks for reply and yes, you are correct. Your way is much better. I will attempt to now go back and change all of my code and posts over the next few weeks.
I am using MySQL Enterprise Edition firewall, so I am protected from SQL-injection attacks – See: https://www.mysql.com/products/enterprise/firewall.html for more information.