在Docker中安装SQL Server的ODBC驱动并添加Python测试代码

# 使用官方的Ubuntu 20.04镜像作为基础镜像

FROM ubuntu:20.04

# 设置环境变量以避免交互式安装提示

ENV DEBIAN_FRONTEND=noninteractive

# 更新包列表并安装必要的软件包

RUN apt-get update && \

    apt-get install -y \

    wget \

    curl \

    gnupg2 \

    apt-transport-https \

    unixodbc \

    unixodbc-dev \

    odbcinst \

    build-essential \

    software-properties-common \

    python3 \

    python3-pip && \

    apt-get clean && \

    rm -rf /var/lib/apt/lists/*

# # 添加 Microsoft 的包签名密钥和包存储库

# RUN wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb && \

#     dpkg -i packages-microsoft-prod.deb && \

#     rm packages-microsoft-prod.deb

# # 安装 .NET SDK

# RUN apt-get update && \

#     apt-get install -y dotnet-sdk-8.0 && \

#     apt-get clean && \

#     rm -rf /var/lib/apt/lists/*

# 添加 Microsoft SQL Server ODBC 驱动的密钥和存储库

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \

    curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# 更新包列表并安装 Microsoft ODBC Driver for SQL Server

RUN apt-get update && \

    ACCEPT_EULA=Y apt-get install -y msodbcsql17 && \

    apt-get clean && \

    rm -rf /var/lib/apt/lists/*

# 配置 ODBC 驱动

RUN echo "[ODBC Driver 17 for SQL Server]" > /etc/odbcinst.ini && \

    echo "Description = Microsoft ODBC Driver 17 for SQL Server" >> /etc/odbcinst.ini && \

    echo "Driver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1" >> /etc/odbcinst.ini

# 配置 ODBC 数据源

RUN echo "[MSSQLDataSource]" > /etc/odbc.ini && \

    echo "Driver = ODBC Driver 17 for SQL Server" >> /etc/odbc.ini && \

    echo "Server = localhost" >> /etc/odbc.ini && \

    echo "Port = 1433" >> /etc/odbc.ini && \

    echo "Database = testdb" >> /etc/odbc.ini && \

    echo "UID = sa" >> /etc/odbc.ini && \

    echo "PWD = YOUR_PASSWORD" >> /etc/odbc.ini

# 安装 pyodbc 用于测试连接

RUN pip3 install pyodbc

# 添加测试脚本

COPY mssqltest_odbc.py /mssqltest_odbc.py

# 设置默认命令

CMD ["python3", "/mssqltest_odbc.py"]

 

---

Python测试脚本：

import pyodbc

# 配置SQL Server数据库连接

dsn = 'MSSQLDataSource'

user = 'sa'

password = 'YOUR_PASSWORD'

database = 'testdb'

# 创建连接字符串

conn_str = f'DSN={dsn};UID={user};PWD={password};DATABASE={database}'

# 尝试连接到数据库

try:

    conn = pyodbc.connect(conn_str)

    print("Connected to the database successfully!")

    conn.close()

except Exception as e:

    print(f"Failed to connect to the database: {e}")