[파이썬] Streamlit 웹 개발 - 7: DB

이전 내용

[파이썬] Streamlit 웹 개발 - 6: 저장 관련

---

데이터베이스 연동

 

이전에 데이터베이스를 연동하여 로컬 MySQL에 저장된 데이터베이스, 테이블, 데이터를 로드하여 출력해 보았다. (이전 내용 참고) 추가로, 데이터베이스 내에 있는 테이블 목록을 셀렉트박스로 선택하여 선택된 테이블의 데이터를 출력할 수 있도록 했다.

 

이번에는 csv 파일을 MySQL 데이터베이스에 연동시키고, 테이블을 생성하고, 데이터를 삽입하는 함수를 만들어 메인 함수로 이들을 실행할 수 있게 코드를 작성해 본다.

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

# MySQL 정보: 실무에서는 보안상 정보를 직접 넣어놓지는 않음.
DB_HOST = 'localhost'
DB_PORT = '3306'
DB_NAME = 'tabledb'
DB_USER = 'root'
DB_PASS = '1234'
DB_TABLE = 'cars' # 테이블 이름

# CSV 파일 경로
CSV_FILE_PATH =  './cars.csv'

# 데이터베이스와 프로그램 간 연결 (파이프라인)
def create_database_connection():
    try:
        connection = mysql.connector.connect( # 데이터베이스를 연동하는 객체
            host=DB_HOST,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASS
        )
        if connection.is_connected():
            print("MySQL 데이터베이스에 성공적으로 연결되었습니다.")
            return connection

    except Error as e:
        print(f"데이터베이스 연결 오류 발생: {e}")
        return None
    
# 프로그램에서 테이블 생성
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,
            foreign_local_used VARCHAR(20),
            color VARCHAR(20),
            wheel_drive INT,
            automation VARCHAR(20),
            seat_make VARCHAR(20),
            price BIGINT,
            description VARCHAR(100),
            make_year INT,
            manufacturer VARCHAR(50)
        );
        """
        cursor.execute(create_table_query) # 쿼리 실행
        connection.commit()
        print(f"'{DB_TABLE}' 테이블이 성공적으로 생성되었습니다.")
    
    except Error as e:
        print(f"테이블 생성 중 오류 발생: {e}")
    finally:
        cursor.close()

# 데이터 삽입 함수
def insert_data(connection, df):
    try:
        cursor = connection.cursor()
        insert_query = f"""
        INSERT INTO {DB_TABLE} (
            foreign_local_used, color, wheel_drive, automation, seat_make,
            price, description, make_year, manufacturer
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);
        """

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

# 메인 함수:
def main():
    # CSV 파일 읽기 (첫번째 열을 인덱스로 설정)
    try:
        df = pd.read_csv(CSV_FILE_PATH, index_col=0)
        print("CSV 파일을 성공적으로 읽었습니다.")
    except FileNotFoundError:
        print(f"파일 '{CSV_FILE_PATH}'을 찾을 수 없습니다.")
        return
    except Exception as e:
        print(f"CSV 파일 읽기 중 오류 발생: {e}")
        return
    
    # 컬럼 이름 변경 (띄어 쓰기를 '_' 로 변경)
    expected_columns = [
        "foreign_local_used", "color", "wheel_drive", "automation", "seat_make",
        "price", "description", "make_year", "manufacturer"
    ]
    df.columns = expected_columns # 컬럼 이름 매핑

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

    # 테이블 생성
    create_table(connection)

    # 데이터 삽입
    insert_data(connection, df)

    # 연결 종료
    if connection.is_connected():
        connection.close()
        print("MySQL 연결이 종료되었습니다.")

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

 

이 코드를 VSCode에서 실행해보면

 

잘 작동하는 것을 확인할 수 있다.

---

함수별 설명

 

◆ create_database_connection()

: MySQL 데이터베이스에 연결하기 위한 기능을 수행

 

◆ create_table(connection)

: 연결된 데이터베이스에서 테이블을 생성하는 기능

• 매개변수: connection - 연결된 데이터베이스 객체.
• 기능: 지정된 테이블이 존재하지 않을 경우 새로운 테이블 생성.
• 예외 처리: 테이블 생성 중 오류가 발생할 경우 메시지 출력.
• 마무리: 커서를 닫아 자원 정리.

◆ insert_data(connection, df)

: 주어진 데이터프레임을 사용하여 데이터베이스 테이블에 데이터 삽입

• 매개변수:
  - connection: 연결된 데이터베이스 객체.
  - df: 데이터프레임, 삽입할 데이터가 포함되어 있음.
• 기능: 데이터프레임의 데이터를 테이블 삽입.
• 예외 처리: 데이터 삽입 중 오류가 발생할 경우 메시지 출력.
• 마무리: 커서를 닫아 자원을 정리.

◆ main()

: 전체 프로그램의 흐름 관리

• CSV 파일 읽기: 지정된 CSV 파일을 읽고, 읽기 성공 여부 체크.
• 컬럼 이름 변경: 데이터프레임의 열 이름을 지정된 형식으로 변경.
• 데이터베이스 연결: 데이터베이스에 연결하고, 연결 여부 확인.
• 테이블 생성 및 데이터 삽입: 테이블을 생성하고 데이터 삽입.
• 연결 종료: 작업이 끝난 후 데이터베이스 연결을 안전하게 종료.

---

Streamlit을 통해 화면으로 띄우기
: 데이터 읽어오는 함수 설정

 

이제 cars 테이블의 데이터들을 Streamlit에서 띄울 수 있도록 데이터를 가져와 출력하는 코드를 붙여 넣는다.

(이전 내용에서 작성한 코드 활용)

이제 터미널에서 streamlit run 으로 실행해 보면,

from sqlalchemy import create_engine, inspect

# 데이터 읽어오기

# SQLAlchemy 엔진 생성 (MySQL용)
engine = create_engine(f"mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}")

# 인스펙터 생성
inspector = inspect(engine)

# 데이터베이스의 테이블 목록 조회
table_names = inspector.get_table_names()

# Streamlit 앱
st.title("MySQL 데이터 조회")

selected_table = st.selectbox("조회할 테이블을 선택하세요", table_names)

# 데이터 가져오는 함수
def fetch_data():
    query = f"SELECT * FROM {selected_table};"
    df = pd.read_sql(query, engine) # SQLAlchemy 엔진 사용
    return df

if st.button("데이터 로드"):
    df = fetch_data()
    st.dataframe(df) # 데이터 출력

 

기존의 데이터베이스에 테이블을 생성했으므로, 'cars' 라는 테이블이 추가되어 표시되는 것을 볼 수 있다.

 

◆ main 함수에 데코레이터 추가하기 (수정)

코드를 실행하는 데 테이블을 생성하고, 레코드를 삽입하고 하는 과정이 계속 반복되므로, @st.cache_data 데코레이터를 이용해 함수가 변경될 때만 함수를 호출하도록 설정해 주었다.

▶  main 함수에 데코레이터를 적용하기 보다는 테이블을 생성하고 삽입하는 쪽에 데코레이터를 주는 게 더 적절하다.

 

메인함수 위에 데코레이터를 설정하고, 메인 함수가 실행되는 부분은 주석처리를 해줬다.

# 메인 함수:
@st.cache_data
def main():



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

 

▶ 수정

# 프로그램에서 테이블 생성
@st.cache_data
def create_table(connection):

...

# 데이터 삽입 함수
@st.cache_data
def insert_data(connection, df):

---

다음 내용

[파이썬] Streamlit 웹 개발 - 8: 대시보드 꾸미기