Rstudio Connection Database

I. Using Rstudio to connect MySQL database

We often store a large amount of data in a database such as MySQL, so as to facilitate our data extraction and operation, and many times when we use R for data analysis, we usually want R to directly and MySQL database connection, so that we directly to the large-scale data processing. Of course, the data stored in MySQL is structured.

Some friends will encounter some problems when using R to connect databases such as MySQL, so this article focuses on MySQL as an example, describes how to use R to connect MySQL data source basic steps. The steps are as follows:

1, R download RODBC package, installed.

Install packages ("RODBC"); #安装RODBC包

2. Open the link http://dev.mysql.com/downloads/connector/odbc, download mysql ODBC, install:

3. Control Panel---> Management tools---> Data source (ODBC) (select 32-bit, 64-bit based on PC)---> Click add---> select the mysql ODBC driver option (Unicode and ANSI select any one).

Data source Name (DSN data source name) fill in the name you want to use, for example: Mysql_data;

Description can be filled out, for example MyData

TCP/IP Server native server IP, typically: 127.0.0.1 or localhost

User name of your MySQL username

Password your MySQL password

The database will then appear in your MySQL database, select a database.

4. Open the interface of R to call the database: (I use Rstudio)

(1) library (RODBC);

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

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

(4) Data<-sqlfetch (channel, "Titanicdata"); # Save the contents of table Titanicdata to data frame

5. At this point, R has successfully connected to the MySQL database and obtained data from the Titanicdata table in the Niesheng database in the MySQL library.

6, since the ability to connect to the database, then in R can also use SQL statements for data processing and extraction.

Example 1: Storing age-not-empty data in a data1 data set.

Data1 <-sqlquery (channel, "Selectsurvived, Age, class, sex from titanicdata where age<> ' null ';

Example 2: The survival of the survived, class class, gender sex are converted to 1, 0 such values, easy to analyze later.

Titanic <-sqlquery (channel, "select" If t.survived= ' yes ' then 1 ELSE 0 END) survived, age, [case] t.class= ' 1 St ' then 1 if t.class= ' 2st ' then 2 else 3 end) class,

(Case if t.sex= ' male ' then 1 else 0 end) Sex

From Titanicdata t where age<> ' null ' ORDER by survived,age; ");

7, R can not only connect MySQL, but also can connect other data sources, including access, Excel, SQL Server database and so on. Specifically, you can refer to the specific use of Odbcconnect in R.

Second, install RODBC Library
1. Enter the R language GUI interface (RGUI.EXE) and select "Package/Installer Package" from the menu bar
2, pull down in the pop-up window, select Rodbc, click OK
3, in the ODBC data Source Manager will need to add the database, here I use SQL Server2008, driver selection native Client10.0

4. Enter the connection statement in the R language window

> Library (RODBC)

* * Here is loading RODBC library

> Channel<-odbcconnect ("MyTest", Uid= "Ripley", Case= "ToLower")

* * Connect the "MyTest" database that you just added into the data source

**ch <-odbcconnect ("Some DSN", uid = "user", pwd = "* * * *")

* * Indicates the username is user, password is * * *, if not set, you can directly ignore

> Data (usarrests)

* * the "usarrests" table is written into the database (this table is brought by R)

> sqlsave (channel,usarrests,rownames = "state", ADDPK = TRUE)

* * Save the data stream, then open SQL Server to see the new usarrests table

> rm (usarrests)

> SqlTables (Channel)

* * give the table in the database

> SqlFetch (Channel, "usarrests", Rownames = "state")

* * Content in output usarrests table

> SQLQuery (Channel, "SELECT * from Usarrests")

* * Call the SELECT query statement and return the result (figure)

> Sqldrop (Channel, "usarrests")

* * Delete Table

> Odbcclose (Channel)

* * Finally remember to close the connection of course, this way can also read the Excel, Access table content, the exact method is similar, here no longer repeat