Pandas 2.0下小众Providex数据库pyodbc兼容方案咨询

问题背景 #

根据Gord Thompson的说明，pyodbc并未被弃用，但仅适用于有对应SQLAlchemy方言的数据库。我使用的是Sage 100 ERP基于的小众Providex数据库，在Pandas 1.4.4及以下版本中，可通过cnxn = pyodbc.connect("DSN=SOTAMAS16",autocommit=True)调用read_sql()读取数据，但会触发警告。我不需要通过ODBC写入数据，不想一直用旧版Pandas，且不排斥使用SQLAlchemy，想知道能否通过PVXODBC驱动自定义SQLAlchemy方言来使用现有ODBC DSN。

尝试过的方法及报错 #

我曾尝试用SQLAlchemy连接，代码如下：

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())

但出现报错：

pyodbc.Error: ('37000', '[37000] [ProvideX][ODBC Driver]Expected lexical element not found: FROM (1015) (SQLExecDirectW)')

原因是该数据库并非Microsoft SQL Server，只是我找到的最接近的方言。

解决方案 #

当然可以通过自定义SQLAlchemy方言来适配Providex数据库，以下是具体实现步骤：

1. 定义并注册自定义方言 #

创建继承自SQLAlchemy ODBCDialect的Providex方言类，注册后让SQLAlchemy识别该方言：

from sqlalchemy.dialects import registry
from sqlalchemy.dialects.odbc.base import ODBCDialect

class ProvidexODBCDialect(ODBCDialect):
    name = "providex"
    supports_native_boolean = False
    # 可根据Providex的SQL语法特点，重写标识符处理、语句生成等方法

# 注册自定义方言
registry.register("providex.odbc", __name__, "ProvidexODBCDialect")

2. 使用自定义方言创建连接引擎 #

注册完成后，使用对应格式的URL创建SQLAlchemy引擎，即可正常执行查询：

from sqlalchemy import create_engine, text
import pandas as pd

# 自定义方言的连接URL格式：providex+odbc://用户名:密码@DSN名称?参数
engine = create_engine("providex+odbc://user:pwd@SOTAMAS16?autocommit=True")

# 执行查询
sql3 = "SELECT ItemCode FROM CI_Item"
items = pd.read_sql_query(sql=text(sql3), con=engine.connect())

3. 按需适配SQL语法细节 #

如果遇到SQL语法兼容问题（比如表/列名引用规则、特定函数语法），可以在自定义方言类中重写对应方法，例如：

• 重写identifier_preparer来适配Providex的标识符引用方式
• 调整get_select_precolumns等方法来生成符合Providex要求的SELECT语句
• 参考Providex官方SQL文档，针对性修改方言逻辑

注意事项 #

• 确保PVXODBC驱动已正确安装并配置好目标DSN
• 由于仅需读取数据，无需实现写入相关的方言方法，专注于查询语法适配即可
• 测试从简单查询开始，逐步验证复杂SQL的兼容性

内容的提问来源于stack exchange，提问作者dain