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: