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
@alexanderluiscampino
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

naingaungphyo commented Nov 27, 2020

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

@naingaungphyo
Copy link

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
Copy link

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
Copy link

crisnicogonzalez commented Apr 9, 2021

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

@alexanderluiscampino
Copy link

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.

@manush-7
Copy link

manush-7 commented Dec 22, 2021

@alexanderluiscampino I used the layer created by you uploaded that to Lambda Layer. Created the same structure for Inline code. I still get the error.
module 'pyodbc' has no attribute 'connect'
image

@alexanderluiscampino
Copy link

alexanderluiscampino commented Dec 22, 2021

Hi @manush-7 , did you attempt to use it as a layer? The screenshot above looks like it is part of your deploy package. That will not work like that.
The layer code will go into /opt, when the AWS Lambda spins up.

what version of python are you using? Make use of the zip files on my repo. You can directly upload that as a layer.

@manush-7
Copy link

manush-7 commented Dec 22, 2021

I'm using python 3.8. I even tried 3.6

I have created layer using the zip file (https://github.com/alexanderluiscampino/lambda-layers/blob/master/pyodbc-9ef8961d-ce5b-4603-b397-03c9a6316eca.zip).

@alexanderluiscampino
Copy link

alexanderluiscampino commented Dec 22, 2021

For python 38 you have to use the zip I just uploaded.
since these are compiled libs, they need to be specific.

@alexanderluiscampino
Copy link

alexanderluiscampino commented Dec 22, 2021

Alternatively, you can build you own layer. Here is the code for it:

ARG VARIANT="3.8"
FROM lambci/lambda:build-python${VARIANT}
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 openssl \
    && ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" \
    && export LDFLAGS="-L/root/lib" \
    && pip install pyodbc adodbapi pyDes --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 17 for SQL Server]\nDriver = ODBC Driver 17 for SQL Server\nDescription = My ODBC Driver 17 for SQL Server\nTrace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDescription = Microsoft ODBC Driver 17 for SQL Server\nDriver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1\nUsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python \
    && cp -r /root/* /opt/python \
    && mv /opt/python/lib /opt \
    && mv /opt/python/bin /opt \
    && cd /opt \
    && zip -r /pyodbc.zip .

This is for 3.8. You can change the ARG value at the top, but I have only used for 38 so far.

@manush-7
Copy link

manush-7 commented Dec 22, 2021

Thanks @alexanderluiscampino it worked.

@medichops1
Copy link

medichops1 commented Mar 3, 2022

Hi all; Got myself into a bit of a mess with this one. Does anyone have a fully working zip that you can upload?

@paulalex
Copy link

paulalex commented Mar 3, 2022

Hi all; Got myself into a bit of a mess with this one. Does anyone have a fully working zip that you can upload?

pyodbc layer

@medichops1
Copy link

medichops1 commented Mar 3, 2022

Thanks @paulalex thats a huge help.

@jgc93
Copy link

jgc93 commented Mar 4, 2022

When building in CDK using the experimental PythonFunction, the imports are placed in a requirements.txt file in the lambda folder.
I have uploaded a zip file to Lambda in the console, and am adding the layer to the PythonFunction lambda in my CDK stack.

But if I add pyodbc to the requirements.txt file, the docker build receives this error in SAM gcc: error trying to exec 'cc1plus': execvp: No such file or directory

If i omit pyodbc from the requirements.txt file, I receive the other area of can't import module lambda-handler.py .... lib.so.2 no such file or directory

any help regarding using the CDK and the lambda PythonFunction construct would be greatly appreciated!

@Neleawan
Copy link

Neleawan commented Mar 14, 2022

@jordan-brough
Copy link

jordan-brough commented Jun 13, 2022

Here's a Python 3.9 version that's working for me. I've also published it on StackOverflow here.

Based on:

# Dockerfile
# See https://stackoverflow.com/a/72617192/58876

FROM amazon/aws-lambda-python:3.9
ENTRYPOINT []

WORKDIR /root

# Get development tools to enable compiling
RUN yum -y update
RUN yum -y groupinstall "Development Tools"

# Get unixODBC and install it
RUN yum -y install tar gzip
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.11.tar.gz -O
RUN tar xvzf unixODBC-2.3.11.tar.gz
WORKDIR /root/unixODBC-2.3.11
RUN ./configure --sysconfdir=/opt/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home
RUN make install
WORKDIR /root
RUN mv /home/* .
RUN mv unixODBC-2.3.11 unixODBC-2.3.11.tar.gz /tmp

# Install msodbcsql
RUN curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
RUN yum -y install e2fsprogs openssl
RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN cp -r /opt/microsoft/msodbcsql .

# Install pyodbc
# Need "unixODBC-devel" to avoid "src/pyodbc.h:56:10: fatal error: sql.h: No such file or directory" during pip install
RUN yum -y install unixODBC-devel
RUN export CFLAGS="-I/root/include"
RUN export LDFLAGS="-L/root/lib"
RUN pip install pyodbc adodbapi pyDes --upgrade -t .

# Create odbc.ini and odbcinst.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDriver = ODBC Driver 17 for SQL Server\nDescription = My ODBC Driver 17 for SQL Server\nTrace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDescription = Microsoft ODBC Driver 17 for SQL Server\nDriver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1\nUsageCount = 1' > /root/odbcinst.ini

# Generate the zipped file that can be uploaded as a Lambda Layer
RUN mkdir -p /opt/python
RUN cp -r /root/* /opt/python
RUN mv /opt/python/lib /opt
RUN mv /opt/python/bin /opt
WORKDIR /opt
RUN zip -r /pyodbc.zip .
# Build the image (this will take a while...)
$ docker build --platform=linux/amd64 -t mssql-lambda .
# Copy the zipped file to /tmp/pyodbc.zip on your computer
$ docker run --platform=linux/amd64 --rm --volume /tmp:/local-folder mssql-lambda cp /pyodbc.zip /local-folder/

Upload /tmp/pyodbc.zip from your computer to AWS as a Lambda Layer.

Create a Python 3.9 Lambda to test the layer:

import pyodbc

def lambda_handler(event, context):
    driver = '{ODBC Driver 17 for SQL Server}'
    server = 'XXX'
    port = '1433'
    database =  'XXX'
    username = 'XXX'
    password = 'XXX' # ideally put this in a SecretsManager secret instead of directly in the code
    connection = pyodbc.connect(f'DRIVER={driver};SERVER={server},{port};DATABASE={database};UID={username};PWD={password}')
    cursor = connection.cursor()
    query = """
        select @@version
    """

    cursor.execute(query)

    for row in cursor.fetchall():
        print(row[0])

@hben-align
Copy link

hben-align commented Jul 24, 2022

Hi,

I'm trying to configure Lambda with python runtime and I try to connect to a PG instance, I have unixodbc files and the odbc files for pg and I get the following error:
[unixODBC][Driver Manager]Can't open lib '/var/task/lib/psqlodbcw.so' : file not found

I tried both WSL and CentOS machine to get the files from,
the way I did it was: downloading the jar file for unixODBC and configuring it using the /var/task path then make and make install,
and I did the same for the postgresql-odbc: downloaded the jar file, configured it to the /var/task path then make and make install,
and then I had the following content:
image
inside lib I had this:
image

then I copied the entire /var/task folder to the windows machine, changed the ini file as specified above, and .ziped it and uploaded it to AWS Lambda using CDK and python.

odbcinst.ini is:
[PostgreSQL Unicode]
Description=Postgres odbc
Driver=/var/task/lib/psqlodbcw.so
UsageCount=1

and odbc.ini is:
[PostgreSQL Unicode]
Driver = PostgreSQL Unicode
Description = My ODBC Driver For PostgreSQL
Trace = No

also from within the lambda function itself, I export 2 env for the .ini files:
os.environ['ODBCINI']="/var/task"
os.environ['ODBCSYSINI']="/var/task"
image

for some reason its just not working :|
somebody knows how to fix it for pyodbc and postgres?

@alexanderluiscampino
Copy link

alexanderluiscampino commented Jul 24, 2022

@hben-align I have never used pyodbc to connect to PG instances, it seems that your process is more or less correct, but with .so files its always hard to actually detect what might be wrong. Someone else more knowledgeable in that field might provide more help.

But I ask, why not using psycopg2 to connect to PG instance? Here is the already built layer for lambda. I use that layer in production on the daily, it's great!

@hben-align
Copy link

hben-align commented Jul 25, 2022

@alexanderluiscampino I used psycopg2 (which I compiled myself) but this project was written using pyodbc for consistency (due to the compatibility with other engines)...
if I have no other choice I'll change the code to psycopg2, but I'm trying to avoid it for now because its a lot of work :\

@hben-align
Copy link

hben-align commented Jul 25, 2022

btw, I've managed to get it working on a local ubuntu machine, but in lambda I still get the "[unixODBC][Driver Manager]Can't open lib '/var/task/lib/psqlodbcw.so' : file not found" error...
even when zipping the working dir from ubuntu

lol, printing the directory after the failure print out the file in the right path :\\
image
image

@hben-align
Copy link

hben-align commented Jul 27, 2022

@alexanderluiscampino
I've managed to make it work (pyodbc with pg odbc)
I'll post here a comment with how I did it later :)

@hben-align
Copy link

hben-align commented Jul 27, 2022

This method is for using pyodbc with pg odbc inside the task (not as a layer)

create a new ec2 machine with amazon linux 2.

then run:
sudo yum update

cd /home/ec2-user

download unixODBC and postgresql odbc

curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.11.tar.gz -O
curl https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-13.02.0000.tar.gz -O
tar xzvf unixODBC-2.3.11.tar.gz
tar -zxvf psqlodbc-13.02.0000.tar.gz

compile unixODBC (install necessary packages)

cd /home/ec2-user/unixODBC-2.3.11/
sudo yum groupinstall "Development Tools"
./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/var/task
sudo make
sudo make install

compile psqlodbc (install necessary packages)

cd /home/ec2-user/psqlodbc-13.02.0000/
sudo amazon-linux-extras enable postgresql9.6
sudo yum install -y postgresql-libs postgresql-server postgresql postgresql-devel
./configure --with-libpq=/var/task --with-unixodbc=/var/task --sysconfdir=/var/task --prefix=/var/task
sudo make
sudo make install

fill up nessesery libs (using root permissions)

sudo su
cd /var/task/lib
cp /lib64/libpq.so.5 .
cp /lib64/libdl.so.2 .
cp /lib64/libpthread.so.0 .
cp /lib64/libc.so.6 .
cp /lib64/libssl.so.10 .
cp /lib64/libcrypto.so.10 .
cp /lib64/libgssapi_krb5.so.2 .
cp /lib64/libldap_r-2.4.so.2 .
cp /lib64/ld-linux-x86-64.so.2 .
cp /lib64/libkrb5.so.3 .
cp /lib64/libcom_err.so.2 .
cp /lib64/libk5crypto.so.3 .
cp /lib64/libz.so.1 .
cp /lib64/libkrb5support.so.0 .
cp /lib64/libkeyutils.so.1 .
cp /lib64/libresolv.so.2 .
cp /lib64/liblber-2.4.so.2 .
cp /lib64/libsasl2.so.3 .
cp /lib64/libssl3.so .
cp /lib64/libsmime3.so .
cp /lib64/libnss3.so .
cp /lib64/libnssutil3.so .
cp /lib64/libplds4.so .
cp /lib64/libplc4.so .
cp /lib64/libnspr4.so .
cp /lib64/libselinux.so.1 .
cp /lib64/libcrypt.so.1 .
cp /lib64/librt.so.1 .
cp /lib64/libpcre.so.1 .

cd /var/task
zip -r /home/ec2-user/task.zip .

change .ini files as stated above

odbc.ini:
[PostgreSQL Unicode]
Driver=PostgreSQL
Description = My ODBC Driver For PostgreSQL
Trace = No

odbcinst.ini:
[PostgreSQL]
Description=Postgres odbc
Driver=/var/task/lib/psqlodbcw.so
UsageCount=1

now call the driver by using "PostgreSQL"

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