How do I update certain columns of an SQLAlchemy row without changing unspecified columns?

Question

I have a table with 3 rows like this:

| serverID | behavior  | limit  |
---------------------------------
| 102      | action1   | 5      |
| 103      | action2   | 8      |

I have this function here:

def set_warn_settings(serverID, behavior, limit):
with INSERTION_LOCK:
    try:
        row = SESSION.query(WarnSettings).filter(WarnSettings.serverID == serverID).first()
        if row:
            row.behavior = behavior
            row.limit = limit

            SESSION.add(row)
            SESSION.commit()
        else:
            action = WarnSettings(serverID, behavior, limit)

            SESSION.add(action)
            SESSION.commit()
    finally:
        SESSION.close()

When I call the function and one of the arguments is None, the column's value is replaced with None instead of leaving it like was before. The function is sometimes called with only one of the arguments having something else than None.

How can I update a certain column's value while leaving the rest untouched, all from one function?

`row.behavior = behavior if behavior is not None else row.behavior` — Stephen Rauch, Jul 29 '18 at 05:41

score 0 · Accepted Answer · answered Jul 29 '18 at 05:44

So it depends on what your requirements are...but you should check the value of behavior and limit before setting:

if behavior is not None:
    row.behavior = behavior
if limit is not None:
    row.limit = limit

However, you might consider that there will be times when you WANT to be able to set those fields to None. In that case you could set your parameters to a specific object by default. Example:

no_value = object()
def set_warn_settings(serverID, behavior=no_value, limit=no_value):

    ...
    if behavior is not no_value:
        row.behavior = behavior
    if limit is not no_value: 
        row.limit = limit
    ...

score 0 · Answer 2 · answered Jul 29 '18 at 09:39

One way you could handle this would be to use the **kwargs construct in your function signature. Then instead of using positional arguments when calling your function, call it with only the keyword arguments of the attributes that you wish to update:

def set_warn_settings(serverID, **kwargs):
    with INSERTION_LOCK:
        try:
            row = SESSION.query(WarnSettings).get(serverID)
            if row:
                for k, v in kwargs.items():
                    setattr(row, k, v)
                # i removed SESSION.add(row) from here as you don't need to 
                # re-add an instance that was retrieved via Session.query.
                SESSION.commit()
            else:
                action = WarnSettings(**kwargs)
                SESSION.add(action)
                SESSION.commit()
        finally:
            SESSION.close()

This will only update the values of the attribute names that you pass as keyword arguments to the function if the row exists, and will create a new WarnSettings instance with the passed in values if a row doesn't already exist. It will also not overwrite values of attributes that you don't want to have set to None but will also allow you to explicitly set a value to None if you need to.

Usage examples based on your example data:

set_warn_settings(102, behavior='action2')  # will change value of 'behavior' and not change 'limit'.
set_warn_settings(103, behavior='action1', limit=10)  # will change both fields
set_warn_settings(104, behavior='action3', limit=3)  # will create a new instance

Note I also used the .get() method in the function which is a neat way to query by primary key and return None if a row doesn't exist.

How do I update certain columns of an SQLAlchemy row without changing unspecified columns?

2 Answers2