Building pyodbc for Lambda’s Python 3.9 Runtime

Getting Python to connect to MSSQL in Lambda isn’t as simple as installing other dependencies via pip. For earlier versions of Lambda runtimes, I followed the pattern outlined in this gist which builds both unixODBC and pyodbc and prepares them for inclusion in a Lambda layer. This has worked well for me to date, but I recently needed to solve this for a new project using Python 3.9.

My approach uses a multi-stage Dockerfile based on the Lambda Python 3.9 base image:

FROM public.ecr.aws/lambda/python:3.9 as builder

ENV ODBCINI=/opt/odbc.ini
ENV ODBCSYSINI=/opt/
ARG UNIXODBC_VERSION=2.3.9

RUN yum install -y gzip tar openssl-devel && yum groupinstall "Development Tools" -y

RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-${UNIXODBC_VERSION}.tar.gz -O \
    && tar xzvf unixODBC-${UNIXODBC_VERSION}.tar.gz \
    && cd unixODBC-${UNIXODBC_VERSION} \
    && ./configure --sysconfdir=/opt --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/opt \
    && make \
    && make install

RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
RUN yum install e2fsprogs.x86_64 0:1.43.5-2.43.amzn1 fuse-libs.x86_64 0:2.9.4-1.18.amzn1 libss.x86_64 0:1.43.5-2.43.amzn1 -y
RUN ACCEPT_EULA=Y yum install -y msodbcsql17

ENV CFLAGS="-I/opt/include"
ENV LDFLAGS="-L/opt/lib"

RUN mkdir /opt/python/ && cd /opt/python/ && pip install pyodbc -t .

FROM public.ecr.aws/lambda/python:3.9

COPY --from=builder /opt/python /opt/python
COPY --from=builder /opt/microsoft /opt/microsoft
COPY --from=builder /opt/lib /opt/lib

Lambda Layer

To create a Lambda layer using this approach, first build the image:

docker build -t pyodbc-3-9 .

Next, you’ll want to extract the dependencies from the filesystem (available in /opt):

docker run --rm --entrypoint bash -v $PWD:/local pyodbc-3-9 -c "cp -R /opt /local"

Now you’ve got everything needed to build the layer on your local filesystem under ./opt. The next step is to zip it up and create the layer. I handled this with terraform in order to easily make the layer available in all environments:

locals {
  zip_file = "./${var.name}.zip"
}


data "archive_file" "init" {
  type          = "zip"
  output_path   = local.zip_file
  source_dir    = "./opt"
}

resource "aws_lambda_layer_version" "lambda_layer" {
  filename            = local.zip_file
  description         = "This Lambda layer includes pyodbc dependencies"
  layer_name          = "${var.name}-${terraform.workspace}"
  compatible_runtimes = ["python3.9"]
  source_code_hash    = data.archive_file.init.output_base64sha256
}

Container Images

Another approach is to use a Lambda container image. This might be useful if you have other runtime dependencies that require building outside of pip. The trade-off here is that you are required to fully manage the image, including copying your app code to the /var/task directory as well as installing all dependencies.

An example using pipenv might look something like this:

# Above builder code snipped for brevity

# ...

FROM public.ecr.aws/lambda/python:3.9

COPY --from=builder /opt/python /opt/python
COPY --from=builder /opt/microsoft /opt/microsoft
COPY --from=builder /opt/lib /opt/lib

RUN pip install --upgrade pip && pip install pipenv

COPY Pipfile .
COPY Pipfile.lock .

RUN pipenv lock --requirements --keep-outdated > requirements.txt && pip install -r requirements.txt -t /var/task

COPY app ./app

CMD ["app.alb_handler"]

As you can see, after installing dependencies and adding your code, you’ll need to set the handler. If you’re using the Serverless Framework, you can build and deploy the image along with your Lambda with something like this:

provider:
  name: aws
  timeout: 60
  memorySize: 512
  versionFunctions: false
  stage: ${opt:stage, "dev"}
  region: us-east-1
  ecr:
    images:
      appimage:
        path: ./

functions:
  api:
    image:
      name: appimage

Example Usage

Once the dependencies are in place, you can begin using pyodbc:

import pyodbc

conn_str = (    
    r'DRIVER=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1;'
    r'SERVER=DB_HOST;'
    r'DATABASE=DB_NAME;'
    r'UID=user;'
    r'PWD=pass;'
)

cnxn = pyodbc.connect(conn_str)

Or pyodbc with SQLAlchemy:

params = urllib.parse.quote_plus(conn_str)
engine_str = f"mssql+pyodbc:///?odbc_connect={params}"
engine = create_engine(engine_str)
Session = sessionmaker(bind=engine)
session = Session()
Share this: Facebooktwitterlinkedin