Ubuntu下SQLAlchemy连SQL Server驱动缺失及密码含@问题求助

• 连接127.0.0.1:1433上的SQL Server实例时触发sqlalchemy.exc.DBAPIError，错误信息：

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

• 不确定ODBC驱动需安装在SQL Server Docker镜像还是本地VM：
  • Docker镜像内/etc/odbcinst.ini配置：

    [ODBC Driver 17 for SQL Server]
    Description=Microsoft ODBC Driver 17 for SQL Server
    Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.2.1
    UsageCount=1
    

  • 本地VM的/etc/odbcinst.ini为空
• 连接用Python代码：

  from sqlalchemy import create_engine
  
  server = "127.0.0.1,1433"
  user = "sa"
  password = "Pass@12345"
  db_name = "test_database"
  
  engine = create_engine(f'mssql+pyodbc://{user}:{password}@{server}/{db_name}?driver=ODBC Driver 17 for SQL Server')
  
  connection = engine.connect()
  
  print("connected")
  

• 附加问题：密码包含@时如何处理？

环境信息

• SQL Server：sqlserver:2022-latest Docker镜像，运行在127.0.0.1:1433
• 操作系统：Ubuntu 22.04
• Python版本：3.10.6
• SQLAlchemy版本：2.0.16

1. 驱动安装位置确认 #

驱动必须安装在运行Python代码的本地VM中。因为Python脚本在本地执行，需要本地的ODBC驱动建立与Docker内SQL Server的连接，Docker镜像内的驱动仅服务于容器内部程序，和本地脚本无关。

2. Ubuntu 22.04安装ODBC Driver 17 for SQL Server #

执行以下命令完成安装：

# 更新源并安装依赖
sudo apt-get update
sudo apt-get install -y unixodbc-dev

# 添加微软官方源
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

# 安装驱动
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

安装完成后，/etc/odbcinst.ini会自动生成正确配置。可以执行odbcinst -q -d验证驱动，输出应包含[ODBC Driver 17 for SQL Server]。

3. 密码含@的处理方案 #

直接拼接带@的密码会导致URL解析错误，两种解决方式：

方式一：URL编码密码 #

用urllib.parse.quote_plus对密码编码后再拼接：

from sqlalchemy import create_engine
from urllib.parse import quote_plus

server = "127.0.0.1,1433"
user = "sa"
password = "Pass@12345"
db_name = "test_database"

encoded_pwd = quote_plus(password)
engine = create_engine(f'mssql+pyodbc://{user}:{encoded_pwd}@{server}/{db_name}?driver=ODBC Driver 17 for SQL Server')

connection = engine.connect()
print("connected")

方式二：使用SQLAlchemy URL构造器 #

借助sqlalchemy.engine.URL.create构建连接URL，自动处理特殊字符：

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

server = "127.0.0.1,1433"
user = "sa"
password = "Pass@12345"
db_name = "test_database"

conn_url = URL.create(
    "mssql+pyodbc",
    username=user,
    password=password,
    host=server.split(',')[0],
    port=int(server.split(',')[1]),
    database=db_name,
    query={"driver": "ODBC Driver 17 for SQL Server"}
)

engine = create_engine(conn_url)
connection = engine.connect()
print("connected")

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