向資料庫查詢

The world’s most valuable resource is no longer oil, but data.

The Economist

獲取資料(Getting Data)在資料科學專案中扮演攻擊發起點,如果這個專案目的是協助我們制定資料驅動的策略(data-driven strategy),而非傳統倚賴直覺的「根據經驗」策略,那麼為專案細心盤點資料來源與整理獲取方法,將可以為決策奠基穩固的基礎。常見的資料來源可以分為三種:

  1. 檔案
  2. 資料庫
  3. 網頁資料擷取

資料輸入與輸出我們討論了如何透過 R 語言載入表格式檔案(包含 CSV 資料、Excel 試算表)、非表格式檔案(包含 TXT 資料、JSON 資料),這個小節我們要討論第二種資料來源:資料庫,以輕量化、建構於本機端的 SQLite 關聯式資料庫作範例。

關於 SQLite 資料庫

SQLite 是一個基於 C 語言所建構的小型、快速、自我包含(Self-contained)、高可靠性且功能齊全的 SQL 資料庫引擎,可能是世界上被使用最廣泛的資料庫引擎,在每一台 Android 手機、每一台 iPhone 手機、每一部 Mac 筆電、每一部 Windows 10 筆電、每個 Chrome 瀏覽器、每個 Firefox 瀏覽器與每個 Safari 瀏覽器中都有 SQLite 的蹤影,因此可以估算世界上隨時隨地皆有超過 1 兆個 SQLite 資料庫在運作著,具有文件格式穩定、跨平台與向後兼容等特性,SQLite 原始碼是開源軟體授權,每個人都能自由使用。

安裝與載入套件

將 R 語言與本機端 SQLite 關聯式資料庫連接的主要套件是 DBIRSQLite, DBI 全名為 Database Interface,兩者各別負責與 SQLite 的前端與後端連結,其中 DBI 專注前端公開 API, RSQLite 則著手後端通信的驅動程式(driver)。

我們可以選擇透過命令列(Console)以 install.packages() 函數進行安裝。

# 安裝 RSQLite 與 DBI 套件
pkgs <- c("RSQLite", "DBI")
install.packages(pkgs)
1
2
3
## > # 安裝 RSQLite 與 DBI 套件
## > pkgs <- c("RSQLite", "DBI")
## > install.packages(pkgs)
## trying URL 'https://cran.rstudio.com/bin/macosx/el-capitan/contrib/3.5/RSQLite_2.1.1.tgz'
## Content type 'application/x-gzip' length 3593092 bytes (3.4 MB)
## ==================================================
## downloaded 3.4 MB
## 
## trying URL 'https://cran.rstudio.com/bin/macosx/el-capitan/contrib/3.5/DBI_1.0.0.tgz'
## Content type 'application/x-gzip' length 876902 bytes (856 KB)
## ==================================================
## downloaded 856 KB
## 
## 
## The downloaded binary packages are in
##  /var/folders/0b/r__z5mpn6ldgb_w2j7_y_ntr0000gn/T//RtmpUTj2il/downloaded_packages
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

或是透過圖形化介面(Graphic User Interface, GUI)的方法安裝,在右下角的 packages 頁籤點選 install,再輸入套件名稱接著點選安裝。

透過圖形化介面安裝

我們可以選擇透過命令列(Console)以 library() 函數將套件載入環境來使用。

# 載入 DBI 套件
library(DBI)
1
2
## > # 載入 DBI 套件
## > library(DBI)
1
2

或是透過圖形化介面(Graphic User Interface, GUI)在右下角的 packages 頁籤下搜尋 DBI 然後將前面的核取方框打勾。

透過圖形化介面載入

DBI 連結前端,搭配特定資料庫系統套件中的驅動程式來完成後段介接之作法是 R 語言和資料庫相連課題中的標準分工,例如 SQLite 資料庫就使用 RSQLite 套件、MySQL 資料庫則使用 RMySQL 套件、PostgreSQL 資料庫使用 ODBC 套件。

常用的 DBI 套件函數

DBI 套件專責資料庫前端的公開 API,其中常用的基礎函數有:

  • dbConnect() 函數:建立與資料庫的連結,多數的 DBI 套件函數會以 conn 參照這個函數所輸出的物件
  • dbDisconnect() 函數:中斷與資料庫的連結
  • dbWriteTable() 函數:創建資料庫表格
  • dbExecute() 函數:執行資料庫的指令並回傳受影響的觀測值個數
  • dbListFields() 函數:列出指定表格的欄位名稱
  • dbReadTable() 函數:讀取整張資料庫表格
  • dbGetQuery() 函數:執行查詢資料庫指令
  • dbRemoveTable() 函數:刪除整張資料庫表格

