PyODBC進行SQLServer連線

發表於 分類於 閱讀次數：

前言

舊系統年代久遠已經難以進行維護，預計將舊系統的功能改採Python語言進行翻新，在新舊系統過度之際，為減少作業人員出現資料需要重複填表的情況，盡量以舊系統的資料庫存放資料作為新系統的資料來源，故研究如何透過Python提供的Pyodbc套件與SQL Server互動。

目前公司的SQL Server為2012版本，架設於 Windows Server 2008。

技術原理

flowchart TD

    Python["程式 (Python / pyodbc)"]
    ODBC["連接工具 (ODBC Driver Manager)"]
    Driver["驅動程式 (FreeTDS)"]
    SQLServer["資料庫 (SQL Server 2012)"]

    Python -->|呼叫 ODBC| ODBC
    ODBC -->|使用 DSN找驅動程式| Driver
    Driver -->|透過 TDS 協議溝通| SQLServer
名稱 解釋
ODBC (Open Database Connectivity) 資料庫通用介面，程式可以透過它連接不同資料庫，而不用管底層細節
FreeTDS 開源程式庫，實作 SQL Server / Sybase 的溝通協議 (TDS)，可作為 ODBC 驅動程式
TDS 協議 (Tabular Data Stream) SQL Server 與程式之間交換資料和指令的「語言」
DSN (Data Source Name) 連線設定檔，儲存資料庫位置、名稱、驅動程式和帳號密碼，程式只需指定 DSN 名稱即可連線
Driver / 驅動程式 負責把程式指令轉成資料庫可以理解的格式，像是「翻譯器」

執行環境

Dockerfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
FROM python:3.11-slim

RUN apt-get update && \
    apt-get install -y --no-install-recommends \
        freetds-bin \
        freetds-dev \
        tdsodbc \
        unixodbc \
        unixodbc-dev && \
    rm -rf /var/lib/apt/lists/*

RUN pip install pyodbc

# 註冊 FreeTDS ODBC driver（必要）
RUN echo "[FreeTDS]\n\
Description = FreeTDS Driver\n\
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so\n" \
    >> /etc/odbcinst.ini

WORKDIR /app
COPY test.py .

CMD ["python3", "test.py"]

test.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import pyodbc

# 需要進行個別電腦進行修改的，建議用.env操作比較安全
# YOUR_SERVER_ID
# YOUR_DATABASE
# YOUR_USER_NAME
# YOUR_PASSWORD

conn_str = (
    "DRIVER=FreeTDS;"
    "SERVER=[YOUR_SERVER_IP];"
    "PORT=1433;"
    "DATABASE=[YOUR_DATABASE];"
    "UID=[YOUR_USER_NAME];"
    "PWD=[YOUR_PASSWORD];"
    "TDS_Version=7.2;"
)

print("Connecting...")
conn = pyodbc.connect(conn_str)
print("Connection successful!")
cursor = conn.cursor()
cursor.execute("SELECT @@version") #要執行的SQL語法放這裡
row = cursor.fetchone()
print(f"SQL Server version: {row[0]}")
conn.close()

在容器環境中出現以下資料就表示連線成功囉!

Fig1. 連線成功畫面

錯誤紀錄

微軟提供連接SQL Server的驅動程式(msodbcsql18)，其層級等同於FreeTDS的腳色，但公司內部的SQL Server版本為2012，無法順利進行操作，出現Unsupport Protocol。

經查可能是 msodbcsql18 的連線協議強制要進行加密(TLS1.2)，然而舊版本的SQL Server沒有提供加密的功能。