MySQL :: Add RSS feeds to your Twitter stream using MySQL and Perl

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.

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

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

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

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;

}

100

101

102

103

104

105

106

107

108

109

110

111

112

#!/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;

}

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

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

#!/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;

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