행위

R MYSQL 연결

DB CAFE

1 MYSQL 연결

1.1 Mysql 패키지 설치

install.packages("RMySQL")
library(RMySQL)


1.2 Mysql 연결

drv = dbDriver("MySQL")
con = dbConnect(drv,host="호스트",dbname="디비명",user="사용자",pass="패스워드")

1.3 테이블 목록 가져오기

1) 테이블 목록

dbListTables(con)

2) 테이블의 컬럼 목록

dbListFields(con, '테이블명')

1.4 SELECT 쿼리 실행

dbSendQuery(con, 'SELECT * FROM TB_XXX')

1.5 INSERT 쿼리 실행

dbWriteTable(con, name='table_name', value=data.frame.name)

1.6 MYSQL 사용 예제

# 1.라이브러리 로드 
library(RMySQL)

# 2.DB Connection 
# drv = dbDriver("MySQL")
# con = dbConnect(drv,host="호스트",dbname="디비명",user="사용자",pass="패스워드") 
con <- dbConnect(MySQL(),dbname = '<dbName>',user = '<dbId>', password="<dbPasswd>")
 
# 3. 테이블/컬럼 목록 
dbListTables(con)
dbListFields(con, "<tableName>")
 
## 3. Import and export data.frames:
d <- dbReadTable(con, "<tableName>")
# load dataframe
dfData <- read.csv("./<csvFileName>.csv", head = T)
head(dfData)
tail(dfData)

# 4. dbWriteTable 이용하여 삽입 수행
dbWriteTable(con, "<csvFileName>", dfData, overwrite = T) ## table from a data.frame
 
##5. Run an SQL statement and extract its output in pieces (returns a result set)
## 데이터베이스로부터 데이터를 쿼리를 사용하여 가져옴 
## 데이터 형식은 DataFrame
#5.1 SendQurey 방식(FETCH 방식)
rs <- dbSendQuery(con, "select count(*) from <tableName>")
d1 <- fetch(rs, n = 10000)
d2 <- fetch(rs, n = -1) # 전체 fetch

class(d2) 
# > class(d2)
# [1] "data.frame"

#5.2 GetQuery 방식 (FETCH 없이 사용)
df.table <- dbGetQuery(con, "select * from <tableName>")
head(df.table)
class(df.table)
 
# 7. 연결 정보 조회 
summary(MySQL(), verbose = TRUE)
summary(con, verbose = TRUE)
summary(rs, verbose = TRUE)

dbListConnections(MySQL())
dbListResultSets(con)
dbHasCompleted(rs)
 
# 8. 커멋,롤백, 연결 종료 
# dbCommit() # 참고
# dbRollback() # 참고
# MySQL 확인 결과 dbWriteTable 결과는 AutoCommite 으로 사료

dbDisconnect(con) # disconnect

2 DataFrame INSERT

import pymssql
import pandas as pd
 
# ------------------------------
# -- Create Dataframe
# ------------------------------
df = pd.DataFrame({"a_key":[111,222,333], "b_sect":['a1','a2','a3',],
                   "c_mop":["aaaaa","bbbbb","ccccc"], "d_mop":["aaaaa","bbbbb","ccccc"],
                   "e_mop":["aaaaa","bbbbb","ccccc"], "f_mop":["aaaaa","bbbbb","ccccc"]})
 
print (df)
print ("... dataframe", "." * 100, "\n")
 
 
# ------------------------------
# -- Connection String
# ------------------------------
conn = pymssql.connect(host='hostname', user='user', password='password', database='db')
cur = conn.cursor()
 
 
# ------------------------------
# -- Create SQL
# ------------------------------
sql = "insert into mop.cn values(%d, %s, %s, %s, %s, %s);"
data = [tuple(x) for x in df.values]
 
print("sql:", sql)
print("data:", data)
print (",,, sql_statement", "," * 100, "\n")
 
 
# ------------------------------
# -- Execute SQL
# ------------------------------
cur.executemany(sql, data)
conn.commit()