How to pip install pyodbc in the Dockerfile?

1

One of my tasks involves using pyodbc package to insert records in an Azure SQL database. I am unable to install the package via requirements.txt. From what I have read around, I need to install odbc in the Dockerfile itself. The instructions are available from Microsoft below, but I am having a hard time writing them for the Dockerfile.

Source:

Dockerfile:
FROM quay.io/astronomer/ap-airflow:1.10.14-1-buster-onbuild

Also, will this fix my issue, or is there another way around achieving my task of inserting records?

2

I was able to make it work. For anyone else facing the same issue. Make sure on Azure portal you have added the Client IP Address for Astronomer.

Dockerfile

FROM quay.io/astronomer/ap-airflow:1.10.14-1-buster-onbuild

USER root

RUN apt-get update \
  && apt-get -y install gcc gnupg2 \
  && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
  && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list

RUN apt-get update \
  && ACCEPT_EULA=Y apt-get -y install msodbcsql17 \
  && ACCEPT_EULA=Y apt-get -y install mssql-tools

RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc \
  && echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc \
  && source ~/.bashrc

RUN apt-get -y install unixodbc-dev \
  && apt-get -y install python-pip \
  && pip install pyodbc

example-pyodbc.py

from airflow import DAG
from airflow.utils.dates import days_ago
from airflow.operators.python_operator import PythonOperator

from datetime import datetime
import pyodbc

default_args = {
    'owner': 'airflow',
}

dag = DAG(
    'example_pyodbc',
    default_args=default_args,
    start_date=days_ago(2),
    tags=['example'],
)

def insert_date():

    f = "%Y-%m-%d %H:%M:%S"
    now = datetime.now()
    now_f = now.strftime(f)

    server = 'tcp:xyz.database.windows.net'
    database = 'xyz'
    username = 'xyz'
    password = 'xyz'   
    driver= '{ODBC Driver 17 for SQL Server}'

    try:
        cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+
                        ';PORT=1433;DATABASE='+database+
                        ';UID='+username+';PWD='+ password)
        cursor = cnxn.cursor()
        cursor.execute("INSERT INTO MY_TABLE VALUES('" + now_f +"')")
        cursor.commit()
        print("Success")
    except Exception as error:
        print(error)

insert_date_task = PythonOperator(
    task_id='insert_date',
    python_callable=insert_date,
    dag=dag
)

insert_date_task