Cannot connect to Azure SQL using an alpine docker image with Python

Question

I have an alpine based docker image, with support for Python, through which I am trying to connect to Azure SQL service. Here is my simple connection code. I am getting an error when connecting to SQL server in Azure.

conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")nect)")

import pyodbc
server = 'blah1.database.windows.net'
database = 'mydb1'
username = 'myadmin'
password = 'XXXXXX'
driver= 'ODBC Driver 17 for SQL Server'
conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
c = conn.cursor()
c.execute("SELECT * FROM dbo.customers")

print(c.fetchall())
print(type(c.fetchall()))

conn.commit()
conn.close()


Here is my Dockerfile: 

FROM tiangolo/uwsgi-nginx:python3.7-alpine3.8

RUN apk update
RUN apk add gcc libc-dev g++ libffi-dev libxml2 unixodbc-dev

LABEL Name=code9 Version=0.0.1
EXPOSE 8000
ENV LISTEN_PORT=8000

ENV UWSGI_INI uwsgi.ini

WORKDIR /app
ADD . /app

RUN chmod g+w /app
RUN chmod g+w /app/db.sqlite3


RUN python3 -m pip install -r requirements.txt

I am assuming that I am unixODBC will take care of the connection with SQL server in Azure or do I need to install MS SQL driver for alpine? Is there one available? I couldn't find one. Please help.

@stackoverflow.com/users/7867968/c-nivs. Here is error message: conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")nect)") — mujno
@stackoverflow.com/users/7867968/c-nivs - empty array [ ] Also, did you erase a detailed reply from this thread? I swear I saw something from you and now when I have come back to this thread. it is gone. Will you please post it back. It was about using Debian image and finding drivers.... — mujno
I put it back, but note that it doesn't use an alpine image, which is why I deleted it, so I'm not entirely sure it will work in an alpine environment (since that's using apk add and not apt-get, among other things) — C.Nivs

C.Nivs C.Nivs · Accepted Answer · 2019-04-29T19:02:36

To confirm setup:

apk update && apk add build-base unixodbc-dev freetds-dev
pip install pyodbc

Why install both unixodbc and freetds? Pyodbc's pip install requires the packages in unixodbc-dev and the gcc libraries in build-base, so no getting around that. The freetds driver tends to have fewer issues with pyodbc, and is leaned on heavily by pymssql, which I've been using in docker in lieu of pyodbc. That's a personal preference, though, you could just include the unixodbc driver. Now, to find the driver

import pyodbc
pyodbc.drivers()
# []

Pyodbc can't locate them, but they are definitely installed, so we can find them with a shell script:

find / -name *odbc.so
/usr/lib/libtdsodbc.so
/usr/lib/libodbc.so

Now, we can automate this with the subprocess library to set the driver location manually:

import subprocess

s = subprocess.Popen('find / -name *odbc.so -type f', stdout=subprocess.PIPE, shell=True).communicate()

f, _ = s

# You can change this particular loop to select whatever driver you prefer
driver = [driver for driver in f.decode().split() if 'tds' in driver][0]

driver
# '/usr/lib/libtdsodbc.so'

username = 'someuser'
server = 'someserver'
database = 'somedatabase'

conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

Alternatively, you can add the configuration to /etc/odbcinst.ini as mentioned here:

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

Then

import pyodbc

pyodbc.drivers()
['FreeTDS']

Cannot connect to Azure SQL using an alpine docker image with Python

2 Answers