Understanding SQLite

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:

  • Atomicity refers to the guarantee that all the tasks of the database are performed.
  • Consistency ensures that the database remains in a consistent manner throughout, similar to how it was before we started.
  • Isolation refers to the requirement that other operations cannot access or see the data in an intermediate state during a transaction.
  • Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction.

Understanding features of SQLite

The following are the features of SQLite database that follows ACID properties:

  • Zero configuration
  • Cross-platform-supported disk format
  • Faster than client-server type of database system
  • Easy to use API

We will require the following prerequisites for using SQLite and R together:

  • SQLite installation
  • RSQLite installation

Installing SQLite

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

Installing RSQLite

We can install RSQLite by following the given command:

# installing RSQLite library from CRAN in R
Install.packages("RSQLite")

Importing the data into R

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=""))
}

Understanding data manipulation

We can manipulate the dataset using the following commands:

dbBeginTransaction(con)
rs <- dbSendQuery(con, "DELETE from candidates WHERE age > 50")
Exporting the data from Rdata(USArrests)
dbWriteTable(con, "USArrests", USArrests)
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset