[파이썬] 프로젝트 : 웹 페이지 구축 - 6(DB 연동)

이전 내용

[파이썬] 프로젝트 : 대시보드 웹 페이지 구축하기 - 5

---

Streamlit에 CSV 파일 연동하기

 

현재까지 진행 사항은

• 데이터 분석을 위한 가상의 데이터 생성하기 ▶ 오프라인, 온라인 데이터 2개
• 생성된 데이터로 데이터 분석 및 시각화 진행하기 ▶ 오프라인 데이터만 진행
• 머신러닝 모델 만들기 (보완 필요)

이러하다.

이제 이 데이터를 웹 페이지에 띄우는데, MySQL 과 연동시켜 DB로 저장될 수 있게끔 하려고 한다.

그래서 먼저 MySQL Workbench에서 recycle 이라는 Database를 생성해줬다.

CREATE DATABASE recycle;

 

그리고나서 VSCode로 넘어와서 DB를 연동시키고 테이블을 생성하여 csv 파일을 해당 테이블에 삽입하고 조회하여 데이터프레임으로 띄우는 작업을 진행해 본다.

순서를 다시 한 번 정리하면,

파이썬과 DB 연동 ▶ 데이터베이스 테이블 생성 ▶ CSV 파일 로드 ▶ 생성된 테이블에 CSV 상의 데이터 삽입
▶ 데이터 삽입 후 조회 ▶ 판다스를 이용해 데이터프레임으로 변경 ▶ Streamlit을 이용해 웹 페이지로 띄우기

 

여태까지 수업(+실습) 시간에 다뤘던 코드들이 여러 종류들이라 이 중에 맞는 것을 가져와 해당 데이터에 맞게 수정해 주는 작업이 필요했다. (DB 불러오는 게 그 첫시작인데 좀 헤맸다.)

 

1. 필요한 라이브러리 import 및 DB 연동시키는 함수 정의(+기타)

우선, DB 로그인 정보를 secrets.toml 파일로부터 가져올 것이므로, 해당 Streamlit 의 .py 파일이 있는 폴더에 '.streamlit' 이라는 폴더를 생성하여 toml 파일을 넣어놔야 한다.

CSV 파일은 필자의 깃허브에 올려둬서 해당 링크로부터 불러온다. DB의 테이블명을 미리 지정해줘서 후에 다른 테이블을 생성 시 해당 변수만 수정하면 되도록 해준다.

 

아래는 그 부분의 코드다.

import streamlit as st
import pandas as pd
import mysql.connector
from mysql.connector import Error
import numpy as np

# CSV 파일 경로 설정 (깃허브 raw url)
CSV_FILE_PATH = 'https://raw.githubusercontent.com/..../recycling_off.csv'

# 테이블 이름 설정
DB_TABLE = 'recycle_off'

# 메인 페이지 너비 넓게 (가장 처음에 설정해야 함)
st.set_page_config(layout="wide") 

# MySQL 연결 정보 (secrets.toml에서 가져옴)
def get_db_connection():
    try:
        secrets = st.secrets["mysql"]
        connection = mysql.connector.connect(
            host=secrets["host"],
            database=secrets["database"],
            user=secrets["user"],
            password=secrets["password"]
        )
        if connection.is_connected():
            st.write()
        return connection
    except Error as e:
        st.error(f"데이터베이스 연결 실패: {e}")
        return None

---

2. DB 연동 시킨 후 테이블 생성 및 데이터 삽입하는 함수 정의

그다음으로는 불러온 DB를 가지고 테이블을 생성하고, 해당 테이블에 데이터를 삽입해 주는 함수를 정의한다.

쿼리문의 칼럼명과 CSV 파일의 칼럼명은 일치해야 한다. 

그리고! 중요한 것!

컬럼명에 띄어쓰기가 들어가 있을 경우에는 그대로 넣으면 인식을 못하므로 꼭 `(백틱) 으로 감싸줘야 한다.

수업시간에 들었는데 까맣게 잊고 있다가 테이블 생성 및 데이터 삽입이 계속 안 되어 왜 그럴까 고민하다 다시 기억해 냈다.

 

또한, 테이블 생성 및 데이터 삽입은 매번 호출되면 비효율적이므로, 데코레이터(@)를 사용하여 내용이 변경되었을 때만 함수가 호출되도록 설정해 주었다.

# 프로그램에서 테이블 생성
@st.cache_data
def create_table(_connection):  # 인자 이름에 "_" 추가
    try:
        cursor = _connection.cursor()
        create_table_query = f"""
        CREATE TABLE IF NOT EXISTS {DB_TABLE} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            날짜 DATETIME,
            지역 VARCHAR(20),
            방문자수 INT,
            연령대 VARCHAR(20),
            성별 VARCHAR(10),
            `이벤트 종류` VARCHAR(20),
            참여자수 INT,
            참여비율 FLOAT
        );
        """
        cursor.execute(create_table_query)  # 쿼리 실행
        _connection.commit()
        print(f"'{DB_TABLE}' 테이블이 성공적으로 생성되었습니다.")
    except Error as e:
        st.error(f"테이블 생성 중 오류 발생: {e}")
    finally:
        cursor.close()

# 데이터 삽입 함수
@st.cache_data
def insert_data(_connection, df):  # 인자 이름에 "_" 추가
    try:
        cursor = _connection.cursor()
        insert_query = f"""
        INSERT INTO {DB_TABLE} (
            날짜, 지역, 방문자수, 연령대, 성별, `이벤트 종류`, 참여자수, 참여비율
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """

        # 판다스 데이터프레임을 numpy 배열로 만들어 배열을 하나씩 꺼내 튜플로 생성
        data = [tuple(row) for row in df.values]
        cursor.executemany(insert_query, data)  # 여러 줄 처리
        _connection.commit()
        print(f"{cursor.rowcount}개의 레코드가 '{DB_TABLE}' 테이블에 삽입되었습니다.")
    
    except Error as e:
        st.error(f"데이터 삽입 중 오류 발생: {e}")
    finally:
        cursor.close()

---

3. 데이터 가져오는 함수 적용

테이블을 생성하고 해당 테이블에 데이터를 적용했으면 이제 이를 조회하는 함수를 적용해 준다.

그리고 테이블을 가져와서 웹 페이지에 데이터프레임으로 띄우기 위한 작업도 추가해 준다.

 

이때, 후에 데이터 분석 시 변수 적용에 문제가 없게끔 한글로 되어 있는 칼럼명들을 영어로 변경해 준다.

# 데이터 가져오기 (캐싱 적용)
@st.cache_data
def get_data():
    connection = get_db_connection()
    if connection is None:
        return pd.DataFrame()
    
    try:
        query = f"SELECT * FROM {DB_TABLE}"
        df = pd.read_sql(query, connection)
        # 열 이름 조정
        df = df.rename(columns={
            "날짜":"DATE", 
            "지역":"CITY", 
            "방문자수":"VISITORS", 
            "연령대":"age", 
            "성별":"gender", 
            "이벤트 종류":"CAMP", 
            "참여자수":"PART", 
            "참여비율":"P_Ratio"
        })
        return df
    except Error as e:
        print(f"데이터 조회 중 오류 발생: {e}")
        return pd.DataFrame()
    finally:
        if connection.is_connected():
            connection.close()

---

4. 메인 함수 설정

이제 메인 함수를 설정하여 진행해야 할 일련의 과정들을 넣어준다.

단, 데이터프레임을 불러올 때 inf (값의 무한대) 값을 결측값으로 바꾸어, 데이터 분석 과정에서 발생할 수 있는 오류나 문제를 예방 한다.

# 메인 함수
def main():
    
    # CSV 파일 읽기
    try:
        df = pd.read_csv(CSV_FILE_PATH, encoding="UTF8")
        print("CSV 파일을 성공적으로 읽었습니다.")
        df.replace([np.inf, -np.inf], np.nan, inplace=True)
    except FileNotFoundError:
        st.error(f"파일 '{CSV_FILE_PATH}'을 찾을 수 없습니다.")
        return
    except Exception as e:
        st.error(f"CSV 파일 읽기 중 오류 발생: {e}")
        return

    # 데이터베이스 연결
    connection = get_db_connection()
    if connection is None:
        return

    # 테이블 생성
    create_table(connection)  # 호출 시 변경된 인자 사용

    # 데이터 삽입
    insert_data(connection, df)  # 호출 시 변경된 인자 사용

    # 연결 종료
    if connection.is_connected():
        connection.close()

# 메인 함수 실행
if __name__ == "__main__":
    main()

---

5. Streamlit 페이지에 띄우기

마지막으로 Streamlit에 해당 데이터를 띄우기 위한 코드를 작성한 뒤

# Streamlit UI
st.title("오프라인 마케팅 데이터")

if st.button("데이터 조회"):
    df = get_data()
    if not df.empty:
        st.dataframe(df)  # 데이터프레임 출력
    else:
        st.warning("조회된 데이터가 없습니다.")

 

터미널에서 Streamlit run 으로 실행하면!

---

웹 페이지 레이아웃 짜보기

 

이제 DB 연동은 됐으므로, 웹 페이지를 어떻게 구성할 지 대략적인 레이아웃을 짜봐야 한다.

레이아웃을 짜기 위해 많은 시간을 활용하기엔 효율성이 떨어지므로, 대략적인 레이아웃만 짜본다.

• 데이터가 두 개라서 슬라이더를 적용하면 데이터의 칼럼이 다를 경우 오류가 발생할 수 있어 두 데이터의 공통 항목인 날짜, 그리고 이를 토대로 요일(애초에 CSV 생성 시 같이 만들었으면 좋았으련만.. 지금 생성하게 되면 팀원분들이 전부 쿼리를 수정해야 하므로 불러온 데이터프레임에서 만들기로 한다.) 두 가지 기준으로 각각 슬라이더와 Multi Select Box를 줘서 기간별 또는 요일별로 데이터를 조회할 수 있도록 해본다.
• 메인 페이지에는 탭을 구분해서 탭1에는 오프라인 데이터가, 탭2에는 온라인 데이터가 출력되도록 한다.
• 각 탭 내에는 상단에 데이터프레임, 하단에는 그래프가 출력되는 데, 이 안에서 조회 기준 (ex. 지역, 성별, 캠페인 등)을 둬서 그 조건에 부합하는 데이터가 출력되도록 구현해 본다.

이제 이 레이아웃을 토대로 코드를 열심히 (!) 짜면 된다.

---

다음 내용

[파이썬] 프로젝트 : 대시보드 웹 페이지 구축하기 - 7