R链接数据库

此篇文章主要介绍R链接数据库的方法, 先以MySQL为基准, 其余数据库的链接方式会持续更新:

主流的数据库: MySQL, 等等.

笔主安装环境

• 系统版本

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) #关闭连接

## 查询数据, 可以输入你想要的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