R 기초 17 - 정형 데이터 처리(RDBMS 연동) SQLite, MariaDB 연동

 

 

RDBMS 를 사용하기 위해서는 java가 다운되어 있어야된다. 또한 환경변수에 등록해놔야 된다.

 

# 정형 데이터 처리 : RDBMS와 연동
# RJDBC api를 사용
install.packages("rJava") # install
install.packages("DBI")   # install
install.packages("RJDBC") # install
# Sys.setenv(JAVA_HOME="c:/자바 설치 경로 입력") 근데 굳이 설정 안 해도 된다.
library(rJava)
library(DBI)
library(RJDBC)

# 개인용 DB : Sqlite와 연동
install.packages("RSQLite")
library(RSQLite)
mtcars   # 자동차 정보 dataset 기본용 data
dim(mtcars)

conn <- dbConnect(RSQLite::SQLite(), ':memory:') # ram에다가 저장
conn
dbWriteTable(conn = conn, "mtcar", mtcars) # table 작성(mtcar이라는 테이블 명으로 mtcars를 넣어준다.)
dbListTables(conn)
dbListFields(conn, "mtcar")  # mtcar 테이블의 칼럼명이 호출된다.
query <- "select mpg, cyl,carb from mtcar"
resultAll <- dbGetQuery(conn, query)  # sql문을 이용하여 호출
resultAll
query <- "select mpg, cyl, carb mpg+100 as good from mtcar where mpg >= 30"
resultPart <- dbGetQuery(conn, query)  # sql문을 이용하여 호출
resultPart

# 운격 DB : MariaDB와 연동
drv <- JDBC(driverClass = "org.mariadb.jdbc.Driver",
            classPath = "C:/work/mariadb-java-client-2.6.2.jar")
conn <- dbConnect(drv = drv, "jdbc:mysql://127.0.0.1:3306/test", "root","123") # ip주소와 포트번호, database명, ID, PW를 입력한다.
conn

dbListTables(conn)  # test table 호출

# sangdata table로 crud 경험
query <- "select * from sangdata"
goodsAll <- dbGetQuery(conn, query) # query문 참조
goodsAll
is(goodsAll)
head(goodsAll, 3)
mean(goodsAll$su)
hist(goodsAll$su)
barplot(goodsAll$su, col = rainbow(10), names.arg = goodsAll$sang)

goods <- dbGetQuery(conn, "select * from sangdata where sang like '가죽%'")
goods

query <- "select code as 코드, sang as 품명, su as 수량, dan as 단가 from sangdata order by code desc"
df <- dbGetQuery(conn, query)
str(df)

MariaDB와 연동 시 위의 경로에 맞게 위의 경로를 입력하면 된다.

압축해제는 하지 않아도 위의 org.mariadb.jdbc.Driver 클래스 경로만 입력하면 된다.

오라클로 할 때는 위의 경로를 오라클 경로로 바꿔주기만 하면 된다.

 

밑의 사진은 압축해제를 한 모습으로 보여주기 위해서 압축해제를 했을 뿐, 하지 않아도 된다.

 

압축 해제한 모습

 

R로 insert, update, delete

# 레코드 추가(insert)
iquery <- "insert into sangdata values(13, '고구마', 34, 5000)"
dbSendUpdate(conn, iquery)

df <- data.frame(code = 14, sang = '립 밤', su = 12, dan = 2000)
df
dbSendUpdate(conn, "insert into sangdata values(?,?,?,?)", df$code, df$sang, df$su, df$dan)
dbGetQuery(conn, "select * from sangdata")

# 레코드 수정
uquery <- "update sangdata set sang = '현성운동화' where code = 14"
dbSendUpdate(conn, uquery)
dbSendQuery(conn, "select * from sangdata")

# 레코드 삭제
dbSendUpdate(conn, "delete from sangdata where code = 11")
dbSendQuery(conn, "select * from sangdata")

 

여러개 insert 하기

# 여러개 insert

sangdf <- read.csv("sangpum.csv", header=T, fileEncoding = "euc-kr")
sangdf

# 참고
paste("Hi", "Everybody")  # 공백을 만들어준다. 합쳐진다.("Hi Everybody")
paste0("Hi", "Everybody") # 공백 없이 합쳐진다.("HiEverybody")

install.packages("dplyr")
library(dplyr)  # %>% 이 연산자를 사용하기 위함이다.

# 여러 개의 행을 가진 data.frame의 자료를 DB에 추가하려면 함수 작성
func <- function(conn, table, df){    # trimws 함수는 앞 뒤의 공백을 없애준다.  collapse는 ,로 구분하겠다는 의미
  batch <- apply(df, 1, FUN = function(x) paste0("'", trimws(x), "'", collapse = ",")) %>% # apply는 함수를 실행하는 함수이다.
                    paste0("(",.,")", collapse = ",\n")
  # print(batch)
  iquery <- paste("insert into", table, "values", batch)
  dbSendUpdate(conn, iquery)
}

# push data
func(conn, "sangdata", sangdf)

dbDisconnect(conn) # 작업을 끝내고 연결을 끊어주어야된다.

여러개의 data를 한 번에 insert할 수 없다. 함수도 없다. 그러므로 프로그램을 짜서 넣어주어야 한다.