R과 MySQL 연동 패키지(RMySQL)

install.packages("RMySQL")   #R과 MySQL연동을 위한 패키지를 다운로드 합니다.

library(RMySQL)   # RMySQL 패키지를 로드합니다.

1. Connect and authenticate to one or more MySQL databases:

con <- dbConnect(MySQL(), group = "lasers")

con2 <- dbConnect(MySQL(), user="opto", password="pure-light",

dbname="lasers", host="merced")

2. List tables and fields in a table:

dbListTables(con)

dbListFields(con, "table_name")

3. Import and export data.frames:

RMySQL-package 3

d <- dbReadTable(con, "WL")

dbWriteTable(con, "WL2", a.data.frame) ## table from a data.frame

dbWriteTable(con, "test2", "~/data/test2.csv") ## table from a file

4. Run an arbitrary SQL statement and extract all its output (returns a data.frame):

dbGetQuery(con, "select count(*) from a_table")

dbGetQuery(con, "select * from a_table")

5. Run an SQL statement and extract its output in pieces (returns a result set):

rs <- dbSendQuery(con, "select * from WL where width_nm between 0.5 and 1")

d1 <- fetch(rs, n = 10000)

d2 <- fetch(rs, n = -1

6. Run multiple SQL statements and process the various result sets (note the client.flag value

in the dbConnect call):

con <- dbConnection(MySQL(), dbname = "rs-dbi",

client.flag = CLIENT_MULTI_STATEMENTS)

script <- paste("select * from WL where width_nm between 0.5 and 1"

"select * from lasers_id where id LIKE ’AL100

sep = ";")

rs1 <- dbSendQuery(con, script)

d1 <- fetch(rs1, n = -1)

if(dbMoreResults(con)){

rs2 <- dbNextResult(con)

d2 <- fetch(rs2, n=-1)

}

7. Get meta-information on a connection (thread-id, etc.):

summary(MySQL(), verbose = TRUE)

summary(con, verbose = TRUE)

summary(rs, verbose = TRUE)

dbListConnections(MySQL())

dbListResultSets(con)

dbHasCompleted(rs)

8. Close connections:

dbDisconnect(con)

dbDisconnect(con2)