Pandas read_sql_query with SQLAlchemy 2

Question

I have been running Pandas with SQLAlchemy in "Future mode" for about two weeks now and everything has been working okay. This morning PIP has started pulling SQLAlchemy 2.0 as default, which has caused some issues.

sql_query = '''
    SELECT [StartDate] 
      , [EndDate] 
    FROM [dbo].[Accounts]
'''

with source_engine.connect() as source_connection:
    sql_query = pd.read_sql_query(text(sql_query), source_connection)
    df_source = pd.DataFrame(sql_query)

This is now producing this error: TypeError: init() got multiple values for argument 'schema'

Is there something wrong with this code, or is this a compatibility issue?

Setting the SQLAlchemy version back to 1.4 works, but I want to get this working with 2.0

Answer 1

Below is my original answer but since pandas>=2.0.0 is released, now it is enough to update pandas,

pip install --upgrade "pandas>=2.0.0"

---

Original answer:

As you stated, a workaround is to downgrade to SQLAlchemy<2.0,

pip install --upgrade sqlalchemy==1.4.47

There is already a fix in pandas (https://github.com/pandas-dev/pandas/pull/48576). Either install pandas from git or wait for a new release.

Edit: @DrD pointed out that the commit is already merged and will be part of pandas 2.x, also the comments point to the probability of pandas < 2.0 never supporting SQLAlchemy 2.x.