R language-Connect MySQL database method

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Directory (?) [+]

There are 2 common methods used in the R language connection database:

1. Using the R Database interface

Connect MySQL, use the Rmysql package, use the front Rmysql package to install first.

Library (Rmysql)

There are 2 ways to connect:

(1) Using Dbconnect
Conn <-Dbconnect (MySQL (), dbname = "Rmysql", username= "Rmysql", password= "Rmysql", host= "127.0.0.1", port=3306)

Data manipulation methods:

Dbwritetable (conn, "TableName", data) #写表
Dbreadtable (conn, "TableName") #读表
Dbdisconnect (conn) #关闭连接

(2) using SQLDF package

To install the SQLDF package first, call it next:

>library (SQLDF)

> Sqldf ("SELECT * from T_data", dbname= "test", drv= "MySQL", user= "root", password= "", host= "127.0.0.1", port=3306)

After the connection, the parameters behind the SQL query statement can be omitted, for example:

> Sqldf ("select * from T_data limit 0,10")

2. Connect using ODBC

To configure ODBC (Open Database Connectivity) under Windows, follow these steps:
(1) r Download the RODBC package and install it properly.
(2) Download MySQL ODBC in http://dev.mysql.com/downloads/connector/odbc, install it well.
(3) Windows: Control Panel, management tools, data Source (ODBC), double-click Add-----Select MYSQLODBC driver
Fill in: Data source name an entry you want to use the name, you name it, for example: Mysql_data;
Description A random fill, such as MyData
TCP/IP server fills in the native server IP, generally: 127.0.0.1
User fills in your MySQL username
Password fill in your MySQL password
The database will then appear in your MySQL database, select a database.
Are you sure.
(4) Open the interface of R to call the database:

Library (RODBC);

Channel <-odbcconnect ("Mysql_data", uid= "root", pwd= "123");

SqlTables (channel); #查看数据中的表

Data<-sqlfetch (channel, "Kegg") # View the contents of the table and save it in the data frame

Reference article:

1, R language connection MySQL database steps and simple to use the data in the MySQL database (study notes). http://www.dataguru.cn/thread-289411-1-1.html

2, R language connection MySQL database step. Http://blog.sina.com.cn/s/blog_ab3fbf1b0101komj.html

3, R connect MySQL database method memo. http://www.r-bloggers.com/lang/chinese/1247

R language-Connect MySQL database method