How to install Pyodbc for Sqlserver.

Pyodbc sqlserver lambda

# Start a container that mimic the lambda environment
docker run -it --rm --entrypoint bash  -e ODBCINI=/var/task -e ODBCSYSINI=/var/task -v "$PWD":/var/task  lambci/lambda:build-python2.7

# Then, download ODBC source code, compile and take the output
curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O
tar xvzf unixODBC-2.3.5.tar.gz
cd unixODBC-2.3.5
./configure  --sysconfdir=/var/task  --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home
make install
cd ..
mv /home/* .
mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp/

# Install MSsql odbc driver
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
ACCEPT_EULA=Y yum -y install msodbcsql
export CFLAGS="-I/var/task/include"
export LDFLAGS="-L/var/task/lib"

# Then you can install pyodbc (or pip install -t . -r requirements.txt)
pip install pyodbc -t .

cp -r /opt/microsoft/msodbcsql .

cat <<EOF > odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/var/task/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1
EOF

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

# Test if it works
python -c "import pyodbc; print(pyodbc.drivers());"
python -c 'import pyodbc;conn = pyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server}; SERVER=YOUr_SERVER:ADD;PORT=1443;DATABASE=TestDB;UID=SA;PWD=<YourStrong!Passw0rd>");crsr = conn.cursor();rows = crsr.execute("select @@VERSION").fetchall();print(rows);crsr.close();conn.close()'

I will check an alternative to this because Its seems that the msodbcsql package is using an unixODBC dependency that is no longer present in the resent AMIs.

bash-4.2# ACCEPT_EULA=Y yum -y install msodbcsql
Loaded plugins: ovl, priorities
4 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql-13.1.9.2-1.x86_64
--> Processing Dependency: e2fsprogs for package: msodbcsql-13.1.9.2-1.x86_64
--> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql-13.1.9.2-1.x86_64
--> Running transaction check
---> Package e2fsprogs.x86_64 0:1.43.5-2.43.amzn1 will be installed
--> Processing Dependency: libss = 1.43.5-2.43.amzn1 for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: e2fsprogs-libs(x86-64) = 1.43.5-2.43.amzn1 for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libfuse.so.2(FUSE_2.8)(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libfuse.so.2(FUSE_2.6)(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libfuse.so.2(FUSE_2.5)(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libss.so.2()(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libfuse.so.2()(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libext2fs.so.2()(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
--> Processing Dependency: libe2p.so.2()(64bit) for package: e2fsprogs-1.43.5-2.43.amzn1.x86_64
---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql-13.1.9.2-1.x86_64
---> Package unixODBC.x86_64 0:2.2.14-14.7.amzn1 will be installed
--> Running transaction check
---> Package e2fsprogs-libs.x86_64 0:1.43.5-2.43.amzn1 will be installed
---> Package fuse-libs.x86_64 0:2.9.4-1.18.amzn1 will be installed
---> Package libss.x86_64 0:1.43.5-2.43.amzn1 will be installed
---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql-13.1.9.2-1.x86_64
--> Finished Dependency Resolution
Error: Package: msodbcsql-13.1.9.2-1.x86_64 (packages-microsoft-com-prod)
           Requires: unixODBC >= 2.3.1
           Available: unixODBC-2.2.14-14.7.amzn1.i686 (amzn-main)
               unixODBC = 2.2.14-14.7.amzn1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Thanks Carlo. That is the exact screenshot that I am getting when trying to complete the package build

Confirming that the same steps work in Redhat AMI.

run with

 yum install unixODBC --disablerepo=amzn*

you need to bypass the amzn repolist for installing correct unixODBC

I created a Dockerfile based on this and the fixes mentioned since. It creates a .zip file usable as a lambda layer.
https://gist.github.com/ca0abinary/e4825841d47d987ffc78ed62e5619055

Hi Everyone!

I am facing a similar problem as most of you did before!
May be I am missing something!! :(

The Test Script works locally inside the docker container.
Could you please help me understand the steps that needs to be done after you zip the files.

This is what i did -

I created a lambda_function.py and added inside the zip folder
Created a zip of it and uploaded it to the Lambda function ( Not as a layer)
Contents of my zip are :
total 12
drwxr-xr-x 3 root root 4096 Nov 20 23:28 microsoft
drwxr-xr-x 4 root root 4096 Nov 20 23:28 mssql-tools
drwxr-xr-x 3 root root 4096 Nov 20 23:22 python
bash-4.2# cd /opt
When i try to run the lambda it throws me an error saying that no module named "pyodbc"
Appreciate any help here! been stuck at this for a while now!

Thanks,
Raghu

Hi @rags1357! The gist I provided above has been updated with a comment that includes steps to test locally with AWS SAM. It includes a template.yaml with embedded python test code.

Thanks @ca0abinary! Appreciate it.