R connects Oracle, MySQL, SQL Server

1, download MYSQL-CONNECTOR-ODBC, and install

2. Windows: Control Panel, administrative Tools, data source (ODBC)

->ODBC Data Manager, System dsn-> add->mysql ODBC Driver

->data Source Name (for example:mysql_data)

Description

TCP/IP server (server IP)

Port (eg: 3306)

User

Password

Database

Test (show connection succeeded)

"Name", "Driver" in "System data source"

3. In the R interface

3.1. Package----Install package->RODBC

3.2. Library (RODBC)

3.3. View available data sources

> odbcdatasources ()
DBASE Files Excel Files
"Microsoft Access DBASE Driver (*.dbf, *.ndx, *.mdx)" "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.XLSB)"
MS Access Database MySQL
"Microsoft Access Driver (*.mdb, *.accdb)" "MySQL ODBC 5.3 ANSI Driver"
Mysql_data
"MySQL ODBC 5.3 ANSI Driver"

3.4,data_table <-odbcconnect ("mysql_data", uid= "root", pwd= "123")

> data_table
RODBC Connection 1
Details:
Case=tolower
Dsn=mysql_data
Uid=root
pwd=******

3.5. View the tables in the database

> sqlTables (data_table)

TABLE_CAT Table_schem table_name Table_type REMARKS

1 Sakila actor TABLE

2 Sakila actor_info View view

3 Sakila Address TABLE

4 Sakila Category TABLE

5 Sakila City TABLE

6 Sakila Country TABLE

7 Sakila Customer TABLE

3.6. View the contents of the table and save it in the data frame

> Data_country<-sqlfetch (data_table, "country")

> Data_country

COUNTRY_ID Country Last_update

1 7 Armenia 2006-02-15 04:44:00

2 8 Australia 2006-02-15 04:44:00

3 9 Austria 2006-02-15 04:44:00

4 2006-02-15 04:44:00

3.7. SQL query

> Sql_country<-sqlquery (data_table, "Select Country_id,country,last_update from country where country_id<5 ORDER BY country ")
> Sql_country
COUNTRY_ID Country Last_update
1 1 Afghanistan 2006-02-15 04:44:00
2 2 Algeria 2006-02-15 04:44:00
3 3 American Samoa 2006-02-15 04:44:00
4 4 Angola 2006-02-15 04:44:00

3.8.

The head function gets the first N records

> Head (data_country,n=5)
COUNTRY_ID Country Last_update
1 1 Afghanistan 2006-02-15 04:44:00
2 2 Algeria 2006-02-15 04:44:00
3 3 American Samoa 2006-02-15 04:44:00
4 4 Angola 2006-02-15 04:44:00
5 5 Anguilla 2006-02-15 04:44:00

The head function gets the post n record
> Tail (data_country,n=4)
COUNTRY_ID Country Last_update
106 106 Virgin Islands, U.S. 2006-02-15 04:44:00
107 107 Yemen 2006-02-15 04:44:00
108 108 Yugoslavia 2006-02-15 04:44:00
109 109 Zambia 2006-02-15 04:44:00

3.9. Close the R connection to the database

> Odbcclose (data_table)

R connects Oracle, MySQL, SQL Server