【R】 用R連接、讀取及寫入MySQL資料庫

最近因緣際會下開始需要抓取資料庫的資料，有鑑於此我在自己的Windows電腦安裝了MySQL，安裝方法可以參考下面這篇 ⟹Windows上的MySQL安裝教學 ，很棒的是MySQL內建了一組資料，練習起來方便許多～接著我將進行用R串接MySQL、讀取資料、寫入資料等等的過程。

➤準備需要的Package

一開始我們要準備R連接MySQL相關的套件，不過在這裡要特別注意的事，使用R的這台電腦必須要安裝ODBC（Open Database Connectivity，開放資料庫互連）的驅動程式，才能夠順利的連上欲連線的資料庫。以下是R所需要的套件。

install.packages("RODBC")
install.packages("dbConnect")
install.packages("DBI")
install.packages("gWidgets")
install.packages("RMySQL")
install.packages("xlsx")

library(RODBC)
library(dbConnect)
library(DBI)
library(gWidgets)
library(RMySQL)
library(xlsx)


➤連線測試、讀取資料

接著我們用dbConnect()，輸入安裝時設定好的帳號密碼以及相關資訊，應該就可以順利的連上資料庫了，接著我們還可以利用dbListTables() 來檢視這個資料庫裡所包含的資料表，也可以用dbGetQuery() 直接在R輸入SQL語法做查詢。

connect = dbConnect(MySQL(), dbname = "world",username = "root", password = "haha"
,host = "localhost")
dbListTables(connect)
country = dbGetQuery(connect ,"select * from country")


成功將資料讀取!

➤寫入資料
接下來，我們甚至還能用R將資料寫入資料庫裡，這邊我直接在R用data.frame()虛構一個假資料表寫入，基本上也可以讀取現有的excel資料檔然後將其寫入。下面圖可以看出這個虛構的資料表"Survey"已經被我成功入了！如果我想要移除這個資料表，只要用dbRemoveTable() 就可以了。

id = 1:5
name = c("Jack","Lisa","Eric","John","Ivy")
age = c(23,40,45,38,28)
gender = c("M","F","M","M","F")
income = c(80000,65000,38000,45000,55000)
data = data.frame(id,name,age,gender,income)

dbWriteTable(connect,"survey",data)
dbListTables(connect)


成功將資料寫入MySQL！

➤讀取資料、產生報表

最後是一個小小練習，我利用MySQL內建的資料表，練習了讀取資料和產生報表的步驟。

city = dbGetQuery(connect, "select*from city")
countrylanguage = dbGetQuery(connect,"select*from countrylanguage")
merge = merge(x = countrylanguage,y = city, by = "CountryCode", all = F)
setwd("D:/Data analysis/R/Blog/MySQL")
write.xlsx(merge,file="merge.xlsx")


成功產出報表！

➤心得&結論

其實一開始要用R來串接資料庫有幾個原因，第一個是SQL資料庫程式通常不具備產出統計圖表的功能，猶如我上一篇練習的利用經緯度畫出熱區地圖，最基本的方式是先從資料庫下SQL語法抓取資料，另存成Excel之類的資料檔後再用R讀取，不過這樣顯得多了一個步驟了。第二個原因是，因為我在下某些SQL語法的時候發生了一些狀況，在某些運算下是沒有辦法跨資料表，甚至是跨資料庫查詢的，像是我想合併計算MySQL的資料表"A"和MSSQL的資料表"B"就會變得很困難(Maybe可能我是SQL語法的初學者XD)，不過學會了用R串接資料庫真的方便許多，Key好程式碼之後簡單Run一下，從連接資料庫、讀取資料、產生報表一氣呵成，真是太方便啦~