SQLAlchemy+pyodbc使用ODBC Driver 18如何配置信任服务器证书

ODBC Driver 18 及以上版本默认开启连接加密，且默认不信任非公共CA签发的服务器证书，你在ODBC管理工具中勾选「Trust Server Certificate」本质是给驱动传递了信任证书的连接参数，只需要把对应参数按SQLAlchemy pyodbc方言的规则透传即可，不需要额外修改自定义功能包逻辑。

方法1：直接拼接连接字符串 #

直接在原有连接串的查询参数部分追加pyodbc支持的加密、证书信任参数即可，注意参数名和取值要和pyodbc要求完全匹配，URL中的空格用+替换：

driver = 'ODBC+Driver+18+for+SQL+Server'
engine_string = prefix + '://' + username + ':' + password + '@' + server + '/' + database + '?driver=' + driver + '&Encrypt=no&TrustServerCertificate=yes'

注意：如果账号密码中包含@、/、&这类URL特殊字符，手动拼接会出现解析错误，优先使用下面的方法2构造连接。

方法2：通过SQLAlchemy内置URL构造方法生成连接（推荐） #

使用URL.create方法构造连接对象时不需要手动处理参数转义，会自动适配URL编码规则，稳定性更高：

from sqlalchemy.engine import URL
from sqlalchemy import create_engine

# 构造连接URL
conn_url = URL.create(
    drivername="mssql+pyodbc", # 即你原有代码中的prefix
    username=username,
    password=password,
    host=server,
    database=database,
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "Encrypt": "no",
        "TrustServerCertificate": "yes"
    }
)

# 生成engine实例，可直接适配原有基于SQLAlchemy实现的自定义功能包
engine = create_engine(conn_url)

注意事项 #

• 不要直接将pyodbc直连生成的连接对象传入SQLAlchemy，该对象缺少SQLAlchemy MSSQL方言的适配逻辑，无法兼容上层封装的功能包。
• 后续如果要切换为安全的证书验证模式，只需要将Encrypt参数值改为yes，移除TrustServerCertificate=yes配置，同时将服务器证书的根CA导入客户端受信任根证书存储区即可，无需调整业务代码。

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