update or insert item based on existence in table dynamodb python

Question

The Problem

I'm trying to look into table item by item, so that if an item already exists then I should be able to update it, if not then I should be able to insert it.

However, I learnt that update works like upsert(update/insert) as well. My case doesn't suit that as well.

My Case

1. Check if item exists in table and store the flag (boolean)
2. Check if flag is 0, (item not available) then, insert the item and add current timestamp into column Inserted_dttm
3. Check if flag is 1, (item available) then, update the item and add current timestamp into column Updated_dttm (and not Inserted_dttm)

The Trial

I've been looking query() is a good option over get_item(), however your solutions are welcomed with any of it.

def lambda_handler(event, context):
    x = TrainDataProcess()
    file_name = 'Training_data/' + event['file_name']
    s3.Object(bucket_name, file_name).download_file('/tmp/temp.xlsx')
    table_name = 'training_data'

    x.load_excel(name='/tmp/temp.xlsx')
    x.load_headers(skiprows=0)
    x.data_picking()

    table = dynamoDB_client.Table(table_name)
    load = x.return_records_json()

    try:
        with table.batch_writer() as batch:
            for record in load:
                flag = table.query(TableName=table_name, )
                if flag == 0:
                    record['inserted_dttm'] = get_dttm()
                    batch.put_item(Item=record)
                elif flag == 1:
                    record['updated_dttm'] = get_dttm()
                    batch.update_item(Item=record)
            return True
    except Exception as e:
        return e

Answer 1

DynamoDB has the concept of conditional expressions (https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html) which you can use to identify if you are upserting or inserting.

Unfortunately, however, it does not have the ability to dynamically change the insert/update depending on the result of the condition. i.e. you can enforce that the Updated_dttm is only set on an update, and Inserted_dttm is only set on an insert, but you cannot do both in a single operation.

You can, however, do this in two steps (for now I'll assume updates are more common than inserts, but you can reverse the logic if that is not true):

1. Attempt to update (set Updated_dttm) with the condition attribute_exists(PARTITION_KEY_NAME)
2. If that fails with ConditionalCheckFailedException then assume inserting (set Inserted_dttm)

n.b. if you have a partition and sort key you'll need to use the condition attribute_exists(PARTITION_KEY_NAME) and attribute_exists(SORT_KEY_NAME)