在Docker中安裝PostgreSQL的ODBC驅動並新增Python測試程式碼

# 使用官方的Ubuntu映象作為基礎映象

FROM ubuntu:20.04

# 設定環境變數以避免互動式安裝提示

ENV DEBIAN_FRONTEND=noninteractive

# 更新包列表並安裝必要的軟體包

RUN apt-get update && \

apt-get install -y \

build-essential \

unixodbc \

unixodbc-dev \

odbcinst \

wget \

python3 \

python3-pip \

curl \

gnupg && \

apt-get clean && \

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

# 安裝 PostgreSQL ODBC 驅動

RUN apt-get update && \

apt-get install -y odbc-postgresql && \

apt-get clean && \

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

# 配置 ODBC 驅動

RUN echo "[PostgreSQL]" > /etc/odbcinst.ini && \

echo "Description = ODBC for PostgreSQL" >> /etc/odbcinst.ini && \

echo "Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so" >> /etc/odbcinst.ini && \

echo "Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so" >> /etc/odbcinst.ini && \

echo "Driver64 = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so" >> /etc/odbcinst.ini && \

echo "Setup64 = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so" >> /etc/odbcinst.ini && \

echo "FileUsage = 1" >> /etc/odbcinst.ini

# 配置 ODBC 資料來源

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

echo "Driver = PostgreSQL" >> /etc/odbc.ini && \

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

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

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

echo "Username = postgres" >> /etc/odbc.ini && \

echo "Password = YOUR_PASSWORD" >> /etc/odbc.ini && \

echo "ReadOnly = No" >> /etc/odbc.ini && \

echo "RowVersioning = No" >> /etc/odbc.ini && \

echo "ShowSystemTables = No" >> /etc/odbc.ini && \

echo "ConnSettings = " >> /etc/odbc.ini

# 設定預設命令

#CMD ["bash"]

# 安裝 pyodbc 用於測試連線

RUN pip3 install pyodbc

# 新增測試指令碼

COPY pgtest_odbc.py /pgtest_odbc.py

# 設定預設命令

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

---

Python測試程式碼：

import pyodbc

# 配置資料庫連線

dsn = 'PostgreSQLDataSource'

user = 'postgres'

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}")