使用pyodbc将docker python连接到SQL server

Question

我正在尝试连接docker容器中运行的pyodbc python脚本，以登录MSSQL数据库。我尝试了各种docker文件，但无法建立连接（构建docker或python尝试连接时失败），是否有人使用pyodbc有工作的dockerfile：

Dockerfile文件：

# Use an official Python runtime as a parent image
FROM python:2.7-slim

# Set the working directory to /app
WORKDIR /app

# Copy the current directory contents into the container at /app
ADD . /app

# Install any needed packages specified in requirements.txt
RUN pip install -r requirements.txt

# Run app.py when the container launches
CMD ["python", "App.py"]

要求.TXT

pyodbc

app. Py

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                            'Server=xxxx;'
                            'Database=xxx;'
                            'UID=xxxx;'
                            'PWD=xxxx')

cursor = connection.cursor()

cursor.execute("SELECT [Id],[Name] FROM [DCMM].[config].[Models]")
for row in cursor.fetchall():
    print(row.Name)


connection.close()

构建容器docker build-t sqltest。

输出：

Sending build context to Docker daemon  4.096kB
Step 1/5 : FROM python:2.7-slim
 ---> 426d65ab9a72
Step 2/5 : WORKDIR /app
 ---> Using cache
 ---> 725f35122880
Step 3/5 : ADD . /app
 ---> 3feb8b7744f7
Removing intermediate container 4214091a111a
Step 4/5 : RUN pip install -r requirements.txt
 ---> Running in 27aa4dcfe738
Collecting pyodbc (from -r requirements.txt (line 1))
  Downloading pyodbc-4.0.17.tar.gz (196kB)
Building wheels for collected packages: pyodbc
  Running setup.py bdist_wheel for pyodbc: started
  Running setup.py bdist_wheel for pyodbc: finished with status 'error'
  Failed building wheel for pyodbc
  Complete output from command /usr/local/bin/python -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-EfWsmy/pyodbc/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" bdist_wheel -d /tmp/tmpa3S13tpip-wheel- --python-tag cp27:
  running bdist_wheel
  running build
  running build_ext
  building 'pyodbc' extension
  creating build
  creating build/temp.linux-x86_64-2.7
  creating build/temp.linux-x86_64-2.7/src
  gcc -pthread -fno-strict-aliasing -g -O2 -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -fPIC -DPYODBC_VERSION=4.0.17 -DSQL_WCHART_CONVERT=1 -I/usr/local/include/python2.7 -c src/cursor.cpp -o build/temp.linux-x86_64-2.7/src/cursor.o -Wno-write-strings
  unable to execute 'gcc': No such file or directory
  error: command 'gcc' failed with exit status 1

  ----------------------------------------
  Running setup.py clean for pyodbc
Failed to build pyodbc
Installing collected packages: pyodbc
  Running setup.py install for pyodbc: started
    Running setup.py install for pyodbc: finished with status 'error'
    Complete output from command /usr/local/bin/python -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-EfWsmy/pyodbc/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /tmp/pip-BV4sRM-record/install-record.txt --single-version-externally-managed --compile:
    running install
    running build
    running build_ext
    building 'pyodbc' extension
    creating build
    creating build/temp.linux-x86_64-2.7
    creating build/temp.linux-x86_64-2.7/src
    gcc -pthread -fno-strict-aliasing -g -O2 -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -fPIC -DPYODBC_VERSION=4.0.17 -DSQL_WCHART_CONVERT=1 -I/usr/local/include/python2.7 -c src/cursor.cpp -o build/temp.linux-x86_64-2.7/src/cursor.o -Wno-write-strings
    unable to execute 'gcc': No such file or directory
    error: command 'gcc' failed with exit status 1

    ----------------------------------------
Command "/usr/local/bin/python -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-EfWsmy/pyodbc/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /tmp/pip-BV4sRM-record/install-record.txt --single-version-externally-managed --compile" failed with error code 1 in /tmp/pip-build-EfWsmy/pyodbc/
The command '/bin/sh -c pip install -r requirements.txt' returned a non-zero code: 1

Answer 1

根据Kåre Rasmussen的回答，这里有一个完整的dockerfile供进一步使用。

确保根据您的架构编辑最后两行！它们应该反映到libtdsodbc的实际路径。so和libtdsS.so。

如果您不确定libtdsodbc的路径。so和libtdsS。所以，试试dpkg——搜索libtdsodbc。so和dpkg——搜索libtdsS.so。

FROM python:3

#Install FreeTDS and dependencies for PyODBC
RUN apt-get update && apt-get install -y tdsodbc unixodbc-dev \
 && apt install unixodbc-bin -y  \
 && apt-get clean -y

RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/arm-linux-gnueabi/odbc/libtdsodbc.so\n\
Setup = /usr/lib/arm-linux-gnueabi/odbc/libtdsS.so" >> /etc/odbcinst.ini

之后，安装PyODBC，复制您的应用程序并运行它。

Answer 2

最近通过运行这个，我发现有必要额外包含以下行（请注意，没有此步骤它不会构建）：

运行apt get install--重新安装build essential-y

完整的Dockerfile如下所示：

# parent image
FROM python:3.7-slim

# install FreeTDS and dependencies
RUN apt-get update \
 && apt-get install unixodbc -y \
 && apt-get install unixodbc-dev -y \
 && apt-get install freetds-dev -y \
 && apt-get install freetds-bin -y \
 && apt-get install tdsodbc -y \
 && apt-get install --reinstall build-essential -y

# populate "ocbcinst.ini"
RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so\n\
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so" >> /etc/odbcinst.ini

# install pyodbc (and, optionally, sqlalchemy)
RUN pip install --trusted-host pypi.python.org pyodbc==4.0.26 sqlalchemy==1.3.5

# run app.py upon container launch
CMD ["python", "app.py"]

这里有一种方法可以在应用程序内部建立连接。py，通过sqlalchemy（假设端口1433）：

import sqlalchemy as sa
args = (username, password, server, database)
connstr = "mssql+pyodbc://{}:{}@{}/{}?driver=FreeTDS&port=1433&odbc_options='TDS_Version=8.0'"
engine = sa.create_engine(connstr.format(*args))

Answer 3

需要运行：

sudo apt-get install gcc

需要添加一个odbcinst.ini文件，其中包含：

[FreeTDS]Description=FreeTDS Driver Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

需要在docker文件中添加文件夹

ADD odbcinst.ini /etc/odbcinst.ini
RUN apt-get update
RUN apt-get install -y tdsodbc unixodbc-dev
RUN apt install unixodbc-bin -y
RUN apt-get clean -y

需要将. py中的连接更改为

connection = pyodbc.connect('Driver={FreeTDS};'
                            'Server=xxxxx;'
                            'Database=DCMM;'
                            'UID=xxxxx;'
                            'PWD=xxxxx')

现在，容器编译并从SQL server获取数据