Recommended solution: https://docs.aws.amazon.com/lambda/latest/dg/vpc-rds.html
Coded a python script (as attached) for insert the biller details to the dev db, it works well per my testing on the dev ec2 instance (xxx-util-services). It use the "awslambda-psycopg2" library to for the Postgres connection.
https://github.com/jkehler/awslambda-psycopg2
Next step is to make the script to be a AWS lambda compatiable (with Lambda function handlers) and then package with the library into a AWS Lambda zip package.
#!/usr/bin/python
import psycopg2
from datetime import datetime
db_host = "xxxxxx-esb-xx.xxxxx.xx-xxxx.rds.xxxxx.com"
db_port = 5432
db_name = "xxx"
db_user = "xxx"
db_pass = "xxxx"
db_table = "xxxxx"
biller_code = "111111"
biller_short_name = "test_short"
biller_long_name = "test_long"
def make_conn():
conn = None
try:
conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" % (db_name, db_user, db_host, db_pass))
print "connected to postgres db successfully"
except:
print "I am unable to connect to the database"
return conn
try:
connection = make_conn()
cursor = connection.cursor()
cursor.execute("SELECT * FROM billers where biller_code = '%s';" % biller_code)
numOfRows = cursor.rowcount
print("%s rows found for the biller code: %s" % (numOfRows, biller_code))
if(numOfRows<=0):
dt = datetime.now()
cursor.execute("insert into billers (biller_code, biller_short_name, biller_long_name, active, min_length, max_length, reg_exp, check_digit_algo, created_at, updated_at) values (%s, %s, %s, true, 0, 100, 'NONE', 'NONE', %s, %s)", (biller_code, biller_short_name, biller_long_name, dt, dt))
connection.commit()
print("inserted %s rows successfully" % cursor.rowcount)
except (Exception, psycopg2.Error) as error :
print ("Error caught", error)
finally:
#closing database connection.
if(connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")