此篇文章主要介紹R鏈接數(shù)據(jù)庫的方法, 先以MySQL為基準, 其余數(shù)據(jù)庫的鏈接方式會持續(xù)更新:

主流的數(shù)據(jù)庫: MySQL, 等等.

筆主安裝環(huán)境

• 系統(tǒng)版本

macOS Mojave Version 10.14.2

• R版本

R version 3.5.2 (2018-12-20) -- "Eggshell Igloo"
Copyright (C) 2018 The R Foundation for Statistical Computing
Platform: x86_64-apple-darwin18.2.0 (64-bit)

MySQL

安裝MySQL

brew install mysql
mysql.server start 

若遇到問題, 請點擊打開: MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 和客戶端不能鏈接

CREATE DATABASE Curtis;
CREATE user 'Curtis'@'localhost' IDENTIFIED BY 'Curtis';
# ALTER USER 'Curtis'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Curtis';
GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE,INDEX,REFERENCES 
ON Curtis.* TO 'Curtis'@'localhost';
FLUSH PRIVILEGES;

1. RMySQL

install.packages('RMySQL')
library(RMySQL)
conn <- dbConnect(
  MySQL(), dbname = "Curtis", username = "Curtis", password = "Curtis", 
  host = "localhost", port = 3306
)
# dbWriteTable(conn, "tablename", data) #寫表
# dbReadTable(conn, "tablename")  #讀表
# dbDisconnect(conn) #關閉連接

## 查詢數(shù)據(jù), 可以輸入你想要的sql進行查詢
res <- dbSendQuery(conn, "SHOW DATABASES")
dbFetch(res)
dbClearResult(res)
dbDisconnect(conn) # 用完記得關閉鏈接

2. sqldf

# 這個包依賴有點多, 首次安裝需要一會時間.
install.packages('sqldf')
library(sqldf)
detach("package:RMySQL", unload = TRUE)
sqldf(
  "SHOW DATABASES", dbname = "Curtis", drv = "MySQL", 
  user = "Curtis", password = "Curtis", host = "localhost", port = 3306
)

# 報錯
Attaching package: ‘RMySQL’

The following object is masked from ‘package:RSQLite’:

    isIdCurrent

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Access denied for user 'dugangtao'@'localhost' (using password: NO)
Error in !dbPreExists : invalid argument type