R Connect MySQL Database method

Warning messages: Inodbcdriverconnect ("Dsn=rdata; Uid=root "):  [RODBC] error:state IM002, code 0, message [MICROSOFT][ODBC Driver Manager] No data source name found and no default driver specified

The above error occurs when the R language is fetching data from the database because of an error caused by not configuring an ODBC data source, the following describes the solution to the problem, and describes how R connects to the MySQL database
I. Configuring an ODBC data source
1. Download mysql ODBC "http://dev.mysql.com/downloads/connector/odbc/", if you feel registered trouble can click on My network link "link:/http Pan.baidu.com/s/1c0pwyhe Password: g8ha Note: This package applies to Windows 64-bit  "
2. After installing MySQL ODBC Add, first open the Control Panel-management tool-ODBC data source (corresponding to your system to open the corresponding data source)

3.ODBC Data Source Additions




4. Setting ODBC data source parameters


The above completes the configuration of the data source, then opens the R software to connect the MySQL database with the RODBC package

Second, download and load the RODBC package

install.packages ("RODBC"   ); #下载RODBC包 Library ("RODBC"); #加载RODBC包

Third, the use

1. Create and open a link"Odbcconnect ( " data Source name ", uid=" username ", pwd=" password " ), return the MySQL connection ID, and the data source name is set above>con = odbcconnect ("Rdata", uid= "root", pwd= "" ); #数据源: Rdata (name when set), User name: root, password is empty2. Reads a data table from a database and returns a data frame"SqlFetch (MySQL connection ID,' table name '  )" >RESULT1 = sqlFetch (conn, ' Test ');> result1 #显示结果 name score1Zhangsan -2Wangwu983. Submitting a query to a database" sqlquery (MySQL connection ID,"SQL command  " )" >result2 = sqlquery (conn, ' select * from Test where score>90 ');> result2 name score1Wangwu984. Close connection "close (MySQL connection id)" >Close (conn);

Common errors:

>conn = Odbcconnect ("Rdata", uid= "root", pwd= "")Warning messages:1:In odbcdriverconnect ("Dsn=rdata; Uid=root "):[RODBC] error:state HY000, Code 2003, message [Mysql][odbc 5.3 (a) Driver]can ' t connect to MySQL server on ' 127.0.0.1 ' (10061)2:In odbcdriverconnect ("Dsn=rdata; Uid=root "):ODBC Connection Failed
　　
This may be due to the fact that MySQL is not turned on and can only be connected after opening

Supplement: MySQL ODBC ANSI driver and Unicode differences: The Unicode driver version provides support for more character sets, that is, support for multiple languages, while the ANSI driver version is scoped only for a limited set of characters.

R Connect MySQL Database method