행위

"R MYSQL 연결"의 두 판 사이의 차이

DB CAFE

(Dataframe -> INSERT 쿼리 실행)
 
(같은 사용자의 중간 판 7개는 보이지 않습니다)
1번째 줄: 1번째 줄:
== MYSQL +Dataframe 연결 ==
+
== MYSQL + Dataframe 연결 ==
  
 
=== Mysql 패키지 설치 ===
 
=== Mysql 패키지 설치 ===
11번째 줄: 11번째 줄:
 
<source lang=r>
 
<source lang=r>
 
drv = dbDriver("MySQL")
 
drv = dbDriver("MySQL")
con = dbConnect(drv,host="호스트",dbname="디비명",user="사용자",pass="패스워드")  
+
db_conn = dbConnect(drv,host="호스트",dbname="디비명",user="사용자",pass="패스워드")  
 
</source>
 
</source>
  
=== 테이블 목록 가져오기 ===
+
=== 테이블 목록 ===
1) 테이블 목록
 
 
<source lang=r>
 
<source lang=r>
dbListTables(con)
+
dbListTables(db_conn)
 
</source>
 
</source>
2) 테이블의 컬럼 목록  
+
=== 컬럼 목록 ===
 
<source lang=r>
 
<source lang=r>
dbListFields(con, '테이블명')
+
dbListFields(db_conn, '테이블명')
 
</source>
 
</source>
 +
 
=== SELECT 쿼리 실행 ===
 
=== SELECT 쿼리 실행 ===
 
<source lang=r>
 
<source lang=r>
dbSendQuery(con, 'SELECT * FROM TB_XXX')
+
sel_data <- dbSendQuery(db_conn, 'SELECT * FROM TB_XXX')
 
</source>
 
</source>
=== INSERT 쿼리 실행 ===
+
 
 +
=== Dataframe -> DB 테이블 생성/추가 ===
 
<source lang=r>
 
<source lang=r>
dbWriteTable(con, name='table_name', value=data.frame.name)
+
dbWriteTable(db_conn, name='table_name', value=data.frame.name)
 
</source>
 
</source>
  
 
=== MYSQL 사용 예제 ===
 
=== MYSQL 사용 예제 ===
 
<source lang=r>
 
<source lang=r>
# 1.라이브러리 로드
+
## S4 method for signature 'MySQLConnection,character,data.frame'
library(RMySQL)
+
dbWriteTable(
 +
  db_conn,
 +
  tablename,
 +
  value,
 +
  field.types = NULL,
 +
  row.names = TRUE,
 +
  overwrite = FALSE,
 +
  append = FALSE,
 +
  ...,
 +
  allow.keywords = FALSE
 +
)
 +
 
 +
## S4 method for signature 'MySQLConnection,character,character'
 +
dbWriteTable(
 +
  db_conn,
 +
  tablename,
 +
  value,
 +
  field.types = NULL,
 +
  overwrite = FALSE,
 +
  append = FALSE,
 +
  header = TRUE,
 +
  row.names = FALSE,
 +
  nrows = 50,
 +
  sep = ",",
 +
  eol = "\n",
 +
  skip = 0,
 +
  quote = "\"",
 +
  ...
 +
)
 +
</source>
 +
 
 +
==== Argument 설명 ====
 +
<source lang=r>
 +
db_conn
 +
a MySQLConnection object, produced by dbConnect
 +
 
 +
tablename
 +
a character string specifying a table name.
 +
 
 +
value
 +
a data.frame (or coercible to data.frame) object or a file name (character). In the first case, the data.frame is written to a temporary file and then imported to SQLite; when value is a character, it is interpreted as a file name and its contents imported to SQLite.
 +
 
 +
field.types
 +
character vector of named SQL field types where the names are the names of new table's columns. If missing, types inferred with dbDataType).
 +
 
 +
row.names
 +
A logical specifying whether the row.names should be output to the output DBMS table; if TRUE, an extra field whose name will be whatever the R identifier "row.names" maps to the DBMS (see make.db.names). If NA will add rows names if they are characters, otherwise will ignore.
 +
 
 +
overwrite
 +
a logical specifying whether to overwrite an existing table or not. Its default is FALSE. (See the BUGS section below)
 +
 
 +
append
 +
a logical specifying whether to append to an existing table in the DBMS. Its default is FALSE.
 +
 
 +
...
 +
Unused, needs for compatibility with generic.
  