於本機端建立資料庫

我們使用 DBI 套件中的 dbConnect() 函數使用 SQLite 資料庫的驅動程式連結在本機端桌面創建的 demo.db 檔案,這個檔案就是一個空的自我包含 SQLite 資料庫。

# 安裝 RSQLite 與 DBI 套件
#pkgs <- c("RSQLite", "DBI")
#install.packages(pkgs)
# 載入 DBI 套件
library(DBI)

# 於桌面建立 demo.db
#user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
db_path <- paste0(user_desktop, "demo.db")
con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
1
2
3
4
5
6
7
8
9
10
11
## > # 安裝 RSQLite 與 DBI 套件
## > pkgs <- c("RSQLite", "DBI")
## > #install.packages(pkgs)
## > # 載入 DBI 套件
## > library(DBI)
## > 
## > # 於桌面建立 demo.db
## > #user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
## > user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
## > db_path <- paste0(user_desktop, "demo.db")
## > con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
1
2
3
4
5
6
7
8
9
10
11

執行完畢之後切換到自己的桌面,將會看到 demo.db 檔案已經被成功創建。

demo.db 檔案被成功創建在桌面

建立資料庫中的表格資料(C)

我們會使用 R 語言的套件 gapminder 的簡易版 gapminder 資料作為建立表格資料的示範,關於它背後的故事及典故,讀者可以在基礎探索資料分析中獲得詳細資訊。

# 安裝 gapminder 套件
# install.packages("gapminder")
# 載入 gapminder 套件
library(gapminder)
# 觀察有幾個觀測值、幾個變數
dim(gapminder)
# 觀察前六列
head(gapminder)
1
2
3
4
5
6
7
8
## > # 安裝 gapminder 套件
## > # install.packages("gapminder")
## > # 載入 gapminder 套件
## > library(gapminder)
## > # 觀察有幾個觀測值、幾個變數
## > dim(gapminder)
## [1] 1704    6
## > # 觀察前六列
## > head(gapminder)
## # A tibble: 6 x 6
##   country     continent  year lifeExp      pop gdpPercap
##   <fct>       <fct>     <int>   <dbl>    <int>     <dbl>
## 1 Afghanistan Asia       1952    28.8  8425333      779.
## 2 Afghanistan Asia       1957    30.3  9240934      821.
## 3 Afghanistan Asia       1962    32.0 10267083      853.
## 4 Afghanistan Asia       1967    34.0 11537966      836.
## 5 Afghanistan Asia       1972    36.1 13079460      740.
## 6 Afghanistan Asia       1977    38.4 14880372      786.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

創建表格之前先以 DBI 套件中的 dbListTable() 函數觀察資料庫中是否有資料(由於是空白資料庫,所以得到輸出結果是 character(0)),接著呼叫同屬 DBI 套件的 dbWriteTable() 函數,將 gapminder 這個資料框(tibble 格式)寫入 demo.db 檔案中,再以 dbListTable() 函數觀察資料庫中有哪些表格資料,這時將會看到新創建的 gampinder 表格。

# 安裝 RSQLite, DBI, gapminder 套件
#pkgs <- c("RSQLite", "DBI", "gapminder")
#install.packages(pkgs)
# 載入 DBI, gapminder 套件
library(DBI)
library(gapminder)

# 與 demo.db 連結
user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
#user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
db_path <- paste0(user_desktop, "demo.db")
con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
dbListTables(con)
dbWriteTable(con, name = "gapminder", value = gapminder)
dbListTables(con)
dbDisconnect(con)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
## > # 安裝 RSQLite, DBI, gapminder 套件
## > #pkgs <- c("RSQLite", "DBI", "gapminder")
## > #install.packages(pkgs)
## > # 載入 DBI, gapminder 套件
## > library(DBI)
## > library(gapminder)
## > 
## > # 與 demo.db 連結
## > user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
## > #user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
## > db_path <- paste0(user_desktop, "demo.db")
## > con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
## > dbListTables(con)
## character(0)
## > dbWriteTable(con, name = "gapminder", value = gapminder)
## > dbListTables(con)
## [1] "gapminder"
## > dbDisconnect(con)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

