1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
|
# Basic Functions for RSQLite
# For datatypes, refer to the following link
# https://www.sqlite.org/datatype3.html
# install.packages('RSQLite')
library(RSQLite)
setwd("C:/Users/EBKim/Desktop")
# Construction of SQLite connection
conSqlite = dbConnect(drv = SQLite(), dbname = "test.db")
# Write a data frame (mtcars: Pre-existing data frame)
mtcars
dbWriteTable(conSqlite, "mtcars", mtcars)
# Remove a table
dbSendQuery(conn = conSqlite, "Drop table testTable") # Remove a table, "testTable" if exists.
dbSendQuery(conn=conSqlite,
"CREATE TABLE testTable
(IDs TEXT,
Birth DATETIME,
Tel TEXT,
Age INTEGER,
PRIMARY KEY (IDs, Tel))
") # Send a Query (create table, drop table, update, insert, delete, etc.)
dbListTables(conSqlite) # Get all table names
dbListFields(conSqlite, "testTable") # Get all column names from a table 'table1'
dbSendQuery(conn = conSqlite, "Drop table mtcars")
dbListTables(conSqlite) # Get all table names
# Insert records
dbSendQuery(conn = conSqlite, "insert into testTable (IDs, Birth, Tel, Age) values ('EBKim', '1977-11-11 12:01:34.111', '010-1111-1111', 42)")
dfTemp1 = dbGetQuery(conSqlite,'select * from testTable' ); dfTemp1 # Get records
# Write a data frame
dfTemp2 = data.frame(
IDs = c("JBPark", "GDJin", "Temp"),
Birth = c("1996-01-02", "1989-02-03 12:02", "1977-01-01"),
Tel = c("010-2222-2222", "010-3333-3333", "010-4567-4567"),
Age = c(24, 30, 42)
); dfTemp2
dbWriteTable(conn = conSqlite, name="testTable", dfTemp2, append=T, row.names=F)
dfTemp1 = dbGetQuery(conSqlite,'select * from testTable' ); dfTemp1 # Get records
# Update records
dbSendQuery(conn = conSqlite, "update testTable set Birth = '1996-02-02', Tel = '010-2345-6789' where IDs = 'JBPark'")
dfTemp1 = dbGetQuery(conSqlite,'select * from testTable' ); dfTemp1 # Get records
# Delete records
dbSendQuery(conn = conSqlite, "delete from testTable where IDs = 'GDJin'")
dfTemp1 = dbGetQuery(conSqlite,'select * from testTable' ); dfTemp1 # Get records
# Write a data frame
dfTemp2 = data.frame(
IDs = c("IHYoo", "SJKim", "Temp2"),
Birth = c("1996-01-02", "1989-02-03 12:02", "1977-01-01"),
Tel = c("010-2222-2222", "010-3333-3333", "010-4567-4567"),
Age = c(24, 30, 42)
); dfTemp2
dbWriteTable(conn = conSqlite, name="testTable", dfTemp2, append=T, row.names=F)
dfTemp1 = dbGetQuery(conSqlite,'select * from testTable' ); dfTemp1 # Get records
# Group by
dfTemp1 = dbGetQuery(conSqlite,"select Age, count(IDs) from testTable group by Age" ); dfTemp1
|