Tag Archives: r connect sql server

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')

Continue reading