Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@alexanderluiscampino
Copy link

alexanderluiscampino commented Jul 24, 2022

@hben-align I have never used pyodbc to connect to PG instances, it seems that your process is more or less correct, but with .so files its always hard to actually detect what might be wrong. Someone else more knowledgeable in that field might provide more help.

But I ask, why not using psycopg2 to connect to PG instance? Here is the already built layer for lambda. I use that layer in production on the daily, it's great!

@hben-align
Copy link

hben-align commented Jul 25, 2022

@alexanderluiscampino I used psycopg2 (which I compiled myself) but this project was written using pyodbc for consistency (due to the compatibility with other engines)...
if I have no other choice I'll change the code to psycopg2, but I'm trying to avoid it for now because its a lot of work :\

@hben-align
Copy link

hben-align commented Jul 25, 2022

btw, I've managed to get it working on a local ubuntu machine, but in lambda I still get the "[unixODBC][Driver Manager]Can't open lib '/var/task/lib/psqlodbcw.so' : file not found" error...
even when zipping the working dir from ubuntu

lol, printing the directory after the failure print out the file in the right path :\\
image
image

@hben-align
Copy link

hben-align commented Jul 27, 2022

@alexanderluiscampino
I've managed to make it work (pyodbc with pg odbc)
I'll post here a comment with how I did it later :)

@hben-align
Copy link

hben-align commented Jul 27, 2022

This method is for using pyodbc with pg odbc inside the task (not as a layer)

create a new ec2 machine with amazon linux 2.

then run:
sudo yum update

cd /home/ec2-user

download unixODBC and postgresql odbc

curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.11.tar.gz -O
curl https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-13.02.0000.tar.gz -O
tar xzvf unixODBC-2.3.11.tar.gz
tar -zxvf psqlodbc-13.02.0000.tar.gz

compile unixODBC (install necessary packages)

cd /home/ec2-user/unixODBC-2.3.11/
sudo yum groupinstall "Development Tools"
./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/var/task
sudo make
sudo make install

compile psqlodbc (install necessary packages)

cd /home/ec2-user/psqlodbc-13.02.0000/
sudo amazon-linux-extras enable postgresql9.6
sudo yum install -y postgresql-libs postgresql-server postgresql postgresql-devel
./configure --with-libpq=/var/task --with-unixodbc=/var/task --sysconfdir=/var/task --prefix=/var/task
sudo make
sudo make install

fill up nessesery libs (using root permissions)

sudo su
cd /var/task/lib
cp /lib64/libpq.so.5 .
cp /lib64/libdl.so.2 .
cp /lib64/libpthread.so.0 .
cp /lib64/libc.so.6 .
cp /lib64/libssl.so.10 .
cp /lib64/libcrypto.so.10 .
cp /lib64/libgssapi_krb5.so.2 .
cp /lib64/libldap_r-2.4.so.2 .
cp /lib64/ld-linux-x86-64.so.2 .
cp /lib64/libkrb5.so.3 .
cp /lib64/libcom_err.so.2 .
cp /lib64/libk5crypto.so.3 .
cp /lib64/libz.so.1 .
cp /lib64/libkrb5support.so.0 .
cp /lib64/libkeyutils.so.1 .
cp /lib64/libresolv.so.2 .
cp /lib64/liblber-2.4.so.2 .
cp /lib64/libsasl2.so.3 .
cp /lib64/libssl3.so .
cp /lib64/libsmime3.so .
cp /lib64/libnss3.so .
cp /lib64/libnssutil3.so .
cp /lib64/libplds4.so .
cp /lib64/libplc4.so .
cp /lib64/libnspr4.so .
cp /lib64/libselinux.so.1 .
cp /lib64/libcrypt.so.1 .
cp /lib64/librt.so.1 .
cp /lib64/libpcre.so.1 .

cd /var/task
zip -r /home/ec2-user/task.zip .

change .ini files as stated above

odbc.ini:
[PostgreSQL Unicode]
Driver=PostgreSQL
Description = My ODBC Driver For PostgreSQL
Trace = No

odbcinst.ini:
[PostgreSQL]
Description=Postgres odbc
Driver=/var/task/lib/psqlodbcw.so
UsageCount=1

now call the driver by using "PostgreSQL"

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