# SQLite3 query in Python returns less data than what appears in the Slicer DICOM module

**URL:** <https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058>\
**Category:** Development\
**Tags:** dicom\
**Created:** [June 15, 2023, 6:29pm UTC](https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058 "2023-06-15T18:29:56Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![smsmt](https://sea2.discourse-cdn.com/flex002/user_avatar/discourse.slicer.org/smsmt/32/66423_2.png) [@smsmt](https://discourse.slicer.org/u/smsmt)\
**Post date:** [June 15, 2023, 6:29pm UTC](https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058/1 "2023-06-15T18:29:56Z")

</div>

Hello everyone,

I am currently working on constructing a database of DICOM tags using 3D Slicer. I have imported DICOM image data of approximately 700 patients into the Slicer, which seems to have successfully processed all the files as the DICOM tags within the Slicer UI show data for all the patients.

To extract this data, I used the “ctkDICOM.sql” file that Slicer generated. This file is around 2GB in size, with an additional 20GB cache SQL file. When I attempt to parse the “ctkDICOM.sql” file using the sqlite3 module in Python, however, I find that data for around 200 patients seems to be missing.

Despite this, there doesn’t appear to be an issue with the original DICOM data, as all patient data is correctly displayed in the Slicer UI. I have double-checked the DICOM files, and they don’t seem to be the problem.

I was wondering if anyone could provide some guidance on this. Specifically, my goal is to generate a single SQL file using Slicer that contains all the DICOM tag information for these patients.

Any help or insights would be greatly appreciated!

Thank you in advance.

---

<div class="post-metadata">

**Author:** ![pieper](https://sea2.discourse-cdn.com/flex002/user_avatar/discourse.slicer.org/pieper/32/8_2.png) [@pieper](https://discourse.slicer.org/u/pieper)\
**Post date:** [June 15, 2023, 8:26pm UTC](https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058/2 "2023-06-15T20:26:29Z")

</div>

The dicom database is managed by [the CTK code](https://github.com/commontk/CTK/tree/master/Libs/DICOM), including the mapping from what’s in the database to what’s displayed on the screen, as generally described here:

> <https://github.com/commontk/CTK/blob/master/Libs/DICOM/Core/ctkDICOMDisplayedFieldGenerator.h#L34-L49>

That should explain how the fields are generated. But there’s no reason 200 patients should be missing from the database if they are displayed on the GUI (they should be the same). Maybe try recreating the issue, perhaps with public data, and if you can let us know the steps and hopefully someone can help you troubleshoot.

---

<div class="post-metadata">

**Author:** ![lassoan](https://sea2.discourse-cdn.com/flex002/user_avatar/discourse.slicer.org/lassoan/32/13_2.png) [@lassoan](https://discourse.slicer.org/u/lassoan)\
**Post date:** [June 16, 2023, 1:32pm UTC](https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058/3 "2023-06-16T13:32:34Z")

</div>

[SQlite3 sets default limits for query results](https://www.sqlite.org/limits.html). It seems that with your select query you reached the default limit of 500 records. You can use the [`LIMIT` clause](https://www.beekeeperstudio.io/blog/sqlite-limit) in your query to get all the records.

---

<div class="post-metadata">

**Author:** ![smsmt](https://sea2.discourse-cdn.com/flex002/user_avatar/discourse.slicer.org/smsmt/32/66423_2.png) [@smsmt](https://discourse.slicer.org/u/smsmt)\
**Post date:** [June 16, 2023, 6:16pm UTC](https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058/4 "2023-06-16T18:16:43Z")

</div>

Thanks Andras and Pieper for your thoughts. I’ve tried a few things already:

1. I used a “LIMIT” command to see if there’s an issue with sqlite3, but that didn’t help.
2. I also opened the ctkDICOM.sql file using a tool called DB browser, but I still saw the same problem.

I noticed that 200 patients are missing, but they’re not just at the end of the list. They’re missing randomly when I try to look at the data using the DB browser or Python. But, I can see them when I use the Slicer UI.  
Also, some data shows as ‘None’ when I look at it in the DB browser or Python, but it’s there when I use the Slicer UI.

So, my guess is that Slicer might be using cache file called ctkDICOMTagCache.sql to fill in the missing data and patients. That might be how Slicer manages to show all the data.

Now, I’m going to try moving the data to a new computer. I want to see if I still have the same problems with the ctkDICOM.sql generated file by Slicer and the missing data.

---

<div class="post-metadata">

**Author:** ![lassoan](https://sea2.discourse-cdn.com/flex002/user_avatar/discourse.slicer.org/lassoan/32/13_2.png) [@lassoan](https://discourse.slicer.org/u/lassoan)\
**Post date:** [June 16, 2023, 6:58pm UTC](https://discourse.slicer.org/t/sqlite3-query-in-python-returns-less-data-than-what-appears-in-the-slicer-dicom-module/30058/5 "2023-06-16T18:58:23Z")

</div>

You can ignore the tag cache file, that just stores some fields for faster access. Only `ctkDICOM.sql` content matters. You can find the list of patients in `Patients` table.

Note that none of these files are part of the public Slicer API. If you can find a way to extract useful information from the sqlite files then that is fine for us, but we do not support this (because that would impose many limitations on how we can evolve the internal design in the future). The public API for the Slicer DICOM database is the [ctkDICOMDatabase](https://github.com/commontk/CTK/blob/master/Libs/DICOM/Core/ctkDICOMDatabase.h) object that is accessible in Slicer Python environment as `slicer.dicomDatabase`.