Querying a SQLite database using the RSQLite Package

SQLite is a server-less SQL Database that does not require a separate server process. You do not need a configuration (Zero configuration) and it does not need any administration. The whole database is stored in a single cross-platform disk (or on your mobile device). It is very popular and widely used with mobile applications as the database is stored in the device and can be used without network connection. No external dependencies.

RSQLite is a R Package that is useful in manipulating a SQLite database. The Package can be downloaded from on of the CRAN mirrors. The one I got was from a mirror in France.
When you install the package you will be installing the following:

Packages.png

The RSQLite package has a built-in database with many tables called datasets db which is actually a file called datasets.sqlite.

In a previous post I described in detail creating a SQLite database table  using Visual Studio 2015 Community and an extension. The database created has an extension .db.

SQLite database can be extension .db or .sqlite. The database created using the Visual Studio 2015 called Sept27-2016.db was renamed Sept27-2016.sqlite.

After installing the package you can run the following code in RGUI to connect to the database created using Visual Studio by indicating the path to the .db file as shown.

---------------------
library("RSQLite")
Warning message:
package ‘RSQLite’ was built under R version 3.2.5

> db <-dbConnect(SQLite(),dbname="C:\\Users\\Jayaram\\Documents\\Blog2017\\MSSS\\SQLite\\RSQLite\\Sept17-2016.sqlite")

> dbListTables(db)
[1] "Sept22"
>
---------------------
Once you get the table, you can query as shown:

> dbReadTable(db,"Sept22")
[1] Id    Fname LName Age 
<0 rows> (or 0-length row.names)
> dbGetQuery(db, "SELECT * from Sept22")
[1] Id    Fname LName Age 
<0 rows> (or 0-length row.names)
---------------
Here is the table created in Visual Studio 2015 which has no data in it at present.