[PYTHON] 구글 스프레드 시트 데이터를 mysql 에 저장하기

0. 목표

 - 구글 스프레드 시트 데이터를 mysql 에 저장하기

1. 사전준비

 1) google api 를 통한 python 과 google spreadsheet 연동

   * 링크 참조

 2) mysql 테이블 생성

  - ADD UNIQUE 를 하는 이유는 나중에 UPSERT 를 하기 위함

CREATE TABLE pbj_db.spreadsheet_test
(
	datetime datetime,
    result int
) ENGINE = INNODB;

ALTER TABLE pbj_db.spreadsheet_test ADD UNIQUE (`datetime`);

 3) 구글시트 더미 데이터 준비

2. 실습

 1) library 호출

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pymysql

 2) 스프레드 시트 연동하기

scope = ['https://spreadsheets.google.com/feeds']
json_file_name = '/Users/pbj0812/Desktop/ouath/발급받은.json'
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name, scope)
gc = gspread.authorize(credentials)
spreadsheet_url = '구글시트 링크'

 3) 시트 연결하기

doc = gc.open_by_url(spreadsheet_url)
worksheet = doc.worksheet('a')

 4) 1열, 2열 데이터 가져오기

  - 첫 줄은 필드명이므로 [1:]

col_date = worksheet.col_values(1)[1:]
col_result = worksheet.col_values(2)[1:]

 5) mysql insert 쿼리 만들기

def insert_query(inp1, inp2):
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', db='pbj_db', charset='utf8')
    cursor = db.cursor()
    sql = '''INSERT INTO pbj_db.spreadsheet_test VALUES(''' + "'"+str(inp1) + "'," + str(inp2) + ''') ON DUPLICATE KEY UPDATE result=result;'''
    cursor.execute(sql)
    result = cursor.fetchall
    db.commit()
    db.close()

 6) 실행

for i in range(len(col_date)):
    insert_query(col_date[i], col_result[i])

3. 결과