How we can connect to oracle database through aws lambda python?

0
votes

I have a requirement in which I need to connect to oracle database and get some data from db table. So as I am a beginner in aws lambda so i started with below example.

import cx_Oracle
import os
import logging

logger = logging.getLogger()
logger.setLevel(logging.INFO)

def lambda_handler(event, context):

logger.info('begin lambda_handler')

dsn = cx_Oracle.makedsn("hostname", "1521", "sid")
con = cx_Oracle.connect("user_id", "password", dsn)
cur = con.cursor()

#logger.info('username: ' + username)
#logger.info('host: ' + host)

sql = """SELECT COUNT(*) AS TEST_COUNT FROM DUAL"""

cur.execute(sql)
columns = [i[0] for i in cur.description]
rows = [dict(zip(columns, row)) for row in cur]
logger.info(rows)

con.close()
logger.info('end lambda_handler')
return "Successfully connected to oracle."

I created below structure in linux for creating deployment package.

Parent_Directory

-lib(Under this library i placed all the oracle instant client files)

-cx_oracle files

lmbda_function.py

after deploying the package on aws lambda and testing it, I get below error.

[ERROR] DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "/var/task/lib/libclntsh.so: file too short". See https://oracle.github.io/odpi/doc/installation.html#linux for help Traceback (most recent call last):   File "/var/task/lambda_function.py", line 29, in lambda_handler

Please somebody help me to achieve this, also if there is any better option to connect with oracle from aws lambda than please share.

3
python-3.xoracleaws-lambda

3 Answers

0
votes

I was running into the same problem. After reading this article it looks like you are missing the libaio.so.1 file. I am guessing you downloaded the instant client from a windows machine because from what I understand, the libaio file is not included when downloading onto the windows platform. I was able to download the libaio.so.1 file from this repo. Just make sure to put it in the lib folder with the other oracle instant client files.

0
votes

  1. Snip up an Ec2 instance

  2. Download cx_Oracle-7.3.0-cp37-cp37m-manylinux1_x86_64.whl - https://pypi.org/project/cx-Oracle/#files

  3. Download Oracle instant client - https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html (instantclient-basiclite-linux.x64-21.3.0.0.0)

  4. Download lib64aio1-0.3.111-1pclos2018.x86_64.rpm

  5. In Ec2 instance, please follow below steps

    1. sudo yum update
    2. Install pip ( sudo yum install python3-pip) – If pip is not installed. I think by default we will get pip in Ec2. You can check the version by running command “pip3 –version”
    3. The next step is to install Pipenv. A virtual environment is a tool to keep the dependencies required by different projects in separate places, by creating virtual Python environments for them.
    4. sudo pip3 install virtualenv
    5. Virtualenv oraclelambda(some folder name), you will see a directory with name oraclelambda
    6. source oraclelambda/bin/activate
    7. Pip install cx_Oracle-7.3.0-cp37-cp37m-manylinux1_x86_64.whl (You will notice cx_Oracle installed in oraclelambda directory)
    8. sudo yum remove libaio-0.3.109-13.amzn2.0.2.x86_64
    9. sudo yum install lib64aio1-0.3.111-1pclos2018.x86_64.rpm
    10. Next go to usr/lib64 – you will see libaio.so.1.0.1 (copy this file)
    11. Next step extract oracle instant client
    12. Paste the libaio.so.1.0.1 file into instant client folder and rename the libaio.so.1.0.1 file to libaio.so.1
    13. Next step is grab that instant client folder for instance it would be instantclient_21_3 out from the extracted zip and zip it
    14. Create new folder with name python and create the another lib folder inside the python folder.
    15. Next step is copy the python3.7 folder from oraclelambda/lib64 and place it in phthon/lib folder. The folder structure would be python\lib\python3.7\site-packages\
    16. Now zip the python folder

  6. At this point we have two zip files

    1. Instantclient
    2. Python

  7. The above two zip files we are going to update them in lambda layers

  8. Create the lambda, update the zip files into the layers, have proper security groups created.

  9. Please add below to lambda’s environment variables so that it can locate the Oracle Instant Client library.

    Key  = LD_LIBRARY_PATH
Value = /opt/
0
votes

I was having a similar issue. It was resolved after I downloaded the Oracle Client library & libaio and bundled it with cx_Oracle while creating a Lambda Layer. You may follow the below video I created for this activity. Hope it is helpful to you - https://www.youtube.com/watch?v=BYiueNog-TI&ab_channel=BorrowedCloud