Docker+FastAPI+SQLAlchemy连接MSSQL遇Unicode转换失败求助

问题背景 #

在Alpine Linux镜像构建的Docker环境中，使用FastAPI+SQLAlchemy+pyodbc连接采用Japanese_CI_AS排序规则的MSSQL数据库时，触发以下错误：

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server]Unicode conversion failed (22) (SQLGetData)')

已尝试禁用pyodbc连接池（pyodbc.pooling = False）但无效；添加ENV LD_PRELOAD /usr/lib/preloadable_libiconv.so后错误消失，但数据库查询无返回结果。

环境配置 #

Dockerfile #

FROM python:3.12.3-alpine
WORKDIR /app
COPY requirements.txt .

RUN apk update
RUN apk add gcc libc-dev g++ libffi-dev libxml2 unixodbc-dev
RUN apk add bash curl gnupg
RUN apk add gnu-libiconv gnu-libiconv-dev

#Download the desired package(s)
RUN curl -O https://download.microsoft.com/download/3/5/5/355d7943-a338-41a7-858d-53b259ea33f5/msodbcsql18_18.3.3.1-1_amd64.apk
RUN curl -O https://download.microsoft.com/download/3/5/5/355d7943-a338-41a7-858d-53b259ea33f5/mssql-tools18_18.3.1.1-1_amd64.apk

#(Optional) Verify signature, if 'gpg' is missing install it using 'apk add gnupg':
RUN curl -O https://download.microsoft.com/download/3/5/5/355d7943-a338-41a7-858d-53b259ea33f5/msodbcsql18_18.3.3.1-1_amd64.sig
RUN curl -O https://download.microsoft.com/download/3/5/5/355d7943-a338-41a7-858d-53b259ea33f5/mssql-tools18_18.3.1.1-1_amd64.sig

RUN curl https://packages.microsoft.com/keys/microsoft.asc  | gpg --import -
RUN gpg --verify msodbcsql18_18.3.3.1-1_amd64.sig msodbcsql18_18.3.3.1-1_amd64.apk
RUN gpg --verify mssql-tools18_18.3.1.1-1_amd64.sig mssql-tools18_18.3.1.1-1_amd64.apk

#Install the package(s)
RUN apk add --allow-untrusted msodbcsql18_18.3.3.1-1_amd64.apk
RUN apk add --allow-untrusted mssql-tools18_18.3.1.1-1_amd64.apk

ENV GLIBC_VER=2.35-r1
RUN apk update --no-cache && \
    apk add --no-cache git curl binutils && \
    curl -sL https://alpine-pkgs.sgerrand.com/sgerrand.rsa.pub -o /etc/apk/keys/sgerrand.rsa.pub && \
    curl -sLO "https://github.com/sgerrand/alpine-pkg-glibc/releases/download/${GLIBC_VER}/glibc-${GLIBC_VER}.apk" && \
    curl -sLO "https://github.com/sgerrand/alpine-pkg-glibc/releases/download/${GLIBC_VER}/glibc-bin-${GLIBC_VER}.apk" && \
    apk add --force-overwrite --no-cache glibc-${GLIBC_VER}.apk glibc-bin-${GLIBC_VER}.apk && \
    rm -rf /var/cache/apk/*

RUN pip install -r requirements.txt

ENV PYTHONPATH=./fast-api

COPY . .
EXPOSE 5002

requirements.txt #

fastapi==0.110.1
uvicorn==0.29.0
SQLAlchemy==2.0.29
pandas==2.2.2
pyodbc==5.1.0
email-validator==2.1.1

database.py #

import sqlalchemy as sa
from sqlalchemy import create_engine
import pyodbc
pyodbc.pooling = False

SQLALCHEMY_DATABASE_URL = sa.engine.url.URL(
    "mssql+pyodbc",
    username="user",
    password="pass",
    host="hostname",
    port=1433,
    database="test",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "TrustServerCertificate": "yes",
    },
)
print(SQLALCHEMY_DATABASE_URL)

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
    , pool_recycle=1500
    )

解决方案 #

1. 调整iconv预加载与字符集配置 #

Alpine的gnu-libiconv处理日文Unicode转换时需明确指定字符集，修改Dockerfile：

# 替换原有LD_PRELOAD配置，添加字符集环境变量
ENV LD_PRELOAD=/usr/lib/preloadable_libiconv.so
ENV LC_ALL=ja_JP.UTF-8
ENV LANG=ja_JP.UTF-8

# 在安装glibc后添加日文语言包安装步骤
RUN apk add --no-cache tzdata && \
    cp /usr/share/zoneinfo/Asia/Tokyo /etc/localtime && \
    echo "Asia/Tokyo" > /etc/timezone && \
    apk add --no-cache locale && \
    echo "ja_JP.UTF-8 UTF-8" >> /etc/locale.gen && \
    locale-gen ja_JP.UTF-8

2. 修改数据库连接字符串 #

在database.py的连接参数中添加字符集指定：

SQLALCHEMY_DATABASE_URL = sa.engine.url.URL(
    "mssql+pyodbc",
    username="user",
    password="pass",
    host="hostname",
    port=1433,
    database="test",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "TrustServerCertificate": "yes",
        "charset": "utf8"
    },
)

3. 强制pyodbc的Unicode编码规则 #

在database.py开头添加编码设置：

import pyodbc
# 禁用连接池
pyodbc.pooling = False
# 强制指定Unicode编码
pyodbc.setencoding(encoding='utf-8')
pyodbc.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
pyodbc.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')

4. 切换至Debian基础镜像（备选方案） #

若Alpine的iconv兼容性问题难以彻底解决，可改用Debian镜像规避：

FROM python:3.12.3-slim-bookworm
WORKDIR /app
COPY requirements.txt .

# 安装MS ODBC驱动依赖
RUN apt-get update && apt-get install -y --no-install-recommends \
    curl gnupg2 unixodbc-dev gcc g++ \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/12/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql18 mssql-tools18 \
    && apt-get clean && rm -rf /var/lib/apt/lists/*

RUN pip install -r requirements.txt

ENV PYTHONPATH=./fast-api

COPY . .
EXPOSE 5002

验证方法 #

启动容器后执行测试查询：

from sqlalchemy.orm import sessionmaker
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

db = SessionLocal()
# 查询含日文字符的记录
result = db.execute(sa.text("SELECT * FROM your_table WHERE id = 1")).fetchone()
print(result)
db.close()

若能正常打印含日文字符的结果，说明问题已解决。

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