Skip to content

Instantly share code, notes, and snippets.

@ca0abinary ca0abinary/Dockerfile
Last active Mar 25, 2020

Embed
What would you like to do?
Docker - Build pyOdbc Microsoft SQL AWS Lambda Layer
FROM lambci/lambda:build-python3.7
WORKDIR /root
RUN yum -y update
RUN 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=/opt/python --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
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo && \
yum -y install freetds e2fsprogs && \
ACCEPT_EULA=Y yum -y install msodbcsql --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" && \
export LDFLAGS="-L/root/lib" && \
pip install pyodbc requests "pymssql<3.0" adodbapi --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 13 for SQL Server]\n\
Driver = ODBC Driver 13 for SQL Server\n\
Description = My ODBC Driver 13 for SQL Server\n\
Trace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 13 for SQL Server]\n\
Description = Microsoft ODBC Driver 13 for SQL Server\n\
Driver = /opt/python/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2\n\
UsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python && \
cp /usr/lib64/libsybdb.so.5 /root/lib/libsybdb-89a09a88.so.5.1.0 && \
cp -r /root/* /opt/python && \
mv /opt/python/lib /opt && \
mv /opt/python/bin /opt && \
cd /opt && \
rm -fr microsoft && \
zip -r /python-odbc.zip .
@jconway4

This comment has been minimized.

Copy link

jconway4 commented Nov 5, 2019

Build failed:

Step 8/10 : ADD odbc.ini /root
ADD failed: stat /var/lib/docker/tmp/docker-builder209335974/odbc.ini: no such file or directory

Original repo's code with the cat invocations worked fine for .ini files. I'll post a workaround if anyone else is having this problem.

@thanuj11

This comment has been minimized.

Copy link

thanuj11 commented Nov 13, 2019

@jconway4 can you please post your workaround which works. I am figuring out how to install pyodbc for AWS Lambda and not successful. Maybe I will be successful with your workaround.

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

ca0abinary commented Nov 13, 2019

@thanuj11 Try using AWS SAM, put pyodbc in the requirements.txt and build with sam build --use-container. That should work fine for using pyodbc in a Lambda function, even testing locally should work.

@thanuj11

This comment has been minimized.

Copy link

thanuj11 commented Nov 15, 2019

@thanuj11 Try using AWS SAM, put pyodbc in the requirements.txt and build with sam build --use-container. That should work fine for using pyodbc in a Lambda function, even testing locally should work.

@ca0abinary Thanks for the reply, I tried using AWS SAM but still it fails with the error below
Build Failed
Error: PythonPipBuilder:ResolveDependencies - {pyodbc==4.0.25(sdist)}
Running PythonPipBuilder:ResolveDependencies

Details
python_verison:3.6
SAM template version:3.6

Any comments on how to make this work for AWS lambda will be a great help

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

ca0abinary commented Nov 18, 2019

@thanuj11 - these are the commands I use to build and create a lambda layer zip file. Simply including the uploaded lambda layer in your lambda function should enable you to use pyodbc without a requirements.txt.

docker build -t pyodbc .
docker run -d --name pyodbc pyodbc true
docker cp pyodbc:/python-odbc.zip ./python-odbc.zip
docker rm -f pyodbc
@rags1357

This comment has been minimized.

Copy link

rags1357 commented Nov 19, 2019

@ca0abinary I am facing a similar problem trying to run MSSQL from a Lambda using pyodbc. The instructions you mentioned above doesn' t seem to be building the container. I am trying to do it on my Mac

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

ca0abinary commented Nov 20, 2019

@rags1357 @thanuj11 @jconway4 Looks like the build image was updated which broke this script so I took the opportunity to fix and refactor:

Changes:

  • .ini files are built within in the Dockerfile instead of being copied from the source directory
  • Added adodbapi which is a python-only library to remind myself that it exists
  • Updated the pymssql reference so it works

How to build:
This is my workflow, sorry if it is overly complicated for your needs:

docker build -t pyodbc .
docker run -d --name pyodbc pyodbc true
docker cp pyodbc:/python-odbc.zip .
docker rm -f pyodbc &> /dev/null

This should produce a python-odbc.zip in the current directory suitable for upload as a lambda layer.

@rags1357

This comment has been minimized.

Copy link

rags1357 commented Nov 20, 2019

@ca0abinary Thanks! Will give it a shot and let you know how it goes

@rags1357

This comment has been minimized.

Copy link

rags1357 commented Nov 20, 2019

Hello! @ca0abinary.,

So here is the steps i am following -
To the generated python-odbc.zip i add the lambda_function.py where i try to connect to the mssql server.
I zip the content with including the python file and upload it to lambda as zip.

When i try to run the test. It still throws me error as cannot import module "pyodbc" and also the driver not found.

Snippet of my connection code in "lambda_function.py" is as follows -

'''
try:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL
Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

except pyodbc.DatabaseError as e:
print("A MSSQLDatabaseException has been caught.",e.message)
'''

Please let me know if I am missing something?

@rags1357

This comment has been minimized.

Copy link

rags1357 commented Nov 20, 2019

To be precise, the error is
{
"errorMessage": "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")",
"errorType": "Error",
"stackTrace": [
" File "/var/task/lambda_function.py", line 33, in lambda_handler\n cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)\n"
]
}

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

ca0abinary commented Nov 21, 2019

@rags1357 I've spent some time crafting a test scenario which can be run locally.

  1. Create the zip file
  2. Extract the zip file to PythonOdbc (this is important because AWS SAM doesn't handle .zip files)
  3. Put the following template.yaml in the base directory such that it is at teh same level as the PythonOdbc directory:
    AWSTemplateFormatVersion: 2010-09-09
    
    Resources:
      Function:
        Type: AWS::Lambda::Function
        Properties:
          FunctionName: GcAuthUserMigration
          Timeout: 30
          InlineCode: |
            import pyodbc
    
            from os import environ
            server = environ.get('SERVER')
            username = environ.get('USERNAME')
            password = environ.get('PASSWORD')
    
            def handler(event, context):
                connectionString = f"Driver={{ODBC Driver 13 for SQL Server}};Server={server};Database=master;UID={username};PWD={password};"
                dbConnection = pyodbc.connect(connectionString)
                cursor = dbConnection.cursor()
                cursor.execute("select * from sys.databases")
                query_results = [dict(zip([column[0] for column in cursor.description], row)) for row in cursor.fetchall()]
                print(repr(query_results))
          Layers: [!Ref Layer]
          Handler: lambda.handler
          Runtime: python3.7
          Environment:
            Variables:
              SERVER: <<SQL Server Goes Here>>
              USERNAME: <<SQL Username Goes Here>>
              PASSWORD: <<SQL Password Goes Here>>
      Layer:
        Type: AWS::Serverless::LayerVersion
        Properties:
          ContentUri: PythonOdbc
  4. Replace the <<SQL _ Goes Here>> environment variable with your SQL server location
  5. Run sam local invoke in the directory with the template.yaml file
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.