[업무자동화] DB 테이블 명세서 자동화: Google Spreadsheet api + Python으로 5분 만에 정리하기

😵 테이블 명세서가 없다고?

회사에 처음 입사해서 가장 놀랐던 건,DB 테이블 명세서가 아예 없다는 사실이었다.

나만 그런 게 아니었다.
같이 들어온 경력직 분들도 다들 가장 먼저 “테이블 명세서 어디 있어요?”부터 물어봤다.
하지만 돌아오는 대답은 대부분 “없어요… 직접 보면서 파악하셔야 해요.”

결국 컬럼 하나하나 직접 쿼리 날려보면서 용도 추측하고, 테이블 구조 파악하는 데 시간을 엄청 쓰게 됐다.

그래서 이걸 자동화하면 어떨까 싶었다.
DB에서 테이블 정보 조회해서, 그 결과를 구글 스프레드시트로 자동으로 쏴주는
스크립트를 만들면, 다음 사람들은 최소한 그 고생은 안 해도 되지 않을까..? ㅠㅠ

실제 운영 중인 테이블만 추려내기

일단 시스템 데이터베이스에 얼마나 많은 테이블이 있는지부터 파악해봤다!

-- 1549

SELECT COUNT(*)

FROM information_schema.tables

WHERE table_schema = 'databaseName';

총 1549개가 찍힌다. 물론 이걸 전부 사용하는 건 아닐 거다.

그래서 이번에 서비스 운영을 맡게 되면서,
각 모듈 담당자분들께 실제로 사용하는 테이블 목록과 간단한 설명 자료를 공유해달라고 부탁드렸다.

 

위 사진은 그때 실제로 전달받은 파일 일부이다.
테이블명과 간략한 설명이 정리되어 있었다.

 

 SELECT 
        ORDINAL_POSITION AS 순번,
        COLUMN_NAME AS 컬럼명,
        COLUMN_TYPE AS 데이터타입,
        IF(COLUMN_KEY = 'PRI', '✅', '') AS PK,
        IS_NULLABLE AS NULL허용,
        COLUMN_DEFAULT AS 기본값,
        COLUMN_COMMENT AS 컬럼설명
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'WCHOLIDAY'
    ORDER BY ORDINAL_POSITION

 

이 정보를 바탕으로 데이터베이스에 쿼리를 날려 테이블 상세 정보를 추출했다.

 

추출한 항목은 다음과 같다:
컬럼명, 데이터 타입, PK 여부, NULL 허용 여부, 기본값, 컬럼 설명 등이다.

준비는 끝났다..

이제 쿼리 결과를 Google Spreadsheet API로 자동화해서 테이블 명세서를 만들 계획이다.

 

google spreadsheet api 설정하기

Google 스프레드시트는 구글에서 제공하는 엑셀 같은 서비스인데, 다른 사람이랑 공유도 쉽고, API를 활용하면 Python으로 업무 자동화도 가능하다.

설정 과정이 길어질 것 같아서, 그 부분은 따로 정리해뒀다.

https://choddu.tistory.com/20

[업무자동화] 구글 스프레드시트 API 사용법 (feat. Google Cloud Console)

 

코드 전체 예시

각 모듈별 담당자에게 받은 테이블 내용을 미리 정리해두었다.
그리고 아래는 Google Spreadsheet를 통해 자동화하는 코드다.

import pandas as pd
import pymysql
import gspread
from gspread_dataframe import set_with_dataframe
from oauth2client.service_account import ServiceAccountCredentials
import time

# 1. DB 연결
conn = pymysql.connect(
    host='host 정보',
    port=3306,
    user='user id',
    password='user pw',
    database='접속하려는 database',
    charset='utf8mb4'
)

# 2. 구글 인증
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('/credentials.json', scope)
client = gspread.authorize(creds)

# 3. 스프레드시트 열기
spreadsheet = client.open("테이블 명세서")

# 4. '테이블 목록' 시트 로딩
table_ws = spreadsheet.worksheet("테이블 목록")
data = table_ws.get_all_records()
table_list = pd.DataFrame(data)

