Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
pyodbc and unixODBC for MSSQL as a lambda layer
# use https://github.com/lambci/docker-lambda to simulate a lambda environment
docker run -it --rm --entrypoint bash -e ODBCINI=/opt/odbc.ini -e ODBCSYSINI=/opt/ lambci/lambda:build-python3.7
# download and install unixODBC
# http://www.unixodbc.org/download.html
curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.7.tar.gz -O
tar xzvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --sysconfdir=/opt --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/opt
make
make install
cd ..
rm -rf unixODBC-2.3.7 unixODBC-2.3.7.tar.gz
# download and install ODBC driver for MSSQL 17
# https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
yum install e2fsprogs.x86_64 0:1.43.5-2.43.amzn1 fuse-libs.x86_64 0:2.9.4-1.18.amzn1 libss.x86_64 0:1.43.5-2.43.amzn1
ACCEPT_EULA=Y yum install msodbcsql17 --disablerepo=amzn*
export CFLAGS="-I/opt/include"
export LDFLAGS="-L/opt/lib"
cd /opt
cp -r /opt/microsoft/msodbcsql17/ .
rm -rf /opt/microsoft/
# install pyodbc for use with python.
# Notice the folder structure to support python 3.7 runtime
# https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html#configuration-layers-path
mkdir /opt/python/
cd /opt/python/
pip install pyodbc -t .
cd /opt
cat <<EOF > odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1
EOF
cat <<EOF > odbc.ini
[ODBC Driver 17 for SQL Server]
Driver = ODBC Driver 17 for SQL Server
Description = My ODBC Driver 17 for SQL Server
Trace = No
EOF
# package the content in a zip file to use as a lambda layer
cd /opt
zip -r9 ~/pyodbc-layer.zip .
# to test it locally:
# unzip the content of your layer to your local environment, to /var/opt/ for example:
unzip pyodbc-layer.zip -d /var/opt/
# In your local environment, have your lambda function handy at /var/task/lambda_function.py
import pyodbc
server = 'myserver'
database = 'mydb'
username = 'myuser'
password = 'mypwd'
def lambda_handler(event, context):
# TODO implement
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("select @@version;")
row = cursor.fetchone()
while row:
print(row[0])
row = cursor.fetchone()
# Time to test
docker run --rm -v /var/task:/var/task -v /var/opt:/opt lambci/lambda:python3.7 lambda_function.lambda_handler '{"some": "event"}'
# useful links
https://medium.com/devopslinks/aws-lambda-microsoft-sql-server-how-to-66c5f9d275ed
https://stackoverflow.com/questions/47682991/aws-lambda-function-to-connect-to-sql-server-with-python
https://gist.github.com/carlochess/658a98589709f46dbb3d20502e48556b
@pcb-submittable

This comment has been minimized.

Copy link

@pcb-submittable pcb-submittable commented Jun 4, 2019

Excellent script, thanks. I am having one issue however, after I install unixODBC I try this command:

ACCEPT_EULA=Y yum install msodbcsql17

But it fails with this error:
Error: Package: msodbcsql17-17.3.1.1-1.x86_64 (packages-microsoft-com-prod)
Requires: unixODBC >= 2.3.1
Available: unixODBC-2.2.14-14.7.amzn1.i686 (amzn-main)
unixODBC = 2.2.14-14.7.amzn1

Any ideas on why this is occurring? I copied all of your script word for word. I've been stuck compiling this function and your script is as close as I've gotten. Thanks!

@nkranes

This comment has been minimized.

Copy link

@nkranes nkranes commented Jun 5, 2019

I'm having the same issue.

@diriver63

This comment has been minimized.

Copy link
Owner Author

@diriver63 diriver63 commented Jun 5, 2019

Not sure if Microsoft updated their repo and messed up the dependencies check, or the docker image changed. Anyway, I'm attaching the zip file here as well

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Jun 6, 2019

Excellent script, thanks. I am having one issue however, after I install unixODBC I try this command:

ACCEPT_EULA=Y yum install msodbcsql17

But it fails with this error:
Error: Package: msodbcsql17-17.3.1.1-1.x86_64 (packages-microsoft-com-prod)
Requires: unixODBC >= 2.3.1
Available: unixODBC-2.2.14-14.7.amzn1.i686 (amzn-main)
unixODBC = 2.2.14-14.7.amzn1