我們亦可以使用符合 SQLite 資料庫格式的 SQL 語法,以 dbExecute() 函數建立表格與輸入觀測值。

# 安裝 RSQLite, DBI 套件
#pkgs <- c("RSQLite", "DBI")
#install.packages(pkgs)
# 載入 DBI 套件
library(DBI)

# 與 demo.db 連結
user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
#user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
db_path <- paste0(user_desktop, "demo.db")
con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
dbListTables(con)
create_statement <- "
CREATE TABLE cities (
 city TEXT NOT NULL,
 country TEXT NOT NULL
);
"
insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan');"
dbExecute(con, statement = create_statement)
dbExecute(con, statement = insert_statement)
dbListTables(con)
dbDisconnect(con)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
## > # 安裝 RSQLite, DBI 套件
## > #pkgs <- c("RSQLite", "DBI")
## > #install.packages(pkgs)
## > # 載入 DBI 套件
## > library(DBI)
## > 
## > # 與 demo.db 連結
## > user_desktop <- "/Users/kuoyaojen/Desktop/" # Mac 使用者更換為自己的使用者名稱
## > #user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
## > db_path <- paste0(user_desktop, "demo.db")
## > con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
## > dbListTables(con)
## character(0)
## > create_statement <- "
## + CREATE TABLE cities (
## +  city TEXT NOT NULL,
## +  country TEXT NOT NULL
## + );
## + "
## > insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan');"
## > dbExecute(con, statement = create_statement)
## [1] 0
## > dbExecute(con, statement = insert_statement)
## [1] 3
## > dbListTables(con)
## [1] "cities"
## > dbDisconnect(con)
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

讀取資料庫中的表格資料(R)

讀取表格之前可以使用 DBI 套件中的 dbListFields() 函數觀察資料庫表格的變數名稱,呼叫 dbReadTable() 函數,將獲得與執行 SQL 查詢指令 SELECT * FROM cities; 相同的輸出(整個表格內容);若呼叫 dbGetQuery() 函數則可以對資料庫表格傳入 SQL 查詢指令。

# 安裝 RSQLite, DBI, gapminder 套件
#pkgs <- c("RSQLite", "DBI", "gapminder")
#install.packages(pkgs)
# 載入 DBI, gapminder 套件
library(DBI)
library(gapminder)

# 與 demo.db 連結
user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
#user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
db_path <- paste0(user_desktop, "demo.db")
con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
dbListTables(con)
dbWriteTable(con, name = "gapminder", value = gapminder)
create_statement <- "
CREATE TABLE cities (
 city TEXT NOT NULL,
 country TEXT NOT NULL
);
"
insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan');"
dbExecute(con, statement = create_statement)
dbExecute(con, statement = insert_statement)
dbListTables(con)
# dbListFields()
dbListFields(con, name = "cities")
dbListFields(con, name = "gapminder")
# dbReadTable()
dbReadTable(con, "cities")
# dbGetQuery()
dbGetQuery(con, statement = "SELECT * FROM gapminder WHERE country = 'Taiwan';")
dbDisconnect(con)
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
## > # 安裝 RSQLite, DBI, gapminder 套件
## > #pkgs <- c("RSQLite", "DBI", "gapminder")
## > #install.packages(pkgs)
## > library(DBI)
## > library(gapminder)
## > 
## > # 與 demo.db 連結
## > user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
## > #user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
## > db_path <- paste0(user_desktop, "demo.db")
## > con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
## > dbListTables(con)
## character(0)
## > dbWriteTable(con, name = "gapminder", value = gapminder)
## > create_statement <- "
## + CREATE TABLE cities (
## +  city TEXT NOT NULL,
## +  country TEXT NOT NULL
## + );
## + "
## > insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan');"
## > dbExecute(con, statement = create_statement)
## [1] 0
## > dbExecute(con, statement = insert_statement)
## [1] 3
## > dbListTables(con)
## [1] "cities"    "gapminder"
## > # dbListFields()
## > dbListFields(con, name = "cities")
## [1] "city"    "country"
## > dbListFields(con, name = "gapminder")
## [1] "country"   "continent" "year"      "lifeExp"   "pop"       "gdpPercap"
## > # dbReadTable()
## > dbReadTable(con, "cities")
##     city       country
## 1 Taipei        Taiwan
## 2 Boston United States
## 3  Tokyo         Japan
## > # dbGetQuery()
## > dbGetQuery(con, statement = "SELECT * FROM gapminder WHERE country = 'Taiwan';")
##    country continent year lifeExp      pop gdpPercap
## 1   Taiwan      Asia 1952   58.50  8550362  1206.948
## 2   Taiwan      Asia 1957   62.40 10164215  1507.861
## 3   Taiwan      Asia 1962   65.20 11918938  1822.879
## 4   Taiwan      Asia 1967   67.50 13648692  2643.859
## 5   Taiwan      Asia 1972   69.39 15226039  4062.524
## 6   Taiwan      Asia 1977   70.59 16785196  5596.520
## 7   Taiwan      Asia 1982   72.16 18501390  7426.355
## 8   Taiwan      Asia 1987   73.40 19757799 11054.562
## 9   Taiwan      Asia 1992   74.26 20686918 15215.658
## 10  Taiwan      Asia 1997   75.25 21628605 20206.821
## 11  Taiwan      Asia 2002   76.99 22454239 23235.423
## 12  Taiwan      Asia 2007   78.40 23174294 28718.277
## > dbDisconnect(con)
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

