Python( mariaDB, MySql ) - DB접속, 데이터 조회/변경 하기 ( Pandas DataFrame -> DB -> DataFrame )

회아래 DataFrame 객체를 mariaDB API와 Pandas API를 사용하여 Insert, Select하여 사용법을 비교해본다.

df.tail(5)

	code	Open	High	Low	Close	Volume
Date						
2021-02-16	005930	16900	17500	16400	16500	4405229
2021-02-17	005930	16450	16450	15650	15750	3048389
2021-02-18	005930	15600	15900	15100	15150	2039271
2021-02-19	005930	15150	15600	14650	15250	2114635
2021-02-22	005930	15250	15600	15150	15400	625018

 

Library 설치

pip install pandas 
pip install mariadb # mariadb connector library
pip install sqlalchemy # Pandas query용 engine 생성

 

1. Insert

공통사항: Library import, Connection 객체를 얻는다. 

import pandas as pd
import mariadb
from sqlalchemy import create_engine

conn  = mariadb.connect(
    user='root', 
    password='1234', 
    database='mystock', 
    host='localhost',
    port=3306
)

# 위 커넥션 정보와 동일하게 입력
engine = create_engine("mysql://{user}:{pw}@localhost/{db}".format(user='root', pw='1234', db='mystock'))

  1.1. executemany 사용

data = list(df.itertuples(index=False, name=None))

data[0]

# OUTPUT

[('005930', '2016-02-24', 23560, 23580, 23220, 23440, 140484),
 ('005930', '2016-02-25', 23440, 23740, 23440, 23580, 133635),
 ('005930', '2016-02-26', 23600, 23740, 23440, 23440, 177344),
 ('005930', '2016-02-29', 23580, 23880, 23520, 23560, 288680),
 ('005930', '2016-03-02', 24000, 24140, 23920, 23940, 236372)]

DataFrame을 tuple Array로 변환하여 executemany의 Parameter 인자로 넣는다. 

sql = """
INSERT INTO daily_prices (
    code,
    date,
    open,
    high,
    low,
    close,
    volume
) VALUES (
    ?, ?, ?,
    ?, ?, ?,
    ? 
)
"""

cs = conn.cursor()
cs.executemany(sql, data)
conn.commit()

  1.2. Dataframe API, to_sql 사용

별도의 sql 쿼리문을 작성할 필요 없이 DataFrame column과 Table column이 매핑되어 입력된다.

df.to_sql('daily_prices', engine, if_exists='replace')

Insert 데이터 양이 많은 경우 chunk size, multi를 지정하여 Bulk Insert 처리 할 수있다. 

df.to_sql('daily_prices', engine, if_exists='replace', chunksize=1000, multi='multi')

 

2. Select

select_sql = "SELECT date, open, high, low, close FROM daily_prices WHERE date >= '2021-01-01'"

 

  2.1. execute 사용

cs = conn.cursor()
cs.execute(select_sql)
rs = cs.fetchall()

print(rs[:5])

# OUTPUT
[(datetime.datetime(2021, 1, 4, 0, 0), 81000, 84400, 80200, 83000),
 (datetime.datetime(2021, 1, 5, 0, 0), 81600, 83900, 81600, 83900),
 (datetime.datetime(2021, 1, 6, 0, 0), 83300, 84500, 82100, 82200),
 (datetime.datetime(2021, 1, 7, 0, 0), 82800, 84200, 82700, 82900),
 (datetime.datetime(2021, 1, 8, 0, 0), 83300, 90000, 83000, 88800)]

  #1: 쿼리를 execute 함수로 실행한다.

  #2: fetchall() 함수를 실행하여 결과를 받는다.

  2.1.2. DataFrame 변환

df = pd.DataFrame(rs, columns=['date', 'open', 'high', 'low', 'close'])

df.index = df.date
df = df[df.columns[1:]]

ddf.tail(5)

# OUTPUT

            open	high	low	close
date				
2021-02-16	84500	86000	84200	84900
2021-02-17	83900	84200	83000	83200
2021-02-18	83200	83600	82100	82100
2021-02-19	82300	82800	81000	82600
2021-02-22	83800	84200	83100	83400

  #1: 위에서 얻은 Tuple Array를 DataFrame에 파라미터로 넣어 DataFrame 객체를 얻는다. 

  #2: index를 지정한다.

  #3: date컬럼을 삭제한다. 

  2.2. DataFrame API, read_sql_query사용

selected_df = pd.read_sql_query(select_sql, engine, index_col='date')

query없이 Table의 모든 데이터를 조회하는 경우 read_sql_table을 사용할 수 있다.

 

 

DataFrame을 사용하여 데이터를 조회하거나 조작한다면 Pandas에서 제공하는 API를 사용하는게 더 편리해보인다. SQLAlchemy 을 사용하여 ORM 매핑으로 데이터를 조회하는 것도 방법이다.