Any ideas on why this is occurring? I copied all of your script word for word. I've been stuck compiling this function and your script is as close as I've gotten. Thanks!

just do this:

curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

yum install e2fsprogs

sudo ACCEPT_EULA=Y yum install mssql-tools unixODBC-devel -y --disablerepo=amzn*

export CFLAGS="-I/opt/include"

export LDFLAGS="-L/opt/lib"

cd /opt

cp -r /opt/microsoft/msodbcsql17/ .

rm -rf /opt/microsoft/
@diriver63

This comment has been minimized.

Copy link
Owner Author

@diriver63 diriver63 commented Jun 6, 2019

@alexanderluiscampino beat me to it... I added the following lines to the script to workaround the dependencies check
yum install e2fsprogs.x86_64 0:1.43.5-2.43.amzn1 fuse-libs.x86_64 0:2.9.4-1.18.amzn1 libss.x86_64 0:1.43.5-2.43.amzn1 ACCEPT_EULA=Y yum install msodbcsql17 --disablerepo=amzn*

@pcb-submittable

This comment has been minimized.

Copy link

@pcb-submittable pcb-submittable commented Jun 6, 2019

Thanks, I appreciate it!

@wallentx

This comment has been minimized.

Copy link

@wallentx wallentx commented Jun 7, 2019

This is awesome. What happened to the zip file?

@naren-ng

This comment has been minimized.

Copy link

@naren-ng naren-ng commented Aug 20, 2019

I'm getting the below error after following the exact steps. Am I missing something?

>>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1' : file not found (0) (SQLDriverConnect)")

@brunoshimada

This comment has been minimized.

Copy link

@brunoshimada brunoshimada commented Aug 20, 2019

I'm getting the below error after following the exact steps. Am I missing something?

>>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1' : file not found (0) (SQLDriverConnect)")

Had the same problem, maybe something has upgraded because this file libmsodbcsql-17.3.so.1.1 is actually libmsodbcsql-17.4.so.1.1 now.

After I changed it in odbcinst.ini I managed to at least run a simple connection to a local database

@naren-ng

This comment has been minimized.

Copy link

@naren-ng naren-ng commented Aug 26, 2019

I'm getting the below error after following the exact steps. Am I missing something?

>>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1' : file not found (0) (SQLDriverConnect)")

Yes, the error was due to the version.
libmsodbcsql version 4 has been installed so after changing the path, it works.

@bsnithin44

This comment has been minimized.

Copy link

@bsnithin44 bsnithin44 commented Sep 26, 2019

Can somebody provide the .zip package of the layer? It would be really helpful for me

Thanks

@alexanderluiscampino

This comment has been minimized.

@nickflees

This comment has been minimized.

Copy link

@nickflees nickflees commented Oct 8, 2019

Thank you! This was very well done and incredibly useful!

@tmhalbert

This comment has been minimized.

Copy link

@tmhalbert tmhalbert commented Oct 31, 2019

Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1 needs to be Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1 now

@thern001

This comment has been minimized.

Copy link

@thern001 thern001 commented Nov 5, 2019

Thank you guys this worked. I had to export the zip file to my local computer using scp and then add to s3, and im sure there is an easier way, but this was my first time using linux.

@fasalex

This comment has been minimized.

Copy link

@fasalex fasalex commented Nov 19, 2019

here you go: @bsnithin44
https://www.dropbox.com/s/8qv9tl1a160y9e1/pyodbc-9ef8961d-ce5b-4603-b397-03c9a6316eca.zip?dl=0

@alexanderluiscampino
The link seems broken. Is it possible to upload the layer zip file?

@rags1357

This comment has been minimized.

Copy link

@rags1357 rags1357 commented Nov 21, 2019

Hi Everyone!

