So with all these elements floating about it would be nice to bring it all into some form of order.  Now assuming we want to run this regularly, every hour for example, it would be good to have everything in a file to run in one go.  First there’s a little housekeeping to do.

Saving to the Database.

The RTwitterUtils.r file had a function with no code in it, this was SaveTweetToDB().

So, what we want is for this method to take all the elements we need and insert it into the database. We know there are the following: a twitter id, a username, the tweet and a sentiment score.

SaveTweetToDB<-function(dbconn, tweetid, tweetuser, tweetdata, sentimentscore) {
  dbBeginTransaction(dbconn)
  tweetTextEdit<-iconv(tweetdata, to="UTF-8-MAC", sub='byte')
  sql<-paste("insert into twitterdata (twitterid, twitteruser, twitterdata,sentimentscore) values ('", tweetid,"','",tweetuser,"','",tweetTextEdit,"',",sentimentscore,")")
  print(sql)
  tryCatch(dbSendQuery(dbconn, sql), error=function(e) { print("caught error on insert")})
     dbCommit(dbconn)
}

So assuming that we have a database connection we can send data to the SQLite3 database to be store, we’re using the sentiment method from Part 4 to get the score. This just stores it all for us.  I’m sure there are neater ways to get this in but it works as a rough and ready prototype.

Putting it all together

There we have it, the elements of a system. Plenty of scope for improvement and I’m already thinking of the amends I could make but regardless let’s get it working first.

We need a R script to pull this all together so the Pi only need run one thing. So a new file with the following will do everything we need it to, pull the data, score it and save it all to the database.

source("/Users/Jason/work/rpisentiment/RTwitterUtils.r")
RunTwitterSentiment<-function(searchterm, qty){
   pos.words = LoadPosWordSet()
   neg.words = LoadNegWordSet()
 
   tweets<-TweetFrame(searchterm, qty)
 
   db<-GetConnection()
 
  by(tweets, 1:nrow(tweets), function(row){ 
    print(row$text)
    tweetScore = 0
    sentimentOkay = TRUE
    tryCatch(
       tweetScore<-GetScore(row$text, pos.words, neg.words)
       , error=function(e) {
       sentimentOkay = FALSE
    })
 
    if(sentimentOkay) {
      SaveTweetToDB(db, row$id, row$screenName, row$text, tweetScore)
    }
  }
 )
 
 CloseConnection(db)
}

From top to bottom….

Firstly load the source for the exsting RTwitterUtils.r script, this will expose all the methods for database connections, scoring and saving. Load in the positive and negative word sets into memory.  Then we pull search term into a dataframe. Open up a database connection to SQLite3 and then iterate through each row of the data frame, getting the sentiment score and if all is okay saving the results to the database.

Job done nicely.

From the command line you can see your work in action.

sqlite> select * from twitterdata ORDER by rowId desc limit 10;

And you’ll get the results with the sentiment scores.

338638489530818561 | panciirganesha | #ladygaga http://t.co/W19G8EEDSs |0
 338637924151214082 | Cazzbum | I love that lavender blonde, the way she moves, the way she walks... #blonde #selfie #ladygaga… http://t.co/0Xeosrz8Fl |1
 338637183072215040 | BBBaseado | RT @Kliam_: I want these cupcake !!!! #littlemonsters #ladygaga #mothermonster http://t.co/oW5uCCyRl5 |0
 338637175358894080 | BBBaseado | RT @LoganWicke: good morning @ladygaga @TaraSavelo #littlemonsters #morning #ladygaga http://t.co/Z2vKfpvhz2 |1
 338637175581196290 | JhoanZaazo | Para mí esta canción es un himno! #LadyGaga #Bornthisway
http://t.co/wuYYnZSUlc |0
 338636606175051776 | malemonsta | Y cuando dice "Now Scream"......... me pongo to cerdaco. @ladygaga #ladygaga |-1
 338635072221622273 | JesusIsNewBlack | RT @joyrisner: #Shazam~ Born This Way by #LadyGaga. http://t.co/3qSr7byLwZ |0
 338634644981415937 | Gagameumundo | RT @Kliam_: She is on every magazine !! #littlemonsters #ladygaga #mothermonster http://t.co/1BBQ3uapdq |0
 338634416953884672 | instant_RT | RT @PittDating: #50cent #bieber #britneyspears #charliesheen #ladygaga #obama #oprah @PittDating #perezhilton #ryanseacrest #the_real_shaq #theellenshow |0
 338634402823290880 | joyrisner | #Shazam~ Born This Way by #LadyGaga. http://t.co/3qSr7byLwZ |0

Now this is only a starting point. We’ve got the basics down. Part 6 is going to be about using Cron to do the task regularly and we’ll introduce some Python web basics to make it all a little more presentable.