Example to connect to a SQLite database using R Project for Statistical Computing

Getting source data into your R environment is the first step in data or statistical analysis, whether this be in R or R-Studio. Often this data resides in relational data stores. For those new to R, this is how to access the SQLite database from R.

Edit highlighted lines as appropriate.

# Load the SQLite library
  library("RSQLite");
  
# Assign the sqlite datbase and full path to a variable
  dbfile = "/Library/WebServer/Documents/Dash2.5/data/livedb.sqlite";
  
# Instantiate the dbDriver to a convenient object
  sqlite = dbDriver("SQLite");
  
# Assign the connection string to a connection object
  mydb = dbConnect(sqlite, dbfile);
  
# Request a list of tables using the connection object
  dbListTables(mydb);
  
# Assign the results of a SQL query to an object
  results = dbSendQuery(mydb, "SELECT * FROM DailyCensus");
  
# Return results from a custom object to a data.frame
  data = fetch(results);
  
# Print data frame to console
  data;
  
# Clear the results and close the connection
  dbClearResult(results);
Advertisements