Updating Data

Use update() to modify existing rows. You pass the table name, the new values as a dictionary, and the WHERE condition. The statement is committed for you.

db.update(table, data, condition)

condition is required

All three arguments are positional and required. db.update('settings', {'mode': 0}) raises TypeError: update() missing 1 required positional argument: 'condition'. To update every row, pass a condition that is always true, such as "1=1".

update() does not work on Python 3.10 or newer

Unlike select() and delete(), this method reaches collections.Iterable on every call — the check runs against the data dictionary before it ever looks at the condition. On Python 3.10+ every call raises AttributeError: module 'collections' has no attribute 'Iterable', whatever you pass. Use execute() with a raw UPDATE statement instead. See Limitations.

Parameters

Parameter	Type	Description
`table`	str	Name of the table
`data`	dict	Column-value pairs to set. Values are quoted as strings.
`condition`	dict or str	Required. Dict keys are joined with `AND`; a string is injected verbatim.

Update with a dictionary condition

When condition is a dict, all keys are joined with AND:

# UPDATE products SET price='899.99', stock='45' WHERE id='1';
db.update('products', {
    'price': 899.99,
    'stock': 45,
}, {
    'id': 1,
})

Update with a SQL string condition

A string condition is passed straight through, so operators other than AND work:

# Mark all pending orders older than 7 days as expired
db.update('orders',
    {'status': 'expired'},
    "status = 'pending' AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)"
)

Update every row

There is no way to omit the condition, so use a tautology when you really mean every row:

# UPDATE settings SET maintenance_mode='0' WHERE 1=1;
db.update('settings', {'maintenance_mode': 0}, "1=1")

Generated SQL

# dict condition:
db.update('products', {'price': 50}, {'category': 'sale', 'active': 1})
# → UPDATE products SET price='50' WHERE category='sale' AND active='1';

# string condition:
db.update('products', {'price': 50}, "category='sale' OR category='clearance'")
# → UPDATE products SET price='50' WHERE category='sale' OR category='clearance';

Return value

update() returns None. To find out how many rows were affected, read count() immediately afterwards:

db.update('products', {'price': 50}, {'category': 'sale'})
print(db.count(), "rows updated")

Values are not escaped

Both data and a dict condition are concatenated into the SQL string without escaping. An apostrophe in a value breaks the statement, and user input reaching either argument is a SQL injection vector. See Limitations.

Next step

Delete records →