호쌤
호쌤 Just For Fun

[Python] Pandas의 DataFrame과 데이터베이스 직접 연동

크리에이티브 커먼즈 라이선스 ITPAPER(호쌤,쭈쌤)에 의해 작성된 ≪[Python] Pandas의 DataFrame과 데이터베이스 직접 연동≫은(는) 크리에이티브 커먼즈 저작자표시-비영리-동일조건변경허락 4.0 국제 라이선스에 따라 이용할 수 있습니다.
이 라이선스의 범위 이외의 이용허락을 얻기 위해서는 leekh4232@gmail.com으로 문의하십시오.

[Python] Pandas의 DataFrame과 데이터베이스 직접 연동

SqlAlchemy 패키지를 활용하여 Pandas의 DataFrame과 데이터베이스를 직접 연동시키는 방법을 소개합니다.

#01. 패키지 설치

1
pip install --upgrade sqlalchemy
▶ 출력결과
1
2
Requirement already up-to-date: sqlalchemy in c:\python36\lib\site-packages (1.3.18)
Note: you may need to restart the kernel to use updated packages.

#02. 연동준비

1) 패키지 참조

1
2
3
4
5
import pymysql
from sqlalchemy import create_engine
import pandas as pd
from pandas import DataFrame
from matplotlib import pyplot

2) 데이터베이스 연동에 필요한 접속 정보

1
2
3
4
5
6
7
HOSTNAME = "localhost"
PORT     = 3306
USERNAME = "root"
PASSWORD = "123qwe!@#"
DATABASE = "myschool"
CHARSET1  = "utf8"     # MySQL에서 사용할 캐릭터셋 이름
CHARSET2  = "utf-8"    # Python에서 사용할 캐릭터셋 이름

#03. 데이터베이스 접속하기

1) 접속 문자열 생성

mysql+mysqldb://아이디:비밀번호@DB서버주소:포트번호/DB이름?charse=인코딩

1
2
3
con_str_fmt = "mysql+mysqldb://{0}:{1}@{2}:{3}/{4}?charset={5}"
con_str = con_str_fmt.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE, CHARSET1)
con_str
▶ 출력결과
1
'mysql+mysqldb://root:123qwe!@#@localhost:3306/myschool?charset=utf8'

2) pymysql을 사용하여 MySQL 연동 객체 설치하기

1
2
pymysql.install_as_MySQLdb()
import MySQLdb

3) 데이터베이스 접속

1
2
engine = create_engine(con_str, encoding=CHARSET2)
conn = engine.connect()

#05. 데이터 저장하기

1) 인덱스 컬럼이 없는 DataFarme의 경우

DataFarme 생성

엑셀읽기, 크롤링 등의 방법으로 수집한 데이터를 DataFrame으로 생성한다.

1
2
3
4
5
6
df1 = DataFrame([
        {"deptno": 300, "dname": "학과1", "loc": "위치1"},
        {"deptno": 301, "dname": "학과2", "loc": "위치2"},
        {"deptno": 302, "dname": "학과3", "loc": "위치3"}
    ])
df1
▶ 출력결과
deptno dname loc
0 300 학과1 위치1
1 301 학과2 위치2
2 302 학과3 위치3

DataFrame을 db에 저장하기

to_sql() 함수는 지정된 테이블이 존재하지 않을 경우 새로 생성하여 데이터를 저장한다.

파라미터 설명
if_exists replace - index값을 기준으로 동일한 데이터 수정
  append : 새로운 행으로 추가
index True 를 지정할 경우 DataFrame의 index가 DB에 함께 저장된다.
  DataFrame의 index를 PK로 사용할 경우만 적용할 것.
인덱스와 함께 저장하기
1
df1.to_sql(name="department_py", con=conn, if_exists='replace', index=True)
저장결과를 DB에서 직접 조회
1
SELECT * FROM department_py
▶ 출력결과
1
2
3
4
5
6
7
+-------+--------+-------+-------+
| index | deptno | dname | loc   |
+-------+--------+-------+-------+
|     0 |    300 | 학과1 | 위치1 |
|     1 |    301 | 학과2 | 위치2 |
|     2 |    302 | 학과3 | 위치3 |
+-------+--------+-------+-------+
테이블 구조 확인
1
DESC department_py
▶ 출력결과
1
2
3
4
5
6
7
8
+--------+--------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| index  | bigint | YES  | MUL | NULL    |       |
| deptno | bigint | YES  |     | NULL    |       |
| dname  | text   | YES  |     | NULL    |       |
| loc    | text   | YES  |     | NULL    |       |
+--------+--------+------+-----+---------+-------+

