[SOPT] 파이썬과 Mysql 그리고 구글스프레드 시트를 연동하여 기획이랑 소통하는 법

오늘은 GO SOPT 32기 앱잼을 하며 똑똑하게 기획과 소통하며 프로젝트에서 더미데이터를 쌓을 수 있었던 방법에 대해 기술하려고 한다

 

사담을 더하자면, 우리 서비스를 요약하자면 건강한 빵집에 대한 정보를 알 수 있고 이 빵집에 대해 리뷰를 남김으로써 다른 사용자와 정보 공유를 할 수 있다

 

사용했던 기술은 다음과 같다

1. Google Spread Sheet

2. Google API

3. python

4. mysql(AWS의 RDS에 구축했다)

5. chatGPT

 

---

1. Google Spread Sheet

기획이 건강한 빵집을 선별하여 필요한 정보를 수집해주었다

서비스에 구축한 데이터베이스에서 필요한 정보 중 이렇게 기획에서 넘겨주어야 할 데이터는 위와 같이 테이블 형태로 미리 양식을 만들어 공유해주었다

 

설명을 위해 필자는 다음과 같은 test 스프레드 시트를 생성했다

 

---

 

2. Google API 설정

1) https://console.cloud.google.com에 접속한다

 

2) google drive api, google sheet api를 다운로드한다

 

3) 연결하고자하는 프로젝트를 생성한다

- 필자는 이미 GEON-BBANG이라는 프로젝트를 생성해둔 상태이다

 

4) API 및 서비스로 이동한다

 

5) 왼쪽 카테고리에서 사용자 인증 정보를 클릭하고 사용자 인증 정보 만들기를 클릭한다

 

6) 사용자 인증 정보 만들기에서 다음과 같이 서비스 계정 만들기를 선택한다

 

7) 서비스 계정의 세부 정보를 적는다

 

8) 권한 설정에서 python 코드로 데이터를 읽어오는 역할만 할 것이기 때문에 viewer로 선택해준다

 

9) 사용자에게 이 서비스 계정에 대한 액세스 권한 부여는 따로하지 않는다

 

10) 다음과 같이 test 계정이 생성된 것을 확인할 수 있다

 

11) 생성된 계정에 대한 액세스 키를 json을 생성한다

 

12) 다운로드 된 json 파일에서 client_email을 확인한다

 

13) 연결하고자하는 google spread sheet를 생성한 서비스 계정의 이메일(위의 사진에서의 client_email)과 공유한다

 

14) 문서의 url을 기억한다

- 필자가 접근하고자 하는 google spread sheet의 url은 다음과 같다

- https://docs.google.com/spreadsheets/d/14LS-YIDMKMSCrRnOXCQYgaJZifeV5Q6rlr-hC9c1DTU

 

---

3. python에서 Google API 및 mysql 사용하기

1) 코드를 작성하기 전 Google API와 mysql을 사용하기 위해 패키지를 설치해주어야 한다

pip3 install oauth2client
pip3 install gspread

pip3 install pymysql

 

2) 다음과 같이 스프레드 시트를 파이썬과 연결한다

- 스프레드시트 URL에는 기억해야 한다고 했던 URL을 넣으면 된다. 이때, 뒤에 /edit?usp=sharing을 붙여야한다

- ex) https://docs.google.com/spreadsheets/d/14LS-YIDMKMSCrRnOXCQYgaJZifeV5Q6rlr-hC9c1DTU/edit?usp=sharing

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
json_file_name = 'json 파일명'

# 스프레드시트 인증 정보 가져오기
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name,scope)

# 스프레드시트 api 인증
gc = gspread.authorize(credentials)
sheet_url = 'https://docs.google.com/spreadsheets/d/14LS-YIDMKMSCrRnOXCQYgaJZifeV5Q6rlr-hC9c1DTU/edit?usp=sharing'

doc = gc.open_by_url(sheet_url)
worksheet = doc.worksheet('시트명')
cell_data = worksheet.acell('값을가져올 셀이름').value
print(cell_data)

