Google CloudSQL Postgres instance database INSERT not working from AWS Lambda, however the SELECT works

Question

The service account auth, database username/pwd for connecting cloudSQL all works, the SELECT statement execution works as well, but strangely the INSERT does not work with no clues, no error, no exception. Need an expert advice here.

Update: The Query insights in CloudSQL shows the INSERT query, but the table is still empty !

def execute_statement(db: sqlalchemy.engine.base.Engine, rows) -> None:
    start_time = time.time()
    invoice_month = str(now.month)
    invoice_period = "%s%s" % (now.year, invoice_month.zfill(2))
    
    sql_query = text("""
        INSERT INTO zoom_account
            (id, month, account_number, service, cost, updated)
        VALUES (:id, :month, :account_number, :service, :cost, :updated)
        ON CONFLICT (id, month) 
        DO UPDATE SET account_number = EXCLUDED.account_number, 
        service = EXCLUDED.service, cost = EXCLUDED.cost, 
        updated = EXCLUDED.updated
        RETURNING id
    """)
    data = (
            {"id": str(hash(row.account_num + ", " + row.service_name) % 10000), 
             "month": invoice_period,
             "account_number": row.account_num,
             "service": row.service_name,
             "cost": Decimal(row.estimated_cost),
             "updated": datetime.datetime.now()}
            for row in rows
    )

    with db.connect() as conn:
        try:
            for line in data:
                conn.execute(sql_query, line)
        except SQLAlchemyError as e:
            logger.error(f"Error inserting or updating data into database: {str(e)}")

    # query = text("SELECT * FROM zoom_account")
    # with db.connect() as conxn:
        # res = conxn.execute(query).fetchall()
        # logger.info(f'Result: {res}')
        # This select works without issue

    duration = time.time() - start_time
    logger.info(f"{len(rows)} Insert or update took {duration:.2f} seconds.")

Above lambda function run log shows insert happened without error, but the table is still empty in CloudSQL database:

2023-03-06T11:35:05.206+11:00   2023-03-06 00:35:05,206 - extract - INFO - Initialising database connection ...

2023-03-06T11:35:39.251+11:00   2023-03-06 00:35:39,250 - extract - INFO - 106 Insert or update took 31.11 seconds.

score 1 · Answer 1 · answered Mar 06 '23 at 01:24

ahh silly thing as it looks like.

SQLAlchemy is supposed to handle the transaction by default, but it did not so. So I had to handle it like below to make it work.

with db.connect() as conn:
        try:
            trans = conn.begin()
            for line in data:
                conn.execute(sql_query, line)
            trans.commit()
        except SQLAlchemyError as e:
            trans.rollback()
            logger.error(f"Error inserting or updating data into database: {str(e)}")

Google CloudSQL Postgres instance database INSERT not working from AWS Lambda, however the SELECT works

1 Answers1