# 4-1. '테이블 목록' 시트 ID 저장
table_list_gid = table_ws.id

# 시트 ID 저장용
sheet_links = {}

# 5. 테이블별 명세 업로드
for index, row in table_list.iterrows():
    table_name = row["테이블명"]
    if not table_name:
        continue

    sheet_title = table_name.strip().lower()
    print(f"업로드 중: {table_name}")

    # 해당 테이블 쿼리 검색
    query = f"""
    SELECT 
        ORDINAL_POSITION AS 순번,
        COLUMN_NAME AS 컬럼명,
        COLUMN_TYPE AS 데이터타입,
        IF(COLUMN_KEY = 'PRI', '✅', '') AS PK,
        IS_NULLABLE AS NULL허용,
        COLUMN_DEFAULT AS 기본값,
        COLUMN_COMMENT AS 컬럼설명
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = '{table_name}'
    ORDER BY ORDINAL_POSITION;
    """

    try:
        df = pd.read_sql(query, conn)

        # 기존 시트 삭제
        worksheets = spreadsheet.worksheets()
        for ws in worksheets:
            if ws.title.lower() == sheet_title:
                spreadsheet.del_worksheet(ws)
                break

        # 새 시트 추가
        worksheet = spreadsheet.add_worksheet(title=sheet_title, rows=str(len(df) + 10), cols="10")
        set_with_dataframe(worksheet, df)

        # 시트 ID 저장
        sheet_links[table_name] = worksheet.id

        # 테이블 목록으로 이동 링크 추가
        worksheet.update('I2', f'=HYPERLINK("#gid={table_list_gid}", "← 테이블 목록")')

        time.sleep(5)  

    except Exception as e:
        print(f"❌ {table_name} 처리 중 오류 발생:", e)

# 6. 시트링크 컬럼 추가
table_list["시트링크"] = table_list["테이블명"].apply(
    lambda name: f'=HYPERLINK("#gid={sheet_links.get(name, "")}", "{name}")' if name in sheet_links else ""
)

# 7. 테이블 목록 시트 업데이트
set_with_dataframe(table_ws, table_list)

print("전체 테이블 및 링크 업데이트 완료!")

 

이 코드는 테이블 목록 시트에 적혀 있는 테이블명을 불러와서, 각 테이블 정보를 쿼리로 조회합니다.
조회한 결과는 pandas 데이터프레임으로 정리한 뒤, Google Spreadsheet API를 통해 각 테이블 시트에 자동으로 저장되도록 했어요.

테이블이 워낙 많다 보니, 테이블 목록 시트에는 각 테이블로 바로 이동할 수 있는 하이퍼링크 컬럼을 추가했고,
각 테이블 시트 상단에는 다시 테이블 목록으로 돌아갈 수 있는 링크도 넣어놨습니다.

활용 화면

예를 들어 발령코드 테이블이 궁금하다면,

테이블 목록 시트에서 해당 행의 시트링크 컬럼을 클릭하면 바로 그 테이블 시트로 이동할 수 있어요.
그 시트에서 컬럼명, 데이터 타입, PK 여부 같은 상세 정보를 바로 확인할 수 있고요.

그리고 각 테이블 시트의 I2 셀에는 다시 테이블 목록으로 돌아가는 링크도 넣어놔서 시트 간 이동도 훨씬 편하게 할 수 있습니다.

또 다른 활용 예시로, 여러 테이블 중에서 "급여계좌" 테이블을 찾고 싶다면 Google 스프레드시트의 검색창을 활용하면 됩니다.

또한 "이메일" 컬럼처럼 특정 컬럼명을 찾고 싶을 땐,
검색 범위를 모든 시트 검색으로 설정하면 해당 컬럼이 있는 시트를 빠르게 찾고 바로 이동할 수 있어 정보를 훨씬 효율적으로 확인할 수 있습니다.

 

파이썬 코드를 활용하면, 기존 데이터베이스에 있던 내용을 단 5분 만에 최신 상태로 자동 업데이트할 수 있습니다.
반복적인 수작업 없이, 언제든 최신 테이블 명세를 빠르게 확인할 수 있어요.