API 공공데이터 DB(AWS Aurora)에 삽입하기
API 데이터 활용을 위한 API 키 발급
DATA.GO.KR(공공데이터 포털)에 접속하여 원하는 데이터를 선택한 다음 "활용신청"을 클릭하여 API 서비스 키를 발급받는다
서비스 URL 확인 및 요청/응답 메세지 포맷 확인
참고문서를 클릭하여 "오픈 API 활용 가이드"를 살펴보자
서비스 URL 확인
요청 메세지 항목
요청 메세지를 보낼 때 서비스 URL 뒤에 포함해야할 항목들이다
응답 메세지 항목
요청 메세지를 보냈을 때 받을 수 있는 데이터들이다
요청 메세지 방식과 응답 메세지 상세내용
pageNo=1
numOfRows=10
CreateDT=20200310
endCreateDT=20200315
2020년 3월 10일 부터 2020년 3월 15일 까지의 데이터를 1페이지 당 10개의 결과가 나오도록 요청한다
API 요청을 위해 요청 메세지에 필요한 항목들과 응답 메세지로부터 얻을 수 있는 데이터를 확인했다
이제 필요한 데이터만 추출하고 정제하여 데이터베이스에 저장해보자
필요한 모듈 가져오기(없는 모듈은 설치)
pip install <설치할 패키지명>from urllib.parse import unquote, quote
import xml.etree.ElementTree as et
import json
import requests //HTTP 요청을 보내기 위한 HTTP 라이브러리
import pandas as pd //테이블 형태의 데이터를 다루는 데이터프레임 자료형을 제공
import sys //파이썬 인터프리터가 제공하는 변수와 함수를 직접 제어할 수 있게 해주는 모듈
import time
import boto3
import os
import mysql.connector //파이썬과 데이터베이스를 연결
import csv //csv 파일 읽기, 쓰기
파이썬 코드 분석
str_format은 API 요청을 위한 포맷이고 {0}, {1} ... {4}에는 나중에 만들어질 함수의 인자를 담을 것이다. columns에는 응답받은 데이터 중에서 DB에 저장할 데이터들을 순서대로 정의한다. columns에서 제외된 데이터는 DB에 저장되지 않는다.
그런데 "seq"는 columns에 있는데도 DB에 저장되지 않았는데 그 이유는 다음에 나오는 코드에서 알 수 있다
svc_url = "http://openapi.data.go.kr/openapi/service/rest/Covid19/getCovid19InfStateJson"
serviceKey = " your service key"
str_format = svc_url + "?serviceKey={0}&pageNo={1}&numOfRows={2}&startCreateDt={3}&endCreateDt={4}"
columns = ["seq", "stateDt", "stateTime", "decideCnt", "clearCnt", "examCnt", "deathCnt", "careCnt", "resutlNegCnt",
"accExamCnt", "accExamCompCnt", "accDefRate", "createDt", "updateDt"]
xml.etree를 이용하여 XML 데이터를 객체로 파싱한 뒤 응답받은 데이터(XML 형태)를 트리로 나타낸다
트리에서 Element 단일 노드는 다음과 같다
단일노드: <accDefRate>, <stateTime>, <decideCnt>, ...<resultNegCnt>
def get_covid19_data(startCreateDt, endCreateDt, pageNo=1, numOfRows=10):
q_str = str_format.format(serviceKey, pageNo, numOfRows, startCreateDt, endCreateDt)
res = requests.get(q_str)
xtree = et.fromstring(res.text)
xtree_header = xtree.find('header')
xtree_body_items = xtree.find('body').find('items')
node.finde().text에 의해 각각의 노드 안에 있는 값을 가져와서 리스트 요소 추가 함수(rows.append)를 이용하여 rows 라는 리스트에 추가한다
예) <accDefRate>3.2396602365<accDefRate> ---> 노드: <accDefRate> / 값: 3.2396602365
rows = []
for node in xtree_body_items:
seq = node.find("seq").text
stateDt = node.find("stateDt").text
stateTime = node.find("stateTime").text
decideCnt = node.find("decideCnt").text
clearCnt = node.find("clearCnt").text
examCnt = node.find("examCnt").text
deathCnt = node.find("deathCnt").text
careCnt = node.find("careCnt").text
resutlNegCnt = node.find("resutlNegCnt").text
accExamCnt = node.find("accExamCnt").text
accExamCompCnt = node.find("accExamCompCnt").text
accDefRate = node.find("accDefRate").text
createDt = node.find("createDt").text
updateDt = node.find("updateDt").text
rows.append({"stateDt": stateDt,
"stateTime": stateTime,
"decideCnt": decideCnt,
"clearCnt": clearCnt,
"examCnt": examCnt,
"deathCnt": deathCnt,
"careCnt": careCnt,
"resutlNegCnt": resutlNegCnt,
"accExamCnt": accExamCnt,
"accExamCompCnt": accExamCompCnt,
"accDefRate": accDefRate,
"createDt": createDt,
"updateDt": updateDt})
리스트로 DataFrame(2차원 테이블 데이터 구조를 가지는 자료형)을 생성하여 csv파일로 만든다
여기에서 앞서 seq 데이터가 빠진 이유를 알 수 있는데 columns[1:14]를 보면 0이 아닌 1부터 시작하여 0번째 요소인 "seq"가 빠지고 "stateDt"부터 열을 채우기 때문에 seq가 DB에 저장되지 않았던 것이다
df = pd.DataFrame(rows, columns=columns[1:14])
df.to_csv('covid_db.csv', index=False, header=False)
return df
*__name__ == __main__문은 인터프리터에서 직접 실행했을 경우에만 if문 내의 코드를 돌리게 하는 명령어이다.
sys라는 라이브러리를 import하여 python 이후에 작성된 인자값들이 모조리 출력되도록 한다.
예) 'covid19.py' 라는 이름의 파이썬 파일을 다음과 같이 실행할 때 python python19.py factor1 factor2
['python19.py', 'factor1 ', 'factor2'] 인자값들이 전부 출력된다
이렇게 실행하는 이유는 API 데이터를 받아올 때 오늘 데이터만 받아올건지 또는 특정 기간 동안의 데이터를 받아올건지 나누기 위해서이다. 다음의 코드를 보면 1번째 값이 'period'이면 시작하는 날짜(startdt)와 마지막 날짜(enddt)를 지정하여 특정 기간 동안의 데이터를 받아오고 1번째 값이 'today'이면 시작하는 날짜와 마지막 날짜를 같게 해서 오늘 데이터만 받아온다
if __name__ == '__main__':
arguments = sys.argv
# ['covid19.py', 'pediod', '20210501', '20210501']
if arguments[1] == 'period':
startdt = arguments[2]
enddt = arguments[3]
df = get_covid19_data(startdt, enddt)
# ['covid19.py', 'today']
elif arguments[1] == 'today':
startdt = enddt = time.strftime('%Y%m%d', time.localtime(time.time()))
df = get_covid19_data(startdt, enddt)
print(df)오늘 데이터만 가져오기(20210517)
특정 기간 동안의 데이터 가져오기 (20210401 ~ 20210410)
데이터를 잘 가져오는 것을 확인할 수 있다. 이제는 가져온 데이터를 Aurora에 저장해보자
Aurora 테이블 생성
Aurora에 데이터를 저장하기 전에 미리 테이블과 속성들을 정의해야 한다
테이블 생성이 끝나면 Aurora 엔드포인트와 포트번호, 사용자, 패스워드, 현재 리전, 데이터베이스명을 입력한 다음 Python과 DB를 연결시켜 준다. INSERT문으로 csv 파일의 데이터(콤마로 구분되어있는)를 for~in문을 이용하여 값을 하나하나 뽑아내어 DB에 추가한다.
ENDPOINT=""
PORT="3306"
USR="admin"
PASSWD="admin000"
REGION="ap-northeast-2"
DATABASE="sys"
os.environ['LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN'] = '1'
try:
conn = mysql.connector.connect(host=ENDPOINT, user=USR, passwd=PASSWD, port=PORT, database=DATABASE)
cur = conn.cursor()
sql = 'insert into covid_data_aurora.covid_data (stateDt, stateTime, examCnt, decideCnt, clearCnt, deathCnt, careCnt, resutlNegCnt, accExamCnt, accExamCompCnt, accDefRate, createDt, updateDt) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
f = open('covid_db.csv', 'r', encoding='utf-8')
rd = csv.reader(f)
for line in rd:
cur.execute(sql, (line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], line[8], line[9], line[10], line[11], line[12]))
conn.commit()
conn.close()
f.close()
# cur.execute('insert into covid_data values(df)')
# query_results = cur.fetchall()
# print(query_results)
except Exception as e:
print("Database connection failed due to {}".format(e))
<자주 발생하는 에러 타입 >
1. 데이터가 저장되어 있는 csv 파일을 DB에 insert할 때 주의할 점은 csv 파일의 데이터가 columns 리스트에 있는 속성의 순서대로 저장되어 있기 때문에 그 순서대로 SQL문을 작성해야 한다. 그렇지 않으면 다음과 같은 에러가 출력된다
예) columns = ["seq", "stateDt", "stateTime", "examCnt", "decideCnt" .... updateDt]
sql = 'insert into sys.covid_data (stateDt, stateTime, examCnt, decideCnt .... updateDt)
2. SQL문에서 values (%s, %s, %s .... %s) 개수와 속성의 개수가 맞지 않으면 다음과 같은 에러가 출력된다
예) sql = 'insert into covid_data_aurora.covid_data (stateDt, stateTime, examCnt, decideCnt, clearCnt, deathCnt, careCnt, resutlNegCnt, accExamCnt, accExamCompCnt, accDefRate, createDt, updateDt) ==> 13개
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' ==> 13개
3. for~in 문에서 cur.execute 뒤에 나오는 line[]의 개수가 속성의 개수와 맞지 않으면 다음과 같은 에러가 출력된다
Aurora에 데이터 저장
20200304 ~ 20200331 기간의 데이터가 정상적으로 저장되는 것을 확인할 수 있다
