Connecting to AWS RDS through Chalice for different routes?

Question

A typical chalice app has several routes defined, but how do I properly connect that to an RDS so that I can save POST data, or retrieve data from a db? I already created a database through the RDS panel; I'm just not sure how to access it from Chalice.

Is it simply just having each route connect to the given RDS host with the user/pass?

For example,

from chalice import Chalice
import psycopg2

app = Chalice(app_name='test')
db_user = 'test'
db_pass = 'password'
db_host = 'https://.....'
db_port = 5432

@app.route('/save_data')
def save_data():
    with psycopg2.connect(user=db_user, password=db_pass, ...) as conn:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (1,2))

@app.route('/get_data')
def get_data():
    with psycopg2.connect(user=db_user, password=db_pass, ...) as conn:
        with conn.cursor() as cur:    
            cur.execute("SELECT * FROM test")


@app.route('/something')
def something():
    with psycopg2.connect(user=db_user, password=db_pass, ...) as conn:
        with conn.cursor() as cur:    
            cur.execute(....)  # some other query

If so, is there an easier way to handle this instead of having to specify the db connection each time? Are there weird session/race conditions associated with doing it this way?

Or, if I'm completely off, what's the proper way to connect to the RDS from Chalice routes?

You need to find an ORM that you like for Python. I believe Chalice is just a controller layer. This looks like a solid article to review. https://www.fullstackpython.com/object-relational-mappers-orms.html — George Whitaker, Jun 13 '17 at 12:31
@GeorgeMWhitaker I don't quite understand. An ORM would still be using psycopg2 or some other postgres connector lib to connect to the db under the hood. I'm trying to connect to the db, not trying to find a way to abstract my db tables. — watdeo, Jun 13 '17 at 12:59
ORMs often have an abstraction layer that handles the connection management. Example: http://docs.sqlalchemy.org/en/rel_1_1/orm/session_basics.html#what-does-the-session-do. Which is the root of your question. You can try to write your own but you could probably save a lot of time by using an ORM — George Whitaker, Jun 13 '17 at 18:56

score 0 · Answer 1 · answered Apr 16 '18 at 15:12

I actually have this set up in my Chalice application. What I did was set up Parameter store and store my different database connections there and call them into Chalice.

I made a file called Parameters.py and placed this in it:

import boto3

def getParameter(param_name):
    # Create the SSM Client
    ssm = boto3.client('ssm', region_name='us-east-1')

    # Get the requested parameter
    response = ssm.get_parameters(
        Names=[
            param_name,
        ],
        WithDecryption=True
    )

    # Store the credentials in a variable
    credentials = response['Parameters'][0]['Value']

    return credentials

Then in the I can call it like this:

def dbInsert(query, stage):
    stageParm = stage + "_database"
    parm = Parameters.getParameter(stageParm)
    pJson = json.loads(parm)
    repli = pJson["replication"]
    dbName = pJson["database"]
    writeEndPoint = repli["write"]["host"]
    writeUsername = repli["write"]["username"]
    writePassword = repli["write"]["password"]
    try:
        conn = pymysql.connect(
                writeEndPoint,
                user=writeUsername,
                passwd=writePassword,
                db=dbName,
                connect_timeout=5)
    except Exception as ex:
        return ex
    cur = conn.cursor()
    cur.execute(query)
    results = cur.fetchall()
    cur.close()
    conn.close()
    return results
    results = cur.fetchall()
    cur.close()
    conn.close()
    return results

Hope it helps.

This is not persistent between calls but you can actually set caching in your API gateway to solve this. I didn't need persistence because of the project requirements. This is a great article to explain how the structure works for microservices like lambdas: https://aws.amazon.com/blogs/big-data/from-sql-to-microservices-integrating-aws-lambda-with-relational-databases/ — griff4594, Apr 18 '18 at 16:43

Connecting to AWS RDS through Chalice for different routes?

1 Answers1