R language: Connect to a SQLite database


This article shows how to get some data from an SQLite database and plot some graphs using R language.


RSQLite package


In order to access the SQLite database we will use RSQLite package.

Install RSQLite package:
> install.packages("RSQLite")

Load RSQLite package:
> library(RSQLite)

Show help about RSQLite package:
> help(package=RSQLite)

Help on SQLite function within RSQLite package:
> help(package=RSQLite, SQLite)


Connect to a database file

First we need to connect to a database file (data.db):
> con <- dbConnect(RSQLite::SQLite(), dbname="data.db")

Once we have connection established we can get the data:


List all tables

> dbListTables(con)
[1] "RESULT" "TICKER" "sqlite_sequence"


Get a whole table

Get all columns from a table.
> ticker <- dbGetQuery(con, "select * from TICKER")

Result is a matrix with rows and columns from TICKER table.
> dim(ticker)
[1] 6888 6 # 6888 rows and 6 columns.


Extract one column
> price >- ticker[,5] # Extract column 5

We get now a vector with length 6888.
> length(price)
[1] 6888

We show some graphs about price vector.
> plot(price)
> hist(price)



REFERENCE


https://www.r-bloggers.com/using-sqlite-in-r/