# 2.DB Connection
+
allow.keywords
# drv = dbDriver("MySQL")
+
logical indicating whether column names that happen to be MySQL keywords be used as column names in the resulting relation (table) being written. Defaults to FALSE, forcing mysqlWriteTable to modify column names to make them legal MySQL identifiers.
# 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 이용하여 삽입 수행
+
header
dbWriteTable(con, "<csvFileName>", dfData, overwrite = T) ## table from a data.frame
+
logical, does the input file have a header line? Default is the same heuristic used by read.table, i.e., TRUE if the first line has one fewer column that the second line.
 
##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)
+
nrows
# > class(d2)
+
number of lines to rows to import using read.table from the input file to create the proper table definition. Default is 50.
# [1] "data.frame"
 
  
#5.2 GetQuery 방식 (FETCH 없이 사용)
+
sep
df.table <- dbGetQuery(con, "select * from <tableName>")
+
field separator character
head(df.table)
 
class(df.table)
 
 
# 7. 연결 정보 조회
 
summary(MySQL(), verbose = TRUE)
 
summary(con, verbose = TRUE)
 
summary(rs, verbose = TRUE)
 
  
dbListConnections(MySQL())
+
eol
dbListResultSets(con)
+
End-of-line separator
dbHasCompleted(rs)
 
 
# 8. 커멋,롤백, 연결 종료
 
# dbCommit() # 참고
 
# dbRollback() # 참고
 
# MySQL 확인 결과 dbWriteTable 결과는 AutoCommite 으로 사료
 
  
dbDisconnect(con) # disconnect
+
skip
 +
number of lines to skip before reading data in the input file.
  
 +
quote
 +
the quote character used in the input file (defaults to \".)
 
</source>
 
</source>
  

2020년 12월 20일 (일) 21:49 기준 최신판

thumb_up 추천메뉴 바로가기


1 MYSQL + Dataframe 연결[편집]

1.1 Mysql 패키지 설치[편집]

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


1.2 Mysql 연결[편집]

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

1.3 테이블 목록[편집]

dbListTables(db_conn)

1.4 컬럼 목록[편집]

dbListFields(db_conn, '테이블명')

1.5 SELECT 쿼리 실행[편집]

sel_data <- dbSendQuery(db_conn, 'SELECT * FROM TB_XXX')

1.6 Dataframe -> DB 테이블 생성/추가[편집]

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

1.7 MYSQL 사용 예제[편집]

## S4 method for signature 'MySQLConnection,character,data.frame'
dbWriteTable(
  db_conn,
  tablename,
  value,
  field.types = NULL,
  row.names = TRUE,
  overwrite = FALSE,
  append = FALSE,
  ...,
  allow.keywords = FALSE
)

## S4 method for signature 'MySQLConnection,character,character'
dbWriteTable(
  db_conn,
  tablename,
  value,
  field.types = NULL,
  overwrite = FALSE,
  append = FALSE,
  header = TRUE,
  row.names = FALSE,
  nrows = 50,
  sep = ",",
  eol = "\n",
  skip = 0,
  quote = "\"",
  ...
)

1.7.1 Argument 설명[편집]

db_conn	
a MySQLConnection object, produced by dbConnect

tablename	
a character string specifying a table name.

value	
a data.frame (or coercible to data.frame) object or a file name (character). In the first case, the data.frame is written to a temporary file and then imported to SQLite; when value is a character, it is interpreted as a file name and its contents imported to SQLite.

field.types	
character vector of named SQL field types where the names are the names of new table's columns. If missing, types inferred with dbDataType).

row.names	
A logical specifying whether the row.names should be output to the output DBMS table; if TRUE, an extra field whose name will be whatever the R identifier "row.names" maps to the DBMS (see make.db.names). If NA will add rows names if they are characters, otherwise will ignore.

overwrite	
a logical specifying whether to overwrite an existing table or not. Its default is FALSE. (See the BUGS section below)

append	
a logical specifying whether to append to an existing table in the DBMS. Its default is FALSE.

...	
Unused, needs for compatibility with generic.

allow.keywords	
logical indicating whether column names that happen to be MySQL keywords be used as column names in the resulting relation (table) being written. Defaults to FALSE, forcing mysqlWriteTable to modify column names to make them legal MySQL identifiers.

header	
logical, does the input file have a header line? Default is the same heuristic used by read.table, i.e., TRUE if the first line has one fewer column that the second line.

nrows	
number of lines to rows to import using read.table from the input file to create the proper table definition. Default is 50.

sep	
field separator character

eol	
End-of-line separator

skip	
number of lines to skip before reading data in the input file.

quote	
the quote character used in the input file (defaults to \".)