sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'SELECT DISTINCT symbol FROM stock_prices'

문제 상황

import streamlit as st
import pandas as pd
import os
from sqlalchemy import create_engine
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv('DATABASE_URL')

# MySQL 연결
engine = create_engine(DATABASE_URL)

# 종목 목록 가져오기
@st.cache_data
def get_symbols():
    with engine.connect() as conn:
        result = conn.execute(f"SELECT DISTINCT symbol FROM stock_prices")
        return [row[0] for row in result.fetchall()]
    
# 특정 종목 데이터 가져오기
@st.cache_data
def get_stock_data(symbol):
    query = f"""
        SELECT date, open, high, low, close, volume
        FROM stock_prices
        WHERE symbol = %s
        ORDER BY date DESC
    """
    df = pd.read_sql(query, engine, params=(symbol,))

    return df

# UI
st.title("📈 주식 DB 뷰어")

symbols = get_symbols()
if not symbols:
    st.warning("DB에 저장된 종목이 없습니다.")
else:
    selected_symbol = st.selectbox("종목 선택", symbols)
    df = get_stock_data(selected_symbol)

    st.subheader(f"{selected_symbol} 데이터 미리보기")
    st.dataframe(df, use_container_width=True)

위의 코드에서 streamlit run streamlit_app.py 실행 시, 다음과 같은 에러가 발생한다.

Traceback (most recent call last):
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\sqlalchemy\engine\base.py", line 1412, in execute
    meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\streamlit\runtime\scriptrunner\exec_code.py", line 121, in exec_func_with_error_handling
    result = func()
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 640, in code_to_exec
    exec(code, module.__dict__)
  File "D:\myproj\stock-pattern-recognition-project\streamlit_app.py", line 37, in <module>
    symbols = get_symbols()
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 219, in __call__
    return self._get_or_create_cached_value(args, kwargs, spinner_message)
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 261, in _get_or_create_cached_value
    return self._handle_cache_miss(cache, value_key, func_args, func_kwargs)
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 320, in _handle_cache_miss
    computed_value = self._info.func(*func_args, **func_kwargs)
  File "D:\myproj\stock-pattern-recognition-project\streamlit_app.py", line 18, in get_symbols
    result = conn.execute(f"SELECT DISTINCT symbol FROM stock_prices")
  File "d:\myproj\stock-pattern-recognition-project\.venv\lib\site-packages\sqlalchemy\engine\base.py", line 1414, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'SELECT DISTINCT symbol FROM stock_prices'

해결 방법

# 종목 목록 가져오기
@st.cache_data
def get_symbols():
    with engine.connect() as conn:
        result = conn.execute("SELECT DISTINCT symbol FROM stock_prices")
        return [row[0] for row in result.fetchall()]

위의 코드에서 쿼리를 실행할 때, 단순히 str로 전달하면 안되고, SQLAlchemy의 text로 텍스트를 한 번 묶어서 전달해야한다. 따라서 다음과 같이 코드를 고치면 정상 작동하는 것을 확인할 수 있다.

# 종목 목록 가져오기
@st.cache_data
def get_symbols():
    with engine.connect() as conn:
        query = "SELECT DISTINCT symbol FROM stock_prices"
        result = conn.execute(text(query))
        return [row[0] for row in result.fetchall()]

참고 문헌

https://stackoverflow.com/questions/69490450/objectnotexecutableerror-when-executing-any-sql-query-using-asyncengine