R串連Oracle、MySQL、SQLServer

標籤：

1、下載mysql-connector-odbc，並安裝

2、windows：控制台->管理工具->資料來源(ODBC)

->ODBC資料管理器->系統DSN->添加->MySQL ODBC Driver

->data source name（如：mysql_data）  

description  

TCP/IP Server（伺服器IP）  

port（如：3306）  

user  

password  

database  

test（顯示串連成功）  

->在“系統資料來源”中顯示“名稱”，“驅動程式”

 

3、在R介面

3.1、程式包->安裝程式包->RODBC

3.2、library(RODBC)

3.3、查看可用的資料來源

>  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、  查看資料庫中的表

> 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、查看錶的內容，存到資料框裡

> 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            23                             China           2006-02-15 04:44:00

 

3.7、SQL查詢

> 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、

head函數擷取前n條記錄

 > 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

 

head函數擷取後n條記錄
> 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、關閉R與資料庫的串連

> odbcClose(data_table)

R串連Oracle、MySQL、SQLServer