pyodbc and unixODBC for MSSQL as a lambda layer

pyodbc-unixODBC-lambda-layer

# use https://github.com/lambci/docker-lambda to simulate a lambda environment
docker run -it --rm --entrypoint bash -e ODBCINI=/opt/odbc.ini -e ODBCSYSINI=/opt/ lambci/lambda:build-python3.7

# download and install unixODBC
# http://www.unixodbc.org/download.html
curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.7.tar.gz -O
tar xzvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7

./configure --sysconfdir=/opt --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/opt
make
make install

cd ..
rm -rf unixODBC-2.3.7 unixODBC-2.3.7.tar.gz

# download and install ODBC driver for MSSQL 17
# https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
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
ACCEPT_EULA=Y yum install msodbcsql17 --disablerepo=amzn*
export CFLAGS="-I/opt/include"
export LDFLAGS="-L/opt/lib"

cd /opt
cp -r /opt/microsoft/msodbcsql17/ .
rm -rf /opt/microsoft/

# install pyodbc for use with python.
# Notice the folder structure to support python 3.7 runtime 
# https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html#configuration-layers-path
mkdir /opt/python/
cd /opt/python/
pip install pyodbc -t .

cd /opt
cat <<EOF > odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1
EOF

cat <<EOF > odbc.ini
[ODBC Driver 17 for SQL Server]
Driver = ODBC Driver 17 for SQL Server
Description = My ODBC Driver 17 for SQL Server
Trace = No
EOF

# package the content in a zip file to use as a lambda layer
cd /opt
zip -r9 ~/pyodbc-layer.zip .

# to test it locally:
# unzip the content of your layer to your local environment, to /var/opt/ for example:
unzip pyodbc-layer.zip -d /var/opt/

# In your local environment, have your lambda function handy at /var/task/lambda_function.py

import pyodbc
server = 'myserver'
database =  'mydb'
username = 'myuser'
password = 'mypwd'

def lambda_handler(event, context):
    # TODO implement
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()
    cursor.execute("select @@version;")
    row = cursor.fetchone()
    while row:
        print(row[0])
        row = cursor.fetchone()

# Time to test
docker run --rm -v /var/task:/var/task -v /var/opt:/opt lambci/lambda:python3.7 lambda_function.lambda_handler '{"some": "event"}'

# useful links
https://medium.com/devopslinks/aws-lambda-microsoft-sql-server-how-to-66c5f9d275ed
https://stackoverflow.com/questions/47682991/aws-lambda-function-to-connect-to-sql-server-with-python
https://gist.github.com/carlochess/658a98589709f46dbb3d20502e48556b

Is anyone able to create lambda layer(python 3.9) for postgres pyodbc ?

I just created lambda layer zip for python3.8 and everything is working and tested! Thanks everyone in this gist and Tom! https://github.com/naingaungphyo/lambda_pyodbc_layer-python3.8

thanks for this!

@jordan-brough

any ideas about this? Running in WSL2 via Docker Desktop Windows

 ERROR [16/25] RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*                       0.7s
------
 > [16/25] RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*:
#19 0.405 Loaded plugins: ovl
#19 0.471 Resolving Dependencies
#19 0.472 --> Running transaction check
#19 0.472 ---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
#19 0.485 --> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql-13.1.9.2-1.x86_64
#19 0.494 --> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql-13.1.9.2-1.x86_64
#19 0.494 ---> Package mssql-tools.x86_64 0:17.10.1.1-1 will be installed
#19 0.495 --> Processing Dependency: msodbcsql17 >= 17.3.0.0 for package: mssql-tools-17.10.1.1-1.x86_64
#19 0.499 --> Running transaction check
#19 0.499 ---> Package msodbcsql17.x86_64 0:17.10.2.1-1 will be installed
#19 0.499 ---> Package unixODBC.x86_64 0:2.3.11-1.rh will be installed
#19 0.499 --> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-2.3.11-1.rh.x86_64
#19 0.525 --> Finished Dependency Resolution
#19 0.526 Error: Package: unixODBC-2.3.11-1.rh.x86_64 (packages-microsoft-com-prod)
#19 0.526            Requires: libltdl.so.7()(64bit)
#19 0.526  You could try using --skip-broken to work around the problem
#19 0.689  You could try running: rpm -Va --nofiles --nodigest
------
executor failed running [/bin/sh -c ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*]: exit code: 1

nevermind. all good. If anyone else has the issue, i added
RUN yum -y install libtool-ltdl
before
RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*

Hey guys, I created a layer as a .zip with pyodbc, unix and mssql odbc drivers that work on python 3.11 runtime. Uses mssqlodbc17 drivers.

https://github.com/andersonguse/lambda-layers

thanks @alexanderluiscampino , saved my 1 day time

Here's a github repo with automated builds of the the ODBC 18 drivers for Python 3.11 and 3.12.
Prebuilt packages and Dockerfile for x86_64 and ARM64

https://github.com/davidcomerford/pyodbc-mssql-lambda-layer

@davidcomerford doing God's work. Thank you for this, been wanting to formalize this for ages now.

@davidcomerford doing God's work. Thank you for this, been wanting to formalize this for ages now.

You're very welcome