행위

R mysql

DB CAFE

1 R-MySQL 패키지

1.1 패키지 설치

install.packages("RMySQL")

1.2 SELECT,INSERT,UPDATE,DELETE

# lib import
library(RMySQL)

dbcon<- dbConnect(
  MySQL(),
  user = 'root',
  password = '{설정한 비밀번호}',
  host = '127.0.0.1',
  dbname = 'testdb'
)

# encoding setting
dbSendQuery(dbcon, 'set character set "utf8"')

# SELECT 
SelData <- dbGetQuery(
  dbcon,
  "SELECT * FROM `test`;"
)

# INSERT
dbSendQuery(
  dbcon,
  "INSERT INTO `test` (관리번호, 일련번호, 시도명, 시군구명) 
  VALUES ('123456789', '1', '서울특별시', '강남구');"
)

# UPDATE
dbSendQuery(
  dbcon,
  "UPDATE `test`
  SET 시도명 = '부산광역시', 시군구명 = '해운대구'
  WHERE 시도명 != '부산광역시';"
)

# DELETE
dbSendQuery(
  dbcon,
  "DELETE FROM `test`
  WHERE id = 100001;"
)

1.3 테이블 목록 조회

# connection info & table schma info
dbListTables(dbcon)

1.4 컬럼 목록 조회

dbListFields(dbcon, "<tableName>")

1.5 테이블 읽기

tableData <- dbReadTable(dbcon, "<tableName>")

1.6 CSV 파일 읽기

dfData <- read.csv("./<csvFileName>.csv", head = T)
head(dfData)
tail(dfData)

# dbWriteTable 이용하여 INSERT 수행
dbWriteTable(con, "<csvFileName>", dfData, overwrite = T) ## table from a data.frame

1.7 SendQurey(쿼리 수행) Fetch 방식

  1. 데이터베이스로부터 데이터를 쿼리를 사용하여 가져옴
  2. 데이터 형식은 DataFrame
rs <- dbSendQuery(con, "select count(*) from <tableName>") # Query Test 1
d1 <- fetch(rs, n = 10000)
d2 <- fetch(rs, n = -1) # 전체 fetch
class(d2) 
# > class(d2)
# [1] "data.frame"

1.8 SendQurey(쿼리 수행) 전체 조회

df.table <- dbGetQuery(con, "select * from <tableName>") # Query Test 2
head(df.table)
class(df.table)


1.9 메타 정보 조회

summary(MySQL(), verbose = TRUE)
summary(con, verbose = TRUE)
summary(rs, verbose = TRUE)
dbListConnections(MySQL())
dbListResultSets(con)
dbHasCompleted(rs)

1.10 연결 종료

# dbCommit()
# dbRollback()
# MySQL 확인 결과 dbWriteTable 결과는 AutoCommit 
dbDisconnect(con) # disconnect
  • 주의할 점: dbWriteTable 함수의 옵션인 overwrite=T 이면 data.frame 자료 형식에 맞춰 기존 table field format을 변형
  • 위 예제에서는 dbWriteTable 함수에서 row.names 옵션 지정이 없어 디폴트로 row.names=T 로 수행됨.
  1. 이전 데이터 덮어 쓰기 됨.


- 따라서 이미 생성된 table의 field format에 맞춰서 삽입하려면 다음과 같이 수행해야 했다.

1.11 CSV 로딩 후 데이터 INSERT

  1. 작업 경로 설정
getwd()
setwd('~')
 
# 라이브러리 임포트
library(RMySQL)
# DB 연결 
conn <- dbConnect(MySQL(),
                  dbname = '<database_name>',
                  user = '<user_id>',
                  password="<user_pwd>")
 
# 테이블 확인
dbListTables(conn)
 
# 데이터 로딩
dfData <- read.csv('./~/<insert_data.csv>', header=T, sep=',') # 
 
# data explore
head(dfData)
# field check
dbListFields(con, "<table_name_insert>")
 
# 삽입할 테이블의 필드명 지정! - 동일할 경우 시행 X
names(dfData) <- c('<field_name_1>', '<field_name_2>', '<field_name_3>', ... )
 
# INSERT DATA
dbWriteTable(conn, "table_name_insert", # table to insert
             dfData, # insert data
             overwrite = F, append = T, # !!! keep table field data format
             row.names = F) # 최자측에 자동 행 번호 열을 생성하는 것을 방지함
dbCommit(conn) # commit

1.12 R-Mysql 접속 예제

library(DBI)
# Connect to my-db as defined in ~/.my.cnf
#[rs-dbi]
#database=test
#user=root
#password=

con <- dbConnect(RMySQL::MySQL(), group = "my-db")

dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")

# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)

# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
# Clear the result
dbClearResult(res)

# Disconnect from the database
dbDisconnect(con)