# 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 |
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"
@hben-align : Can you please share the zip package you generated for postgresql for pyodbc?
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!
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*
@alexanderluiscampino
I've managed to make it work (pyodbc with pg odbc)
I'll post here a comment with how I did it later :)