2

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.

Nick ODell
  • 15,465
  • 3
  • 32
  • 66
dain
  • 63
  • 5

1 Answers1

1

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)
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • Which one? Does it solve your problem? – Corralien Apr 06 '23 at 12:27
  • Thanks @Corralien this works. I added one minor variation: pd.DataFrame.from_records(cursor.fetchall(), columns=[col[0] for col in cursor.description]) This gets the column names for me. It just seems a shame that if I want to upgrade to Pandas 2.0 I have to change all of my existing code to not use read_sql. – dain Apr 06 '23 at 12:36
  • Thanks for this trick too :-) I updated my answer related to your comments. – Corralien Apr 06 '23 at 13:21
  • I added one additional line to my code `df = df.apply(pd.to_numeric, errors="ignore")` My database brings in some columns as decimal and pandas had difficulty doing math with them until I changed the type to float. This line of code changes the numeric columns for me. – dain Apr 10 '23 at 13:59