7

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?

Hajar Razip
  • 449
  • 5
  • 11

4 Answers4

4

Just remove future=True from the engine parameters:

engine = create_engine(URL_string, echo=False)

Then you should be good to go!

Hajar Razip
  • 449
  • 5
  • 11
1

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)
Brndn
  • 676
  • 1
  • 7
  • 21
1

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)
Ziur Olpa
  • 1,839
  • 1
  • 12
  • 27
0

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

Dwarf
  • 44
  • 1
  • 6