관리 메뉴

Hee'World

R과 MySQL 연동 패키지(RMySQL) 본문

Programming/R

R과 MySQL 연동 패키지(RMySQL)

Jonghee Jeon 2014. 6. 4. 18:50

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)


'Programming > R' 카테고리의 다른 글

가설과 검정  (0) 2015.03.28
R의 기술통계 명령어  (0) 2015.03.28
샤이니(Shiny) 함수  (0) 2014.05.04
샤이니(Shiny) 패키지 연습1  (0) 2014.05.01
샤이니(Shiny) 패키지  (0) 2014.05.01
Comments