R과 MySQL 연동하기

RMySQL, DBI 패키지를 활용하여 R과 MySQL 연동하기

R
RMySQL
Author

Don Don

Published

July 10, 2022

R과 MySQL을 연동하는 패키지는 여러가지가 있다(RODBC, odbc…). 그 중에서 가장 쉽게 사용할 수 있는 패키지가 RMySQL 패키지이다. 다른 패키지의 경우 odbc driver를 설치해야 정상적으로 작동하는 것 같은데, mac의 경우 설치가 조금 복잡하다. 몇 가지 시도해본 후 내린 결론은 RMySQL 패키지가 가장 쉽게 사용할 수 있는 것 같다.

packages

library(DBI)
library(RMySQL)
library(dplyr)

다음의 패키지를 부착합니다: 'dplyr'
The following objects are masked from 'package:stats':

    filter, lag
The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

DBI 패키지는 R과 데이터베이스간 연결을 위한 패키지이고, RMySQL은 데이터베이스를 연결할 때, MySQL을 사용하도록 선언?하는 패키지이다.

R과 DB 연결

DBI::dbConnect(drv, 
              dname, 
              user, 
              password, 
              host, ...)

  • drv : DBIDriver 종류

  • dbname : MySQL에 저장되어있는 DB 이름

  • user : “root” (default)

  • password : 비밀번호

  • host: localhost(로컬에서 작업할 경우)

혼공 SQL책에 나와있는 market_db DB를 불러왔다.

drv <- dbDriver("MySQL")

con = dbConnect(drv, 
                dbname = "market_db", 
                user = "root", 
                password = pat, 
                host = "localhost")

DB에 있는 테이블 불러오기

market_db 안에 있는 buy table을 불러온 결과는 다음과 같다.

mydata = dbGetQuery(con, "select * from buy")
mydata
   num mem_id prod_name group_name price amount
1    1    BLK        ??       <NA>    30      2
2    2    BLK      ????        ???  1000      1
3    3    APN       ???        ???   200      1
4    4    MMU       ???        ???   200      5
5    5    BLK       ???         ??    50      3
6    6    MMU       ???        ???    80     10
7    7    GRL     ??SQL         ??    15      5
8    8    APN     ??SQL         ??    15      2
9    9    APN       ???         ??    50      1
10  10    MMU        ??       <NA>    30      1
11  11    APN     ??SQL         ??    15      1
12  12    MMU        ??       <NA>    30      4

한글이 깨질 경우 아래 3줄을 입력하면 해결된다(https://leti-lee.tistory.com/17 내용 참고).

dbSendQuery(con, "SET NAMES utf8;") 
<MySQLResult:-1098565776,0,1>
dbSendQuery(con, "SET CHARACTER SET utf8;") 
<MySQLResult:-1207236832,0,2>
dbSendQuery(con, "SET character_set_connection=utf8;")
<MySQLResult:-1098850272,0,3>
mydata = dbGetQuery(con, "select * from buy")
mydata
   num mem_id prod_name group_name price amount
1    1    BLK      지갑       <NA>    30      2
2    2    BLK  맥북프로     디지털  1000      1
3    3    APN    아이폰     디지털   200      1
4    4    MMU    아이폰     디지털   200      5
5    5    BLK    청바지       패션    50      3
6    6    MMU    에어팟     디지털    80     10
7    7    GRL   혼공SQL       서적    15      5
8    8    APN   혼공SQL       서적    15      2
9    9    APN    청바지       패션    50      1
10  10    MMU      지갑       <NA>    30      1
11  11    APN   혼공SQL       서적    15      1
12  12    MMU      지갑       <NA>    30      4

market_db에 어떤 table이 저장되어 있는지 확인해볼 수 있다.

dbListTables(con)
[1] "buy"       "emp_table" "hongong1"  "hongong2"  "hongong3"  "hongong4" 
[7] "member"

R에 테이블 저장

market_db에 있는 buy table을 R data.frame으로 불러올 수 있다.

buy_dat <- dbReadTable(con, "buy")
buy_dat %>% head()
  num mem_id prod_name group_name price amount
1   1    BLK      지갑       <NA>    30      2
2   2    BLK  맥북프로     디지털  1000      1
3   3    APN    아이폰     디지털   200      1
4   4    MMU    아이폰     디지털   200      5
5   5    BLK    청바지       패션    50      3
6   6    MMU    에어팟     디지털    80     10
buy_dat %>% str()
'data.frame':   12 obs. of  6 variables:
 $ num       : int  1 2 3 4 5 6 7 8 9 10 ...
 $ mem_id    : chr  "BLK" "BLK" "APN" "MMU" ...
 $ prod_name : chr  "지갑" "맥북프로" "아이폰" "아이폰" ...
 $ group_name: chr  NA "디지털" "디지털" "디지털" ...
 $ price     : int  30 1000 200 200 50 80 15 15 50 30 ...
 $ amount    : int  2 1 1 5 3 10 5 2 1 1 ...

Citation

BibTeX citation:
@online{don2022,
  author = {Don, Don and Don, Don},
  title = {R과 {MySQL} {연동하기}},
  date = {2022-07-10},
  url = {https://dondonkim.netlify.app/posts/2022-07-10-RMySQL/RMySQL.html},
  langid = {en}
}
For attribution, please cite this work as:
Don, Don, and Don Don. 2022. “R과 MySQL 연동하기.” July 10, 2022. https://dondonkim.netlify.app/posts/2022-07-10-RMySQL/RMySQL.html.