WINDOWS7-32-bit System R language link MySQL database steps

Install R and MySQL is no longer said, there are many online tutorials can be found. The following goes directly to the ODBC installation process.

1. Download and install Mysql-connector-odbc-5.x.x-win32.msi

: http://dev.mysql.com/downloads/connector/odbc/can download the latest version

However, the following issues were encountered during the installation ( the problem will not occur if you install Mysql-connector-odbc-5.3.6-winx64.msi under the tested win10-64 bit version ):

Error 1918. Error INSTALLINGODBC driver MySQL ODBC 5.3 ANSI DRIVER,ODBC error 13: Unable to load MySQL ODBC 5.3 ANSI driver ODBC driver installation routines because of a system error Error code 126: The specified module could not be found. ... \myodbc5s.dll).. Verify that the file MySQL ODB ...

After searching the Internet, we find that the following solutions have not been solved, which are listed in the hope that some people can experiment successfully:

(1) Install the following Visual C + + 2010

(2) Direct download Myodbc5S.dll placed in the appropriate location

Finally, the discovery of Mysql-connector-odbc-5.1.5-win32.msi is available. So after downloading the direct installation is successful.

2, Configuration Mysql-odbc

Click on "Control Panel-system and security-administrative Tools-System dns-Add",

Select "MySQL ODBC 5.1 Driver" and click "Finish".

Fill in the link parameters and click on "Test" test.

If prompted connection successful, the installation is successful.

3, R language installation Load "RODBC" package

Open R software and enter the following code:

> install.packages ("RODBC") > Library (RODBC)

4. Use the R language to establish links with MySQL

Enter the following code:

> Channel=odbcconnect ("Test", uid= "root", pwd= "11221122")

Where "test" is the "Data Source Name" in the MYSQL-ODBC configuration, the UID is set to "User" in the MYSQL-ODBC configuration, and the PWD is Mysql-odbc "Password" in the configuration.

5, R language and MySQL link operation example (1) Query the data table under the database

Enter the following code:

> SqlTables (Channel)

(2) Querying data in a table and returning it to the current data frame

Enter the following code:

> People=sqlfetch (Channel, "People") > People

(3) Conditional query and return to current data frame

Enter the following code:

> SQLQuery (Channel, "Selectname,birthday from people where age < ORDER BY id DESC")

(4) Save the data in the current data frame to the MySQL database

Enter the following code:

> Mtcars> sqlsave (Channel, mtcars, rownames = "Brand", ADDPK = TRUE)

Where Rownames is because the first column has no variable name, so this is set to brand.

Enter the MySQL database and enter the following code in turn:

Mysql> Show databases;mysql> use myfirstdbmysql> show tables;mysql> select * from Mtcars;

The results appear as follows:

As you can see, the data in the data frame has been saved to MySQL.

(5) The R software removes the table that you just added to the MySQL database

Enter the following code:

> Sqldrop (Channel, "Mtcars")

The query results in the database again are:

That is, delete success!

(6) Close the link between R software and MySQL

Enter the following code:

> Odbcclose (Channel)

WINDOWS7-32-bit System R language link MySQL database steps