파이썬 dataframe mysql insert하기

주식 데이터를 분석하면서 주가 데이터를 매번 가져와 전처리하기 보다 나만의 db가 있었으면 해서  mysql에 다양한 주식 데이터를 

올릴려고 한다 

 

우선 파이썬에서 mysql을 사용하는 여러 방법을 간단하게 정리하려한다

 

1. mysql.connector

conn = pymysql.connect(host='localhost',
                       user='root',
                       password='비번',
                       db='Stock',
                       charset='utf8')

sql = """INSERT INTO Weather VALUES (%s, %s, %s, %s, %s)"""
for i, row in weather.iterrows():
    cur.execute(sql, tuple(row))
    conn_aws.commit()
with conn:
    with conn.cursor() as cur:
        cur.execute(sql)
        conn.commit()

위 방식은 insert할때 매 sql문을 execute(실행)해주면 속도가 매우 느리다. 

그래서 사용한게 executemany이다 

2.executemany

# dataframe = user -> list setting
# tqdm : we can see the progress status of 'for iteration'
from tqdm import tqdm
data = []
for i, row in tqdm(user.iterrows()):
    data.append(tuple(row))

# AWS Connection
import mysql.connector
conn_aws = mysql.connector.connect(
    host = "[Host Address]",
    port = [Port],
    user = "[User Name]",
    password = "[password]", 
    database = "[Database Name]"
)

# sql writing and commit
sql = """INSERT INTO User (bicycle_index, time_start, stop_index1, stop_1, count_stop1, time_end, stop_index2, stop_2, count_stop2, cycle, distance) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
cur = conn_aws.cursor(buffered=True)
cur.executemany(sql, data)
conn_aws.commit()

속도는 훨씬 빠르지만 메모리 사용량이 많다. 

 

3.sqlalchemy(create_engine) + to_sql  이용하기

import pandas as pd
import pymysql
from sqlalchemy import create_engine
df = pd.read_csv("csvFile/stock2019.csv",index_col=0)
df.columns = ["ticker","open1","high","low","end1","volume","mount","rate","day1","name1"]
#중복된 값들 제거
df.drop_duplicates(['ticker',"day1"], keep='first',inplace=True)
print(df.shape)
engine = create_engine("mysql+pymysql://root:"+"비번"+"@localhost/df이름", encoding='utf-8')
df.to_sql(name ='price', con = engine, if_exists='append', index=False)

df을 to_sql로 통째로 업로드해서 위 두 방법보다 훨씬 빠르다. 

주의할점은 df와 db table을 columns의 이름이 같아야한다는 점이다. 

 

참고 사이트 

https://go-for-data.tistory.com/entry/01SQLBulkInsert