Skip to content

Instantly share code, notes, and snippets.

@diriver63
Last active January 24, 2024 09:32
Show Gist options
  • Star 53 You must be signed in to star a gist
  • Fork 16 You must be signed in to fork a gist
  • Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
pyodbc and unixODBC for MSSQL as a 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
@KrunchMuffin
Copy link

@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

@KrunchMuffin
Copy link

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*

@andersonguse
Copy link

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

@nimmagaddarajesh
Copy link

thanks @alexanderluiscampino , saved my 1 day time

@davidcomerford
Copy link

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

@alexanderluiscampino
Copy link

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

@davidcomerford
Copy link

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

You're very welcome

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment