AWS Lambda (Python) + MS SQL Server — The Easy Way

A quick way to connect to MS SQL from AWS Lambda using Lambda Layers

https://unsplash.com/photos/D7Q6JpFleK8

pyodbc

pyodbc is an open-source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience. This was built to bridge the odbc connection using python.

Step 1 :

Download only the required file according to your python version. Unpack the .so files in the working directory.

Step 2:

Now open your favorite editor and start coding.

import pyodbc 
def lambda_handler(event,context):
    conn = pyodbc.connect('Driver={SQL Server};'
                          'Server=server_name;'
                          'Database=db_name;'
                          'Trusted_Connection=yes;')

    cursor = conn.cursor()
    cursor.execute('SELECT * FROM db_name.Table')

    for row in cursor:
        print(row)

Step 3:

Save the above file as lambda_function.py so that your lambda can call this handler in your code.

Step 4:

Zip all the files in the folder. Here is a reference structure for you.

├─── <parent_folder>
├─────── lambda_function.py
├─────── libodbc.so.2
├─────── pyodbc.so

Step 5:

Upload the zip file in your lambda function.

All Done? Well No. This will only load the pyodbc library for you but won't be able to connect to MSSQL as this needs OBBC drivers. This means you will have to follow the following steps:

1. Download Unix ODBC drivers
2. Compile Unix ODBC from Source
3. Copy the binaries to the application directory
4. Download Microsoft SQL Server ODBC Driver
5. Install the Microsoft SQL Server ODBC Driver
6. Copy the MS SQL Driver binaries for unixODBC
7. Create .ini files for driver path configurations
8. Package the whole thing together as a .zip file

Too much? I have packaged this already —

kuharan/Lambda-Layers

Just download the zip and the example code if needed. Simply upload this zip as a lambda layer and *snap* you are done!

Here is a quick video that I made while testing all this.

You’ve just connected AWS Lambda to Microsoft SQL Server. Now you are the boss of Serverless Computing!

Note for testers :

Complied with python 3.7 and ODBC Driver 17 for SQL Server.

Related StackOverflow Question on what I initially did and where it stands now —

Unable to use pyodbc with AWS lambda and API Gateway