Connect docker python to SQL server with pyodbc

Question

I'm trying to connect a pyodbc python script running in a docker container to login to a MSSQL database I have tried all sorts of docker files, but not been able to make the connection (fails when bulding the docker or when python tries to connect), Does anyone have a working dockerfile, using pyodbc:

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"] 

requirements.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() 

Bulding the container docker build -t sqltest .

Output:

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

Need to Run:

sudo apt-get install gcc 

need to add a odbcinst.ini file containing:

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

need to add folowing to docker file

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 

need to change connection in .py to

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

Now the container compiles, and gets data from SQL server

Answer 2

Running through this recently I found it was necessary to additionally include the following line (note that it did not build without this step):

RUN apt-get install --reinstall build-essential -y

The full Dockerfile looks as follows:

# 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"] 

Here's one way to then actually establish the connection inside app.py, via sqlalchemy (and assuming port 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))