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