RODBC：ODBC Database Access

06-24

來自專欄每天一個R包

前言

R語言支持多種數據文件的導入，內置的read.table()函數可以輕鬆從txt，csv這類文本文件中導入數據，但想要直接導入Excel數據，甚至訪問資料庫就要依賴外部包了。
RODBC包，可以允許R和一個通過ODBC連接的SQL資料庫之間進行雙向通信。

工具和環境

語言：R 3.3.3
IDE：RStudio 1.0
資料庫：MySQL 5.7
Excel版本：Excel 2013

ODBC介面的配置

Windows系統：控制面板->系統和安全->管理工具->ODBC數據源->雙擊打開

現在添加MySQL數據源（MySQL下載頁面）

Data Source Name：可任意填寫如"MySQL"
Description：可任意填寫如"MySQL"
TCP/IP Server：伺服器IP，本地資料庫一般是127.0.0.1
User：MySQL用戶名
Password：MySQL密碼
Database：選擇1個資料庫

右側的Test鍵可供測試，設置正確的話會提示成功連接

RODBC包的使用說明

# 安裝並載入RODBC包install.packages("RODBC")library(RODBC)# 建立ODBC資料庫連接channel <- odbcConnect("mydsn", uid="username", pwd="password")# 查看資料庫中的表sqlTables(channel)# 讀取數據表並返回一個數據框sqlFetch(channel, sqtable)# 向資料庫提交一個查詢，並返回結果sqlQuery(channel, query)# 將一個數據框寫入或更新(append=True)到資料庫 sqlSave(channel, mydf, tablename = sqtable, append = FALSE)# 返回資料庫表sqtable列的信息sqlColumns(channel, sqtable) # 從資料庫刪除一個表 sqlDrop(channel, sqtable)# 刪除表中的內容sqlClear(channel, sqtable)# 關閉連接 close(channel)

Excel文件

讀取sample.xlsx文件，分別使用sqlFetch()和sqlQuery()函數

> library(RODBC)>> # 確保所在路徑中有文件sample.xlsx> getwd()[1] "C:/Users/Ryan/Documents"> channel <- odbcConnectExcel2007("sample.xlsx")>> # 查看Sheets> sqlTables(channel)[,1][1] "C:\Users\Ryan\Documents\sample.xlsx">> # 讀取Sheet1$的數據> sqlFetch(channel, "Sheet1$") ID OrderNum Price1 1 10 1002 2 20 1503 3 15 2004 4 30 1705 5 25 80>> # 用SQL語句查詢> sqlQuery(channel, "SELECT ID,OrderNum FROM [Sheet1$]") ID OrderNum1 1 102 2 203 3 154 4 305 5 25>> # 關閉連接 > close(channel)

訪問MySQL

將鳶尾花數據集寫入MySQL資料庫，再清空數據並刪除數據表恢復原樣

> library(RODBC)>> # 建立MySQL資料庫連接> channel <- odbcConnect("MySQL", uid="root", pwd="****")>> # 查看資料庫中的表> sqlTables(channel)[1] TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS <0 行> (或0-長度的row.names)>> # 將鳶尾花數據集的前10行觀測寫入新表iris> sqlSave(channel, iris[c(1:10),], "iris", append = FALSE)>> # 將鳶尾花數據集的11-20行觀測更新入表iris> sqlSave(channel, iris[c(11:20),], "iris", append = TRUE)>> # 查看資料庫中的表> sqlTables(channel) TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS1 mydb iris TABLE >> # 返回表iris列的信息> sqlColumns(channel, "iris")[,(3:4)] TABLE_NAME COLUMN_NAME1 iris rownames2 iris sepallength3 iris sepalwidth4 iris petallength5 iris petalwidth6 iris species>> # 從SQL中執行查詢> sqlQuery(channel, "SELECT * FROM iris") rownames sepallength sepalwidth petallength petalwidth species1 1 5.1 3.5 1.4 0.2 setosa2 2 4.9 3.0 1.4 0.2 setosa3 3 4.7 3.2 1.3 0.2 setosa4 4 4.6 3.1 1.5 0.2 setosa5 5 5.0 3.6 1.4 0.2 setosa6 6 5.4 3.9 1.7 0.4 setosa7 7 4.6 3.4 1.4 0.3 setosa8 8 5.0 3.4 1.5 0.2 setosa9 9 4.4 2.9 1.4 0.2 setosa10 10 4.9 3.1 1.5 0.1 setosa11 11 5.4 3.7 1.5 0.2 setosa12 12 4.8 3.4 1.6 0.2 setosa13 13 4.8 3.0 1.4 0.1 setosa14 14 4.3 3.0 1.1 0.1 setosa15 15 5.8 4.0 1.2 0.2 setosa16 16 5.7 4.4 1.5 0.4 setosa17 17 5.4 3.9 1.3 0.4 setosa18 18 5.1 3.5 1.4 0.3 setosa19 19 5.7 3.8 1.7 0.3 setosa20 20 5.1 3.8 1.5 0.3 setosa

> # 刪除表iris中的內容> sqlClear(channel, "iris")>> # 讀取表iris的數據> sqlFetch(channel, "iris")[1] sepallength sepalwidth petallength petalwidth species <0 行> (或0-長度的row.names)

> # 從資料庫刪除表iris> sqlDrop(channel, "iris")> # 查看資料庫中的表> sqlTables(channel)[1] TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS <0 行> (或0-長度的row.names)> # 關閉連接 > close(channel)

備註

對於Excel文件更推薦另存成csv格式導入，

若要直接導入，除了RODBC包之外，還可以使用xlsx包或者XLConnect包