파이썬으로 데이터 API를 MySQL에 저장하기

※ 포스팅의 예시는 업비트 개발자 센터 홈페이지에서 API를 받아 데이터로 저장하였습니다.

※ 주피터 노트북 환경에서 단계별 셀 단위로 실행하였습니다.

1.  API 받아오기

업비트 개발자 센터 : https://docs.upbit.com/에 들어가서 원하는 데이터의 API를 받는다. (예시로 분(Minute) 캔들로 진행한다)

import requests

url = "https://api.upbit.com/v1/candles/minutes/1?market=KRW-BTC&count=1"
headers = {"accept": "application/json"}
response = requests.get(url, headers=headers)

print(response.text)

코드 실행 결과

샘플 코드를 실행하면 사진과 같은 값으로 결과가 출력된다. 

2. DataFrame으로 변환하여 csv로 저장

import pandas as pd
import json

tmp = open("tmp.txt", "w")
tmp.write(response.text)
tmp.close()

with open("tmp.txt") as f:
    _tmp = json.loads(f.read())

df = pd.DataFrame(_tmp)
df.to_csv("chart.csv")

코드를 실행하면 "tmp.txt"와 "chart.csv" 두 개의 파일이 생성된다.

3. MySQL과 연동하기

※ Mysql 서버가 실행되어 있는 상황입니다.

CREATE DATABASE upbit;

MySQL에서 데이터를 저장할 데이터 베이스를 만든다.

import pandas as pd 
import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='upbit', charset='utf8') 
curs = conn.cursor(pymysql.cursors.DictCursor)

위 코드를 실행하면 파이썬과 MySQL이 연동된다.

4. 파이썬 코드로 Table 생성하기

sql = '''create table chart(
idx int,
market VARCHAR(10),
candle_date_time_utc VARCHAR(30),
candle_date_time_kst VARCHAR(30),
opening_price double,
high_price double,
low_price double,
trade_price double,
timestamp long,
candle_acc_trade_price double,
candle_acc_trade_volume	double,
unit int);'''
curs.execute(sql)
conn.commit()

저장된 DataFrame의 컬럼들을 받을 Table을 생성할 수 있는 SQL문을 문자열 형태로 저장하여, execute를 해준다.

MySQL에 반영하기 위해서 commit을 실행해줘야 한다.

MySQL 상태창

위 코드가 정상 실행이 됐다면, 파이썬 코드로  MySQL에서 테이블이 생성된다.

5. Table에 데이터 넣기

sql = '''insert into chart
(idx, market, candle_date_time_utc, candle_date_time_kst, opening_price, high_price,
low_price, trade_price, timestamp, candle_acc_trade_price, candle_acc_trade_volume, unit)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))
conn.commit()

위 코드를 실행하면 Dataframe 형태로 저장된 데이터가 테이블로 저장된다.

SELECT * FROM;

데이터를 조회하면 아래처럼 저장된 데이터를 확인할 수 있다.

코드 실행하면 Table에 데이터가 저장된다

6. 파이썬과 연동 종료

curs.close()
conn.close()

작업을 모두 마쳤으면 close로 연동을 끊어준다.

---

마주한 에러와 해결 방법

에러 코드

sql = '''insert into chart
(market, candle_date_time_utc, candle_date_time_kst, opening_price, high_price,
low_price, trade_price, timestamp, candle_acc_trade_price, candle_acc_trade_volume, unit)
values (%s, %s, %s, %f, %f, %f, %f, %d, %f, %f, %d)
'''

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))
conn.commit()

위 코드에서 2가지 에러가 발생했다.

1. TypeError: not all arguments converted during string formatting

API에서 제공하는 컬럼만 생성하여 데이터를 넣어줬더니 계속 에러가 발생했다.

저장된 excel 데이터를 확인하니 데이터 행 가장 앞에 예상하지 못했던 index의 값이 있어서 숫자가 매칭이 안되며 발생한 문제였다.

테이블을 생성할 때 index 값을 받을 수 있는 컬럼을 생성하고 그 값까지 함께 넣어줬다.

2. TypeError: %d format: a real number is required, not str

데이터 타입에 따라 포맷팅을 해주기 위해서 타입에 맞는 반환 변수를 %s, %f, %d을 해줬었다.

하지만 SQL로 보내서 읽으려면 모든 반환 변수를 데이터 타입과 관계없이 %s로 해줘야 한다.

 

수정 코드

sql = '''insert into chart
(idx, market, candle_date_time_utc, candle_date_time_kst, opening_price, high_price,
low_price, trade_price, timestamp, candle_acc_trade_price, candle_acc_trade_volume, unit)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))
conn.commit()