python peewee upsert only dictionary specified fields

Question

I am new to peewee (v3.17) - using with sqlite(v3.40), trying to write a function that updates or inserts a data to my user table, This is the model class

class MyUser(Model):
    id = AutoField()
    user_mail = TextField(unique=True)
    username_a = TextField(null=True)
    user_id_a = TextField(null=True)
    username_b = TextField(null=True)
    user_id_b = TextField(null=True)

    class Meta:
        table_name = 'my_user'
        database = db

This is the function I am using to add or update my data. Refernce

def upsert_user(**user_data):
    """Insert / update given data to user table"""

    return (MyUser
            .insert(**user_data)
            .on_conflict(
                conflict_target=[MyUser.user_mail],
                preserve=[MyUser.username_a,
                            MyUser.user_id_a,
                            MyUser.username_b,
                            MyUser.user_id_b])
            .execute())

These are my sample data.

data0 = {
    "user_mail": "[email protected]",
    "username_a": "user1_a",
    "user_id_a": "a1",
    "username_b": "user1_b",
    "user_id_b": "b1"
}

upsert_user(**data0)

data1 = {
    "user_mail": "[email protected]",
    "username_a": "user1_a_new",
    "user_id_a": "a1_new"
}

upsert_user(**data1)

Expected only username_a and user_id_a columns to get updated preserving other columns, but other columns got null(the default value) after function call.

I've added all other column names to preserve because I need also these to work,

data2 = {
    "user_mail": "[email protected]",
    "username_b": "user1_b_new",
    "user_id_b": "b1_new"
}

# only update the mentioned colums

data3 = {
    "user_mail": "[email protected]",
    "username_a": "user2_a",
    "user_id_a": "a2"
}

# Add new row 

How can I achieve update/insert only the mentioned dictionary columns.

Answer 1

You don't want preserve then, you want update, e.g.:

def upsert_user(**user_data):
    """Insert / update given data to user table"""

    return (MyUser
            .insert(**user_data)
            .on_conflict(
                conflict_target=[MyUser.user_mail],
                update=user_data)
            .execute())