다양한 기업의 데이터 분석가 구인 공고를 보면, 자격요건/우대사항에 가장 많이 언급되는 것이 python과 SQL 활용능력이라고 해도 무리는 아니라고 생각된다. 기업의 데이터는 SQL db에 저장되어 있는 경우가 많고, 가져온 데이터를 처리하고 분석하는 도구로 가장 많이 쓰이는 프로그래밍 언어는 단연 python이라고 할 수 있겠다.
이는 자연스럽게 python으로 MySQL을 다루어야 할 필요로 이어진다. MySQL db에 저장된 데이터를 불러와서 python으로 분석하는 것은 물론, db로 결과 데이터를 내보낼 수 있고, 경우에 따라서는 db의 데이터를 수정하는 작업을 할 수도 있다. 특히, 이때 데이터 분석가들에게 익숙한 pandas DataFrame을 활용해서 sql db와 데이터를 주고 받을 수 있다면 더욱 편리하게 작업을 할 수 있을 것이다.
자, 그럼 이번에 정리해 볼 내용이 무엇인지는 분명해졌다. python으로 1) MySQL 데이터를 불러오고, 2) MySQL에 데이터를 내보내거나 수정하는 두 가지 작업을 하는 방법을 알아보는 것. 여기엔 주로 pymysql과 sqlalchemy 두 가지 라이브러리가 많이 쓰이는데 그 사용법을 다뤄보자.
목차
1. MySQL 데이터 불러오기
1.1 pymysql 모듈
mysql을 python에서 사용할 수 있게 하는 라이브러리로는 대표적으로 pymysql이 있다.
먼저 pymysql을 설치하자. (terminal)
$ pip install pymysql
Shell
복사
그리고 파이썬에서 아래와 같이 pymysql을 import한다.
import pymysql
Python
복사
connect 메서드로 MySQL에 연결한다.
db = pymysql.connect(
host='', # 접속할 mysql server의 주소
port='3306', # 접속할 mysql server의 포트 번호
user='your_id',
passwd='your_pw',
db='dbname' # 접속할 database명
charset='' # 'utf8' 등 문자 인코딩 설정 (한글 데이터가 깨지지 않도록)
)
Python
복사
1.2 MySQL 데이터 python으로 받기
데이터 분석가가 가장 많이 사용하는 쿼리는 데이터를 조회하기 위한 SELECT 문일 것이다. SELECT 쿼리를 사용해서 MySQL에서 python으로 데이터를 가져오는 가장 기본적인 방법을 알아보자.
먼저, 연결한 DB와 상호작용하기 위해 cursor 객체를 생성한다.
cursor = db.cursor()
Python
복사
cursor 객체를 생성했으면 이후 pymysql의 기본적인 데이터 조회 쿼리 실행은 execute() → fetch(all, one, many) 로 이루어진다. execute은 쿼리를 db로 보내고, fetch는 서버로부터 데이터를 가져오는 역할을 한다.
메서드 | 설명 |
fetchall() | 쿼리 결과의 모든 데이터를 가져오기 |
fetchone() | 쿼리 결과의 1개 행만 가져오기 |
fetchmany() | 쿼리 결과의 데이터 n개 가져오기 |
아래와 같이 실행하면 tuple 형태로 쿼리 결과를 리턴한다.
sql = 'SELECT * FROM table_name'
cursor.execute(sql)
result = cursor.fetchall()
Python
복사
1.3 MySQL 데이터 pandas DataFrame으로 바로 받기
위에서 간단히 언급했다시피, pandas DataFrame으로 MySQL의 데이터를 바로 주고받을 수 있다면 더욱 편리하게 작업을 할 수 있다. MySQL에서 표 데이터를 읽어오는 경우는 pandas의 read_sql() 메서드로 간편하게 불러올 수 있다.
sql = 'SELECT * FROM table_name'
df = pd.read_sql(sql, db)
Python
복사
작업이 끝났으면 DB 연결을 닫는다.
db.close()
Python
복사
2. MySQL로 데이터 보내기/수정하기
이번엔 데이터 분석의 결과 데이터를 MySQL 테이블로 내보내거나, 테이블을 수정/삭제 하는 등의 작업을 하는 방법을 알아보자.
2.1 INSERT/UPDATE/DELETE 쿼리 실행하기
먼저 INSERT/UPDATE/DELETE 등의 DML 쿼리를 실행하는 방법은 SELECT문 쿼리를 실행할 때와 비슷하다. 하지만 fetch()로 쿼리의 데이터를 가져오는 대신 commit 메서드로 db에 쿼리 내용을 반영한다. commit 해주지 않으면 db로 보내진 쿼리는 반영되지 않는다. 정리하자면, DML 쿼리의 실행은 cursor.execute() → db.commit() 메서드로 이루어진다.
sql = '''
{INSERT/UPDATE/DELETE 쿼리}
'''
cursor.execute(sql)
db.commit()
Python
복사
작업이 끝났으면 DB 연결을 닫는다.
db.close()
Python
복사
2.2 DataFrame → MySQL 내보내기
INSERT 문의 경우에는 쿼리를 작성하는 대신 DataFrame의 데이터를 바로 MySQL에 저장할 수 있으면 훨씬 편리할 것이다. 그리고 그런 방법은 당연히 이미 만들어져있다
. 여기선 sqlalchemy 라이브러리를 사용한다.
. 여기선 sqlalchemy 라이브러리를 사용한다. sqlalchemy를 설치한다. (terminal)
$ pip install sqlalchemy
Bash
복사
sqlalchemy create_engine(sql db 연결 라이브러리)을 import하고, db를 연결한다.
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://[id]:[pw]@[mysql주소]:[port]/[db_name]?charset=utf8", encoding='utf-8')
conn = engine.connect()
Python
복사
to_sql() 메서드로 DataFrame을 데이터베이스로 내보낸다.
df.to_sql(name="[table_name]", con=engine, if_exists='append', index=False)
Python
복사
•
DataFrame의 컬럼명과 테이블의 컬럼명을 동일하게 맞춰두면 해당 컬럼에 저장
•
테이블을 만들수도, 기존 테이블에 row를 추가하거나 지우고 새 데이터를 넣을 수도 있지만, to_sql 메서드로 새 테이블을 만드는 방법은 사용하지 않는 것이 좋고, 기존 테이블에 대해 사용하는 것을 추천
◦
[table_name]이 없으면 새 테이블 생성 (테이블 구조가 최적화되지 않음)
◦
[table_name]이 존재할 경우 → if_exists = ‘fail’, ‘append’, ‘replace’ 사용 가능
작업이 끝났으면 연결을 닫는다.
conn.close()
Python
복사
3. 요약
지금까지 살펴본 내용의 핵심을 요약하면 아래와 같다.
3.1 mysql에서 데이터를 불러오기
•
간단한 데이터를 불러올 경우: pymysql. 쿼리 execute() → fetch(one, many, all)
•
표 데이터를 불러올 경우: pymysql. pd.read_sql(sql, db)
3.2 mysql로 데이터 보내기/수정하기
•
INSERT/UPDATE/DELETE DML 쿼리를 실행할 때: pymysql. 쿼리 execute() → commit()
•
표 데이터를 DB 테이블에 INSERT할 때: sqlalchemy. df.to_sql("table_name", con=engine)
3.3 pymysql 사용법
1.
pymysql 모듈 import
2.
MySQL db 연결: pymysql.connect() 메서드로 Connection 객체 생성
3.
Connection 객체로부터 cursor() 메서드로 Cursor 객체 생성
4.
Cursor 객체의 execute() 메서드로 sql 쿼리를 mysql db에 전송
5.
SELECT 쿼리의 경우 fetchall(), fetchone(), fetchmany() 메서드를 사용하여 데이터를 가져옴
•
pd.read_sql() 을 사용하면 데이터를 바로 DataFrame으로 불러올 수 있음
6.
INSERT/UPDATE/DELETE DML(Data Manipulation Language) 쿼리의 경우 Connection 개체의 commit() 메서드를 사용하여 데이터 확정
7.
종료: Connection 객체의 close() 메서드를 사용해 db 연결을 종료
3.4 sqlalchemy 사용법
1.
sqlalchemy create_engine(sql db 연결 라이브러리)을 import
2.
MySQL db 연결: create_engine() 메서드로 engine 객체 생성. connect() 메서드로 db 연결
3.
DataFrame → MySQL 내보내기: df.to_sql() 메서드 사용
4.
종료: close() 메서드로 db 연결 종료