# Dropping dataset from code doesn't properly delete examples **URL:** \ **Category:** Uncategorized\ **Tags:** done, database\ **Created:** [March 25, 2018, 4:48pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437 "2018-03-25T16:48:01Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![daniel](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@daniel](https://support.prodi.gy/u/daniel)\ **Post date:** [March 25, 2018, 4:48pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/1 "2018-03-25T16:48:01Z")
There is an unexpected behavior when adding/dropping datasets and adding samples from code. It seems like Prodigy doesn’t properly remove/unlink examples. Consider the following code: ``` import prodigy from prodigy.components.db import connect from prodigy.util import set_hashes seed_examples = [ {"text": "Example 1"}, {"text": "Example 2"}, ] seed_examples = [set_hashes(eg) for eg in seed_examples] db = connect() # add dataset and samples dataset = db.add_dataset("first_dataset") db.add_examples(seed_examples, ["first_dataset"]) # there should be 2 samples in first dataset examples = db.get_dataset("first_dataset") print("Examples in first dataset: {}".format(len(examples))) # drop the first dataset db.drop_dataset("first_dataset") # add second dataset dataset = db.add_dataset("second_dataset") # there should be no samples in second dataset examples = db.get_dataset("second_dataset") print("Examples in second dataset: {}".format(len(examples))) # add 2 samples db.add_examples(seed_examples, ["second_dataset"]) # there should be 2 samples in second dataset examples = db.get_dataset("second_dataset") print("Examples in second dataset: {}".format(len(examples))) ``` Running this script on a brand new prodigy database prints: ``` Examples in first dataset: 2 Examples in second dataset: 1 Examples in second dataset: 3 ``` Running it again: ``` Examples in first dataset: 2 Examples in second dataset: 2 Examples in second dataset: 4 ``` And third time: ``` Examples in first dataset: 4 Examples in second dataset: 4 Examples in second dataset: 6 ``` The expected behavior is that when dropping a dataset, it should unlink/delete all the samples associated with that dataset. Or am I missing something? When dropping dataset with the `drop` recipe, Prodigy also doesn’t delete the samples and links (it only deletes the first record) but `stats` reports an empty dataset (I guess due to session ids). But I noticed that it sometimes can lead to incorrect sample count as well (can’t reproduce it consistently). By design, should the drop function/recipe remove all records of that dataset and its sessions from the database (that naive expected behavior is that it should)? Thanks. ---
**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\ **Post date:** [March 26, 2018, 3:12pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/2 "2018-03-26T15:12:29Z")
Thanks a lot for the report! We just released `v1.4.1`, which fixes a problem with the hashing that would sometimes return incorrect results when fetching all hashes for a dataset. A similar issue has been reported [on this thread](https://support.prodi.gy/t/old-examples-are-automatically-added-to-new-dataset/222) and I suspect the hashing problem might have been the cause of that. It’s also likely that the incorrect sample count you’ve come across had something to do with this. Re deleting / unlinking examples: Because all examples are added to both the regular dataset and session dataset they’re always linked to at least two sets – potentially more, if you annotate the same examples more than once across sets. The way deleting and unlinking is currently handled isn’t 100% ideal, and we’re working on a better solution. ---
**Author:** ![daniel](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@daniel](https://support.prodi.gy/u/daniel)\ **Post date:** [March 26, 2018, 6:55pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/3 "2018-03-26T18:55:54Z")
Nope, after updating to v1.4.1 and running the code I posted above, it still gives the same incorrect results. Checking the database itself with SQL browser shows that the examples are not deleted, neither the links were removed from the links table. Each iteration adds duplicate examples with same input and task hashes. And since adding datasets reuses the same ids, the linked examples increase each time a dataset is being dropped/re-added. You can replicate it by running the code I posted. ---
**Author:** ![Superscope](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/superscope/32/682_2.png) [@Superscope](https://support.prodi.gy/u/Superscope)\ **Post date:** [March 5, 2019, 8:58am UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/4 "2019-03-05T08:58:51Z")
Hi there when I drop datasets, prodigy stats -ls still shows sessions. And when I inspect the database directly there are still tables present. i can purge the DB manually, but is there a way to do this from the command line? Hope this is the right thread for this. Thanks much. ---
**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\ **Post date:** [March 5, 2019, 9:19am UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/5 "2019-03-05T09:19:54Z")
@Superscope Internally, sessions are also datasets and the individual examples you’re annotating are linked to both a regular dataset and a session dataset (and potentially other datasets). If I remember correctly, Prodigy isn’t currently purging empty sessions automatically – but you should be able to delete them just like regular datasets, by using the session name (timestamp). Alternatively, you can also write a custom Python script that retrieves and checks the sessions and deletes them (e.g. if they’re empty or were created before a certain date etc.). Here’s an example (untested, but should work): ```python from prodigy.components.db import connect db = connect() # uses settings from your prodigy.json for session in db.sessions: # Remove session if it's empty – or implement your own # custom logic here examples = db.get_dataset(session) if not examples: db.drop_dataset(session) ``` For more details on the Python API, check out the `Database` docs in your `PRODIGY_README.html`. ---
**Author:** ![Superscope](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/superscope/32/682_2.png) [@Superscope](https://support.prodi.gy/u/Superscope)\ **Post date:** [March 5, 2019, 2:18pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/6 "2019-03-05T14:18:00Z")
understood…thanks ---
**Author:** ![Nick](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@Nick](https://support.prodi.gy/u/Nick)\ **Post date:** [October 23, 2019, 10:21am UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/7 "2019-10-23T10:21:00Z")
When I drop a dataset using 'prodigy drop' the examples, link and sessions aren't deleted from the database (MySQL). Or are those the 'empty sessions' you mentioned @ines ? For example: 1 - I create a new and empty database. 2 - _prodigy dataset test_ 3 - Then I annotate one example for 'test' 4 - _prodigy drop test_ 5 - The result: **DB \> dataset** ``` { "data": [ { "id": 2, "name": "2019-10-23_12-07-14", "created": 1571825234, "meta": "BLOB", "session": 1 } ] } ``` **DB \> example** ``` { "data": [ { "id": 1, "input_hash": 969126593, "task_hash": -946968246, "content": "BLOB" } ] } ``` **DB \> link** ``` { "data": [ { "id": 2, "example_id": 1, "dataset_id": 2 } ] } ``` ---
**Author:** ![geniki](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@geniki](https://support.prodi.gy/u/geniki)\ **Post date:** [May 28, 2020, 10:28pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/8 "2020-05-28T22:28:44Z")
Hello, I have a similar issue: 1. Create `dataset1` from a json file 2. Annotate a few examples 3. `prodigy drop dataset1` 4. Create `dataset1` again using the same command as in 1. expecting that annotation will start from the beginning but the examples annotated in 2. don't show up for annotation 5. If I create `dataset2` (any different name) from the same json file behaviour seems to be as expected - i.e. annotation starts from scratch. However, I'd like to use the `dataset1` name (to follow an existing naming convention). Am I missing something or indeed this has not been resolved yet? ---
**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\ **Post date:** [May 29, 2020, 10:00am UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/9 "2020-05-29T10:00:26Z")
@geniki Thanks for the report, that's strange! Are you using the latest version of Prodigy? And are you using the default SQLite database setup? Also, when you run `prodigy stats dataset1` _after_ recreating it, does it show the dataset as empty? ---
**Author:** ![geniki](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@geniki](https://support.prodi.gy/u/geniki)\ **Post date:** [May 29, 2020, 11:27am UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/10 "2020-05-29T11:27:16Z")
I'm using Prodigy 1.9.9 on Windows with the default database setup, named session (same name every time). When I run `prodigy stats dataset1` after re-creating, it shows 0 annotations and the right timestamp (i.e. most recent) for dataset creation. The recipe is custom, involves re-setting hashes (possibly incorrectly, could this be a reason?) and the input json file is the result of `db-out` after a first-pass annotation through the data using `ner.correct`. ---
**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\ **Post date:** [May 29, 2020, 1:35pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/11 "2020-05-29T13:35:24Z")
@geniki Thanks for the updates! As a quick sanity check, could you run the following after dropping `dataset1`: ```python from prodigy.components.db import connect db = connect() print(len(db.get_task_hashes("dataset1"))) ``` If this reports anything other than 0, it means that for some (very confusing) reason, Prodigy still thinks there are hashes associated with that dataset, which would cause it to skip examples with those hashes. In that case, you could set `"auto_exclude_current": false` temporarily in your config until you're re-annotated all the examples it skipped. It will basically disable skipping examples present in the current dataset, so Prodigy will send everything out again, even if it thinks it already has the hash in the data. If the above reports 0, it would mean that Prodiy's exclude logic knows there are no existing task hashes and then it's more likely that there's something in the recipe that causes the examples to be skipped. ---
**Author:** ![geniki](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@geniki](https://support.prodi.gy/u/geniki)\ **Post date:** [June 1, 2020, 9:47am UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/12 "2020-06-01T09:47:14Z")
Tried the above with no success. Looking at the verbose Prodigy logs, it seems that named sessions create their additional separate "dataset" in the DB. `prodigy drop dataset1` doesn't delete `dataset1-user1` and that's what Prodigy picks up when I "re-create" the dataset. So to actually delete a dataset, I need to 1) look up all associated "datasets" and 2) manually drop them one by one. Is this the general and desired behaviour? ---
**Author:** ![ines](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/ines/32/3_2.png) [@ines](https://support.prodi.gy/u/ines)\ **Post date:** [June 5, 2020, 7:00pm UTC](https://support.prodi.gy/t/dropping-dataset-from-code-doesnt-properly-delete-examples/437/13 "2020-06-05T19:00:25Z")
Update: > [@textcat.manual Duplicate Samples](https://support.prodi.gy/t/textcat-manual-duplicate-samples/2962/8): > > @cgreco thanks for the confirmation, I think I have a workaround for you. The trouble is that when you use named sessions, it creates the source dataset entries, and another "session" dataset of the form "[dataset\_name]-[session\_id]". So if you used a dataset "foo" and a session "bar" you would end up with examples in the datasets "foo" and "foo-bar". When you drop "foo" it leaves "foo-bar" behind. So in your case, you can work around the problem by dropping the named session datasets in addition to the regular one: > > ```python > prodigy drop my-dataset > prodigy drop my-dataset-justin > > ``` > [@textcat.manual Duplicate Samples](https://support.prodi.gy/t/textcat-manual-duplicate-samples/2962/10): > > > [@textcat.manual Duplicate Samples](https://support.prodi.gy/t/textcat-manual-duplicate-samples/2962/9): > > > > If this is the intended behavior it may be useful to make a note somewhere so that people know they have to clear out the session datasets as well. > > Good point, I'll add a note to the docs for now! We definitely want to have a clearer link between regular datasets and session datasets in the future so Prodigy can automatically remove "orphaned" session datasets – but this may require some changes to the database model.