PyMySQL과 pandas library | COSADAMA Curriculum

PyMySQL을 사용하여 DB를 조작할 때, SELECT와 fetch를 사용하면 원하는 데이터를 python notebook에서 확인할 수 있습니다. fetch의 종류는

fetchone(): 한 행row만 가져옵니다.
fetchmany(size= ): size만큼의 row를 가져옵니다.
fetchall(): 모든 row를 가져옵니다.

등이 있습니다. 아래의 예시를 통해 확인해봅시다!

import pymysql

host_name = 'localhost'
host_port = 3306
username = 'root'
password = '*********'
database_name = 'your_db_name'

db = pymysql.connect(
     host=host_name,     # MySQL Server Address
     port=host_port,     # MySQL Server Port
     user=username,      # MySQL username
     passwd=password,    # password for MySQL username
     db=database_name,   # Database name
     charset='utf8'
 )
db
cursor = db.cursor()

이 실습에서는 이전 '4.6 cmd로 데이터 적재하기'에서 DB에 저장한 데이터를 사용합니다. 아래 코드는 예시일 뿐, 가지고 있는 DB의 TABLE로 실습하시면 됩니다!

# 전체 데이터를 한 행만 가져와 봅시다.
sql = "SELECT * FROM your_table_name"
cursor.execute(sql)
bus1 = cursor.fetchone()

꽤 오래 걸리죠? 제 데스크탑으로는 14.6초 정도 걸리네요. 더 걸리더라도 너무 놀라지 마세요:)

이렇게 하면 결과값은 list 형식으로 출력됩니다. 그렇다면, 우리가 익숙한 dataframe으로 db의 데이터를 가져오는 방법은 없을까요? 물론 있습니다. 바로 익숙한 pandas를 활용해서 말이죠!

사용법은 변수명 = fetchall() 와 같은 형식으로 저장한 후, df명 = pd.dataframe(변수명)으로 데이터프레임을 만들어 주면 끝! 아직 이해가 되지 않는다면 아래 예시를 활용해보세요.

import pandas as pd

bus1 = pd.DataFrame(bus1)
bus1

type(bus1.columns)
# 결과: pandas.core.indexes.range.RangeIndex

컬럼 이름이 rangeindex로 나오는군요 먼저 행열 전환을 해주고, 원래 컬럼명으로 바꿔서 저장해줍시다. pandas 조작법을 그대로 사용하면 됩니다!

import pandas as pd

bus1 = pd.DataFrame(bus1)
bus1.columns = ['사용일자', '노선번호', '노선명', '버스정류장ARS번호', '역명', '승차총승객수', '하차총승객수',
 '등록일자', 'id']

bus1

이제 '승차총승객수'가 1000이 넘는 엄청난 버스 노선이 있는지 해당 데이터만 가져와 봅시다.

# 승차총승객수가 1000이 넘는 엄청난 버스 노선이 있을까요?
sql = "SELECT * FROM your_table_name where 승차총승객수 >1000"
cursor.execute(sql)
bus1000_10 = cursor.fetchmany(size=10)

import pandas as pd

bus1000_10 = pd.DataFrame(bus1000_10)
bus1000_10.columns = ['사용일자', '노선번호', '노선명', '버스정류장ARS번호', '역명', '승차총승객수', '하차총승객수',
 '등록일자', 'id']

bus1000_10

fetchmany10

승차총승객수가 1000이상인 데이터를 다 가져와 봅시다.

# 승차총승객수가 1000이 넘는 엄청난 버스 노선이 있을까요?
sql = "SELECT * FROM your_table_name where 승차총승객수 >1000"
cursor.execute(sql)
bus1000_all = cursor.fetchall()

import pandas as pd

bus1000_all = pd.DataFrame(bus1000_all)
bus1000_all.columns = ['사용일자', '노선번호', '노선명', '버스정류장ARS번호', '역명', '승차총승객수', '하차총승객수',
 '등록일자', 'id']

bus1000_all

fetchall

또한 아래와 같이 포매팅(%)을 통해 다양한 데이터를 한번에 가져올 수도 있습니다. 보유하고 있는 DB의 TABLE에 아래 예시 코드를 참고하여 적용 결과를 확인해보세요.

# SELECT 
sql = "SELECT * FROM your_table_name WHERE 노선번호 REGEXP '^[강남]';"
cursor.execute(sql)

# DELETE
sql = "DELETE FROM your_table_name WHERE 노선번호 REGEXP '^[강남]';"
cursor.execute(sql)
db.commit()

data = [['사용일자', 원하는_날짜], ['노선번호', 원하는_번호], ['노선명', 원하는_노선명], ['버스정류장ARS번호',원하는_번호], 
['역명', 원하는_역명], ['승차총승객수', 원하는_숫자], ['하차총승객수', 원하는_숫자], ['등록일자', 원하는_날짜], ['id', 원하는_id]]

# INSERT 
sql = """INSERT INTO your_table_name('사용일자', '노선번호', '노선명', '버스정류장ARS번호', '역명', '승차총승객수', '하차총승객수',
 '등록일자', 'id') VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);
 """
 
cursor.executemany(sql, data)
db.commit()

data = [['노선명', 10000], ['노선명', 10000]]

# UPDATE 
sql = "UPDATE your_table_name SET `노선명` = %s WHERE `id` < %s;"
cursor.executemany(sql, data)
db.commit()