Notes-RSQLite

Description: Returns the contents of a database table given by name as a data frame.

 

[ ]选择SQLite的原因：

• 开源
• 轻量级
• 安装配置简单
• 不存在繁琐的用户管理
• 兼容标准的SQL语句操作

 

[ ]基础代码：

library(RSQLite)
conn <- DBI::dbConnect(RSQLite::SQLite(), "D:/test.sqlite")
DBI::dbWriteTable(conn, "IRIS", iris)
sql <- "select sum([Petal.Width]) from IRIS"
sql <- "select * from IRIS"
DBI::dbGetQuery(conn, sql)
DBI::dbDisconnect(conn)

 

[ ]数据库操作dbSendQuery(conn = db,

            "drop table if exists MOBILE_PHONE")

dbSendQuery(conn = db,
            "CREATE TABLE MOBILE_PHONE
            (Product_ID INTEGER,
            product_Name TEXT,
            price REAL,
            Brand_name TEXT)")

dbSendQuery(conn = db,
            "INSERT INTO MOBILE_PHONE
            VALUES(1,'iPhone 6s',6000,'Apple')")

dbSendQuery(conn = db,
            "INSERT INTO MOBILE_PHONE
            VALUES(2,'华为P8',3000,'华为')")

dbSendQuery(conn = db,
            "INSERT INTO MOBILE_PHONE
            VALUES(3,'三星 Galaxy S6',5000,'三星')")

　

dbExecute(mydb, 'DELETE FROM iris WHERE "Sepal.Length" < 4')

 

　　

 

 [ ]练习：

library(data.table)

# Access ------------------------------------------------------------------

library(RODBC) # 32-bit windows
odbcCloseAll() # Close connections to ODBC databases.

ACE_10P15 <- local({
  channel <- odbcConnectAccess("20181218.mdb")
  query <- "SELECT * FROM ACE_10P15"
  ACE_10P15 <- as.data.table(sqlQuery(channel, query))
})

sql_ans <- local({ACE_10P15[,c(1:23)]
namelist <- toupper(c('policy_no','entry_age','birth_date','sex',
                      'ins_no','insured_date','terminate_date','prem_type',
                      'prem_term','policy_term','sumins','stdprem',
                      'extra_prem','maxdate','prem_paidtime','status',
                      'surr_date','surr_amount','claim_date','claim_amount',
                      'parsurr_date','parsurr_amount','sv_amount'))
setnames(sql_ans, names(sql_ans), namelist)
sql_ans
}) 

# SQLite ------------------------------------------------------------------

library(RSQLite)
conn <- DBI::dbConnect(RSQLite::SQLite(),"test.sqlite")
DBI::dbWriteTable(conn,"IRIS",iris, overwrite = TRUE)
DBI::dbWriteTable(conn, 'ACE_10P15', ACE_10P15, overwrite = TRUE)
DBI::dbWriteTable(conn, 'sql_ans', sql_ans, overwrite = TRUE)

sql_ans2 <- local({
  sql1 <- 'select * from sql_ans where STATUS <> :x'
  sql2 <- 'SELECT * FROM iris WHERE "Sepal.Length" < :x'
  sql3 <- 'select datetime(INSURED_DATE, "unixepoch", "localtime")'
  as.data.table(dbGetQuery(conn, 
                           sql1, 
                           params = list(x = 6)))
})
DBI::dbWriteTable(conn, 'sql_ans2', sql_ans2, overwrite = TRUE)

sql_ans2[is.na(sql_ans2)] <- 0 
sql_ans2[,sum(SURR_AMOUNT), by = INS_NO]

# Disconnect --------------------------------------------------------------

DBI::dbDisconnect(conn)
unlink("test.sqlite")
close (channel)