-
-
Notifications
You must be signed in to change notification settings - Fork 111
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
.delete_where() does not auto-commit (unlike .insert() or .upsert()) #159
Comments
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. |
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??? I did just db commit it... But it's confusing. |
Yeah I'm going to class this as a bug - that's definitely confusing. |
Independent of the transaction changes in #121 I may be able to check |
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
|
@spdkils can you share a minimal code example that exhibits the behavior you're seeing? |
I confirm the bug. Happens for me in version 3.6. I use the call to delete all the records: I see that |
Just stumbled across this, wondering why none of my deletes were working. |
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. |
sqlite_utils won't commit changes when deleting records in `delete_channel`, might be something to do with the triggers added by full text search. Seems to be a known issue with the package - simonw/sqlite-utils#315 - simonw/sqlite-utils#159 It doesn't seem to have a problem effecting the rows within the function but the changes don't commit. ```python db = Database(db_name) cur = db.execute(f"DELETE FROM Channels WHERE channel_id = ?", [channel_id]) print(f"channels: {cur.rowcount}") cur = db.execute(f"DELETE FROM Subtitles WHERE video_id IN (SELECT video_id FROM Videos WHERE channel_id = ?)", [channel_id]) print(f"subs: {cur.rowcount}") cur = db.execute(f"DELETE FROM Videos WHERE channel_id = ?", [channel_id]) print(f"vids: {cur.rowcount}") # check if effectively deleted cur = db.execute(f"SELECT * FROM Channels WHERE channel_id = ?", [channel_id]) print("channels after: ", cur, cur.fetchall()) cur = db.execute(f"SELECT * FROM Videos WHERE channel_id = ?", [channel_id]) print("Vids after", cur, cur.fetchall()) cur = db.execute(f"SELECT * FROM Videos WHERE channel_id = ?", [channel_id]) ``` other thing I tried: ```python db = Database(db_name) db["Channels"].delete_where("channel_id = ?", [channel_id]) db["Subtitles"].delete_where("video_id IN (SELECT video_id FROM Videos WHERE channel_id = ?)", [channel_id]) db["Videos"].delete_where("channel_id = ?", [channel_id]) ``` Anyway there doesn't seem to be any issues with using python sqlite3 for the delete operations
Example to show the bug still exists in Sqlite-utils v3.36 Create test datatasetsqlite-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 rowsimport 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
>>> |
Documented the use of delete_where, as shown in simonw#159
Documented the use of delete_where, as shown in #159
When you use the delete_where() function on a table, it never commits....
Is that intentional?
The text was updated successfully, but these errors were encountered: