I’m currently writing a Django web app that pulls information from a MS SQL database. Of course, I want this application to be run as cost effectively as possible so I’m placing the Django app in AWS Lambda using Zappa for deployment. I gave a talk on using AWS Lambda and Zappa for a Django project at PSU MacAdmins 2017 called Imaging in 50+ Locations. Needless to say, I love deploying Django apps this way. (This post won’t go into how to use Zappa.)
However, I ran into a problem. The open-source MS SQL library I’m using in my app is pymssql. This is a great library that works on many platforms including AWS Lambda (which is basically just the AWS EC2 Linux AMI). It even supports the latest version of Python 3.6 which I’m also using. However, pymssql relies on FreeTDS to talk to MS SQL. This normally wouldn’t be a problem since the team over pymssql also includes an option to build the software on your machine with a bundled version of FreeTDS in precompiled wheels. Sounds great until you see this error in the browser:
The problem arises when you’re using anything greater than Python 3.5. There are no precompiled wheels for Python 3.6 for any platform. This means that when deploying my app from my test machine, a Mac, Zappa doesn’t upload a Linux compatible version of the pymssql library. It uploads a Darwin version. If I deploy from an EC2 Linux instance Zappa will upload a Linux compatible version of pymssql but it’s still missing FreeTDS. The quickest way to solve this would be for the team over at pymssql to release a precompiled Python 3.6 ‘manylinux’ wheel. Until then I need a workaround.
To keep this post short, after much Google’ing I found a solution that works for my environment:
- Create an EC2 Linux instance based on the Amazon Linux AMI (ami-6057e21a)
- SSH into the instance
- Install the most up-to-date version of Python 3.6, FreeTDS, the ‘gcc’ compiler, and ‘git’:
sudo yum install python36-devel freetds-devel gcc git - Clone your application code from version control:
git clone [your_code_repo_url] - Create a virtual environment for our code dependencies:
virtualenv -p $(python3) virtualenv - Activate your virtual environment and change into your code directory:
source virtualenv/bin/activate && cd [your_code_repo] - Create an environment variable to build pymssql with a bundled version of FreeTDS:
export PYMSSQL_BUILD_WITH_BUNDLED_FREETDS=1 - Install your python dependencies. Make sure to install pymssql from the latest build:
pip install git+https://github.com/pymssql/pymssql.git - Copy the missing ‘libsybdb.so’ file to site-packages within the virtualenv:
cp /usr/lib64/libsybdb.so ../virtualenv/lib/python3.6/site-packages/ - At this point ‘pymssql’ can run within AWS Lambda with Python 3.6
I would love to get feedback from others on how they run pymssql in AWS Lambda or if my method helps you.
Amore Open Source 
Hi Chase, thanks for posting this- it helped clarify the problem for me. Unfortunately, I still seem to be getting this error, which suggests that even though `libsybdb.so.5` is in (what should be) the right location, the import script or interpreter is looking elsewhere for it. Any insights on how to check for where `importlib/__init__.py` is looking for the .so file?
Thanks!
LikeLike
Can you confirm that the libsybdb.so file was copied to the /lib//site-packages directory? If there is any way you can share a gist of your error log from lambda or your application, that might be helpful as well. Feel free to hit me up on Twitter (@chase_tb).
LikeLike
That’s the ‘virtualenv/lib/python/site-packages’ directory. Sorry I didn’t make that very clear in the first reply.
LikeLike
how do you zip your solution to upload that to aws lambda?
LikeLiked by 1 person
I use Zappa (https://github.com/Miserlou/Zappa) for deployment. My project is Django-based so Zappa handles all the deployment for me.
LikeLike
Hi Chase! Thank you for posting this 🙂
I’m trying to follow your steps but am having an issue activating my virtualenv. When I type in the command to activate, I get the error that virtualenv/bin/activate is not a file or directory. I have tried multiple other things, including trying to uninstall virtualenv, but then I get a ‘permission denied’ warning on /usr/local/bin, which is an empty directory…
LikeLike
What is the exact commands you are using to create the virtualenv and then to activate it?
LikeLike
It is probably because you haven’t included the ‘source’ command at the beginning. By not running source, you are trying to run the ‘activate’ file directly, which you cannot do. Source executes the contents of the file.
LikeLike
Hi, I am trying to connect to a Sybase database and get an error even inspite of going through the same setup. I have followed the same set up for SQL server and works perfectly.
And the error is very generic :
File “src/pymssql.pyx”, line 642, in pymssql.connect
pymssql.OperationalError: (20002, b’DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (SERVERNAME:PORT\n’)
Any help is appreciated….
LikeLike
I’m certainly no expert at pymssql and that error is pretty generic. It may even mean there is an issue outside of the pymssql library itself. As this comment suggests (https://github.com/pymssql/pymssql/issues/114#issuecomment-24975829), “I’d recommend posting this instead as a topic on the discussion forum at http://groups.google.com/group/pymssql since it’s not clear that this is a pymssql bug yet. You should mention in your posting what platform you are connecting from (e.g.: Linux, Mac, Windows, etc.), what version of Python you’re using, how you installed pymssql (e.g.: pip install pymssql or python setup.py from code checked out from hg tip, etc.), what version of FreeTDS you have, etc.”
LikeLike