따릉이 데이터 저장

공공API에서 데이터를 받아서 SQL에 넣어주는 작업을 진행중이다...

먼저 API를 사용하려고 사용 신청을 했다.

현재로써는 python말고는 사용할줄 몰라서 python으로 코드를 작성

미리 테이블을 생성

일단은 이렇게 했는데 추후에 바뀔수도 있음
넘어오는 데이터는 다 넣어주었다.

python으로 sql 연결하고 API데이터 받아와서 넣기...

코드가 무진장 길지만 잘라서 설명을 적어보면...

1. 필요한 라이브러리 import, mysql과 연결하기 위한 engine만들기

import json
import requests
import pymysql
from sqlalchemy import text, create_engine
from datetime import date, timedelta

engine = create_engine('mysql+pymysql://bike:dbgusals1@localhost:3306/use_bike')

2. for문을 쓴 이유

출력해서 보니 대여소가 총 2873개소였고 데이터는 시간당 데이터이기 때문에...

for h in range(24): #시간
	for i in range(1, 2002, 1000):#대여소

3. 당일이 아닌 어제 데이터를 받아옴

전날 데이터를 받아오기 위해 date.today()를 사용하여 오늘 날짜를 받아오고
timedelta(1)을 현재 날짜에서 빼주었다.

today = date.today()
yesterday = date.today() - timedelta(1)

4. API접근 URL 생성

이거 왜 두 개야 할 텐데... 1 - 2873개씩 가져와야 함.
하지만 2001부터 1000개를 하면 2873을 넘기 때문에 중복을 피해 주기 위해서 2001부터는 직접 index를 지정해서 받아왔다.
더 좋은 방법이 있으면 알려주시면 좋겠습니다

        if i == 2001:
            url = 	f'http://openapi.seoul.go.kr:8088/707950714679377934386e64746352/json/bikeListHist/{2001}/{2872}/{yesterday.strftime("%Y%m%d")}{h}'

        else:
            url = f'http://openapi.seoul.go.kr:8088/707950714679377934386e64746352/json/bikeListHist/{i}/{i + 999}/{yesterday.strftime("%Y%m%d")}{h}'

5. API 접근해서 데이터 받기

api에 접근해서 데이터를 받아왔다.
json 형식으로 받아오기 때문에 requests.get(url)을 이용하여 데이터 전체를 받아오고
해당하는 데이터만 뽑기 위해 .get을 사용했다.

        response = requests.get(url)
        json_ob = json.loads(response.content)
        json_ar = json_ob.get('getStationListHist')
        json_ar1 = json_ar.get('row')

6. sql문 작성

문자열을 이용하여 sql문을 작성하였다. f''를 이용하여 작성해도 된다.

sql = """
        insert into bike(Date, stationId, stationName, rackTotCnt, parkingBike, shared, stationLatitude, stationLongitude)
        values(
        :Date,
        :stationId,
        :stationName,
        :rackTotCnt,
        :parkingBike,
        :shared,
        :stationLatitude,
        :stationLongitude
        )
        """

7. for문을 이용하여 데이터 자르기(?)

json에서 필요한 데이터를 가져오기 위해 .get을 이용하여 해당하는 데이터를 받아옴.

        for i in json_ar1:
            stationName = i.get('stationName')
            stationId = i.get('stationId')
            parkingBike = i.get('parkingBikeTotCnt')
            shared = i.get('shared')
            rackTotCnt = i.get('rackTotCnt')
            stationLatitude = i.get('stationLatitude')
            stationLongitude = i.get('stationLongitude')

8. 대여소 이름 정제

대여소 이름 대부분이 처음 시작이 숫자였다. 하지만 한 개는 그렇지 않았다.
맨 앞의 숫자와 공백 맨 뒤의 공백을 제거해 주고 변수에 저장.

            if stationName[0].isdigit():
                stationName = stationName[5:].lstrip().rstrip()

9. 딕셔너리 형식으로 각 데이터 저장

딕셔너리 형식으로 데이터를 저장했다.
날짜는 년-월-일 시간:분:초 형식을 사용.
f''를 사용하면 편하다.

 dt = {"Date": f'{yesterday.strftime("%Y-%m-%d")}" "{h}', "stationId": stationId,
                  "stationName": stationName,
                  "parkingBike": parkingBike, 'shared': shared, 'rackTotCnt': rackTotCnt,
                  'stationLatitude': stationLatitude, 'stationLongitude': stationLongitude}

10. 쿼리 실행

execute를 사용하여 쿼리를 실행
dt안에 있는 모든 값을 앞에서 작성했던 쿼리에 매칭시킨다.

engine.execute(text(sql), **dt)

11. 전체 코드

import json
import requests
import pymysql
from sqlalchemy import text, create_engine
from datetime import date, timedelta

engine = create_engine('mysql+pymysql://bike:dbgusals1@localhost:3306/use_bike')

for h in range(24):
    for i in range(1, 2002, 1000):
        today = date.today()
        yesterday = date.today() - timedelta(1)

        if i == 2001:
            url = f'http://openapi.seoul.go.kr:8088/707950714679377934386e64746352/json/bikeListHist/{2001}/{2872}/{yesterday.strftime("%Y%m%d")}{h}'

        else:
            url = f'http://openapi.seoul.go.kr:8088/707950714679377934386e64746352/json/bikeListHist/{i}/{i + 999}/{yesterday.strftime("%Y%m%d")}{h}'

        response = requests.get(url)
        json_ob = json.loads(response.content)
        json_ar = json_ob.get('getStationListHist')
        json_ar1 = json_ar.get('row')

        sql = """
        insert into bike(Date, stationId, stationName, rackTotCnt, parkingBike, shared, stationLatitude, stationLongitude)
        values(
        :Date,
        :stationId,
        :stationName,
        :rackTotCnt,
        :parkingBike,
        :shared,
        :stationLatitude,
        :stationLongitude
        )
        """

        for i in json_ar1:
            stationName = i.get('stationName')
            stationId = i.get('stationId')
            parkingBike = i.get('parkingBikeTotCnt')
            shared = i.get('shared')
            rackTotCnt = i.get('rackTotCnt')
            stationLatitude = i.get('stationLatitude')
            stationLongitude = i.get('stationLongitude')

            if stationName[0].isdigit():
                stationName = stationName[5:].lstrip().rstrip()

            dt = {"Date": f'{yesterday.strftime("%Y-%m-%d")}" "{h}', "stationId": stationId,
                  "stationName": stationName,
                  "parkingBike": parkingBike, 'shared': shared, 'rackTotCnt': rackTotCnt,
                  'stationLatitude': stationLatitude, 'stationLongitude': stationLongitude}
            engine.execute(text(sql), **dt)

12. ....ㅠㅠ

이거 하나 하는데 엄청나게 오랜 시간이 걸렸다... sql도 잘 모르고.. pymysql도 잘 모르고... 일단 계속해야 하니까... 데이터는 이 정도만 저장하고 다음 데이터로 넘어가야겠다.