Unable to read a db whereas it was possible with Jupyter and streamlit run command

Using Streamlit
, ,
1

Hi,
I am trying to load a database from sql database.

I first create this function to read my database. It works fine on Jupyter and in a local run of streamlit…(cf. the function down)

Why did I get this error message and how to cancel it and read my database.

BUT when I tried to deploy my app I get an error message which was:

AttributeError: 'Engine' object has no attribute 'cursor'
2024-12-24 19:01:14.670 Uncaught app execution
Traceback (most recent call last):
  File "/home/adminuser/venv/lib/python3.12/site-packages/streamlit/runtime/scriptrunner/exec_code.py", line 88, in exec_func_with_error_handling
    result = func()
             ^^^^^^
  File "/home/adminuser/venv/lib/python3.12/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 579, in code_to_exec
    exec(code, module.__dict__)
  File "/mount/src/test/app.py", line 31, in <module>
    df = lecture_bdd("vestathena_db", "benchmark_main")
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mount/src/test/app.py", line 27, in lecture_bdd
    df = pd.read_sql(query, engine)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/adminuser/venv/lib/python3.12/site-packages/pandas/io/sql.py", line 706, in read_sql
    return pandas_sql.read_query(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/adminuser/venv/lib/python3.12/site-packages/pandas/io/sql.py", line 2738, in read_query
    cursor = self.execute(sql, params)
             ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/adminuser/venv/lib/python3.12/site-packages/pandas/io/sql.py", line 2672, in execute
    cur = self.con.cursor()
          ^^^^^^^^^^^^^^^
AttributeError: 'Engine' object has no attribute 'cursor'
/mount/src/test/app.py:27: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, engine)

Here is the function to read my database

import streamlit as st
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

def lecture_bdd(DATABASE, table, colonnes=None, condition=None):
    from sqlalchemy.orm import sessionmaker
    SERVER = 'xxxxxxxxx.database.windows.net'
    USERNAME = 'XXXXXXXXXXXXXXXX'
    PASSWORD = 'XXXXXXXXXXXXXXXX'
    DRIVER = 'ODBC Driver 17 for SQL Server'

    connection_url = URL.create(
        drivername="mssql+pyodbc",
        username=USERNAME,
        password=PASSWORD,
        host=SERVER,
        port=1433,
        database=DATABASE,
        query={"driver": DRIVER}
    )

    # Créez un moteur
    engine = create_engine(connection_url)

    # Ouvrez une connexion explicite
    with engine.connect() as connection:
        # Construire la requête SQL
        query = f"SELECT {', '.join(colonnes) if colonnes else '*'} FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        # Lire les données avec Pandas
        df = pd.read_sql(query, connection)
    return df
# Utilisation
df = lecture_bdd("db", "main")
df
2 Likes
2

It seems that the issue you’re facing is related to different versions of pandas between your local and production environments. According to a Stack Overflow thread, one possible fix is to adjust the code like this:

# Works with pandas==2.2.0
with engine.connect() as conn:
    df = pd.read_sql(
        sql=query,
        con=conn.connection
    )

This change should resolve the error. The issue is related to how pandas interacts with the SQL engine, and it seems the engine.connect() method behaves differently in older/newer versions.

1 Like