Connect docker python to SQL server with pyodbc

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

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

Based on Kåre Rasmussen's answer, here's a complete dockerfile for further use.

Make sure to edit the last two lines according to your architecture! They should reflect the actual paths to libtdsodbc.so and libtdsS.so.

If you're not sure about the paths to libtdsodbc.so and libtdsS.so, try dpkg --search libtdsodbc.so and dpkg --search 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

Afterwards, install PyODBC, COPY your app and run it.

For me to solve this issue I also had to add the following 2 lines in the dockerfile:

RUN echo MinProtocol = TLSv1.0 >> /etc/ssl/openssl.cnf
RUN echo CipherString = DEFAULT@SECLEVEL=1 >> /etc/ssl/openssl.cnf