I am facing a similar problem as most of you did before!
May be I am missing something!! :(

The Test Script works locally inside the docker container.
Could you please help me understand the steps that needs to be done after you zip the files.

This is what i did -

  1. I created a lambda_function.py and added inside the zip folder
  2. Created a zip of it and uploaded it to the Lambda function ( Not as a layer)
    Contents of my zip are :
    total 12
    drwxr-xr-x 3 root root 4096 Nov 20 23:28 microsoft
    drwxr-xr-x 4 root root 4096 Nov 20 23:28 mssql-tools
    drwxr-xr-x 3 root root 4096 Nov 20 23:22 python
    bash-4.2# cd /opt
  3. When i try to run the lambda it throws me an error saying that no module named "pyodbc"

Appreciate any help here! been stuck at this for a while now!

Thanks,
Raghu

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Nov 21, 2019

here you guys have it: @fasalex @rags1357

https://github.com/alexanderluiscampino/lambda-layers

just zip it an upload it.

powershell Compress-Archive python pyodbc.zip -force
call aws s3 cp pyodbc.zip s3://layers-bucket/pyodbc/pyodbc.zip
call aws lambda publish-layer-version --layer-name pyodbc^
--description "pyodbc" ^
--license-info "MIT" ^
--content S3Bucket=layers-bucket,S3Key=pyodbc/pyodbc.zip ^
--compatible-runtimes python3.6 python3.7

@plantusd

This comment has been minimized.

Copy link

@plantusd plantusd commented Dec 1, 2019

@Ale
Thanks, but I'm getting
either this
ImportError: libodbc.so.2: cannot open shared object file: No such file or directory
or this
AttributeError: module 'pyodbc' has no attribute 'paramstyle'

@YSArch

This comment has been minimized.

Copy link

@YSArch YSArch commented Dec 26, 2019

Following the script to create pyodbc-layer.zip, it works fine, thanks!

@kaioquinan

This comment has been minimized.

Copy link

@kaioquinan kaioquinan commented Jan 5, 2020

here you go: @bsnithin44
https://www.dropbox.com/s/8qv9tl1a160y9e1/pyodbc-9ef8961d-ce5b-4603-b397-03c9a6316eca.zip?dl=0

Hi, the link is not working, can you post it again? Please

@kaioquinan

This comment has been minimized.

Copy link

@kaioquinan kaioquinan commented Jan 5, 2020

here you guys have it: @fasalex @rags1357

https://github.com/alexanderluiscampino/lambda-layers

just zip it an upload it.

powershell Compress-Archive python pyodbc.zip -force
call aws s3 cp pyodbc.zip s3://layers-bucket/pyodbc/pyodbc.zip
call aws lambda publish-layer-version --layer-name pyodbc^
--description "pyodbc" ^
--license-info "MIT" ^
--content S3Bucket=layers-bucket,S3Key=pyodbc/pyodbc.zip ^
--compatible-runtimes python3.6 python3.7

Hi,Do you have any contact so you can help with the file above?

@kaioquinan

This comment has been minimized.

Copy link

@kaioquinan kaioquinan commented Jan 5, 2020

I followed all the steps and created the file but I received the message below:

[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 24, in lambda_handler1
    cnxn = pyodbc.connect(con_string)
END RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f
REPORT RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f Duration: 6.90 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 57 MB Init Duration: 118.61 ms
error1

Can anybody help me?

@scdekov

This comment has been minimized.

Copy link

@scdekov scdekov commented Jan 16, 2020

I was able to build the layer zip from https://github.com/alexanderluiscampino/lambda-layers with the following commands

git clone git@github.com:alexanderluiscampino/lambda-layers.git
cd lambda-layers/pyodbc
zip -r pyodbc.zip *
aws s3 cp pyodbc.zip s3://lambda-layers/pyodbc/pyodbc.zip

For some reason the aws cli command for publishing the layer version was failing, so I manually created layer version passing the zip file s3 location.

Than you have to package your lambda code along with the installed pyodbc python library(pip install pyodbc), upload it and use the layer.

@jpatel1985

This comment has been minimized.

Copy link

@jpatel1985 jpatel1985 commented Jan 24, 2020

I followed all the steps and created the file but I received the message below:

[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 24, in lambda_handler1
    cnxn = pyodbc.connect(con_string)
END RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f
REPORT RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f Duration: 6.90 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 57 MB Init Duration: 118.61 ms
error1

Can anybody help me?

I am getting the same error. Can anyone please assist?

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Jan 25, 2020

Hi @jpatel1985 , @kaioquinan

I'm not sure why you are getting the error of missing a file with the steps above. I include the zip file of my layer. It has all the files.
Also, make sure that you are using the correct driver version, it has to match what it says in the .ini files, so 17.

https://github.com/alexanderluiscampino/lambda-layers

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Jan 25, 2020

Hi Everyone!

I am facing a similar problem as most of you did before!
May be I am missing something!! :(

The Test Script works locally inside the docker container.
Could you please help me understand the steps that needs to be done after you zip the files.

This is what i did -

  1. I created a lambda_function.py and added inside the zip folder
  2. Created a zip of it and uploaded it to the Lambda function ( Not as a layer)
    Contents of my zip are :
    total 12
    drwxr-xr-x 3 root root 4096 Nov 20 23:28 microsoft
    drwxr-xr-x 4 root root 4096 Nov 20 23:28 mssql-tools
    drwxr-xr-x 3 root root 4096 Nov 20 23:22 python
    bash-4.2# cd /opt
  3. When i try to run the lambda it throws me an error saying that no module named "pyodbc"

Appreciate any help here! been stuck at this for a while now!

Thanks,
Raghu

you gotta have a folder called pyodbc where you put the pyodbc module. you upload your function code and include this pyodbc folder with it. then you can import pyodbc

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Jan 25, 2020

I followed all the steps and created the file but I received the message below:
[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 24, in lambda_handler1
    cnxn = pyodbc.connect(con_string)
END RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f
REPORT RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f Duration: 6.90 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 57 MB Init Duration: 118.61 ms
error1
Can anybody help me?

I am getting the same error. Can anyone please assist?

I think all the files and folder pertaining to the pyodbc module must be inside a pyodbc folder under your root. Otherwise you cannot do a import pyodbc. It is easier if you just add the zip file as a layer and import it in your lambda function

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Jan 25, 2020

Did a final test. Placed all the files pertaining to the pyodbc module inside a folder called pyodbc.

image

then created a lambda_function.py with a basic functionality just to import pyodbc. zipped up the contents as follows:

image

Create a lambda function with this zip file. Which basically has the following structure:

  • pyodbc
  • lambda_function.py

Tested and it works. pyodbc is successfully imported.

@jpatel1985 looking at your example, I think you dont have the complete set of files within your lambda. Because if you had, you wouldnt be able to edit inline the lambda, as you are, since the total size would be above 10mb ( AWS limit for inline editing). There are very large libs in this module

@jpatel1985

This comment has been minimized.

Copy link

@jpatel1985 jpatel1985 commented Jan 25, 2020

@alexanderluiscampino - Thank you for your help. I followed your recommendation and created a lambda function with this zip file with the following structure:

  • pyodbc
  • lambda_function.py

Uploaded zip to the lambda and executed. It is able to load pyodbc but I am getting the following error:

@kaioquinan

This comment has been minimized.

Copy link

@kaioquinan kaioquinan commented Jan 25, 2020

Installed it on one a layer, please feel free to send an email kaiofquinan@gmail.com and I can schedule a conference and show you how to do it thanks

@kaioquinan

This comment has been minimized.

Copy link

@kaioquinan kaioquinan commented Jan 25, 2020

I am busy today until 18:00, i will be back home around 19:00 est, send me your skype

@jpatel1985

This comment has been minimized.

Copy link

@jpatel1985 jpatel1985 commented Jan 26, 2020

I am busy today until 18:00, i will be back home around 19:00 est, send me your skype

I am waiting for you to join the conf. call details I sent over. Let me know if this time does not work for you. We can meet at someother time.

@jpatel1985

This comment has been minimized.

Copy link

@jpatel1985 jpatel1985 commented Jan 26, 2020

@kaioquinan I was able to load the pyodbc layer that @alexanderluiscampino provided to layer and everything seems to be working fine now. Thank you both for your assistance. Appreciate it.

@p-doyle

This comment has been minimized.

Copy link

@p-doyle p-doyle commented Jan 29, 2020

I am getting this error when trying to set this up:

{
  "errorMessage": "module 'pyodbc' has no attribute 'connect'",
  "errorType": "AttributeError",
  "stackTrace": [
    "  File \"/var/task/lambda_function.py\", line 9, in lambda_handler\n    conn = pyodbc.connect(conn_str)\n"
  ]
}

I've tried with the pyodbc package built with this script as well as with the one provided by @alexanderluiscampino. Running dir(pyodbc) returns just this: ['__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__spec__']

My zip structure is:
lambdapackage.zip
pyodbc/
lambda_function.py

Any ideas?

@jrouffiac

This comment has been minimized.

Copy link

@jrouffiac jrouffiac commented Jan 30, 2020

I am having the same issue as @p-doyle and I have the same folder structure. I also have tried both by following this script and by using the zip file that @alexanderluiscampino kindly provides. I get the same error:

{
  "errorMessage": "module 'pyodbc' has no attribute 'connect'",
  "errorType": "AttributeError",
  "stackTrace": [
    "  File \"/var/task/lambda_function.py\", line 9, in lambda_handler\n    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)\n"
  ]
}

I have defined all the db creds above that, so it's not that.
@YSArch

This comment has been minimized.

Copy link

@YSArch YSArch commented Jan 30, 2020

Make sure your lambda and pyodbc layer use python3.7 as runtime version.

@p-doyle

This comment has been minimized.

Copy link

@p-doyle p-doyle commented Jan 30, 2020

The layer was 3.7 and I tried with 3.7 and 3.8 runtimes and same result.

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Jan 30, 2020

Did you use the .zip file I provided on my github?

The zip file contains all the files you need to create that layer.

@jrouffiac

This comment has been minimized.

Copy link

@jrouffiac jrouffiac commented Jan 31, 2020

Apologies - my error was due to not fully understanding how layers worked - I thought the zip uploaded to the lambda itself was what I needed to do. Only just realised it's a shared library. Creating a layer separately and calling the db code from the lambda function appears to have solved that. I am now getting a timeout when trying to establish the database connection, so I suspect that I am using the wroing type of role - next step in the learning experience :)

Thanks for all involved for the info on here.

@Abhishek-Shaw-Kolkata

This comment has been minimized.

Copy link

@Abhishek-Shaw-Kolkata Abhishek-Shaw-Kolkata commented Jan 31, 2020

Hi i am getting error as
module 'pyodbc' has no attribute 'connect'.
My zip structure is:
lambdapackage.zip
pyodbc
lambda_function.py

@p-doyle

This comment has been minimized.

Copy link

@p-doyle p-doyle commented Jan 31, 2020

Sorry for being so dense but I am still getting the same error, this is my folder structure. I zip that and upload it to lambda. I don't really understand how the rds_connector.py file comes into play...
structure

This islambda_function.py:

import pyodbc

def lambda_handler(a, b):
    conn = pyodbc.connect('myconnstr')

@jpatel1985

This comment has been minimized.

Copy link

@jpatel1985 jpatel1985 commented Jan 31, 2020

@p-doyle - Save the pyodbc.zip from (https://github.com/alexanderluiscampino/lambda-layers Note: Use the zip version and rename it as pyodbc) and Save that zip in lambda layer.
image
and then you can use edit code inline for lambda_funtion.py

Thanks to @alexanderluiscampino for uploading the lambda layer.

@p-doyle

This comment has been minimized.

Copy link

@p-doyle p-doyle commented Jan 31, 2020

Wow sorry I am stupid. Didn't realize a lambda layer is something setup in the console... Got it working now... Thanks for the help!!!

@gokul-gopinath-p

This comment has been minimized.

Copy link

@gokul-gopinath-p gokul-gopinath-p commented Feb 5, 2020

@alexanderluiscampino

i am getting error as below

'message': "module 'pyodbc' has no attribute 'paramstyle'\n",
i have downloaded
https://github.com/alexanderluiscampino/lambda-layers/blob/master/pyodbc-9ef8961d-ce5b-4603-b397-03c9a6316eca.zip
and then unzipped and pasted all the files inside a folder named "pyodbc" and uploaded to layers

Please let me know why i am getting the error mentioned above

Thank you

@osmeai

This comment has been minimized.

Copy link

@osmeai osmeai commented Feb 24, 2020

Hi i am getting error as
module 'pyodbc' has no attribute 'connect'.
My zip structure is:
lambdapackage.zip
pyodbc
lambda_function.py

I had the same issue and I fixed it by switching the runtime from 3.6 to 3.7.

@maheshvemula

This comment has been minimized.

Copy link

@maheshvemula maheshvemula commented Mar 12, 2020

Hi, do you have pyodbc later for python 3.8 or 3.6?
Thanks for your help

@pankajPizone

This comment has been minimized.

Copy link

@pankajPizone pankajPizone commented Apr 17, 2020

I followed all the steps and created the file but I received the message below:

[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 24, in lambda_handler1
    cnxn = pyodbc.connect(con_string)
END RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f
REPORT RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f Duration: 6.90 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 57 MB Init Duration: 118.61 ms
error1

Can anybody help me?

Did you solved this? i'm facing same problem with pyodbc even i upload packages of pyodbc

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Apr 17, 2020

I followed all the steps and created the file but I received the message below:
[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 24, in lambda_handler1
    cnxn = pyodbc.connect(con_string)
END RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f
REPORT RequestId: d18aff24-8db4-4f8b-9a20-2904fae5374f Duration: 6.90 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 57 MB Init Duration: 118.61 ms
error1
Can anybody help me?

Did you solved this? i'm facing same problem with pyodbc even i upload packages of pyodbc

Hi,

The package structure you have is not correct. It needs to be in a layer, since the ODBC Driver 17 for SQL Server is defined as being on the /opt/ dir. When you include it with your function, as you did, packaged lambda, you will have to change the pointers of the ODBC Driver 17 for SQL Server to the correct dir. I'd recommend just going to my github, download the layer in a zip file, upload it to the layers repo of lambda and importing the layer in your lambda. As other have done above!

@erwinfeser

This comment has been minimized.

Copy link

@erwinfeser erwinfeser commented Apr 26, 2020

It worked great for months until the last deployment, a few days ago, when I started receiving this error message:
Error loading pyodbc module: /lib64/libm.so.6: version 'GLIBC_2.29' not found (required by /var/task/pyodbc.cpython-38-x86_64-linux-gnu.so)
I tried with python 3.7 and 3.8.

@erathinam

This comment has been minimized.

Copy link

@erathinam erathinam commented Apr 28, 2020

@alexanderluiscampino I tried uploading the zip file as a lambda layer and adding that layer to that lambda function using the management console. But I am still getting the error Unable to import module 'app': No module named 'pyodbc'. Am i missing something here?

@sheldonhull

This comment has been minimized.

Copy link

@sheldonhull sheldonhull commented Apr 28, 2020

I got this to work a few months ago and deployed with terraform for python 3.8 as a lambda layer.
I wanted to ask if there is a normal approach to local testing now that I created a layer. Multistage docker build or what? How do I actually test my sql server lambda queries now so the layer is involved properly. Guessing docker multistage build or something like that, but newer to lambda and figured someone here would know best.

@awsPLC

This comment has been minimized.

Copy link

@awsPLC awsPLC commented Apr 29, 2020

I got this working adding as a lambda layer. I am not sure about others but my biggest issue was trying to run under python version 2.x; make sure you configure the layer with 3.x support and build your lambda in python 3.x . Also, you should run a test first printing out the drivers as the drive is now '{ODBC Driver 17 for SQL Server}' and not '{ODBC Driver 13 for SQL Server}' Here is my WORKING example code:

import pyodbc

def main(event, lambda_context):
    #print("helloworld")

    test_pyodbc()

def test_pyodbc():
    print('Attempting Connection...')
    conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER=<enter your server>;PORT=1433;DATABASE=<enter your database name>;UID=<enter your username>;PWD=<enter your password>");
    print('Connected!!!')
    cursor = conn.cursor()
    
    
    #### Test information; Specific to my database
    cursor.execute("SELECT TOP (10) [timestamp],[value] FROM [mqtt].[dbo].[data]") 
    rows = cursor.fetchall()
    for row in rows:
        print(row.timestamp, row.value)

run a lambda test results in:

ART RequestId: <key> Version: $LATEST
['ODBC Driver 17 for SQL Server']
Attempting Connection...
Connected!!!
b'\x00\x00\x00\x00\x00\x00\x07\xd1' 123
b'\x00\x00\x00\x00\x00\x00\x07\xd2' 456
b'\x00\x00\x00\x00\x00\x00\x07\xd3' 789
b'\x00\x00\x00\x00\x00\x00\x07\xd4' 1260
b'\x00\x00\x00\x00\x00\x00\x07\xd5' 1295
b'\x00\x00\x00\x00\x00\x00\x07\xd6' 1325
b'\x00\x00\x00\x00\x00\x00\x07\xd7' 5
b'\x00\x00\x00\x00\x00\x00\x07\xd9' 5
b'\x00\x00\x00\x00\x00\x00\x07\xdb' 5
b'\x00\x00\x00\x00\x00\x00\x07\xdd' 5
END RequestId: <key>
REPORT RequestId: <key>	Duration: 498.13 ms	Billed Duration: 500 ms	Memory Size: 128 MB	Max Memory Used: 54 MB	Init Duration: 148.43 ms
@nrjchnd2

This comment has been minimized.

Copy link

@nrjchnd2 nrjchnd2 commented May 25, 2020

Hello,
I am getting below issue when I try to upload zip to aws lambda. The connection to sql server works just fine from docker container.

START RequestId: 6f3ec44e-aac0-4cc3-b5ed-f8935c4dff4d Version: $LATEST
[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/var/task/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/lang/lib/python3.8/imp.py", line 234, in load_module
    return load_source(name, filename, file)
  File "/var/lang/lib/python3.8/imp.py", line 171, in load_source
    module = _load(spec)
  File "<frozen importlib._bootstrap>", line 702, in _load
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/var/task/test.py", line 9, in <module>
    conn=pyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server};SERVER=***;PORT=1433;DATABASE=***;UID=***;PWD=***")END RequestId: 6f3ec44e-aac0-4cc3-b5ed-f8935c4dff4d
REPORT RequestId: 6f3ec44e-aac0-4cc3-b5ed-f8935c4dff4d  Duration: 1583.04 ms    Billed Duration: 1600 ms    Memory Size: 128 MB Max Memory Used: 15 MB  
Unknown application error occurred

Can someone please help me out here.
Python version - 3.8 (container + aws lambda)

@paulalex

This comment has been minimized.

Copy link

@paulalex paulalex commented Jun 8, 2020

When running this script everything appears to work but when I enter the repl and import pyodbc and then run >>> print(pyodbc.drivers()) the output is an empty array.

On closer inspection it looks like the download of libmsodbcsql requires openssl or it doesnt download, so I just added this to your list of yum dependencies and it seems all good.

How do you then use this zip file for local testing and development? Do you just run all your tests in a container?

@siran

This comment has been minimized.

Copy link

@siran siran commented Jun 9, 2020

After minor changes this worked like charm for us.
For some reason with the installation procedure of the initial post, y have to set environment variables in Lambda:

LD_RUN_PATH=/opt:/opt/lib:/opt/msodbcsql17:/opt/msodbcsql17/lib64
LD_LIBRARY_PATH=/opt:/opt/lib:/opt/msodbcsql17:/opt/msodbcsql17/lib64
ODBCINI=/opt
ODBCSYSINI=/opt

We then adapted the initial script so we could do directly import pyodbc (and avoid doing [1]). The change was:

# install pyodbc for use with python.
# Notice the folder structure to support python 3.7 runtime
# https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html#configuration-layers-path

# changed installation path here
INSTALL_PATH=/opt/python/lib/python3.7/site-packages

mkdir -p $INSTALL_PATH || true
cd $INSTALL_PATH
pip install pyodbc -t .

@paulalex probably you can use some of the previous environment variables, I think I ran through your issue too...

[1]

import sys
sys.path.append('/opt')
sys.path.append('/opt/python')
@paulalex

This comment has been minimized.

Copy link

@paulalex paulalex commented Jun 9, 2020

I cannot actually connect to any database's from a python lambda using this driver. Do the env vars that need to be exported to the container also need to be exported as env vars inside the lambda function as well?

I consistently get the exception (even with the env vars exported):

'[HYT00] [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired (0) (SQLDriverConnect)'

@paulalex

This comment has been minimized.

Copy link

@paulalex paulalex commented Jun 9, 2020

After minor changes this worked like charm for us.
For some reason with the installation procedure of the initial post, y have to set environment variables in Lambda:

LD_RUN_PATH=/opt:/opt/lib:/opt/msodbcsql17:/opt/msodbcsql17/lib64
LD_LIBRARY_PATH=/opt:/opt/lib:/opt/msodbcsql17:/opt/msodbcsql17/lib64
ODBCINI=/opt
ODBCSYSINI=/opt

We then adapted the initial script so we could do directly import pyodbc (and avoid doing [1]). The change was:

# install pyodbc for use with python.
# Notice the folder structure to support python 3.7 runtime
# https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html#configuration-layers-path

# changed installation path here
INSTALL_PATH=/opt/python/lib/python3.7/site-packages

mkdir -p $INSTALL_PATH || true
cd $INSTALL_PATH
pip install pyodbc -t .

@paulalex probably you can use some of the previous environment variables, I think I ran through your issue too...

[1]

import sys
sys.path.append('/opt')
sys.path.append('/opt/python')

I tried adding the ODBCINI and ODBCSYSINI but to no avail, I will try your other two. If it works, you have saved me from staying up til past midnight tonight trying to get this working!

**Edit after an evening of testing I can confirm that the script above (which I based my own scripts on) does work... I have tested against versions 13 and 17 of the driver and both work from inside the lambda container from the repl to a sql server instance, and from a lambda (baked into a layer) to an sql server instance in my own AWS account.

The issues I have connecting from a lambda in another AWS account to an on premise database must be unrelated because when I remove that lambda from the VPC I AM then able to connect to a public mssql server instance in my own AWS account over the internet.

For further information I did not have to do either @sirans steps of amending the path or exporting any environment variables, what I did do is to ensure I used python 3.8 version of the container, ensure my layer is compatible with python 3.8 and ensure my lambda run-time is python 3.8.

@paulalex

This comment has been minimized.

Copy link

@paulalex paulalex commented Jun 10, 2020

For anyone who might have the same use case as me see this: mkleehammer/pyodbc#537

In a nutshell: Named instances are not yet enabled on Linux version of the ODBC Driver 17 for SQL Server., so everything was actually working fine and I wasted the best part of two days trying to work out what was going on, but it turns out that because I was trying to connect to a named instance server\instance_name the driver never sends any traffic out.

@siran

This comment has been minimized.

Copy link

@siran siran commented Jun 11, 2020

I cannot actually connect to any database's from a python lambda using this driver. Do the env vars that need to be exported to the container also need to be exported as env vars inside the lambda function as well?

I consistently get the exception (even with the env vars exported):

'[HYT00] [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired (0) (SQLDriverConnect)'

In fact you are getting timeout error. So everything seems working.
Only your database is not responding to the Lambda.
Is your database in the same VPC/Subnet/Security group as Lambda?
Does the SG have the ports open?
etc

(Glad it worked for you, @paulalex.

I had written this comment for you before I read your comment. Maybe someone finds them useful...)

@naingaungphyo

This comment has been minimized.

Copy link

@naingaungphyo naingaungphyo commented Nov 27, 2020

Can anyone share compiled zip file(lambda layer) for python3.8 environment?

@naingaungphyo

This comment has been minimized.

Copy link

@naingaungphyo naingaungphyo commented Nov 27, 2020

I just created lambda layer zip for python3.8 and everything is working and tested! Thanks everyone in this gist and Tom!
https://github.com/naingaungphyo/lambda_pyodbc_layer-python3.8

@bendog

This comment has been minimized.

Copy link

@bendog bendog commented Jan 21, 2021

I just created lambda layer zip for python3.8 and everything is working and tested! Thanks everyone in this gist and Tom!
https://github.com/naingaungphyo/lambda_pyodbc_layer-python3.8

Thanks @naingaungphyo, saved me a lot of trouble!

@crisnicogonzalez

This comment has been minimized.

Copy link

@crisnicogonzalez crisnicogonzalez commented Apr 9, 2021

Hi! How can I add more dependencies on this layer? Like Pandas, Numpy?

@alexanderluiscampino

This comment has been minimized.

Copy link

@alexanderluiscampino alexanderluiscampino commented Aug 21, 2021

@crisnicogonzalez ideally you don't want to make your layers too heavy. I'd recommend just building another layer with pandas + numpy. And if your lambda needs both, just add them both. In many situations, you will just need one or the other.

But you can always open the zip, download it locally and do a pip install -t . pandas numpy where the pyodbc contents where extracted to and rezip and create the layer again.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment