row.update() or row.pk

[Gravitar64]

Hi,

fantastic framework for working with Sqlite3 databases!!!

I tried to update spezific rows in a table and used

for row in db[tablename]: newValue = row["counter"] * row["prize"]
row.update({"Fieldname": newValue}) print(row)

This updates the value in the printet row, but not in the database. So I switched to

db[tablename].update(id, {"Filedname": newValue})

This works fine. But row.update would be nicer, because no need for the id (its that row), no need for the tablename and the db (all defined in the for row ... loop).

Thx

[simonw]

The big challenge here is that the rows returned by this library aren't objects, they are Python dictionaries - so adding methods to them isn't possible without changing the type that is returned by these methods.

Part of the philosophy of the library is that it should make it as easy as possible to round-trip between Python dictionaries and SQLite table data, so I don't think adding methods like this is going to fit.

[simonw]

I did add a slightly clumsy mechanism recently to help a bit here though: the pks_and_rows_where() method: https://sqlite-utils.datasette.io/en/stable/python-api.html#listing-rows-with-their-primary-keys

More details in the issue for that feature: #240

The idea here is that if you want to call update you need the primary key for the row - so you can do this:

for pk, row in db["sometable"].pks_and_rows_where():
    db["sometable"].update(pk, {"modified": 1}")

The pk may end up as a single value or a tuple depending on if the table has a compound primary key - but you don't need to worry about that if you use this method as it will return the correct primary key value for you.

[simonw]

I'm re-opening this as a research task because it may be possible to cleanly implement this using a dict subclass - some notes on that here: https://treyhunner.com/2019/04/why-you-shouldnt-inherit-from-list-and-dict-in-python/

Since this would just be for adding methods (and maybe a property for returning the primary keys for a row) the usual disadvantages of subclassing dict described in that article shouldn't apply.

One catch: dictionaries already have a .update() method! So would have to pick another name.

[simonw]

If an.update() method doesn't work because it collides with an existing dictionary method a .pk property could still be nice:

for row in db["sometable"].rows:
    db["sometable"].update(row.pk, {"modified": 1})