Using pyodbc module in Linux based Azure App Service

Published Jan 06 2021 07:58 PM 3,639 Views
Microsoft

 

Pyodbc is an open-source Python module that helps in accessing ODBC database.


The following blog explains on how this module can be used for Linux based Azure App Service.

  1. Using Blessed Image:
    The blessed image of Linux based Azure App Service doesn’t have this module installed by default, as this is used for a specific business logic.

    The following steps helps in the installation of this module on blessed images:
    1. Deploy your code via Git repository or via local Git.
    2. The App Service deployment engine automatically activates  a virtual environment and runs pip install -r requirement.txt for you when you deploy to a Git repository or perform zip package deployment.

      You could refer to our documentation at Customize Build Automation which explains this in detail.

  2. Using Custom Image:
    You could build a custom image with pyodbc pre-installed.

    A sample image is available at vijaysaayi/Using-Pyodbc-in-Linux-App-Service (github.com)  

  The folder structure is as follows :

  vijaysaayi_0-1609313998942.jpeg

               

A Dockerfile is a text document that contains all the commands a user could call on the command line to assemble an image. This file is necessary when you’re creating a custom docker container image.

As you can see from the contents of my Dockerfile, I’ve mentioned the pre-requisites needed to install pyodbc: apt-get update and apt-get install -y --no-install-recommends build-essential gcc unixodbc-dev. Once the pre-requisites are installed, I install requirements.txt in my image. I also installed SSH on the image, since SSH needs to be specifically enabled on custom images.

 

02 - dockerfile.png

 

Here’s what my requirements.txt looks like:

vijaysaayi_2-1609313998981.jpeg

 

The entry point for my App is init.sh and does 2 things:

  1. Start the SSH Service
  2. Run main.py which starts a flask webserver
     vijaysaayi_3-1609313998983.jpeg

Now since pyodbc has been successfully installed already, it can be directly.

 

The following is my sample App Service that that return the first cell value from a given tableName

05 - main-py.png

 

To build this image, make sure you have the Docker Extension installed on Visual Studio Code. Once you have this installed, you can easily, build the image by Right click Dockerfile > Build Image.

I connected my VSCode to my Dockerhub account and pushed my image to my Dockerhub repository on hub.docker.com.

You can choose to push your image to Dockerhub or Azure Container Registry or a private registry of your choice.

vijaysaayi_5-1609313999004.jpeg


Here’s the link to the custom image pushed to DockerHub:

https://hub.docker.com/repository/docker/vijaysaayi/pyodbc-linux-appservice-demo

 

