According to Gord Thompson here: Getting a warning when using a pyodbc Connection object with pandas
pyodbc is not deprecated and I agree, for databases with a SQLAlchemy dialect. But I am using an obsure database - Providex on which Sage 100 ERP is based. I can use pyodbc as in cnxn = pyodbc.connect("DSN=SOTAMAS16",autocommit=True) to access/read data with read_sql() in 1.4.47 or below, with warnings. I cannot and do not want to write data to the database with the ODBC interface. I also do not want to be relegated to using old releases of Pandas. I am not opposed to SQLAlchemy. Is there a way to use my ODBC DSN by perhaps, creating my own SQLAlchemy dialect using the PVXODBC ODBC Driver?
I can come close to being able to use SQLAlchemy with
sql3 = "SELECT ItemCode FROM CI_Item"
engine = create_engine("mssql+pyodbc://user:pwd@SOTAMAS16?autocommit=True")
items= pd.read_sql_query(sql=text(sql3), con=engine.connect())
to which I get this error: "pyodbc.Error: ('37000', '[37000] [ProvideX][ODBC Driver]Expected lexical element not found: FROM (1015) (SQLExecDirectW)')" which makes sense since the database is not Microsoft Sequel Server. This the closest dialect that I have found.
Why don't use pyodbc to execute the query and load results with pd.DataFrame.from_records. Something like:
import pandas as pd
import pyodbc
cnxn = pyodbc.connect('DSN=SOTAMAS16', autocommit=True)
cursor = cnxn.cursor()
cursor.execute('SELECT ItemCode FROM CI_Item')
cols = [c[0] for c in cursor.description]
df = pd.DataFrame.from_records(cursor.fetchall(), columns=cols)