- 설명을 위해 설정했던 것들을 위의 python 코드에 적용하여 실행하면 구글 스프레드 시트에 있던 aaa 문자열을 가져오는 것을 확인할 수 있다

 

3) pymysql 연결을 위한 db_config를 생성한다

- 앱잼에서는 RDS를 사용했지만 설명을 위해 local mysql을 이용해보도록 하겠다

- python에서 다음과 같이 local database 정보를 담아둘 db_config를 생성한다

# MySQL 연결 설정
db_config = {
    'host': 'localhost',
    'user': 'id',
    'password': 'password',
    'database': 'database_name',
    'charset': 'utf8'
}

 

4) 데이터를 저장할 database 및 테이블 생성한다

- 다음과 같이 테스트 데이터 베이스를 생성한다

- keyword를 입력받는 테이블을 생성한다

 

5) 데이터베이스에 스프레드 시트에서 가져온 데이터를 insert한다

- 최종 코드는 다음과 같다

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pymysql

scope = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
json_file_name = './geon-bbang-ca0cfed252e9.json'

# MySQL 연결 설정
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'password',
    'database': 'test',
    'charset': 'utf8'
}

# 스프레드시트 인증 정보 가져오기
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name,scope)

# 스프레드시트 api 인증
gc = gspread.authorize(credentials)
sheet_url = 'https://docs.google.com/spreadsheets/d/14LS-YIDMKMSCrRnOXCQYgaJZifeV5Q6rlr-hC9c1DTU/edit?usp=sharing'

doc = gc.open_by_url(sheet_url)
worksheet = doc.worksheet('시트1')
cell_data = worksheet.acell('A1').value
print(cell_data)

# db 연결
conn = pymysql.connect(**db_config)        
cur = conn.cursor()

# 스프레드 시트로부터 가져온 데이터
keyword = cell_data

# db에 넣는 작업
sql = "INSERT INTO keyword (keyword_name) VALUES (%s)"
vals = keyword
cur.execute(sql,vals)
conn.commit()
cur.close()
conn.close()

- mysql로 확인하면 스프레드시트에서 가져온 데이터가 잘 들어가는 것을 확인할 수 있다

 

---

 

4. 마무리

위의 방법대로 한다면 복잡한 테이블 연관관계를 가지는 서비스에서 손쉽게 dummy data 를 쌓을 수 있다

그러나 테이블에 필드가 많다면 pymysql query 작성 과정에서 휴먼에러가 발생할 수도..있다 그럴땐 chatGPT를 사용하자

자신이 작성한 코드를 보내고 sql 쿼리의 오류를 잡아달라는 식으로 요청하면 생각보다 잘 찾아준다

ps. 새벽에 작업하다 쿼리를 못짤 것 같을 때 부탁해도 좋다 ㅎ 2주를 밤새야하는 앱잼에서 지피티는 좋은 친구가 되어주었다 하지만 개인의 능력 향상을 위해서는 직접 짜는 것이 좋다 보안에 있어서도 테이블 구조를 노출시키는 것은 바람직하지 않기 때문이다 ^_^

또한 이번 앱잼을 통해 느낀 것은 기획이랑의 소통은 정말 중요하다는 점이다

더미 데이터를 쌓기 위해 파이썬 코드를 짜면서 느낀점은 테이블에 어떤 데이터를 어떤 형식으로 명확하게 저장해야할지였다

예를 들어 빵집의 휴무일을 예로 들자면

월, 화 휴무

일, 월, 화 휴무

토, 일, 월, 화 휴무

등등.. 한주의 시작을 어디로 잡아야할지에 대한 생각이 들었다

이럴 때 기획과 소통하여 스프레드 시트에 일->토 순으로 쌓아달라고 요청하여 명확한 기준을 가지고 더미데이터를 정제하여 저장할 수 있었다

개발자가 기획에게 서비스에 대한 디테일을 질문하면 오히려 좋아한다 나도 앱잼하면서 알았다 ㅎㅎ

추가적인 회고는 나중에 올리는 것으로 하고 오늘은 기획과 소통하며 구글스프레드시트, python, mysql을 이용하여 더미데이터 쌓는 법을 공유해보았다

모두 행복 앱잼했기를!!!