The following steps could help you deploy the above image to your Linux based App Service in case you would like to test it.

  1. Create an App Service Web App Container with the following configuration:
    07 - create new webapp.png
    07 - create new webapp - page 2.png

  2. Your DB connection string value in App Setting needs to be in the following format:
    DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password
  3. When you browse to the App Service , you should see the following response.  08 - sample response.png

               

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2038985%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20pyodbc%20module%20in%20Linux%20based%20App%20Services%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2038985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fbf.am%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EWelcome%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2021962%22%20slang%3D%22en-US%22%3EUsing%20pyodbc%20module%20in%20Linux%20based%20Azure%20App%20Service%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021962%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fpypi.org%2Fproject%2Fpyodbc%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EPyodbc%3C%2FA%3E%20is%20an%20open-source%20Python%20module%20that%20helps%20in%20accessing%20ODBC%20database.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EThe%20following%20blog%20explains%20on%20how%20this%20module%20can%20be%20used%20for%20Linux%20based%20Azure%20App%20Service.%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3E%3CSTRONG%3E%3CU%3EUsing%20Blessed%20Image%3A%3CBR%20%2F%3E%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FSTRONG%3EThe%20blessed%20image%20of%20Linux%20based%20Azure%20App%20Service%20doesn%E2%80%99t%20have%20this%20module%20installed%20by%20default%2C%20as%20this%20is%20used%20for%20a%20specific%20business%20logic.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20following%20steps%20helps%20in%20the%20installation%20of%20this%20module%20on%20blessed%20images%3A%3COL%20class%3D%22lia-list-style-type-lower-roman%22%3E%0A%3CLI%3EDeploy%20your%20code%20via%20Git%20repository%20or%20via%20local%20Git.%3C%2FLI%3E%0A%3CLI%3EThe%20App%20Service%20deployment%20engine%20automatically%20activates%26nbsp%3B%20a%20virtual%20environment%20and%20runs%20%3CSTRONG%3Epip%20install%20-r%20requirement.txt%3C%2FSTRONG%3E%20for%20you%20when%20you%20deploy%20to%20a%20Git%20repository%20or%20perform%20zip%20package%20deployment.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20refer%20to%20our%20documentation%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fnam06.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fapp-service%252Fconfigure-language-python%2523customize-build-automation%26amp%3Bdata%3D04%257C01%257CVijay.Saayi%2540microsoft.com%257C540f166da86e4eae5cfb08d8a6c32e92%257C72f988bf86f141af91ab2d7cd011db47%257C1%257C0%257C637442704575205589%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26amp%3Bsdata%3DuL8gkfcsCiYP4HyXfbV4VrsGpfLNxPWyTs0aUCs%252FeRk%253D%26amp%3Breserved%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ECustomize%20Build%20Automation%3C%2FA%3E%26nbsp%3Bwhich%20explains%20this%20in%20detail.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3E%3CU%3EUsing%20Custom%20Image%3A%3CBR%20%2F%3E%3C%2FU%3E%3C%2FSTRONG%3EYou%20could%20build%20a%20custom%20image%20with%20pyodbc%20pre-installed.%3CBR%20%2F%3E%3CBR%20%2F%3EA%20sample%20image%20is%20available%20at%20%3CA%20style%3D%22font-family%3A%20inherit%3B%20background-color%3A%20%23ffffff%3B%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fvijaysaayi%2FUsing-Pyodbc-in-Linux-App-Service%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Evijaysaayi%2FUsing-Pyodbc-in-Linux-App-Service%20(github.com)%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%20The%20folder%20structure%20is%20as%20follows%20%3A%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22vijaysaayi_0-1609313998942.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243436iF91AD2970D6A9F49%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22vijaysaayi_0-1609313998942.jpeg%22%20alt%3D%22vijaysaayi_0-1609313998942.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EA%20%3CSTRONG%3EDockerfile%3C%2FSTRONG%3E%20is%20a%20text%20document%20that%20contains%20all%20the%20commands%20a%20user%20could%20call%20on%20the%20command%20line%20to%20assemble%20an%20image.%20This%20file%20is%20necessary%20when%20you%E2%80%99re%20creating%20a%20custom%20docker%20container%20image.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EAs%20you%20can%20see%20from%20the%20contents%20of%20my%20Dockerfile%2C%20I%E2%80%99ve%20mentioned%20the%20pre-requisites%20needed%20to%20install%20pyodbc%3A%20%3CSTRONG%3Eapt-get%20update%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Eapt-get%20install%20-y%20--no-install-recommends%20build-essential%20gcc%20unixodbc-dev.%20%3C%2FSTRONG%3EOnce%20the%20pre-requisites%20are%20installed%2C%20I%20install%20requirements.txt%20in%20my%20image.%20I%20also%20installed%20SSH%20on%20the%20image%2C%20since%20SSH%20needs%20to%20be%20specifically%20enabled%20on%20custom%20images.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2202%20-%20dockerfile.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243453i88EABBBCC92B1C6B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%2202%20-%20dockerfile.png%22%20alt%3D%2202%20-%20dockerfile.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EHere%E2%80%99s%20what%20my%20requirements.txt%20looks%20like%3A%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22vijaysaayi_2-1609313998981.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243438i713C2EB93E6A75CA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22vijaysaayi_2-1609313998981.jpeg%22%20alt%3D%22vijaysaayi_2-1609313998981.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EThe%20entry%20point%20for%20my%20App%20is%20init.sh%20and%20does%202%20things%3A%3C%2FP%3E%0A%3COL%20class%3D%22lia-list-style-type-lower-roman%22%3E%0A%3CLI%20class%3D%22lia-indent-padding-left-30px%22%3EStart%20the%20SSH%20Service%3C%2FLI%3E%0A%3CLI%20class%3D%22lia-indent-padding-left-30px%22%3ERun%20main.py%20which%20starts%20a%20flask%20webserver%3CBR%20%2F%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22vijaysaayi_3-1609313998983.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243439i9C0A8EC41DF13405%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22vijaysaayi_3-1609313998983.jpeg%22%20alt%3D%22vijaysaayi_3-1609313998983.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ENow%20since%20pyodbc%20has%20been%20successfully%20installed%20already%2C%20it%20can%20be%20directly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EThe%20following%20is%20my%20sample%20App%20Service%20that%20that%20return%20the%20first%20cell%20value%20from%20a%20given%20tableName%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2205%20-%20main-py.png%22%20style%3D%22width%3A%20447px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244692i3D2DFCDAF75E28E4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%2205%20-%20main-py.png%22%20alt%3D%2205%20-%20main-py.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ETo%20build%20this%20image%2C%20make%20sure%20you%20have%20the%20%3CSTRONG%3EDocker%20Extension%3C%2FSTRONG%3E%20installed%20on%20Visual%20Studio%20Code.%20Once%20you%20have%20this%20installed%2C%20you%20can%20easily%2C%20build%20the%20image%20by%20%3CSTRONG%3ERight%20click%20Dockerfile%20%26gt%3B%20Build%20Image%3C%2FSTRONG%3E.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EI%20connected%20my%20VSCode%20to%20my%20Dockerhub%20account%20and%20pushed%20my%20image%20to%20my%20Dockerhub%20repository%20on%20hub.docker.com.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EYou%20can%20choose%20to%20push%20your%20image%20to%20Dockerhub%20or%20Azure%20Container%20Registry%20or%20a%20private%20registry%20of%20your%20choice.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22vijaysaayi_5-1609313999004.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243440iE8ABF0F91DE58FF3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22vijaysaayi_5-1609313999004.jpeg%22%20alt%3D%22vijaysaayi_5-1609313999004.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CBR%20%2F%3EHere%E2%80%99s%20the%20link%20to%20the%20custom%20image%20pushed%20to%20DockerHub%3A%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CA%20href%3D%22https%3A%2F%2Fhub.docker.com%2Frepository%2Fdocker%2Fvijaysaayi%2Fpyodbc-linux-appservice-demo%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fhub.docker.com%2Frepository%2Fdocker%2Fvijaysaayi%2Fpyodbc-linux-appservice-demo%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EThe%20following%20steps%20could%20help%20you%20deploy%20the%20above%20image%20to%20your%20Linux%20based%20App%20Service%20in%20case%20you%20would%20like%20to%20test%20it.%3C%2FP%3E%0A%3COL%20class%3D%22lia-list-style-type-lower-roman%22%3E%0A%3CLI%20class%3D%22lia-indent-padding-left-30px%22%3ECreate%20an%20App%20Service%20Web%20App%20Container%20with%20the%20following%20configuration%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2207%20-%20create%20new%20webapp.png%22%20style%3D%22width%3A%20758px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243449iA7C7908619600809%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%2207%20-%20create%20new%20webapp.png%22%20alt%3D%2207%20-%20create%20new%20webapp.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2207%20-%20create%20new%20webapp%20-%20page%202.png%22%20style%3D%22width%3A%20734px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243450i291FF29D816CAE06%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%2207%20-%20create%20new%20webapp%20-%20page%202.png%22%20alt%3D%2207%20-%20create%20new%20webapp%20-%20page%202.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22lia-indent-padding-left-30px%22%3EYour%20%3CSTRONG%3EDB%20connection%20string%3C%2FSTRONG%3E%20value%20in%20App%20Setting%20needs%20to%20be%20in%20the%20following%20format%3A%20%3CBR%20%2F%3E%3CSTRONG%3E%3CEM%3EDRIVER%3D%7BODBC%20Driver%2017%20for%20SQL%20Server%7D%3BSERVER%3D'%2Bserver%2B'%3BDATABASE%3D'%2Bdatabase%2B'%3BUID%3D'%2Busername%2B'%3BPWD%3D'%2B%20password%3C%2FEM%3E%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22lia-indent-padding-left-30px%22%3EWhen%20you%20browse%20to%20the%20App%20Service%20%2C%20you%20should%20see%20the%20following%20response.%26nbsp%3B%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2208%20-%20sample%20response.png%22%20style%3D%22width%3A%20952px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243451i09F7A35502A4C14C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%2208%20-%20sample%20response.png%22%20alt%3D%2208%20-%20sample%20response.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2021962%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20following%20blog%20explains%20on%20how%20pyodbc%20module%20can%20be%20used%20for%20Linux%20based%20Azure%20App%20Service.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2021962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20App%20Service%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPython%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWeb%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 07 2021 05:01 PM
Updated by: