This is a very basic how to, I’ve  just saved here this here for future reference 

I wanted to build game that had a life of it’s own. To do this I wanted to get lots of lists of things. A list of emotions, list of names, etc etc. If I have a look around I might be able to find lists of these things, but since there were so many different categories of things I wanted lists of I decided to scrape it from Wikipedia, well dbpedia. I did this in R because it’s what I know.

First I wanted to store the list in a database, I went with MySQL because I have mamp on my machine making it easy to work with. The RMySQL package is easy to install on OS X, I’ve heard people say there isn’t a binary on Windows so you may have to compile it yourself. Anyway, for me it was a case of:


I’m using dbpedia so I installed the sparql plugin


Now load the packages:



Define the endpoint as dbpedia’s and set options as NULL

endpoint <- “”
options <- NULL

Even though I’m not using them all I’m stealing the sparql prefixes that the dbpedia endpoint uses

prefix <- “PREFIX owl: <>
PREFIX xsd: <>
PREFIX rdfs: <>
PREFIX rdf: <>
PREFIX foaf: <>
PREFIX dc: <>
PREFIX dbpedia2: <>
PREFIX dbpedia: <>

I then wrote a pretty straight forward query to grab all the English labels to things in the emotion category of wikipedia and put this in to my query, along with the prefixes.

query <- paste(prefix,
‘SELECT  ?isValueOf ?name
?isValueOf <> <> .
?isValueOf rdfs:label ?name
FILTER(LANG(?name) = “” || LANGMATCHES(LANG(?name), “en”))


result <- SPARQL(endpoint,q,ns=prefix,extra=options)$results

gets me the list of emotions in English in a table like so:


emotions scraped from wikipedia

Now for my game I want these in database. Later I’m going to want more information on them, I can grab that from info Wikipedia later, but that can wait until I’m more certain that my game will work. I set up a database called classroom_story (the name of my game) and created a table to store my emotions. Because I’m using MAMP I had to create a symbolic link like so:

ln -s /Applications/MAMP/tmp/mysql.sock /tmp/mysql.sock

The results can then be written to the mysql table very easily:

con <- dbConnect(MySQL(), user=”username”, password=”password”,  dbname=”classroom_game”, host=”localhost”,client.flag=CLIENT_MULTI_STATEMENTS)
# write it back

Currently this is just a list, but I could pull back whatever I want from Wikipedia, I’m going to want to pull out lots of information about lots of things to build my game.

Categories: Data Analytics


Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *