R语言连接数据库

最近使用Rstudio Server从数据库获取数据次数越来越多,期间也涉及到了Mysql、SQL server数据库,并且在安装过程中也出现了很多的问题,这里做一个记录和总结,本文为笔记,并不是相关的教程,请见谅。

Mysql/MariaDB

连接mysql和MariaDB数据库可以使用的包有两个,分别为"RMySQL"和"RMariaDB",因为MariaDB数据库是完全兼容mysql,所以这两个包是通用mysql和MariaDB数据库。

而目前通过"RMySQL"包的Github作者介绍,该包将逐渐被"RMariaDB"包被淘汰,所以在安装的时候尽量使用"RMariaDB"来连接mysql。

使用下面的命令来安装这两个包:

install.packages('RMariaDB')

install.packages('RMySQL')

如果安装过程中报错提示如下:

-----------------------------[ ANTICONF ]-----------------------------
Configure could not find suitable mysql/mariadb client library. Try installing:
 * deb: libmariadbclient-dev | libmariadb-client-lgpl-dev (Debian, Ubuntu)
 * rpm: mariadb-connector-c-devel | mariadb-devel | mysql-devel (Fedora, CentOS, RHEL)
 * csw: mysql56_dev (Solaris)
 * brew: mariadb-connector-c (OSX)
If you already have a mysql client library installed, verify that either
mariadb_config or mysql_config is on your PATH. If these are unavailable
you can also set INCLUDE_DIR and LIB_DIR manually via:
R CMD INSTALL --configure-vars='INCLUDE_DIR=... LIB_DIR=...'
--------------------------[ ERROR MESSAGE ]----------------------------
<stdin>:1:10: fatal error: mysql.h: No such file or directory
compilation terminated.
-----------------------------------------------------------------------

则可以根据提示安装相应系统的包

apt install libmariadbclient-dev

然后使用下列命令建立连接

library("RMariaDB")
myConn = dbConnect(MariaDB(), dbname = 'testdb', host = '127.0.0.1',
                            user = 'root', password = 'password')

建立连接后就可以使用该包提供的相关函数

dbListTables(myConn)
# output: 
[3] "Accounts"                "mainuser"                "results_Accounts"     

Sql server

Sql Server我在Linux和Windows上都使用过,但是配置的方式都不太一样。

windows

首先安装RODBC:

install.packages('RODBC')

然后打开Windows的控制面板,然后依次点击进入"系统和安全">"管理工具">"ODBC 数据源(* 位)",然后你会看见如下的界面:

然后点击添加选择"SQL Server"驱动

然后输入名称和SQL Server服务器地址,描述可以不用输入,这里的名称会等下在R中使用

下一步选择输入你得到的数据库账户和密码

下一步可以根据自身选择是否选择默认的数据库

下一步过后,就可以直接点击完成,完成的时候可以选择测试数据源,可以验证账户密码以及连接情况。

配置完ODBC数据源后,就可以在R中开始连接了,其中odbcConnect函数第一个参数就是添加的数据源名称,我上面建立的时候填写的是testDB

library(RODBC)

myConn <- odbcConnect('testDB', uid='test', pwd='test') 

sqlQuery(myConn, 'select * from test')

Linux/Ubuntu

Linux需要手动安装sql server的驱动,然后搭配"odbc"包,这里还有其他的方式,但是个人感觉"odbc"包比较简单。

首先还是先在R中安装’odbc’包

install.packages('odbc')

如果出现了以下的报错信息

------------------------- ANTICONF ERROR ---------------------------
Configuration failed because odbc was not found. Try installing:
 * deb: unixodbc-dev (Debian, Ubuntu, etc)
 * rpm: unixODBC-devel (Fedora, CentOS, RHEL)
 * csw: unixodbc_dev (Solaris)
 * brew: unixodbc (Mac OSX)
To use a custom odbc set INCLUDE_DIR and LIB_DIR and PKG_LIBS manually via:
R CMD INSTALL --configure-vars='INCLUDE_DIR=... LIB_DIR=... PKG_LIBS=...'
--------------------------------------------------------------------

则安装提示信息安装相应的系统包

apt install unixodbc-dev

安装完成后,使用odbcListDrivers()查看是否具备SQL server驱动

odbcListDrivers()

如果输出其中没有SQL Server等字符,这个时候就需要安装SQL server驱动,安装的过程可以按照下面的代码依次在系统中执行

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 20.04
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

按照上面的代码执行后,再次查看驱动,正常情况下将会看到相应的SQL Server驱动

unique(odbcListDrivers()[[1]])

# [1] "ODBC Driver 17 for SQL Server"

这个时候就可以在R当中连接SQL server数据库,需要注意在dbConnect函数Driver参数需要填入驱动的全称,而在密码那一项可以调用rstudio的apirstudioapi::askForPassword,在执行的时候弹出输出框输入密码,而减少在代码中暴露密码的风险

library(odbc)

myConn <- dbConnect(odbc(),
                 Driver = "ODBC Driver 17 for SQL Server",
                 Server = "127.0.0.1",
                 Database = "mydbname",
                 UID = "test",
                 PWD = rstudioapi::askForPassword("Database password"),
                 Port = 1433)

dbListTables(myConn)

参考文档

  1. R语言连接数据库(MySQL)
  2. RMySQL GitHub
  3. RMariaDB Github
  4. win10操作系统的ODBC数据源配置sqlserver数据库
  5. Connect to a Database
  6. Install the Microsoft ODBC driver for SQL Server (Linux)

Leave a Reply

Your email address will not be published. Required fields are marked *