How to connect a python container Docker to a sqlserver

Question

I want to create a python3 container that can connect to a SQLSERVER, I’m using Django in my application and it doesn’t have a native driver to make that connection. I created an image to configure python3 with its drivers:

FROM ubuntu:19.10

# apt-get and system utilities
RUN apt-get clean && apt-get update && apt-get install -y \
    curl apt-utils apt-transport-https debconf-utils gcc build-essential g++-9

# adding custom MS repository
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

# install SQL Server drivers
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev

# install SQL Server tools
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN /bin/bash -c "source ~/.bashrc"

# python libraries
RUN apt-get update && apt-get install -y \
    python3.6 python3-pip python3-dev python3-setuptools \
    --no-install-recommends

# install necessary locales
RUN apt-get update && apt-get install -y locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen

RUN pip3 install --upgrade pip

# install SQL Server Python SQL Server connector module - pyodbc
RUN pip3 install pyodbc

And the dockerfile, which uses the image built above, of the application is:

FROM matheusrbarbosa/python3-mssql

RUN mkdir app

COPY . /app

RUN apt-get update

RUN pip3 install -r app/requirements.txt

EXPOSE 8000

WORKDIR /app/webservice/
CMD ["python3", "manage.py", "runserver", "0.0.0.0:8000"]

And the settings on settings.py is

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': DB_NAME,
        'USER': DB_USERNAME,
        'PASSWORD': DB_PASSWORD,
        'HOST': DB_HOST,
        'PORT': 1433,
    }
}

The connection values are passed by environment variables...

The problem is that I have tried everything to make the driver of this connection work, and have not succeeded. The driver error is:

('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

Answer 1

Going through the container files I found the . ini that supposedly indicates the ODBC version installed, there was ODBC Driver 17 for SQL Server, that is, the container driver version was 17, while the lib default was 13. So to point out and correct version of the driver added the following options on settings.py

DATABASES = {
'default': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': DB_NAME,
    'USER': DB_USERNAME,
    'PASSWORD': DB_PASSWORD,
    'HOST': DB_HOST,
    'PORT': 1433,
    'OPTIONS': {
        'driver': 'ODBC Driver 17 for SQL Server',
    },
  }
}

This way python will search for the driver of version 17.