<Python, SQL> pymysql 라이브러리(with Pandas DataFrame)

pymysql

pymysql 라이브러리란 이름 그대로 MySQL을 Python 환경에서 사용할 수 있게 만들어주는 라이브러리이다.

 

우선 pip install pymysql로 라이브러리를 설치했다면

mysql 작업환경에서 DB와 테이블을 생성해주어야한다. 

DBeaver 캡쳐

 

나는 DBeaver에서 MySQL을 연결하여 DB와 데이터 테이블을 생성해주었다.

pymysql 라이브러리로 mysql을 연결하는 코드는 위와 같다. 

 

먼저 필요한 라이브러리를 import 해준다.

import pymysql
import pandas as pd

 

그리고 connection 객체를 pymysql의 connect() 함수를 이용해 연결해준다. 

이 때 호스트명, 유저 이름, 패스워드, DB 이름 등이 파라미터로 필요하다.

host_name = '127.0.0.1'
username = 'root'
password = '1234'  
database_name = 'marketing_campaign' 

connection = pymysql.connect(
                             host=host_name,     # MySQL 서버 주소/ 여기선 로컬 호스트
                             user=username,      # MySQL 유저 이름
                             passwd=password,    # 유저 이름에 대한 MySQL 패스워드
                             db=database_name,   # DB명
                             charset='utf8'      # 인코딩 형식
)

 

MySQL 접속이 성공했다면 connection 객체로 부터 cursor() 함수를 호출해 cursor 객체를 가져올 수 있다.

 

cursor란 데이터 베이스에서 데이터를 쿼리해서 가져올 때 사용하는 객체이다. 파일의 포인터와 비슷해서 

cursor를 통해 질의 결과에 직접 접근할 수 있다.

 

cursor = connection.cursor()

 

DB에서 조회하고자 하는 SQL 쿼리를 작성해준 뒤

sql = """
      select * 
      from customer_info 
      where Marital_Status = 'Single'
      """

 

execute() 함수를 이용해 sql을 실행시키고 그 결과를 커서로부터 fetchall 함수를 이용해 받아올 수 있다. 

cursor.execute(sql)
result = cursor.fetchall()

 

fetchall을 통해 받아온 결과(result)는 튜플 형태로 저장되어서 반복문을 통해 출력하는 것이 가능하다.

for i in result : 
    print(i)

 

result의 튜플 자료형을 pandas의 DataFrame() 함수에 집어넣으면 데이터 프레임이 생성되는데 

이 때 컬럼명이 지정되어 있지 않아 0, 1, 2 이렇게 순차적으로 컬럼명이 생성되므로 따로 지정해주어야한다.

customer_info_list = ['ID','Year_Birth','Education','Dt_Customer','Marital_Status','Kidhome','Teenhome','Income']

result_df = pd.DataFrame(res, columns=customer_info_list)
result_df

 

fetchall 함수를 사용하지 않고 pandsd의 read_sql() 함수에 

사전에 정의한 SQL문과 connection을 파라미터로 넣어주게 되면 따로 컬럴명을 지정하지 않아도

조금 더 편하게 SQL 실행 결과를 바로 DataFrame에 넣을 수 있다.

result_df = pd.read_sql(sql, connection)

 

만약 삽입, 갱신, 삭제 등의 쿼리를 작동했다면 connection 객체의 commit() 함수를 이용해서 commit을 해줄 수도 있다.

 

주피터 노트북 환경에서 실행할 때는 큰 문제가 없을 수 있지만

cursor와 connection이 계속 켜져 있으면 다른 프로그램에서는 여러 문제를 일으키기 때문에 불편하니 닫아주자 

 

cursor와 connection의 연결을 close() 함수를 이용해 닫는 것으로 코드가 끝난다.

cursor.close()
connnection.close()

 

전체코드

# 전체 코드

import pandas as pd
import pymysql

connection = pymysql.connect(host='127.0.0.1', 
                             user='root', 
                             password='1234', 
                             db='marketing_campaign', 
                             charset='utf8')
cursor = connection.cursor()

sql = """
      select * 
      from customer_info 
      where Marital_Status = 'Single'
      """
      
result_df = pd.read_sql(sql, connection)
display(result_df)

cursor.close()
connnection.close()