Skip to content

DB engine for pandas: sql.connect or sqlalchemy #476

Closed

Description

@rth

Hello,

I was wondering what's the best practice for using this package with pandas.

  1. It's possible to create a databricks.sql.connect and pass it to pandas.read_sql. This works however it raises
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.
  1. Alternatively it's possible to use SQLAlchemy with a databricks:// URL and pass that to pandas. Doesn't it mean an extra serialization step performance wise though?

What's the recommended way, in particular regarding performance? Would both use CloudFetch for larger queries? I see there are some fixes/improvements done for pandas done in PRs so which API should be used to benefit from those?

Thanks!

cc @kravets-levko

Activity

  1. rth commented on Nov 29, 2024

    @rth
    Author

    Unless one is supposed to use fetchall_arrow and convert the resulting PyArrow table to pandas? Some example would be good (also in #21)

    Edit: Or actually some util function would be even better as proposed in #134

  2. vuanhphung commented on Oct 30, 2026

    @vuanhphung
    Collaborator

    Both work on the latest release (4.8.0), and both go through the same connector underneath, so CloudFetch applies either way. Passing a databricks.sql connection to pd.read_sql still triggers pandas' UserWarning about non-SQLAlchemy connections; a SQLAlchemy connection (via databricks-sqlalchemy) doesn't.

    For performance, the fastest path is to skip pd.read_sql and convert the Arrow result directly:

    with connection.cursor() as cursor:
        cursor.execute(query)
        df = cursor.fetchall_arrow().to_pandas()

    For a 1,000,000-row query against a SQL warehouse, this took 0.9 s, compared with 4.7 s for pd.read_sql on the databricks.sql connection and 5.2 s on a SQLAlchemy connection. fetchall_arrow() needs pyarrow (pip install databricks-sql-connector[pyarrow]). Closing as answered.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions