[SQL] DB(데이터베이스)의 data를 csv로 추출하는 법

포스팅 개요

 보통의 공모전이나 캐글은 csv 형태의 파일을 제공하고 시작하기에 데이터를 추출하는 역량이 필요해 보이지 않을 수 있습니다. 하지만 현업에서는 그런 편한 상황은 주어지지 않을 것입니다. 고로 데이터가 주어지지 않을 때를 대비하여 DB에서 data를 직접 추출하여 csv로 변환하는 2가지 작업에 대해 알아보겠습니다.

---

포스팅 본문

 Python의 pymysql 라이브러리를 활용한 방법과 MySQL Workbench를 이용하는 방법을 소개하겠습니다.

여러분들은 username, password, db명, host Name(or IP address)만 준비해주시면 됩니다.

1. Python을 이용한 data 추출

1-1. Library pymysql 설치

!pip install PyMySQL

1-2. Import Library & DB 연결

 이 부분에서 아까 준비했던 username, password, db명, host Name(or IP address)를 입력해줍니다.

import pymysql
import pandas as pd

# DB 연결
user_db = pymysql.connect(
		user = username,
        	password = password,
        	host = host Name(or IP address),
        	db = db명,
        	charset = 'utf8'
    	  )

1-3. cursor 설정

 연결한 DB와 상호작용하기 위해 cursor 객체를 생성해줘야 합니다. cursor에는 다양한 종류가 있지만 우리는 csv로 추출할 것이기에 DictCursor를 사용하겠습니다.

# cursor 설정
cursor = danvi_db.cursor(pymysql.cursors.DictCursor)

1-4. 데이터 조작하기

 이 부분에서 sql문법을 이용한 다양한 작업을 할 수 있습니다. 우리는 전체 데이터를 뽑는 작업을 시행하겠습니다.

sql 변수의 table name 부분에 추출하고자 하는 table 명을 입력해줍니다. 

# 데이터 조작하기
sql = 'SELECT * FROM `table name`'
cursor.execute(sql)
result = cursor.fetchall()

1-5. 데이터 추출하기

# 결과를 pandas 데이터프레임으로 변환
result = pd.DataFrame(result)
result.to_csv('./file_name.csv', index = False)

 이상으로 Python의 pymysql을 이용하여 DB의 data를 csv로 추출하는 방법을 배워보았습니다. 원하는 table의 전체 data를 csv로 변환하는 함수를 첨부해두겠습니다.

import pymysql
import pandas as pd

def sql2csv(table):
# DB 연결
    danvi_db = pymysql.connect(
        user = username,
        password = password,
        host = host Name,
        db = db명,
        charset = 'utf8'
    )

    # cursor 설정
    cursor = danvi_db.cursor(pymysql.cursors.DictCursor)

    # 데이터 조작하기
    sql = 'SELECT * FROM'+'`'+table+'`'
    cursor.execute(sql)
    result = cursor.fetchall()

    # 결과를 pandas 데이터프레임으로 변환
    result = pd.DataFrame(result)
    result.to_csv('./'+table+'.csv', index = False)
    
    return result

---

2. MySQL Workbench를 이용한 data 추출

2-1. DB 연결

 MySQL Connections의 + 버튼을 눌러 마찬가지로 Hostname, Port, Username, Password를 알맞게 입력하여 DB와 연결해줍니다.

DB 연결

2-2. Data Export

 원하는 Table을 우클릭하여 Table Data Export Wizard를 입력하여줍니다.

Table Export

 

코드로도 추출할 수 있습니다.

2-3. 출력 설정

 그다음 Field Separator를 comma(,)로 변형시키고 File Path를 지정하여 진행하면 작업이 완료됩니다.

Parameter config

3. MySQL Workbench에서 Export하는 다른 방법

3-1. 간단한 코드를 통한 Export

 INTO OUTFILE 뒤에 원하는 파일 경로를 입력하여 실행시켜주면 가볍게 파일을 원하는 곳에 추출할 수 있습니다.

SHOW VARIABLES LIKE "secure_file_priv" 라는 코드를 통해 현재 자신의 저장 폴더를 확인할 수 있습니다.

SELECT * FROM `TABLE NAME`
INTO OUTFILE 'FILE PATH'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

3-2. 코드와 버튼을 결합하여 Table 부분 추출

 SQL 문법을 이용하여 조건에 맞는 Table을 사진의 '여기'라고 적혀 있는 Export 버튼을 통해서도 csv로 추출이 가능하다.

---

 위의 여러 방법을 통해 DB의 data를 추출해볼 수 있었습니다.

 

 실행해본 결과 저는 3-2 방법이 가장 빨랐습니다. SQL 구문을 조금 다룰 줄 안다면 여러모로 편해질 듯 합니다.

 

 궁금한 점 있으시면 언제든 연락주세요. 다들 즐거운 데이터 분석하시길 바라겠습니다. :)