更新資料庫中的表格資料(U)

使用符合 SQLite 資料庫格式的 SQL 語法,以 dbExecute() 函數新增或修改觀測值,與創建表格的範例相似,我們在 cities 表格中新增台北、波士頓與東京的觀測值,不過加入了修改觀測值的操縱將波士頓修改為紐約。

# 安裝 RSQLite, DBI, gapminder 套件
#pkgs <- c("RSQLite", "DBI", "gapminder")
#install.packages(pkgs)
# 載入 DBI, gapminder 套件
library(DBI)
library(gapminder)

# 與 demo.db 連結
user_desktop <- "/Users/USERNAME/Desktop/" # Mac 使用者更換為自己的使用者名稱
#user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
db_path <- paste0(user_desktop, "demo.db")
con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
dbListTables(con)
dbWriteTable(con, name = "gapminder", value = gapminder)
create_statement <- "
CREATE TABLE cities (
 city TEXT NOT NULL,
 country TEXT NOT NULL
);
"
insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan');"
update_statement <- "UPDATE cities SET city = 'New York' WHERE city = 'Boston';"
# dbExecute(): create and insert
dbExecute(con, statement = create_statement)
dbExecute(con, statement = insert_statement)
dbListTables(con)
# dbReadTable()
dbReadTable(con, name = "cities")
# dbExecute(): update
dbExecute(con, statement = update_statement)
# dbReadTable()
dbReadTable(con, name = "cities")
dbDisconnect(con)
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
## > # 安裝 RSQLite, DBI, gapminder 套件
## > #pkgs <- c("RSQLite", "DBI", "gapminder")
## > #install.packages(pkgs)
## > # 載入 DBI, gapminder 套件
## > library(DBI)
## > library(gapminder)
## > 
## > # 與 demo.db 連結
## > user_desktop <- "/Users/kuoyaojen/Desktop/" # Mac 使用者更換為自己的使用者名稱
## > #user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
## > db_path <- paste0(user_desktop, "demo.db")
## > con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
## > dbListTables(con)
## character(0)
## > dbWriteTable(con, name = "gapminder", value = gapminder)
## > create_statement <- "
## + CREATE TABLE cities (
## +  city TEXT NOT NULL,
## +  country TEXT NOT NULL
## + );
## + "
## > insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan');"
## > update_statement <- "UPDATE cities SET city = 'New York' WHERE city = 'Boston';"
## > # dbExecute(): create and insert
## > dbExecute(con, statement = create_statement)
## [1] 0
## > dbExecute(con, statement = insert_statement)
## [1] 3
## > dbListTables(con)
## [1] "cities"    "gapminder"
## > # dbReadTable()
## > dbReadTable(con, name = "cities")
##     city       country
## 1 Taipei        Taiwan
## 2 Boston United States
## 3  Tokyo         Japan
## > # dbExecute(): update
## > dbExecute(con, statement = update_statement)
## [1] 1
## > # dbReadTable()
## > dbReadTable(con, name = "cities")
##       city       country
## 1   Taipei        Taiwan
## 2 New York United States
## 3    Tokyo         Japan
## > dbDisconnect(con)
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

