이번에는 mariadb 혹은 mysql을 python에서 사용하기 위한 연동방법의 두번째로 python에서 mariadb(mysql) 사용하기 두번째 방법을 알아보겠습니다. 이전에는 connect객체, cursor객체를 사용한 일반적인 방법에 대해서 알아보았습니다. 하지만 다른 데이터조작 python package에서도 dbms에서 접근해서 데이터를 쌓고, 데이터를 가져오는 방법이 있다는 것을 들어보셨나요??
pandas가 바로 그 녀석입니다. pandas는 데이터 조작 및 분석을 위한 Python 패키지 입니다. 이를 통해서 Series, DataFrame등 테이블 및 시계열을 조작하기위한 데이터 구조를 제공하는 녀석입니다.
pandas - Python Data Analysis Library
pandas pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language. Install pandas now!
pandas.pydata.org
사실 이거 하나만 해도, 엄청난 분량의 학습할 내용이 있습니다... 하지만 그건 다음에 여유가 있으면 차근차근 다루어 보도록하고... (사실 Tensorflow를 통한 데이터 분석을 한다고 해도 pandas는 필수하고 해도 과언이 아닙니다.) 이번에는 pandas에서 제공하는 sql 관리 방법에 대해서 알아보겠습니다.
아참 pandas는 anaconda설치 시, 자동을 설치되는 패키지 입니다.
위의 pandas 공식 사이트에 오른쪽 아래에 보면 Previous versions가 있는데... 현재 우리가 설치된 버전인 0.25.1에 대한 pdf도 제공하기 때문에, 받아서 보셔도 참 좋을 듯 싶습니다. 그럼 본격적으로 들어가서...
1. to_sql
가장 좋은 방법인 공식사이트에서 찾아보겠습니다. 일단 단순히 sql이라고 검색합니다.
DataFrame도 Series도 모두 to_sql메서드를 제공합니다. 선택해서 들어가보면 아래와 같이 작성하면 된다고 아주 상세히 가이드가 되어있습니다.
[pandas.DataFrame.to_sql]
DataFrame.to_sql(
name: str (저장하고자 하는 SQL table명)
, con (SQLAlchemy connectable 객체)
, schema=None (선택사항, default none)
, if_exists: str = 'fail' (해당테이블이 존재할 경우 선택, fail/replace/append)
, index: bool = True (Dataframe의 index를 컬럼에 포함할지 선택)
, index_label=None (위에 index를 포함할때 index의 label명 지정)
, chunksize=None (한번에 넣을 row 갯수 지정)
, dtype=None
, method=None)자 여기서 대부분 그냥 작성할 수 있습니다. 하지만, 특이한 점이 하나있네요?? 바로 SQLAlchemy connectable객체... 가 뭘까요?? 아무래도 이전에 했던 mysql.connector와 비슷한 아이일까요??
이를 pip에서 찾아보면... The Python SQL Toolkit and Object Relational Mapper 라고 표현되어 있습니다. 혹시나 anaconda는 설치를 자동해줬을까요??
네 기본 패키지 맞습니다. 이걸 어떻게 사용해야 할까요?? python repository인 pipy.org에서 찾아보면... 아참!! 우리는 가상환경을 사용하기 때문에 아래와 같이 가상환경을 활성화 해서 설치를 진행해야 합니다.
conda activate py37_32
conda list sqlalchemy
pip install SQLAlchemy
이렇게 하단에 docs 주소가 걸려있습니다. 이곳에 들어가서 connect로 검색해보니 객체를 생성하는 법이 설명되어 있습니다.
그렇다면...!! 실제 코딩을 해보겠습니다.
from sqlalchemy import create_engine
engine = create_engine('mysql://root:' + '<my password>' + '@localhost/at_project')[Error]
ModuleNotFoundError: No module named 'MySQLdb' 가 발생합니다. python에서는 MySQLdb를 지원하지 않기 때문에 별도의 패키지를 사용해야하며, pandas의 to_sql을 위해서 sqlalchemy를 사용하기 위해서는 MySQLdb와 호환되는 패키지를 찾아야 합니다. 이렇게 찾은 아이는 pymysql 입니다. mysql.connector와 사용법은 대동소이하며, 많이 사용되는 패키지 입니다.
MIT license이네요. 일단 사용하기로 합니다. 당연히 현재 가상환경에 없기때문에 설치를 해줍니다.
pymysql에는 MySQLdb로 보이게 해주는 메서드가 있습니다. 해당 메서드를 실행하고 import를 해주면 MySQLdb가 있다고 인식하게 되고 정상적으로 create_engine이 실행됩니다.
[to_sql]
import pandas as pd
import pymysql
from sqlalchemy import create_engine
pymysql.install_as_MySQLdb()
data = {
'날짜' : ['2020-02-25','2020-02-24','2020-02-23'],
'최고가' : [12000, 13000, 14000],
'최저가' : [6000, 7000, 8000]
}
pd_data = pd.DataFrame(data)
print(pd_data)
engine = create_engine('mysql://root:' + '[password]' + '@localhost/at_project', encoding='utf-8')
pd_data.to_sql('test', engine, if_exists='replace', index=False, index_label=None, chunksize=500)
#############################################
날짜 최고가 최저가
0 2020-02-25 12000 6000
1 2020-02-24 13000 7000
2 2020-02-23 14000 8000
정상적으로 잘 들어갔습니다.
2. read_sql
이번에는 dbms에 있는 데이터를 한번에 읽어와서 dataframe객체에 넣어주는 매서드를 살펴보겠습니다.
[pandas.read_sql]
pandas.read_sql(
sql (실제 sql쿼리)
, con (SQLAlchemy connectable 객체)
, index_col=None (index 컬럼명)
, coerce_float=True (float value로 변경)
, chunksize=None (한번에 뽑을 row 갯수 지정)
)자 여기서 대부분 그냥 작성할 수 있습니다. 역시 SQLAlchemy connectable객체... 가 있습니다. 이제는 고민없이 작성해봅니다. 여기서 추가적으로 확인해 볼 내용은 chunksize를 지정해서 할 때 입니다. 그럼 실제 코드로 확인해 보겠습니다.
selectSql = 'SELECT * FROM TEST'
pd_read_data1 = pd.read_sql(selectSql, engine, index_col=None, chunksize=None)
pd_read_data2 = pd.read_sql(selectSql, engine, index_col='날짜', chunksize=None)
pd_read_data3 = pd.read_sql(selectSql, engine, index_col='날짜', chunksize=2)
print(pd_read_data1)
print(pd_read_data2)
print(pd_read_data3)
#################################################################################
날짜 최고가 최저가
0 2020-02-25 12000 6000
1 2020-02-24 13000 7000
2 2020-02-23 14000 8000
최고가 최저가
날짜
2020-02-25 12000 6000
2020-02-24 13000 7000
2020-02-23 14000 8000
<generator object SQLDatabase._query_iterator at 0x08342DF0>이제는 별로 어려울 부분이 없습니다. 원하는 sql문을 작성하고, read_sql을 통해서 가져온 후 출력할 뿐입니다. 이중에서 차이는 table의 내용을 뽑아서 특정 필드를 index로 할 것인지 아닌지와 한번에 몇개씩 뽑아올 것인지에 대한 내용입니다.
첫번째 테스트는 index를 특별히 지정하지 않아서, pandas의 DataFrame에서 자동으로 index를 추가한 경우이고, 두번째 테스트는 index를 날짜로 지정한 경우입니다. 마지막으로 chunksize를 두어 해당 단위로 나눠서 데이터를 가져오는 경우입니다.
마지막의 경우는 iterator 형으로 리턴이 되기 때문에 별도의 작업이 필요합니다.
[chunksize gethering]
res = pd.DataFrame()
for i in pd_read_data3:
res = pd.concat([res, i])
print(res)
print(res.loc['2020-02-24'])
print(res.loc['2020-02-24']['최고가'])
###############################
최고가 최저가
날짜
2020-02-25 12000 6000
2020-02-24 13000 7000
2020-02-23 14000 8000
최고가 13000
최저가 7000
Name: 2020-02-24, dtype: int64
13000iter 형이기 때문에 for loop를 통해서 반복해서 가져옵니다. 우선 빈 DataFrame을 하나 만들고 concat을 통해서 모두 합쳐주면 됩니다. 이때 concat의 대상은 list로 묶어서 전달합니다. 추가적으로 가져온 데이터를 행 단위로 뽑기위해서는 2차원 배열에 접근하듯이 [행][열]로 하면 안되고... loc라는 메서드로 뽑아내야 합니다.
이렇게 DB를 접근하는 2가지 방법에 대해서 알아보았습니다. 다음에는 현재 테스트한 소스를 refectoring하여 정식 코드로 만들고, 이후 실제 DB설계를 하여 데이터를 저장해 보겠습니다.
-Ayotera Lab-
댓글