Pandas: Resolving SQLAlchemy Connectable and oracledb Connection Errors

Verified FixPython 3.10+Pandas 2.2+, SQLAlchemy 2.0+, oracledb 2.0+Silo: pandas

Quick Fix / Solution Rapide

This error occurs when Python passes a raw DBAPI connection object (such as oracledb.connect()) to pd.read_sql(). In Pandas 2.0+, use a SQLAlchemy Engine / Connection object or fetch records via cursor manually.

Root Cause Analysis

This error occurs when Python applications pass a raw database driver connection object to pd.read_sql() or pd.DataFrame.to_sql() in Pandas 2.0+ without wrapping it in a SQLAlchemy engine or connection context.

Root Cause 1: Deprecation of Raw DBAPI Connections in Pandas 2.0+

Prior to Pandas 2.0, pd.read_sql() tolerated passing raw DBAPI2 connection objects from various database drivers (such as cx_Oracle, psycopg2, mysql-connector). In Pandas 2.0+, the database interface was strictly overhauled: pd.read_sql() exclusively supports SQLAlchemy connectable objects (Engine or Connection), database connection URI strings, or native sqlite3.Connection instances. Passing raw non-sqlite DBAPI connection objects triggers deprecation warnings or TypeError: pandas only supports SQLAlchemy connectable.

Root Cause 2: Transition from cx_Oracle to python-oracledb

The official Oracle database driver for Python transitioned from cx_Oracle to python-oracledb. When using SQLAlchemy with oracledb, the connection dialect string must be specified as oracle+oracledb:// rather than the legacy oracle+cx_oracle:// string.

Root Cause 3: Unclosed Database Cursor Leaks

When developers attempt to bypass SQLAlchemy by executing raw queries on a database cursor, failing to properly close cursors and connections within try...finally or context manager blocks leads to connection pool exhaustion and memory leaks.

Root Cause 4: Type Mapping and Schema Translation Failures

Raw DBAPI connections do not provide standardized type reflection for complex Oracle types (such as CLOB, BLOB, RAW, or TIMESTAMP WITH TIME ZONE). SQLAlchemy provides robust dialect-specific type casting that Pandas relies upon to construct typed DataFrames efficiently.

Reproduction Code (MCVE)

Example: Bug Reproduction

import pandas as pd

class MockOracleRawDBAPIConnection:
    """Simulates a raw oracledb connection object without SQLAlchemy connectable protocol."""
    def __init__(self):
        self.is_connected = True

def run_database_query(raw_conn, query: str):
    # Enforce strict validation: pandas 2.0+ requires SQLAlchemy Connectable or sqlite3
    if not hasattr(raw_conn, "connect") and not hasattr(raw_conn, "cursor"):
        raise TypeError(
            "TypeError: pandas only supports SQLAlchemy connectable (engine/connection) "
            "or database string URI or sqlite3 DBAPI2 connection. Received raw DBAPI connection."
        )
    return pd.read_sql(query, raw_conn)

# Passing raw DBAPI connection triggers TypeError
raw_oracle_conn = MockOracleRawDBAPIConnection()
run_database_query(raw_oracle_conn, "SELECT id, username FROM employees")

Solution 1: Use a SQLAlchemy Engine with oracledb Dialect

Create a SQLAlchemy Engine using the oracle+oracledb:// dialect and pass the engine or connection context manager to pd.read_sql().

Example: Recommended Solution

import pandas as pd
import sqlite3

# In production with Oracle Database:
# from sqlalchemy import create_engine
# engine = create_engine("oracle+oracledb://user:password@hostname:1521/?service_name=XEPDB1")
# with engine.connect() as connection:
#     df = pd.read_sql("SELECT id, name FROM users", con=connection)

# Standalone executable demonstration using in-memory database connectable:
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INT, username TEXT, role TEXT)")
conn.execute("INSERT INTO users VALUES (1, 'alice_admin', 'admin'), (2, 'bob_user', 'member')")
conn.commit()

df = pd.read_sql("SELECT id, username, role FROM users", con=conn)
print("DataFrame fetched via connectable:")
print(df)
conn.close()

Solution 2: Fetch via DBAPI Cursor and Construct DataFrame Manually

If you cannot use SQLAlchemy in your environment, execute the query using the driver cursor, fetch all rows, and pass the column descriptions directly to pd.DataFrame().

Example: Alternative Solution

import pandas as pd
import sqlite3

# Standalone demonstration of the cursor fetch pattern
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

cursor.execute("CREATE TABLE products (sku TEXT, price REAL, stock INT)")
cursor.execute("INSERT INTO products VALUES ('SKU-100', 19.99, 50), ('SKU-200', 45.00, 12)")
conn.commit()

# Execute query and extract column headers from cursor description
cursor.execute("SELECT sku, price, stock FROM products")
rows = cursor.fetchall()
column_names = [desc[0] for desc in cursor.description]

# Build DataFrame cleanly from rows and columns
df_products = pd.DataFrame(rows, columns=column_names)
print("DataFrame constructed from raw DBAPI cursor:")
print(df_products)

cursor.close()
conn.close()

When connecting to Oracle Database in 'thin' mode (the default in python-oracledb without Oracle Client libraries installed), certain advanced Oracle features (such as TAC, LDAP naming, or legacy ANO encryption) require 'thick' mode. If you receive an oracledb driver error upon creating the engine, initialize thick mode at application startup with oracledb.init_oracle_client().

Another frequent pitfall is passing raw parameter strings with SQL injection risks. Always use parameterized queries with SQLAlchemy text(): pd.read_sql(text("SELECT * FROM users WHERE dept = :dept"), con=connection, params={"dept": "IT"}).

Contrast pd.read_sql() with pd.read_sql_query() and pd.read_sql_table(): read_sql_query() is dedicated specifically to SQL query strings, while read_sql_table() reads an entire database table directly without writing SQL statements.

Previous Next