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

輕鬆學習 R 語言:向資料庫查詢以 R 語言連結 SQLite 資料庫Yao-Jen KuoBlockedUnblockFollowFollowingApr 19Photo by Jacob Miller on UnsplashThe world’s most valuable resource is no longer oil, but data.

The Economist獲取資料(Getting Data)在資料科學專案中扮演攻擊發起點,如果這個專案目的是協助我們制定資料驅動的策略(data-driven strategy),而非傳統倚賴直覺的「根據經驗」策略,那麼為專案細心盤點資料來源與整理獲取方法,將可以為決策奠基穩固的基礎。常見的資料來源可以分為三種:檔案資料庫網頁資料擷取在輕鬆學習 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 關聯式資料庫連接的主要套件是 DBI 與 RSQLite, DBI 全名為 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 語言透過 DBI 與 RSQLite 兩個套件連結本機端的 SQLite 資料庫,並利用 DBI 套件所提供的函數實踐四種常見資料庫表格操作,即所謂的 CRUD:Create、Read、Update 與 Delete。延伸閱讀About SQLiteWe the developers hope that you find SQLite useful and we entreat you to use it well: to make good and beautiful…www.

sqlite.

orgDatabases using RDBI separates the connectivity to the DBMS into a "front-end" and a "back-end".

Applications use only the exposed…db.

rstudio.

comImporting Data Into R – Data Importation CourseLearn how to parse data in any format.

Whether it's flat files, statistical software, databases, or data right from the…www.

datacamp.

com.. More details

Leave a Reply