刪除資料庫中的表格資料(D)

使用符合 SQLite 資料庫格式的 SQL 語法,以 dbExecute() 函數刪除觀測值,與創建表格的範例相似,我們在 cities 表格中新增台北、波士頓、東京與倫敦的觀測值,不過加入了刪除觀測值的操縱將倫敦刪去;亦可以使用 dbRemoveTable() 函數獲得與執行 SQL 查詢指令 DROP TABLE cities; 相同的效果,將指定表格 cities 從資料庫中刪除。

# 安裝 RSQLite, DBI, gapminder 套件
#pkgs <- c("RSQLite", "DBI", "gapminder")
#install.packages(pkgs)
# 載入 DBI, gapminder 套件
library(DBI)
library(gapminder)

# 與 demo.db 連結
user_desktop <- "/Users/kuoyaojen/Desktop/" # Mac 使用者更換為自己的使用者名稱
#user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
db_path <- paste0(user_desktop, "demo.db")
con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
dbListTables(con)
dbWriteTable(con, name = "gapminder", value = gapminder)
create_statement <- "
CREATE TABLE cities (
 city TEXT NOT NULL,
 country TEXT NOT NULL
);
"
insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan'), ('London', 'United Kingdom');"
delete_statement <- "DELETE FROM cities WHERE city = 'London';"
# dbExecute(): create and insert
dbExecute(con, statement = create_statement)
dbExecute(con, statement = insert_statement)
dbListTables(con)
# dbReadTable(): before deletion
dbReadTable(con, name = "cities")
# dbExecute(): delete row
dbExecute(con, statement = delete_statement)
# dbReadTable(): after deletion
dbReadTable(con, name = "cities")
# dbRemoveTable()
dbRemoveTable(con, name = "cities")
dbListTables(con)
dbDisconnect(con)
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
## > # 安裝 RSQLite, DBI, gapminder 套件
## > #pkgs <- c("RSQLite", "DBI", "gapminder")
## > #install.packages(pkgs)
## > # 載入 DBI, gapminder 套件
## > library(DBI)
## > library(gapminder)
## > 
## > # 與 demo.db 連結
## > user_desktop <- "/Users/kuoyaojen/Desktop/" # Mac 使用者更換為自己的使用者名稱
## > #user_desktop <- "C:/Users/USERNAME/Desktop/" # Windows 使用者更換為自己的使用者名稱
## > db_path <- paste0(user_desktop, "demo.db")
## > con <- dbConnect(RSQLite::SQLite(), dbname = db_path)
## > dbListTables(con)
## character(0)
## > dbWriteTable(con, name = "gapminder", value = gapminder)
## > create_statement <- "
## + CREATE TABLE cities (
## +  city TEXT NOT NULL,
## +  country TEXT NOT NULL
## + );
## + "
## > insert_statement <- "INSERT INTO cities (city, country) VALUES ('Taipei', 'Taiwan'), ('Boston', 'United States'), ('Tokyo', 'Japan'), ('London', 'United Kingdom');"
## > delete_statement <- "DELETE FROM cities WHERE city = 'London';"
## > # dbExecute(): create and insert
## > dbExecute(con, statement = create_statement)
## [1] 0
## > dbExecute(con, statement = insert_statement)
## [1] 4
## > dbListTables(con)
## [1] "cities"    "gapminder"
## > # dbReadTable(): before deletion
## > dbReadTable(con, name = "cities")
##     city        country
## 1 Taipei         Taiwan
## 2 Boston  United States
## 3  Tokyo          Japan
## 4 London United Kingdom
## > # dbExecute(): delete row
## > dbExecute(con, statement = delete_statement)
## [1] 1
## > # dbReadTable(): after deletion
## > dbReadTable(con, name = "cities")
##     city       country
## 1 Taipei        Taiwan
## 2 Boston United States
## 3  Tokyo         Japan
## > # dbRemoveTable()
## > dbRemoveTable(con, name = "cities")
## > dbListTables(con)
## [1] "gapminder"
## > dbDisconnect(con)
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

小結

在這個小節中我們簡介如何以 R 語言透過 DBIRSQLite 兩個套件連結本機端的 SQLite 資料庫,並利用 DBI 套件所提供的函數實踐四種常見資料庫表格操作,即所謂的 CRUD:Create、Read、Update 與 Delete。

延伸閱讀