docker_mssql
Docker and MSSQL

While working with the cloud platform with micro service architecture, developers generally tends to use database as a service that server the application’s database needs. Considering if the cloud platform is Microsoft Azure, then “SQL Database” service is the most commonly preferred by the developers which is backed by Microsoft’s own SQL Server (MSSQL).

This article aims to help developer to properly configure Docker file for Python and MSSQL application on Ubuntu image or to resolve database driver issues while using pyodbc library. Most of the time, the application is developed on windows systems and the driver compatibility is least considered until deployment. But during testing, few common database errors are:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'driver' : file not found (0) (SQLDriverConnect)")

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1' : file not found (0) (SQLDriverConnect)”)

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

At first glance, it seems common and easy to resolve this issue as suggested by numerous comments in Github issues and Stack overflow. For instance, switching to “FreeTDS” driver by creating odbcinst.ini file which in turn points to assorted “.so” library; or adding “TDS” version to 8.0 in connection string can resolve the above error. Well, the above steps are tedious and no guarantee!

Instead,

Follow these three simple steps:

  1. Change your pyodbc library to pypyodbc
  2. In code, change import statement “import pyodbc” to “import pypyodbc as pyodbc”
  3. Configure Dockerfile as shown below. [ line 8-13 are for MSSQL Driver]. For other Ubuntu version or Linux distribution visit Microsoft Page
FROM ubuntu:18.04

RUN apt-get update
RUN apt install -y curl
RUN apt-get update && apt-get install -y \
        software-properties-common

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN apt-get install -y unixodbc-dev

RUN apt-get install -y python3 python3-dev python3-pip nginx
RUN apt install python3.6-dev
RUN apt-get install build-essential python3.6-dev
RUN apt-get install -y libsm6 libxext6 libxrender-dev

COPY /app_v1-0 /app
WORKDIR /app/

RUN pip3 install -r requirements.txt

ENTRYPOINT ["python3"]
CMD ["stream.py"]

Voila! Goodbye to “pyodbc error”!

Feedback and suggestions related to this article is appreciated! Please let us know in comments.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.