Azure Functions + Docker + PyODBC

In one of our previous articles we explained how to create an Azure Function with Docker Image.

However installing PyODBC may turn out to be a huge challenge as there a plenty of additional dependencies to be installed. Moreover, if you are willing to connect your function to the SQL Server Database it may be even more complicated to install as there are different procedures for different linux distributions.

So, the questions:

What is the linux distribution used in mcr.microsoft.com/azure-functions/base ?

To find it out I analyzed the output log of my previous docker image, especially this part

sudo apt-get -y install poppler-utils

After analyzing the output it is possible to conclude that the linux distribution used in Azure Functions is Debian 9. Great! Now we need to go to the microsoft's official documentation and carefully study step by step the installation process.

Debian 8 and 9

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

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Debian 8
curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Debian 9
curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

Excellent! After we see how to install the ODBC dryver, we need to access the database from the code.

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Important: don't forget to add tcp before the server name.

And here is the docker image

FROM mcr.microsoft.com / azure - functions / python: 2.0

ENV AzureWebJobsScriptRoot = /home/site / wwwroot\
AzureFunctionsJobHost__Logging__Console__IsEnabled = true

COPY. / home / site / wwwroot

RUN apt - get update && \
  apt - get - y install sudo

RUN sudo apt - get - y install poppler - utils

RUN apt - get - y install curl
RUN apt - get - y install--reinstall build - essential
RUN sudo curl https: //packages.microsoft.com/keys/microsoft.asc | apt-key add -
 RUN sudo curl curl https: //packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list
 RUN sudo ACCEPT_EULA = Y apt - get install msodbcsql17# optional: for bcp and sqlcmd
RUN sudo ACCEPT_EULA = Y apt - get install mssql - tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN.~/.bashrc#
optional: for unixODBC development headers
RUN sudo apt - get install - y unixodbc - dev

RUN cd / home / site / wwwroot && \
  pip install - r requirements.txt

And of course, in your requirements.txt you need to put pyodbc.

The following steps are quite straightforward: launch docker build, docker start and docker push.

Works like a charm. Enjoy