Import SQLite database from GitHub

Question

How can I import a SQLite database from a GitHub repository into my R environment?

If I have a SQLite database on my local hard I can do the following, I would like to generalize this to SQLite DB's stored on GitHub:

library("RSQLite") 
db <- dbConnect(SQLite(), dbname="/path_to_file/database.sqlite")
dbListTables(db)
players<- dbGetQuery( db,'
                      select column1 
                      from table1
                      ' )

An example of a link I'd want to import is the following: https://github.com/cmohamma/jeopardy

In case it's not possible to load a SQLite db into memory from a network connection, I'd like to at least know how to download it to disk via command line interface.

I tried accessing the repository via RSelenium but I can't figure out how to get the browser (Chrome) to download anything from GitHub - I can navigate to the file in the repository but I can't identify the download button.

This guy says that SQLite databases must be saved to disk and not just stored in memory... so you have to download the file first and then you can read it using your code... http://stackoverflow.com/questions/21963020/reading-from-sqlite3-remote-databases — cory, Aug 15 '16 at 17:40
@cory alright, very interesting. Thanks for that. I should still be able to download the file to disk then read the tables I want into memory via a command line script (which is what i need help with i guess). I don't really care if its being saved to memory per se (I guess it makes sense that it needs to be saved to disk first). — Cyrus Mohammadian, Aug 15 '16 at 17:59
I guess in-memory storing of sqlite db is a possibility, "When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. " https://www.sqlite.org/inmemorydb.html — Cyrus Mohammadian, Aug 15 '16 at 18:04
If the direct use of an URL to `dbConnect()` would simplify your life, consider filing an issue at https://github.com/rstats-db/RSQLite/issues. — krlmlr, Aug 16 '16 at 09:54

score 6 · Accepted Answer · answered Aug 15 '16 at 19:15

You can save the raw sqlite file to a temporary file:

library("RSQLite") 
temp <- tempfile()
download.file("https://github.com/cmohamma/jeopardy/blob/master/database.sqlite?raw=true", temp)
db <- dbConnect(SQLite(), dbname=temp)
dbListTables(db)
#  [1] "Strike1Players"         "Strike2Players"         "Strike3Players"        
#  [4] "ThreeStrikesClues"      "WrongAnswers"           "categories"            
#  [7] "clue_wrong_answers"     "clues"                  "final"                 
# [10] "final_jeopardy_answers" "game_players"           "games"                 
# [13] "players"                "sqlite_sequence"        "temp"

Import SQLite database from GitHub

1 Answers1