使用SQLAlchemy通过odbc_connect连接SQL Server时登录失败，如何解决？

问题情况 #

使用pyodbc直接连接SQL Server（无论是传入参数还是完整ODBC连接字符串）均可正常访问数据库，但通过SQLAlchemy的URL.create方法结合odbc_connect构建连接时，出现登录失败错误（错误码18456）。

可正常运行的pyodbc代码 #

import pyodbc
import pandas as pd
connection_string = "DRIVER={SQL Server};SERVER=some_server_address,1433;DATABASE=some_db;UID=some_user;PWD=some_password"
conn = pyodbc.connect(connection_string)

cursor = conn.cursor()
df = pd.read_sql_query('''select top 100 * from tableA''', conn)
cursor.close()
conn.close()

失败的SQLAlchemy代码 #

from sqlalchemy.engine import URL
from sqlalchemy import create_engine
import sqlalchemy as sa

connection_string = "DRIVER={SQL Server};SERVER=some_server_address,1433;DATABASE=some_db;UID=some_user;PWD=some_password"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
engine = create_engine(connection_url)
conn = engine.connect()

报错信息 #

InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'some_user'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'some_user'. (18456)")

解决方法 #

方法1：手动编码ODBC连接字符串后直接拼接 #

SQLAlchemy的URL.create对query参数的编码逻辑可能导致ODBC连接字符串中的特殊字符处理异常，改用urllib.parse.quote_plus手动编码后直接拼接连接URL：

from sqlalchemy import create_engine
import urllib.parse

connection_string = "DRIVER={SQL Server};SERVER=some_server_address,1433;DATABASE=some_db;UID=some_user;PWD=some_password"
encoded_conn_str = urllib.parse.quote_plus(connection_string)
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={encoded_conn_str}")
conn = engine.connect()

方法2：使用SQLAlchemy的URL构造器参数而非query #

直接通过URL.create的参数传入连接信息，避免嵌套ODBC连接字符串：

from sqlalchemy.engine import URL
from sqlalchemy import create_engine

connection_url = URL.create(
    "mssql+pyodbc",
    username="some_user",
    password="some_password",
    host="some_server_address",
    port=1433,
    database="some_db",
    query={"driver": "SQL Server"}
)
engine = create_engine(connection_url)
conn = engine.connect()

方法3：升级ODBC驱动版本 #

尝试使用更新的ODBC驱动替换旧的{SQL Server}驱动，部分旧驱动与SQLAlchemy兼容性较差：
修改连接字符串中的DRIVER部分：

connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=some_server_address,1433;DATABASE=some_db;UID=some_user;PWD=some_password"

再结合方法1或方法2重新构建连接。

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