CODEWITHSUNDEEP
rsqlitegithubimportrsqlite
Cyrus Mohammadian
Cyrus Mohammadian

Reputation: 5193

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:

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.

Upvotes: 3

Views: 2359

Answers (1)

Weihuang Wong
Weihuang Wong

Reputation: 13118

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"

Upvotes: 6

Related Questions