TwitterDrinx: A Game


As a sponsor for a Startup Drinks Pittsburgh event, NuRelm was asked to provide some content for the event. On the day before Thanksgiving, a few of us put aside our thoughts on the feasting and gluttony to come, and thought instead about what this might mean.

Should we put together and present a video game controller family tree for everyone’s enjoyment? Nope, been done.  How about a collaborative, crowd-based, hardware/software/wetware development project where we build Raspberry Pi interfaces to little robots that will bring everyone drinks, controlled by the minds of those attending the event? Deemed “too ambitious” and “a buzz kill.”

Group project to build mind-controlled beer servant 'bots deemed "too ambitious" (image:
Group project to build mind-controlled beer servant ‘bots deemed “too ambitious” (image:

Finally, we decided on a nice game with prizes.  A few criteria were agreed to:

  • It better be easy enough to engage a bunch of people trying to relax and get their drink on.
  • Would be cool if it could help promote Startup Drinks in some way.
  • It should be based, to some small degree, on what we at NuRelm do for a living, which is to use the internet to build, connect, and integrate things on the Web.
  • Bonus points if we can get attendees to tweet stuff.

The game we decided on goes something like this:  We ask people to tweet something funny (theme pending) that includes #SUDPgh. We show tweets as they come in, live, on a big screen, and give attendees the URL to see them on their devices if they like.  We give awards based on the funniest tweets, or to, say, the 25th and 50th tweets that come in.

Whatever specific rules we come up with, we need to build a simple platform that displays tweets as they come in via a website.  This article, along with a few more that will be coming soon, will walk through doing that.  We’ll take the opportunities to introduce and use a few interesting technologies, including:

The Plan

We need a few parts to make this work:

  • Search and Store (on the server): One component of our application has to go ask Twitter for recent tweets matching our search criteria, then store the results somewhere that our frontend app (the “Show” bullet below) can access.  Why store these tweets rather than just go get them when our frontend requests them, you ask?  Because we only have a limited number of  API requests, and running separate searches for every client web browser using the site would blow our allocation of API requests in an instant.
  • Get (on the server): Another component on our server will need to grab search requests from our tweet database as needed by the client browsers. This will take the form of a simple, stripped-down API with 2 endpoints: /tweets (get all the tweets we’ve collected), and /tweets/[id] (get tweets starting at a certain ID).  We’ll talk about how to use this on the frontend in a later article.
  • Show (in the browser): Finally, we have to show the results.  For this application, we’ll enjoy the benefits of using a real-time frontend framework, AngularJS.
Get Tweets, store them, make them accessible to a frontend client, show them. Note to Twitter legal team: That is NOT an attempt to recreate the Twitter logo, as there are no ruffles on the wings.
Get Tweets, store them, make them accessible to a frontend client, show them.
Note to Twitter legal team: That is NOT an attempt to recreate the Twitter logo, as there are no ruffles on the wings.

A quick aside:

Thanks to Leland Batey for this tip on how to cleanup whiteboard images with a little bit of Imagemagick.  Linux and Mac users, this makes a handy addition to your .bashrc file:

# Clean up whiteboard images
function cleanwb() {
convert "$1" -morphology Convolve DoG:15,90,0 -negate -normalize -blur 0x1 -channel RBG -level 5%,90%,0.1 "$2"

Twitter Search and Store

We’ll start out in this article discussing the first component on the list, a Twitter search-and-store script. There are plenty of ways to do this, and the approach shown here will use Twitter’s REST API, the trusty Ruby language, the Oxford Comma, and the twitter and sqlite3 gems.

First, create a project directory, then create /config/twitterdrinx.yml and /ruby/search_tweets.rb. Inside of config/twitterdrinx.yml:

consumer_key: ‘your_consumer_key’
consumer_secret: ‘your_consumer_secret’
access_token: ‘your_access_token’
access_token_secret: ‘your_access_token_secret’

search_query: ‘christmas sweater since:2015-11-22 -rt’

To get those API keys, you’ll need to head over to and sign in with the Twitter account info you’ll use to access the API. Once there, simply “Create a New Account” and get the parameters above from the “Keys and Access Tokens” tab of the new account you created.

The final item in that YAML file is the search we’ll conduct on Twitter. The sample search above is looking for tweets containing “christmas sweater” that occurred since 11/22/2015, and which are not retweets.

Now let’s move on to /ruby/search_tweets.rb. Let’s grab a few gems and load a configuration file (more on that soon):

require 'twitter'
require 'sqlite3'
require 'yaml'

params = YAML.load_file(File.dirname(__FILE__) + '/../config/twitterthang.yml')

Next, set your Twitter API keys up:

## Twitter API keys, pulled from config file
CONSUMER_KEY = params['twitter_keys']['consumer_key']
CONSUMER_SECRET = params['twitter_keys']['consumer_secret']
ACCESS_TOKEN = params['twitter_keys']['access_token']
ACCESS_TOKEN_SECRET = params['twitter_keys']['access_token_secret']

Next, setup a search query, and use the twitter gem to start the search:

## Your seach query, pulled from config file
SEARCH_QUERY = params['search_query']

## Everything else
client = do |config|
  config.consumer_key = CONSUMER_KEY
  config.consumer_secret = CONSUMER_SECRET
  config.access_token = ACCESS_TOKEN
  config.access_token_secret = ACCESS_TOKEN_SECRET

tweets =

We’re almost done with the searching, and ready to start storing.  First let’s create a SQLite database, and build a table containing columns for a tweet’s ID, handle, text, number of favorites, number of retweets, and creation date:

  db = File.dirname(__FILE__) + "/tweets.db"
  db.execute "CREATE TABLE IF NOT EXISTS tweets(" +
             "tweet_id INT UNIQUE ON CONFLICT IGNORE, " +
             "handle TEXT, " +
             "tweet_text TEXT, " +
             "num_favs INT, " +
             "num_rts INT, " +
             "img_url TEXT, " +
             "created_at DATETIME)"

Finally, we’ll prepare a statement, loop through the tweets we got from the search, and store each one. Note that we loop backwards using Ruby’s reverse_each method, which lets us store the oldest tweets first. This is a convenient order for retrieving tweets later.

  stmt = db.prepare "INSERT INTO tweets " +
                    "(tweet_id, handle, tweet_text, num_favs, " +
                    " num_rts, img_url, created_at) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?)"
  tweets.take(50).reverse_each do |tweet|

    img_url =[0].media_url.to_s
    img_url = ''
  puts "#{tweet.user.screen_name} (#{tweet.created_at}): " +
       "#{tweet.text} " +
       "[Favs: #{tweet.favorite_count}, " +
       "Retweets: #{tweet.retweet_count}, " +
       "Img URL: #{img_url}, " +
       "ID: #{}, Date: #{tweet.created_at}]"
  stmt.execute, tweet.user.screen_name, tweet.text,
               tweet.favorite_count, tweet.retweet_count,
               img_url, tweet.created_at.to_s
rescue SQLite3::Exception => e
  puts "DB exception occurred: "
  puts e
  stmt.close if stmt
  db.close if db

Once done, close the prepared statement and database connection.

One last nice touch is to create a Gemfile for Ruby:

source ''

gem 'twitter'
gem 'sqlite3'
gem 'yaml'

Test Drive

Test your work so far.  Make sure your Ruby environment and Bundler are installed, then:

bundle install
bundle exec ruby ./search_tweets.rb

You should see some lines print out for tweets that were found.  Now, if you have SQLite installed, check those tweets out in the database by running sqlite3 ./tweets.db and running select * from tweets; once you’re in.  You should see a list of tweets matching your criteria scroll down the screen.

We’ll keep versions of the code here on GitHub as we work. Just grab a copy, and copy twitterdrinx.yml.sample to twitterdrinx.yml and add your Twitter keys.

What’s Next

Our next step is to create an endpoint that will allow us to pull tweets from the database we just created. Stay tuned!

Leave a Reply

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