SQLite is a relational database management system developed with C programming language. SQLite is ACID compliant and implements most of the SQL standard. Unlike other database systems, SQLite doesn't have a standalone process to serve data to client applications. It's an embedded SQL database engine. SQLite system reads and writes directly to the system disk files because it's a file-based database. Related SQL database with multiple tables, indices, and views are contained there and this database file format is supported as cross-platform.
Quick understanding of ACID properties of transactions:
There are a set of properties that needs to be fulfilled to perform the transactions. They are Atomicity, Consistency, Isolation, and Durability. which are explained as follows:
The following are the features of SQLite database that follows ACID properties:
We will require the following prerequisites for using SQLite and R together:
To install the SQLite database in Ubuntu, follow the given commands:
// install sqllite by firing the following commands sudo apt-get purge sqlite3 sqlite3-doc libsqlite3-0 sudo apt-get autoremove sudo apt-get install sqlite3 sqlite3-doc
We can install RSQLite by following the given command:
# installing RSQLite library from CRAN in R Install.packages("RSQLite")
We will see how to insert the data into R with the RSQLite package.
To load an installed package, we use the following command:
#loading the installed package library("RSQLite")
With the following commands, you can connect to DB and list all tables from the database:
# connect to db con <- dbConnect(SQLite(), dbname="data/first.db") # list all tables tables <- dbListTables(con) # exclude sqlite_sequence (contains table information) tables <- tables[tables != "sqlite_sequence"] lDataFrames <- vector("list", length=length(tables)) # create a data.frame for each table for (i in seq(along=tables)) { lDataFrames[[i]] <- dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables[[i]], "'", sep="")) }