Vicente Hernando's web site

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/