PChome

library(data.table)
library(RSQLite)

## Loading required package: DBI

library(DT)

Read a data.table with fread:

res_dt = fread("dataio/pchome.csv")

Create a RSQLite database:

con = dbConnect(SQLite(), "dataio/pchome.sqlite")

dbListTables(con)

## [1] "pchome"

Drop table if exist:

if (dbExistsTable(con, "pchome")) {
    dbRemoveTable(con, "pchome")
}

## [1] TRUE

Write data.frame to table:

dbWriteTable(con, "pchome", res_dt)

## [1] TRUE

List tables:

dbListTables(con)

## [1] "pchome"

List fields of a table:

dbListFields(con, "pchome")

##  [1] "Id"          "cateId"      "picS"        "picB"        "name"       
##  [6] "describe"    "price"       "author"      "brand"       "publishDate"
## [11] "isPick"

Read whole table:

pchome = dbReadTable(con, "pchome")

# solve encoding issue in Windows:
if (.Platform$OS.type == "windows"){
    pchome = apply(pchome, 2, iconv, from = "UTF-8", to = "UTF-8")
}

Display with DT::datatable:

datatable(pchome)

You can fetch results with SQL statement:

res = dbSendQuery(con, "SELECT * FROM pchome WHERE price > 10000")
pchome2 = dbFetch(res)
if (.Platform$OS.type == "windows"){
    pchome2 = apply(pchome2, 2, iconv, from = "UTF-8", to = "UTF-8")
}
str(pchome2)

## 'data.frame':    12 obs. of  11 variables:
##  $ Id         : chr  "DYAD2O-A9006JDQE" "DYAD2R-A9006XKSI" "DPAE03-A9006RR9X" "DYAD2R-A900776LD" ...
##  $ cateId     : chr  "DYAD2O" "DYAD2R" "DPAE03" "DYAD2R" ...
##  $ picS       : chr  "/pic/v1/data/item/201510/D/Y/A/D/2/O/sDYAD2O-A9006JDQE000_560e037b08db2.jpg" "/pic/v1/data/item/201604/D/Y/A/D/2/R/sDYAD2R-A9006XKSI000_56fe3f0b20c2e.jpg" "/pic/v1/data/item/201601/D/P/A/E/0/3/sDPAE03-A9006RR9X000_56970708b9924.jpg" "/pic/v1/data/item/201604/D/Y/A/D/2/R/sDYAD2R-A900776LD000_5718b07ab2c7c.jpg" ...
##  $ picB       : chr  "/pic/v1/data/item/201604/D/Y/A/D/2/O/DYAD2O-A9006JDQE000_57198b2dc111b.jpg" "/pic/v1/data/item/201605/D/Y/A/D/2/R/DYAD2R-A9006XKSI000_572ff4b5655b1.jpg" "/pic/v1/data/item/201601/D/P/A/E/0/3/DPAE03-A9006RR9X000_56970708b6c29.jpg" "/pic/v1/data/item/201604/D/Y/A/D/2/R/DYAD2R-A900776LD000_571f33630a4e5.jpg" ...
##  $ name       : chr  "SONY Xperia Z5" "SONY Xperia Z5 Premium" "Sony 行動微型投影機 MP-CL1" "SONY Xperia Z5 Premium玫瑰石英粉" ...
##  $ describe   : chr  "超值▼送16G卡+行動電源▼SONY Xperia Z5 5.2吋美型防水旗艦機" "▼送32G卡+手機立架+玻璃貼SONY Xperia Z5 Premium" "Sony 行動微型投影機 MP-CL1" "▼送Kitty側掀專用皮套▼SONY Xperia Z5 Premium玫瑰石英粉" ...
##  $ price      : int  16900 19900 11900 20900 12980 14080 17900 13480 16777 12890 ...
##  $ author     : int  NA NA NA NA NA NA NA NA NA NA ...
##  $ brand      : int  NA NA NA NA NA NA NA NA NA NA ...
##  $ publishDate: int  NA NA NA NA NA NA NA NA NA NA ...
##  $ isPick     : int  0 0 0 0 0 0 0 0 0 0 ...

dbClearResult(res)

## [1] TRUE

Or a chunk at a time:

res = dbSendQuery(con, "SELECT * FROM pchome WHERE price > 10000")
while(!dbHasCompleted(res)){
    chunk = dbFetch(res, n = 5)
    print(nrow(chunk))
}

## [1] 5
## [1] 5
## [1] 2

Clear the result:

dbClearResult(res)

## [1] TRUE

Disconnect from the database:

dbDisconnect(con)

## [1] TRUE