How to handle optional arguments when updating rows in Python's sqlite3 module?

Question

I'm trying to create a method to update rows in a sqlite database. I would like to use Python's sqlite3 module to achieve this. My function looks something like this:

import sqlite3

def update(first_name=None, last_name=None, email_address=None, password=None):
    if not email_address:
        raise TypeError("Required keyword argument email_address is missing!")
    conn = sqlite3.connect("users.db")
    c = conn.cursor()
    c.execute(
    """
    UPDATE users
    SET first_name = ?, last_name = ?, password = ?
    WHERE email_address = ?
    """
    )
    conn.commit()
    conn.close()
    return

'email_address' is the unique identifier in this case and is therefore required. The other arguments should be optional.

At this moment, the fields in the database will be overwritten with "NULL" if the user doesn't pass in all arguments, because the keyword arguments default to 'None'. Obviously this implementation is not useful when the user only wants to update only one or a few fields. Especially so if the database includes more fields (this is just an example).

A solution would be to change the SET part of the query depending on whether the keyword argument is 'True' or 'False'. For example, if only 'first_name', 'last_name' and 'email_address' are passed as keyword arguments:

UPDATE users
SET first_name = ?,
    last_name = ?
WHERE email_address = ?

And if only 'first name' and 'email_address' is are passed as keyword arguments:

UPDATE users
SET first_name = ?
WHERE email_address = ?

How can I handle these optional arguments in the query that's passed into the execute() method? sqlite3 seems to be very inflexible because it's string-based. I have considered building a string constructor, but this seems to complex. I hope there's a more elegant way to solve this.

I would put email_address (ALWAYS required) as 1st argument in list. The accept a dictionary where the dict would have zero or more keyed entries. For example {'first_name':'Frank'} which you could then extract by getting dict's keys and converting to key = value string, which you would then append to your SET statement. — Scott
– Scott, Commented Mar 5, 2020 at 3:20

Josh · Accepted Answer · 2020-04-16 07:46:20Z

Ran into a similar problem myself. Here is what I came up with:

import sqlite3

def update(email, params):
    fields_to_set = []
    field_values = []
    # loop thru the params passed
    for key in params:
        # build up the SET statement using the param keys
        fields_to_set.append(key + "=?")
        # save the values to be used with the SET statement
        field_values.append(params[key])
    # join the SET statement together
    set_statement = ", ".join(fields_to_set)
    field_values.append(email)
    cmd = "UPDATE users SET "+set_statement+" WHERE email_address=?"
    conn = sqlite3.connect("users.db")
    c = conn.cursor()
    c.execute(cmd, field_values)
    conn.commit()
    conn.close()

# now you can pass just the parameters you want to update
update("[email protected]", {"first_name": "John", "last_name": "Doe"})
update("[email protected]", {"password": "john doe's password"})

NOTE: this requires that your params keys are identical to your field names

Collectives™ on Stack Overflow

How to handle optional arguments when updating rows in Python's sqlite3 module?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related