CODEWITHSUNDEEP
pythonpandassqlalchemyread-sql
Hajar Razip
Hajar Razip

Reputation: 603

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

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?

Upvotes: 7

Views: 9519

Answers (4)

Dwarf
Dwarf

Reputation: 44

According to the docs, the easiest way to get access to the execute function is through an engine.connect() context manager. You also need to use the text function since the execute function no longer accepts raw strings.

This is what worked for me:

from sqlalchemy import text

with engine.connect() as connection:
    dt= pd.read_sql_query(text(query_string), conn)

https://docs.sqlalchemy.org/en/20/core/connections.html#basic-usage

Upvotes: 0

Ziur Olpa
Ziur Olpa

Reputation: 2133

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.__dict__ for i in query]).drop(columns='_sa_instance_state')
df = custom_read(ExampleTable)

Upvotes: 1

Brndn
Brndn

Reputation: 846

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)

Upvotes: 1

Hajar Razip
Hajar Razip

Reputation: 603

Just remove future=True from the engine parameters:

engine = create_engine(URL_string, echo=False)

Then you should be good to go!

Upvotes: 4

Related Questions