[Python] How can I connect Oracle DB to lambda using the oracledb python module?

Our Oracle database , with version 12.1, is hosted on ec2 as a standalone DB. I am trying to access that server from another account. Using the python module oracleDB in Thin Mode, I wrote a script that allows me to connect to the database.

From my local system, using Visual Studio, I am able to connect to the database. However, when I modify the same code to AWS Lambda and test it, my lambda function times out.

I tried to modify the VPC setting of the database. I set the lambda function as part of an inbound rule and for lambda iam role I provided 'AWSLambdaVPCAccessExecutionRole'.

Could someone explain why is it happening and how I could fix it?

Here is the error message I get:

{
"errorMessage": "2023-11-29T17:33:43.835Z 7bb3a696-cab2-4964-9a09-691995e136f1 Task timed out after 30.01 seconds"
}


Below is the code implementation:

import oracledb
import csv
import os
from datetime import datetime
import boto3
import json


def lambda_handler(event, context):
try:
export_to_csv("Sample")
return {"statusCode": 200, "body": json.dumps("Hello from Lambda!")}

except Exception as e:
print(f"Error: {str(e)}")
return {"statusCode": 500, "body": json.dumps(f"Error: {str(e)}")}

def export_to_csv(table_name):
try:
# Initialize Oracle client
# Oracle connection details
oracle_user = "***"
oracle_password = "****"
oracle_dsn = "****"

s3_bucket_name = "***"
s3_prefix = "****"
aws_access_key = "****"
aws_secret_key = "****"

# Initialize Oracle connection
connection = oracledb.connect(
user=oracle_user, password=oracle_password, dsn=oracle_dsn
)

# Create an S3 client
s3_client = boto3.client(
"s3", aws_access_key_id=aws_access_key, aws_secret_access_key=aws_secret_key
)

if connection:
# Query to get column names
cursor = connection.cursor()
cursor.execute(
f"SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '{table_name}'"
)
column_names = [row[0] for row in cursor.fetchall()]

# Get current date
current_date = datetime.now().strftime("%Y%m%d_%H%M%S")

# Define CSV file path with current date in the filename
csv_file_path = f"output_{current_date}.csv"

with open(csv_file_path, "w", newline="") as csv_file:
csv_writer = csv.writer(csv_file)

csv_writer.writerow(column_names)
cursor.execute(f"SELECT * FROM {table_name} ")
data = cursor.fetchall()

# Write data rows
for row in data:
csv_writer.writerow(row)

# Upload CSV file to S3
s3_object_key = os.path.join(s3_prefix, f"output_{current_date}.csv")
s3_client.upload_file(csv_file_path, s3_bucket_name, s3_object_key)

print("Data transfer to S3 completed")

else:
raise Exception("Oracle connection failed.")

except Exception as e:
print(f"Error is {e}")
raise e

Continue reading...