You’ve probably noticed I’m doing the walkthrough in bit sized pieces, two reasons. Firstly time, to code, investigate and then write up what you’re doing can take time. A labour of love I know and I do enjoy doing it.  Secondly I personally think it’s easier to learn in small segments.  If I posted a huge start-to-finish project it’s harder to learn.

So, Part 3. Sqlite3 and R.

Installing Sqlite3 on to Raspberry Pi

On your Pi log in and then sudo su so you are the root user.  Now we’re going to install the Sqlite3 database command line client.

apt-get install sqlite3

It will download some things and get them installed.

We need a database to hold our tweets but also have a column for a sentiment score. This will become more clear later on when we start saving twitter data from R into the database.  For now though let’s just create the required table so we have it for later.

Open up a text editor and create a file with the following sql:

create table twitterdata (
  twitterid varchar(100),
  twitteruser varchar(100),
  twitterdata varchar(255),
  sentimentscore int
);

It’s the basic of the basic but it suits our needs here.

Next we need to run Sqlite3 and give a name of the database we want to create, then run the above sql file. We can do this in one line from the command line:

sqlite3 twitter.db < name_of_your_sql_file.sql

An easy way to check all is well is to run sqlite3 and run the .tables command.

root@raspberrypi:/home/pi# sqlite3 twitter.db 
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
twitterdata
sqlite>

Getting R ready to access Sqlite3

R requires another library to be able to read/write to the Sqlite3 database.  So fire up R and run the following command:

install.packages("RSQLite", dependencies=TRUE)

It might take some time to build and compile so be patient.

The basic code to connect to our database from R is fairly trivial.

library(RSQLite)
db = dbConnect(dbDriver("SQLite"), dbname="twitter.db")

So where are we at?

We’ve got the fair guts of what we need to retrieve and store tweets. Next time we turn our attention to coding in R the storing of tweets and getting the sentiment.