Search code examples
python-3.xaws-lambdapyodbc

AWS Lambda connecting to SQL with Python and pyodbc

I have an AWS Lambda that I want to connect to my on prem SQL server to read and write data from\to. I am using Python and pyodbc. I have got pyodbc installed (compiled zip file in an S3 bucket added to the lambda through a layer), but when I try and run this code I get an odd error:

import boto3
import pyodbc

s3 = boto3.client('s3')

def lambda_handler(event, context):
    # print(help(pyodbc))
    server = "Server"
    database = "Database"
    username = "AWS-Lamdba-RO"
    password = "Password"
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()

This is the error:

[ERROR] AttributeError: module 'pyodbc' has no attribute 'connect' Traceback (most recent call last):   File "/var/task/lambda_function.py", line 13, in lambda_handler     cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

All I'm finding online is people who are unable to get the pyodbc library installed in the first place, so having got past that sticking point I thought I was free and clear. Can anyone explain what I've run into now?

I got pyodbc from here: https://github.com/Miserlou/lambda-packages/tree/master/lambda_packages/pyodbc

AWS didn't recognise .tar.gz files, so I changed it to a zip file and also added in the folder structure which another googled site told me was necessary: \python\lib\python3.7\site-packages\pyodbc that folder contains: libodbc.so.2 pyodbc.so

I uploaded this Zip file to an S3 bucket and pointed a Lambda layer at it.

Have I done something silly with this?

Solution

  • From your description, I believe you may have gotten your folder structure wrong.

    If you were to look in your zip file, you should see the following structure:

    layerZip.zip
└ python
  └ lib
    └ python3.7
      └ site-packages
        └ pyodbc

    But I believe you may actually have

    layerZip.zip
└ \
  └ python
    └ lib
      └ python3.7
        └ site-packages
          └ pyodbc

    But honestly, just use this structure:

    layerZip.zip
└ python
  └ pyodbc

    It'll work just as well, it's just setting the module up as global instead of per user.