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

Post your code and complete exception details and also how you launched the container — Tarun Lalwani, Sep 25 '17 at 14:38

score 33 · Accepted Answer · answered Sep 27 '17 at 11:11

33

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

answered Sep 27 '17 at 11:11

Kåre Rasmussen

863
1
8
16

13

Still facing issue ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found – Apurva jain Apr 22 '20 at 09:05
This did not solved my problem either. Still facing the same issue as Apurva jain. – Lucas Thimoteo Dec 26 '20 at 03:13
"sudo apt install build-essential" in case "sudo apt-get install gcc" gives you a not found error – Rudig Jun 07 '21 at 08:22

Mark Richardson · Answer 2 · 2020-01-15T16:29:54.997

29

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

edited Jan 15 '20 at 16:29

answered Oct 17 '19 at 16:02

Mark Richardson

488
5
8

what about odbc.ini ? do I need to populate it as well? – Niv Cohen Dec 01 '19 at 12:58
2

You could add something along the lines of the following ... # Populate "odbc.ini" RUN echo "[]\n\ Driver = FreeTDS\n\ Address = \n\ Port = {your_db_port[usually 1433]}\n\ Database = \n\ TDS_Version = 8.0\n" >> /etc/odbc.ini ... in principle all of this information can be passed into the pyodbc connection string, so I believe this step is not strictly necessary. – Mark Richardson Dec 10 '19 at 15:22
Works like a charm - without even populating odbc.ini – Apurva jain Apr 22 '20 at 10:58
Thanks for your comment. I have installed FreeTDS and its dependencies and populated ocbcinst.ini as you suggested in your dockerfile and now I am supposed to connect to a postgres database using the connect function of pyodbc, i.e. cnxn = pyodbc.connect( Driver='FreeTDS', Server='otda_postgres_1 # name of container with the postgres DB Database='postgres', UID=USERNAME, # my username PWD=PASSWORD, # my password autocommit=True # TODO: Set autocommit to False after successfull debugging ) – Kevin Südmersen Jul 26 '20 at 17:32
2

However I get the following error: pyodbc.OperationalError: ('08S01', '[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)'). Do you happen to know why? – Kevin Südmersen Jul 26 '20 at 17:33

Phonolog · Answer 3 · 2019-09-11T10:33:40.607

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.

adding a "RUN ls -l /usr/lib/x86_64-linux-gnu/odbc/" line in the Dockerfile after the installations will list the directory do you can confirm if your files are present or not. — Nikhil VJ, Mar 13 '20 at 13:21

score 8 · Answer 4 · answered May 02 '20 at 17:59

I was unable to use all of the above resolutions, I was keeping al kind of errors relating to the pyodbc package, in particular:

ImportError: libodbc.so.2: cannot open shared object file: No such file or directory.

I ended up with another resolution which defines the ODBC SQL Server Driver specifically for an Ubuntu 18.04 Docker image, in this case ODBC Driver 17 for SQL Server. In my specific use case I needed to make the connection to my MySQL database server on Azure via Flask SQLAlchemy, but the latter is not a necessity for the Docker configuration.

Dockerfile, with most important part adding the Microsoft repository and installing msodbcsql17 and unixodbc-dev:

# Ubuntu 18.04 base with Python runtime and pyodbc to connect to SQL Server
FROM ubuntu:18.04

WORKDIR /app

# apt-get and system utilities
RUN apt-get update && apt-get install -y \
    curl apt-utils apt-transport-https debconf-utils gcc build-essential g++-5\
    && rm -rf /var/lib/apt/lists/*

# adding custom Microsoft repository
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

# 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 -y && \
    apt-get install -y python3-pip python3-dev

# install necessary locales, this prevents any locale errors related to Microsoft packages
RUN apt-get update && apt-get install -y locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen

# copy requirements and install packages, I added this for general use
COPY ./requirements.txt > ./requirements.txt
RUN pip3 install -r ./requirements.txt
# you can also use regular install of the packages
RUN pip3 install pyodbc SQLAlchemy

# and if you are also planning to use Flask and Flask-SQLAlchemy
Run pip3 install Flask Flask-SQLAlchemy

COPY ..

# run your app via entrypoint or change the CMD command to your regular command
COPY docker-entrypoint.sh wsgi.py ./
CMD ["./docker-entrypoint.sh"]

This should build without any errors in Docker.

My database url looked like this:

import urllib.parse

# name the sepcific ODBC driver by version number, we installed msodbcsql17
params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=<your.database.windows.net>;DATABASE=<your-db-name>;UID=<username>;PWD=<password>")

db_uri = "mssql+pyodbc:///?odbc_connect={PARAMS}".format(PARAMS=params)

And for the bonus if you are using Flask-SQLAlchemy, your app config should contain something like this:

app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_DATABASE_URI"] = db_uri  # from above

Happy coding!

score 7 · Answer 5 · answered Jul 14 '20 at 19:32

How to install the necessary dependencies for pyodbc is related to the linux distribution and its version (in docker case, that is the base image of your docker image). If none of the above work for you, you can figure out the commands by trying in the docker container instance.

First, exec into the docker container

docker exec -it <container id> bash

Try various ways to get the distribution name and version of your linux. Then try different instructions in Install the Microsoft ODBC driver for SQL Server (Linux)

Here is a working example for Debian 9 based images, deriving exactly as the document instructions.

# Install pyodbc dependencies
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get -y install msodbcsql17
RUN apt-get -y install unixodbc-dev
RUN pip install pyodbc

score 5 · Answer 6 · answered Sep 02 '21 at 04:22

5

I created a Gist on GitHub on how to do this. I hope it helps. I had to piece things together from what I found on different resources.

https://gist.github.com/joshatxantie/4bcf5d0243fba63845fce7cc40365a3a

Goodluck!

answered Sep 02 '21 at 04:22

Josh Smith

111
1
2

1

This was the only solution that worked with me, thanks – Alex Feb 22 '22 at 22:47
For the sake of this question, I do wish this answer had MANY more votes. Very good gist - thank you for the time you took to write it. – Thom Ives Mar 02 '23 at 23:11

score 4 · Answer 7 · answered Jan 10 '20 at 08:12

4

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

answered Jan 10 '20 at 08:12

prp

914
1
9
24

1

That solved the problem to me too, outside the docker image everything works, even `sqlcmd`, but inside docker image, only this 2 configurations worked fine! Thanks a lot! – eddy85br Mar 05 '20 at 21:39

saeedghadiri · Answer 8 · 2022-09-04T15:13:30.670

For those who wanted to do official microsoft approach to install odbc driver and use python:slim docker image, you can use this as DockerFile:

FROM python:3.9-slim

RUN apt-get -y update && apt-get install -y curl gnupg

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

# download appropriate package for the OS version
# Debian 11
RUN curl https://packages.microsoft.com/config/debian/11/prod.list  \
    > /etc/apt/sources.list.d/mssql-release.list

RUN exit
RUN apt-get -y update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18

Then for sqlalchemy's this can be called:

con_str = f"mssql+pyodbc://{username}:{password}@{host}/{db}?" \
              "driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes"

engine = create_engine(con_str)

I got `error: command 'gcc' failed: No such file or directory`. So changed line 2 to `RUN apt-get -y update && apt-get install -y curl gnupg gcc build-essential`. And last line to `RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc-dev` — viggnah, Feb 01 '23 at 06:24

score 1 · Answer 9 · answered Jan 16 '20 at 21:00

1

I fixed this problem by using pypyodbc instead of pyodbc.

pip install pypyodbc==1.3.5

https://pypi.org/project/pypyodbc/

Found the hint here: https://github.com/Azure/azure-functions-python-worker/issues/249

answered Jan 16 '20 at 21:00

JCM

365
1
2
15

sempaidev · Answer 10 · 2022-11-16T02:32:56.337

1

For not more problem use library for python pymssql this not need install driver

pip install pymssql

import pymssql

conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor(as_dict=True)
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
    print("ID=%d, Name=%s" % (row['id'], row['name']))

conn.close()

and work in docker

edited Nov 16 '22 at 02:32

answered Nov 15 '22 at 23:00

sempaidev

11
2

score 0 · Answer 11 · edited Mar 25 '23 at 10:06

To get pyodbc working with ODBC Driver 17 for SQL Server in my container I did the following:

Get the microsoft.asc key

Here is where to get it from: https://packages.microsoft.com/keys/microsoft.asc

Update Docker file to install and recognize ODBC Driver 17 for SQL Server 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 \
  && apt-get install -y libltdl7 \
  && apt-get install -y libodbc1 \
  && apt-get install -y odbcinst \
  && apt-get install -y odbcinst1debian2 \
  && apt-get install -y locales-all
    
COPY microsoft.asc /usr/
RUN apt-get update
RUN apt-get update \
  && apt-get install -y curl apt-transport-https locales gnupg2 \
  && apt-key add /usr/microsoft.asc
    
RUN curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN exit
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN echo "[ODBC Driver 17 for SQL Server]\n\
  Description=Microsoft ODBC Driver 17 for SQL Server\n\
  Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1\n\
  UsageCount=1" >> /etc/odbcinst.ini

Connect docker python to SQL server with pyodbc

11 Answers11

Linked