R SQLite CRUD – Create, Insert, Update, Delete – Querying databases using R programming language

R SQLite CRUD – Create, Insert, Update, Delete – Querying databases using R programming language.

get packages

install.packages(“DBI”)
install.packages(“RSQLite”)

create database connection

customerdb = dbConnect(RSQLite::SQLite(), “customerdb.sqlite”)

drop table if exists

dbExecute(customerdb, “DROP table customers”)

create table

dbExecute(customerdb, “CREATE TABLE customers (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT, email TEXT)”)
dbGetQuery(customerdb, “SELECT * FROM customers”)

insert record

dbExecute(customerdb,
“INSERT INTO customers (first_name, last_name, email)
VALUES(‘Ankan’, ‘Basu’, ‘basu.ankan@gmail.com’)” )

update record

dbExecute(customerdb,
“UPDATE customers SET first_name=’UpdatedAnkan’ WHERE id=1” )
dbGetQuery(customerdb, “SELECT * FROM customers”)

delete record

dbExecute(customerdb,
“DELETE from customers WHERE id=1” )
dbGetQuery(customerdb, “SELECT * FROM customers”)

Details of code and presentation file is available on my Github Account @
https://github.com/hydrogeologist/sqlite-R

 2,258 total views,  2 views today