Building pyodbc for Lambda’s Python 3.9 Runtime

Dockerfile for lambda layer: pyodbc python3.9

#Runtime python3.9 update 삽질 성공!
#Resource = AWS Cloud9 Linux2
#ref = https://randywestergren.com/building-pyodbc-for-lambdas-python-3-9-runtime/

#setting
$ mkdir folder
$ vim Dockerfile

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

ARG UNIXODBC_VERSION=2.3.9
WORKDIR /root

RUN yum -y update
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/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home \
    && make install \
    && cd .. \
    && mv /home/* . \
    && mv unixODBC-${UNIXODBC_VERSION} unixODBC-${UNIXODBC_VERSION}.tar.gz /tmp
    
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo \
    && yum -y install freetds e2fsprogs openssl \
    && ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" \
    && export LDFLAGS="-L/root/lib" \
    && pip install pyodbc adodbapi pyDes --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 17 for SQL Server]\nDriver = ODBC Driver 17 for SQL Server\nDescription = My ODBC Driver 17 for SQL Server\nTrace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDescription = Microsoft ODBC Driver 17 for SQL Server\nDriver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1\nUsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python \
    && cp -r /root/* /opt/python \
    && mv /opt/python/lib /opt \
    && mv /opt/python/bin /opt \
    && cd /opt \
    && zip -r /pyodbc.zip .

#build & run
$ docker build -t pyodbc-39 .
  #$ docker images
  #$ docker run -d --privileged -v /sys/fs/cgroup:/sys/fs/cgroup:ro [docker image id] /usr/sbin/init
$ docker run --rm --entrypoint bash -v $PWD:/local pyodbc-39 -c "cp -R /opt /local"

#make zip file
$ cd .. # ../environment/folder/opt -> ../environment/folder/
$ mkdir file
$ cd file
$ cp -r ../opt/* .
$ zip -r pyodbc_layer39.zip ./*

#publish to AWS
$ pip install awscli
$ aws lambda publish-layer-version --layer-name pyodbc-layer39 --zip-file fileb://pyodbc_layer39.zip --compatible-runtimes python3.9


#Example Usage (lambda_handler)
import pyodbc

DR = "{ODBC Driver 17 for SQL Server}"
HOST = "host(endpoint)"
DBNAME = "dbname" 
ID = "id"
PW = "pw"
connStr = f'Driver={DR}; Server=tcp:{HOST},1433; Database={DBNAME}; Uid={ID}; Pwd={PW}; Encrypt=yes; TrustServerCertificate=yes; Connection Timeout=30;'
conn = pyodbc.connect(connStr)