Cyrus Mohammadian Cyrus Mohammadian - 2 months ago 13x
R Question

Import SQLite database from GitHub

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:

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

An example of a link I'd want to import is the following:

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.


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

temp <- tempfile()
download.file("", temp)
db <- dbConnect(SQLite(), dbname=temp)
#  [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"