Last active
January 24, 2024 09:32
-
-
Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
pyodbc and unixODBC for MSSQL as a lambda layer
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
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.
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
@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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@jordan-brough
any ideas about this? Running in WSL2 via Docker Desktop Windows