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?
Just remove future=True from the engine parameters:
engine = create_engine(URL_string, echo=False)
Then you should be good to go!
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)
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)