How do I get Django connecting to SQL Server working with AWS Lambda Layers?

Question

I am trying to use Django connected to SQL Server 2019 in an AWS Lambda function using Lambda Layers. I found an example using pyodbc to connect to SQL Server here: https://medium.com/@kuharan/aws-lambda-python-ms-sql-server-the-easy-way-e7667d371cc5.
I downloaded the example Lambda Layer and Lambda function and was able to get that working. However, now I am trying to install Django and django-mssql-backend in the layer in order to use Django to connect to SQL Server 2019. Here is a screenshot of how the directory structure is setup with Django and django-mssql-backend installed:

I zipped up the pyodbc-layers folder and uploaded that into Layers. Here is a screenshot of that:

Then when I run my lambda function, I get the error:

Response:  
{  
    "errorMessage": "Unable to import module 'lambda_function': No module named 'django'",  
    "errorType": "Runtime.ImportModuleError"  
}

Here is a screenshot of my function and the error message:

How do I resolve this? Thanks!

Edit:

Here's all my code as text:

lambda_function.py:

import json
import os
from datetime import datetime, timedelta

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')
django.setup()

from models.models import Schedule


def lambda_handler(event, context):
    games = Schedule.objects.using('default').filter(
        game_date=(
            datetime.today() - timedelta(days=1)
        ).date().strftime('%Y-%m-%d')
    ).values()
    
    print(games)

settings.py:

DEBUG = True

DATABASE_HOSTNAME = 'some_host'
DATABASE_USER = 'some_user'
DATABASE_PASSWORD = 'some_password'
DRIVER = 'ODBC Driver 17 for SQL Server'

SECRET_KEY = 'some_key'

DATE_INPUT_FORMATS = '%Y-%m-%d'

INSTALLED_APPS = (
    'models',
)

# Database
# https://docs.djangoproject.com/en/1.11/ref/settings/#databases

DATABASES = {
    'default': {
        'NAME': 'database_name',
        'ENGINE': 'sql_server.pyodbc',
        'HOST': DATABASE_HOSTNAME,
        'USER': DATABASE_USER,
        'PASSWORD': DATABASE_PASSWORD,
        'OPTIONS': {
            'driver': DRIVER,
            'isolation_level': 'READ UNCOMMITTED'
        },
    }
}

models.models.py:

from django.db import models

class Schedule(models.Model):
    game_id = models.IntegerField(primary_key=True)
    game_date = models.DateField()

    class Meta:
        app_label = 'models'
        managed = True
        db_table = 'Schedule'

Full error message using this code with the lambda layer build following the directions below:

{
  "errorMessage": "('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')",
  "errorType": "InterfaceError",
  "stackTrace": [
    "  File \"/var/task/lambda_function.py\", line 20, in lambda_handler
    print(games)
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/models/query.py\", line 250, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/models/query.py\", line 274, in __iter__
    self._fetch_all()
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/models/query.py\", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/models/query.py\", line 113, in __iter__
    for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/models/sql/compiler.py\", line 1092, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/models/sql/compiler.py\", line 1127, in execute_sql
    sql, params = self.as_sql()
",
    "  File \"/opt/python/lib/python3.7/site-packages/sql_server/pyodbc/compiler.py\", line 177, in as_sql
    supports_offset_clause = self.connection.sql_server_version >= 2012
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/utils/functional.py\", line 80, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
",
    "  File \"/opt/python/lib/python3.7/site-packages/sql_server/pyodbc/base.py\", line 400, in sql_server_version
    with self.temporary_connection() as cursor:
",
    "  File \"/var/lang/lib/python3.7/contextlib.py\", line 112, in __enter__
    return next(self.gen)
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/backends/base/base.py\", line 593, in temporary_connection
    with self.cursor() as cursor:
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/backends/base/base.py\", line 256, in cursor
    return self._cursor()
",
    "  File \"/opt/python/lib/python3.7/site-packages/sql_server/pyodbc/base.py\", line 218, in _cursor
    conn = super()._cursor()
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/backends/base/base.py\", line 233, in _cursor
    self.ensure_connection()
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/backends/base/base.py\", line 217, in ensure_connection
    self.connect()
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/utils.py\", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/backends/base/base.py\", line 217, in ensure_connection
    self.connect()
",
    "  File \"/opt/python/lib/python3.7/site-packages/django/db/backends/base/base.py\", line 195, in connect
    self.connection = self.get_new_connection(conn_params)
",
    "  File \"/opt/python/lib/python3.7/site-packages/sql_server/pyodbc/base.py\", line 314, in get_new_connection
    timeout=timeout)
"
  ]
}

user_248823 · Answer

I created the custom layer using docker tool for python 3.8. The instruction are for linux, as I don't have windows to test. Nevertheless, the steps could be adapted to windows.

The main difficulty was with shared libraries (libltdl and libodbc) that are required for odbc, but are not normally available in lambda environment.

Create a layer

The layer will have django==2.2.12, django-mssql-backend and pyodbc.

# create layer folder on your host operating system
mkdir /tmp/layer1

# run dcoker container for lambda python 3.8
docker run --rm -v /tmp/layer1:/tmp/layer1 -ti lambci/lambda:build-python3.8 /bin/bash

# in the container exectue the following commands

cd /tmp/layer1
yum install -y unixODBC-devel
python -m venv python
source ./python/bin/activate
pip install django==2.2.12 django-mssql-backend pyodbc
zip -r django-odbc.zip .
cp -rvf /usr/lib64/libltdl.so* .
cp -rvf /usr/lib64/libodbc.so* .

Create a layer using django-odbc.zip and remember to set python3.8 as compatible runtime.

Create lambda deployment package with your function

To be executed on your host

mkdir /tmp/lambda_function
cd /tmp/lambda_function
cp -rvf /tmp/layer1/*.so* .

Also add the following the file lambda_function.py:

import json

import django
import pyodbc 

def lambda_handler(event, context):
    # TODO implement 
    
    print(django.get_version())
    
    return {
        'statusCode': 200,
        'body': json.dumps('Hello from Lambda!')
    }

Make the deployment package:

zip -r lambda_function.zip .

Create lambda function using lambda_function.zip and add layer django-odbc.zip it.

How do I get Django connecting to SQL Server working with AWS Lambda Layers?

1 Answers

Create a layer

Create lambda deployment package with your function