3

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.

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

When connecting to SQL server in Azure, the code generates the following error:

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

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.

Jaroslav Bezděk
  • 6,967
  • 6
  • 29
  • 46
mujno
  • 121
  • 2
  • 12
  • What error are you getting specifically? – C.Nivs Apr 29 '19 at 14:06
  • 1
    @https://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 Apr 29 '19 at 18:23
  • What is the output of `pyodbc.drivers()`? – C.Nivs Apr 29 '19 at 18:46
  • @https://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 Apr 29 '19 at 20:39
  • 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 Apr 29 '19 at 20:40
  • I added an edit for alpine, it's a bit convoluted, so I'm not too sure about keeping it up here – C.Nivs Apr 29 '19 at 21:13

2 Answers2

6

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']
C.Nivs
  • 12,353
  • 2
  • 19
  • 44
  • 2
    This was it!!! Thank You! You saved me so much time and hassle @stackoverflow.com/users/7867968/c-nivs . Using FreeTDS and then the associated driver name in the script resolved the issue for me. – mujno Apr 29 '19 at 21:40
  • 1
    Awesome! This has also helped me too. I had to change /etc/odbcins.ini to /etc/odbcinst.ini – Alexis Lucattini Oct 08 '19 at 02:46
0
RUN wget https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.7.2.1-1_amd64.apk  && \
    wget https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.7.1.1-1_amd64.apk  && \
    apk add --allow-untrusted msodbcsql17_17.7.2.1-1_amd64.apk && \
    apk add --allow-untrusted mssql-tools_17.7.1.1-1_amd64.apk
vitaliytv
  • 694
  • 7
  • 9