`pd.read_sql(sql, engine)` raises NotImplementedError: This method is not implemented for SQLAlchemy 2.0

Question

I tried to create a pandas DataFrame directly from my sqlserver database using an sqlalchemy engine:

engine = create_engine(URL_string, echo=False, future=True)
query_string = "..."
dt = pd.read_sql(query_string, engine)

But this raises this error:

File <redacted>/venv/lib/python3.8/site-packages/sqlalchemy/future/engine.py:320, in Engine._not_implemented(self, *arg, **kw)
    319 def _not_implemented(self, *arg, **kw):
--> 320     raise NotImplementedError(
    321         "This method is not implemented for SQLAlchemy 2.0."
    322     )

NotImplementedError: This method is not implemented for SQLAlchemy 2.0.

I do this because using pyodbc's connection alone gives a warning:

UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

I'm using sqlalchemy version 1.4 ... so how do I fix this?

Answer 1

Just remove future=True from the engine parameters:

engine = create_engine(URL_string, echo=False)

Then you should be good to go!

Answer 2

This worked for me to use pyodbc and pandas cohesively. Just replace the query and connection info.

import pandas as pd
import warnings

query = 'SELECT * FROM TABLE'
conn = pyodbc.connect('db connection info')

with warnings.catch_warnings():
     warnings.simplefilter('ignore', UserWarning)
     df = pd.read_sql(query, conn)

Answer 3

Unfortunately this looks to be an open issue that won't be solved till pandas 2.0, you can find some information about this here and here.

I didn't find any satisfactory work around, but some people seems to be using two configurations of the engine, one with the flag future False:

engine2 = create_engine(URL_string, echo=False, future=False)

This solution would be ok if you make query strings as you did, but if you are using the ORM for me the best I could do is a custom function, that is probably far from optimal, but works:

Conditions = session.query(ExampleTable)
def custom_read(query):
    return pd.DataFrame({i:j.__dict__ for i,j in enumerate(query.all())},).T.drop(columns='_sa_instance_state')
df = custom_read(ExampleTable)