[Python] MySQL 조회 결과를 DataFrame으로 저장하기

1. PyMySQL

MySQL 데이터베이스와 연결하기 위한 오픈소스 라이브러리입니다. 해당 라이브러리를 설치하기 위해서 python -m pip install PyMySQL 명령어로 설치가능하지만, 만약 PyCharm 을 이용한다면. Setting에서 Project Interpreter 에서 아래 사진의 + 버튼을 누른뒤 PyMySQL 를 검색하여 설치할 수 있습니다.

설치가 완료가 되면 간단한 예제 코드로 Connection 테스트를 합니다.

import pymysql.cursors
 
connection = pymysql.connect(host='localhost', user='root', password='12', db='ssooni',
                             charset='utf8', autocommit=True)
 
cursor = connection.cursor()
sql = "select * from ssooni.board"
cursor.execute(sql)
 
result = cursor.fetchall()
connection.close()
print(result)

- host  : MySQL가 설치되어 있는 원격지 주소를 넣어 줍니다.

- user / password :  계정명 / 비밀번호

- db : Default로 사용할 Database 이름을 넣어줍니다.

- charset : 인코딩 정보

- autocommit : Query 실행 후 자동으로 commit 명령어를 전송합니다. 

이 부분을 넣지 않으면, Insert나 Update를 실행해도 실제 DB에 반영되지 않습니다. 

실수로 데이터를 삭제 / 수정하는 일을 조금 막고자한다면, 이 옵션을 사용하지 않고, Query 실행 후 commit 할 시점에 connection.commit() 라인을 추가합니다.

  

Connection 객체로 DB 연결을 설정하고 Cursor로 데이터를 가지고 오는 Python에서 자주 볼 수 있는 형태로 데이터를 가지고 옵니다. 실행하면 아래와 같은 결과를 볼 수 있습니다.

실행 결과 

(1, 'ssooni', 'Welcome! Hi Je'), (13, 'aa', 'aaa'), (14, 'aa', 'aaaa'), (15, '?????', '?? ??????\n'))

2. pandas.DataFrame으로 변환

앞선 실행 결과는 Column 이 없어서 각각의 원소들이 어떤 데이터인지 알 수가 없습니다. pandas를 사용하여 Column 명을 할당하고, 데이터를 쉽게 분석하고 싶기도 합니다. 

보통 Python DB 라이브러리에서는 cursor.description 에 각각의 컬럼에 대한 정보를 따로 담습니다. 그래서 따로 함수를 짜서 cursor.description 안에서 컬럼명을 가지고 온 후 매핑을 하는 과정을 작성했습니다만, 최근에 나온 라이브러리는 그것마저 해줍니다.

import pymysql.cursors
 
connection = pymysql.connect(host='localhost', port=3306, user='root', 
                             password='12', db='ssooni',
                             charset='utf8', autocommit=True, 
                             cursorclass=pymysql.cursors.DictCursor)
 
cursor = connection.cursor()
sql = "select * from ssooni.board"
cursor.execute(sql)
result = cursor.fetchall()
print(result)

아까 코드와 달라진 점은 cursorclass에 DictCursor 를 추가했습니다. DB를 조회한 결과를 Column 명이 Key 인 Dictionary로 저장해 줍니다.

실행 결과

[{'bno': 1, 'userName': 'ssooni', 'contents': 'Welcome! Hi Je'}, {'bno': 13, 'userName': 'aa', 'contents': 'aaa'}, {'bno': 14, 'userName': 'aa', 'contents': 'aaaa'}, {'bno': 15, 'userName': '?????', 'contents': '?? ??????\n'}]

 

이전 결과와 달리 Column 명이 있어서 각각의 원소가 의미하는 것이 무엇인지 쉽게 이해 할 수 있습니다.

실행 결과를 Pandas DataFrame으로 바꾸는 것은 더욱 간단합니다. 

import pandas as pd
 
df = pd.DataFrame(result)
print(df)

실행 결과

   bno        contents userName

0    1  Welcome! Hi Je   ssooni

1   13             aaa       aa

2   14            aaaa       aa

3   15     ?? ??????\n    ?????

이제 DataFrame으로 평균을 구하는 등 통계도 할 수 있고, 단순히 CSV 파일로 저장하여 엑셀을 정말 잘하는 팀원에게 노동을 시킬 수도 있습니다. 

도움이 되셨다면 공감 버튼 한 번만 눌러주세요. 

공감 버튼은 저에게 큰 동기부여가 됩니다.