輕鬆學習 R 語言:向資料庫查詢

以 R 語言連結 SQLite 資料庫

郭耀仁 Yao-Jen Kuo
Apr 19 · 22 min read

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

The Economist

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

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

輕鬆學習 R 語言:資料輸入與輸出我們討論了如何透過 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 關聯式資料庫連接的主要套件是 DBIRSQLiteDBI 全名為 Database Interface,兩者各別負責與 SQLite 的前端與後端連結,其中 DBI 專注前端公開 API, RSQLite 則著手後端通信的驅動程式(driver)。

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

## > # 安裝 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

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

透過圖形化介面安裝

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

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

或是透過圖形化介面(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)

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

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

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

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

## > # 安裝 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.

創建表格之前先以 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)
## character(0)
## > dbWriteTable(con, name = "gapminder", value = gapminder)
## > dbListTables(con)
## [1] "gapminder"
## > dbDisconnect(con)

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

## > # 安裝 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)

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

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

## > # 安裝 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)

更新資料庫中的表格資料(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/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)

刪除資料庫中的表格資料(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)
## 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)

小結

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


DataInPoint

DataInPoint 是一個超棒的資料科學專欄,主題涵蓋資料、程式、機器學習與高效能運算。

郭耀仁 Yao-Jen Kuo

Written by

Could that data be any tidier? It is always nice to meet a data enthusiast / 2:43 marathon runner.

DataInPoint

DataInPoint 是一個超棒的資料科學專欄,主題涵蓋資料、程式、機器學習與高效能運算。

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade