星期二, 四月 02, 2013

R连接MySQL数据库方法备忘


R语言连接数据库可以利用数据库的存贮能力和R的计算能力,起到取长补短的效果。之前我们也说过了如何在R中使用SQL,很多教材上也提到了连接MySQL的方法,但是在安装上还需要注意一些细节问题。以下的解决方法也是在网上放狗加撞墙实验得出的结论。


一、win平台
如果是在windows平台上安装,推荐采用ODBC连接方式。除了需要安装R和MySQL之外,还要安装RODBC扩展包,以及MySQL的ODBC驱动。下面是具体步骤:

1、R下载RODBC包,安装好。
2、在http://dev.mysql.com/downloads/connector/odbc
下载mySQL ODBC,安装好。
3、windows:控制面板->管理工具->数据源(ODBC)->双击->添加->选中mysql ODBC driver一项
填写:data source name 一项填入你要使用的名字,自己随便命名,例如:mysql_data;
description一项随意填写,例如mydata
TCP/IP Server 填写服务器IP,本地数据库一般为:127.0.0.1
user 填写你的mysql用户名
password 填写你的mysql密码
然后数据库里会出现你的mysql里的所有数据库,选择一个数据库。
确定。
4、打开R调用数据库:
library(RODBC)
channel <- odbcConnect("mysql_data", uid="abc", pwd="abc")
sqlTables(channel)  #查看数据中的表
data<-sqlFetch(channel,"abc")  # 查看表的内容,存到数据框里

二、linux平台
在linux平台下使用ODBC连接安装比较麻烦,推荐采用JDBC连接方式。具体安装方式如下:

在软件源中增加R官方源,方法具体见官网说明;之后在term中运行发下命令,安装需要的软件:
sudo apt-get update
sudo apt-get install r-base
sudo apt-get install r-dev
sudo apt-get install r-cran-rmysql
sudo apt-get install r-cran-dbi
sudo apt-get install mysql-server my-client
sudo apt-get install libmysqlclient-dev

在R中加载RMySQL两个包,并使用dbConnect函数设置好用户名和密码
library(RMySQL)
con <- dbConnect(MySQL(),
                 user='abc', # 用户名
                 password='abc', # 密码
                 dbname='abc', # 要使用的数据库名称
                 host="localhost") # 主机地址
dbListTables(con) # 列出所有数据库中的表
# 运行sql语句并返回数据到R 
data <- dbGetQuery(con, "select * from abc")
SQL查询可以通过dbSendQuery或dbGetQuery传给数据库管理系统。dbGetQuery传送查询语句,把结果以数据框形式返回。dbSendQuery传送查询,返回的结果是继承"DBIResult"的一个子类的对象。"DBIResult"类可用于取得结果,而且还可以通过调用dbClearResult清除结果。函数fetch用于获得查询结果的部分或全部行,并以列表返回。函数dbHasCompleted确定是否所有行已经获得了,而dbGetRowCount返回结果中行的数目。如果只是简单的读整个表,也可以用dbReadTable函数。


在设置连接时会在R语言环境中明文标示了数据库用户和密码,为了安全起见,可以作如下设置:
在本机目录中定义一个配置文档( ~/.my/cnf),例如定义两个使用MySQL的用户组:
[local]
user = root
password = ultra_secret
host = localhost

[toto]
user = capitaine_flam
password = galaxy
host = milky.way.net

然后在R中使用用户组:

library(RMySQL)
con <- dbConnect(MySQL(), group='toto', dbname="user_profile")
m <- dbGetQuery(con, "SELECT DISTINCT user_id FROM demographics WHERE gender='0'")
dbDisconnect(con)
另外R连接mysql会出现中文乱码变问号情况,查到COS上一个贴子,解决方案如下:
在MySQL的配置文件/etc/mysql/my.cnf中[client]标签下加default-character-set=utf8




5 条评论:

  1. 今天在windows安装使用了mysql成功,多谢博主。

    回复删除
  2. 感谢楼主!昨天在dataguru论坛上看到了一个帖子,跟您的一样,还以为那位是原创呢。。。哎,今天来感谢真正的原创!

    回复删除
  3. Thank you very much for this

    回复删除
  4. 您好,可以请教下中文文本从r输出到mysql(dbSendQuery)出现乱码,其中文本长度产不多30~50个字,例如:
    "货物进出口、技术进出口(法律法规禁止的项目除外,国家法律、法规限制的项目取得许可证后方可经营);批发、零售:普通机械,汽车(除小轿车),汽车配件,电子产品(除专控),工程机械及配件;服务:工程机械租赁(除拆装);其他无需报经审批的一切合法项目***"

    回复删除