.delete_where() does not auto-commit (unlike .insert() or .upsert())

[spdkils]

When you use the delete_where() function on a table, it never commits....

Is that intentional?

[simonw]

See #121 - I need to think harder about how this all interacts with transactions.

You can do this:

with db.conn:
    db["mytable"].delete_where()

But that should be documented and maybe rethought.

[spdkils]

I appreciate the response, it's just unexpected.

If I insert, it commits, if I update it commits, if I upsert it commits... if I delete.. it doesn't???
Confused me...

I did just db commit it... But it's confusing.

[simonw]

Yeah I'm going to class this as a bug - that's definitely confusing.

[simonw]

Independent of the transaction changes in #121 I may be able to check self.conn.in_transaction to see if a transaction is active and, if one is NOT active, execute the delete inside of one. https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.in_transaction

[simonw]

That's strange... this test here doesn't manually commit a transaction and passes:

sqlite-utils/tests/test_delete.py

Lines 17 to 23 in 7805d53

	def test_delete_where(fresh_db):
	table = fresh_db["table"]
	for i in range(1, 11):
	table.insert({"id": i}, pk="id")
	assert 10 == table.count
	table.delete_where("id > ?", [5])
	assert 5 == table.count

[simonw]

@spdkils can you share a minimal code example that exhibits the behavior you're seeing?

[limar]

I confirm the bug. Happens for me in version 3.6. I use the call to delete all the records:
table.delete_where()
This does not delete anything.

I see that delete() method DOES use context manager with self.db.conn: which should help. You may want to align the code of both methods.

[dracos]

Just stumbled across this, wondering why none of my deletes were working.

[dracos]

In a related issue, nearly a year later I just stumbled across this again, as I wondered why none of my rebuild-fts were rebuilding. It looks like: disable_fts in db.py commits; enable_fts partly commits except the last step (due to executescript committing a pending transaction); rebuild_fts won't commit unless manually done as above with e.g. a context manager.

[learning4life]

@spdkils can you share a minimal code example that exhibits the behavior you're seeing?

Example to show the bug still exists in Sqlite-utils v3.36

Create test datataset

sqlite-utils insert global.db power_plants 'global_power_plant_database.csv' --csv

table.delete_where() with no other arguments will not delete every row in the table.

import sqlite_utils
db = sqlite_utils.Database("global.db")
db["power_plants"].count_where("country > ?", [1])
db["power_plants"].delete_where()
db["power_plants"].count_where("country > ?", [1])

Counting rows shows not deleted rows

import sqlite_utils
db = sqlite_utils.Database("global.db")
db["power_plants"].count_where("country > ?", [1])

Results from running above code in bash using docker

[root@b5441fc4c92e DockerSqliteUtils]# sqlite-utils insert global.db power_plants 'global_power_plant_database.csv' --csv
  [###################################-]   99%  00:00:00
[root@b5441fc4c92e DockerSqliteUtils]# python3
Python 3.9.19 (main, May 20 2024, 05:19:58) 
[GCC 8.5.0 20210514 (Red Hat 8.5.0-22)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite_utils
>>> db = sqlite_utils.Database("global.db")
>>> db["power_plants"].count_where("country > ?", [1])
33643
>>> db["power_plants"].delete_where()
<Table power_plants (country, country_long, name, gppd_idnr, capacity_mw, latitude, longitude, primary_fuel, other_fuel1, other_fuel2, other_fuel3, commissioning_year, owner, source, url, geolocation_source, wepp_id, year_of_capacity_data, generation_gwh_2013, generation_gwh_2014, generation_gwh_2015, generation_gwh_2016, generation_gwh_2017, generation_data_source, estimated_generation_gwh)>
>>> db["power_plants"].count_where("country > ?", [1])
0
>>> exit()
[root@b5441fc4c92e DockerSqliteUtils]# python3
Python 3.9.19 (main, May 20 2024, 05:19:58) 
[GCC 8.5.0 20210514 (Red Hat 8.5.0-22)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite_utils
>>> db = sqlite_utils.Database("global.db")
>>> db["power_plants"].count_where("country > ?", [1])
33643
>>>