index를 함께 저장하지 않는 경우

1
df1.to_sql(name="department_py", con=conn, if_exists='replace', index=False)
저장결과를 DB에서 직접 조회
1
SELECT * FROM department_py
▶ 출력결과
1
2
3
4
5
6
7
+--------+-------+-------+
| deptno | dname | loc   |
+--------+-------+-------+
|    300 | 학과1 | 위치1 |
|    301 | 학과2 | 위치2 |
|    302 | 학과3 | 위치3 |
+--------+-------+-------+
테이블 구조 확인
1
DESC department_py
▶ 출력결과
1
2
3
4
5
6
7
+--------+--------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| deptno | bigint | YES  |     | NULL    |       |
| dname  | text   | YES  |     | NULL    |       |
| loc    | text   | YES  |     | NULL    |       |
+--------+--------+------+-----+---------+-------+

2) 인덱스 컬럼이 있는 DataFarme의 경우

가장 이상적인 형태는 DataFarme에 별도의 인덱스 컬럼을 지정하고, DB에 저장할 때 그 컬럼이 함께 저장되도록 설정하는 것이다.

DataFarme 만들기

1
2
3
4
5
6
7
df2 = DataFrame([
        {"deptno": 300, "dname": "학과1", "loc": "위치1"},
        {"deptno": 301, "dname": "학과2", "loc": "위치2"},
        {"deptno": 302, "dname": "학과3", "loc": "위치3"}
    ])
df2.set_index('deptno', inplace=True)
df2
▶ 출력결과
dname loc
deptno
300 학과1 위치1
301 학과2 위치2
302 학과3 위치3

데이터 저장하기

1
df2.to_sql(name="department_py", con=conn, if_exists='replace', index=True)

데이터 조회를 통한 결과 확인

1
SELECT * FROM department_py
1
2
3
4
5
6
7
+--------+-------+-------+
| deptno | dname | loc   |
+--------+-------+-------+
|    300 | 학과1 | 위치1 |
|    301 | 학과2 | 위치2 |
|    302 | 학과3 | 위치3 |
+--------+-------+-------+

#05. 데이터 조회하기

1) 부분조회

원하는 컬럼이름을 나열하고 WHERE로 특정 조건에 맞는 데이터 조회하기

1
2
3
sql = "SELECT deptno, dname, loc FROM department_py WHERE deptno = 300"
df3 = pd.read_sql(sql, index_col='deptno', con=conn)
df3
▶ 출력결과
dname loc
deptno
300 학과1 위치1

2) 전체조회

특정 테이블 데이터를 통째로 다 가져오기

1
2
df4 = pd.read_sql_table("department_py", index_col='deptno', con=conn)
df4
▶ 출력결과
dname loc
deptno
300 학과1 위치1
301 학과2 위치2
302 학과3 위치3

가져올 컬럼 지정하기

1
2
3
4
5
df5 = pd.read_sql_table("department_py", 
                        index_col='deptno',
                        columns=['dname'], 
                        con=conn)
df5
▶ 출력결과
dname
deptno
300 학과1
301 학과2
302 학과3

#06. 데이터베이스 접속 해제

1
conn.close()
Rating:

크리에이티브 커먼즈 라이선스 ITPAPER(호쌤,쭈쌤)에 의해 작성된 ≪[Python] Pandas의 DataFrame과 데이터베이스 직접 연동≫은(는) 크리에이티브 커먼즈 저작자표시-비영리-동일조건변경허락 4.0 국제 라이선스에 따라 이용할 수 있습니다.
이 라이선스의 범위 이외의 이용허락을 얻기 위해서는 leekh4232@gmail.com으로 문의하십시오.

comments powered by Disqus