Exporting to SQLite and overwriting Presults



Hi all,

Just wanted to share some problems we’ve experienced using Export to Database and some issues with teh way this is logically done.


  • We have a user with many images in multiple folders from many experiments

  • We had set up a pipeline which is quite robust and works on his images very well

  • The output sought is simply an area meaurement of the secondary objects identified

  • As the user has many images and will run many more, we had hoped that he could simply append the results to the existing SQLite database

We had expected the last point to work as you offer the option to either DROP the tables or “Overwrite as necessary”

Knowing that when we load images we have the following, quoted from another post here

Each folder is unique and the image name is also unique in our case so we assumed that this uniqueness was used in the database as well.

We found out that by doing several runs, Cell Profiler overwrites the results in the Per_Object and Per_Image tables because the only thing taken into account there is the ImageID and Object IDs as unique columns and these always reset for each new run…

Moreover, using anything to identify the current run other than append a prefix to the tables (Which makes extraction all the more tedious) nothing else works.

Experiment name does nothing:
Checking the database after two runs shows that it did not update or append another experiment name to the corresponding database table. Why would this be included if it does not help with multiple experiments in the same database?

What would make sense is to have an option to APPEND the results to the table, or if you are going to proceed to overwriting, use the same uniqueness criteria as you do for the groups when looking for metadata tags. If you append, the ImageID and ObjectIDs should start at the last object and not be overwritten. Or some combination of unique experiment ID and imageId…

Otherwise we have to create a DB file for each run, which kind of kills the purpose of a database.

Please let me know if we are doing something wrong or what you do to append data to an existing database, so that we can correct this error as soon as possible.

Thank you and best regards.



Alas, I agree that an APPEND option in ExportToDatabase would be nice. But you are correct in that it does not exist as yet. We do have a Github issue for this feature addition. As noted in the Github issue, the measurements (i.e. database table column names) would have to be identical for each run, and so it is not a simple blind append, but it would certainly be doable for us to check.

We have two suggestions as workarounds:
(1) Write new tables (yes, with different Table Prefixes). Then with a python script we have written, you can select tables to re-write as a “master” table. No, this is not very “database-y” and we realize that it is a hacky workaround, but it has worked for us.
(2) Use SQL VIEWs. Again, you’d have to add to the VIEW with each new table, but it has also worked ok for us. It is my understanding (last I checked) that VIEWs can have performance issues because they don’t always respect database indices, but it may suffice for you.

Hope that helps, and we’ll reassess whether we ought to bump up the priority of the relevant Github issue.



I just was looking to into the same thing and found this thread. Is there any update to this topic? I saw that the Github issue is closed by now.



Can you explain your use case and workflow a bit more to help us understand what might be going on and how best to help you? Thanks!


I run my CellProfiler pipeline and use the “exportToDatabase” module to get a SQlite database and a CellProfilerAnalyst properties file to explore my results using CellProfilerAnalyst. This works perfectly fine!

Now I run the same pipeline on another set of images and receive a new database file.

My question is, is there a way to “append” the database files so I can use CellProfilerAnalyst to explore the accumulated results?

I have tried the python script from @David_Logan but unfortunately did not get it to run


From what I can find searching the forum, that python script only works on MySQL, not SQLite databases.

In terms of the larger problem, if you google sqlite concatenate tables you’ll certainly find there are ways to do that; the problem being that presumably the table from your first run has ImageNumbers ranging from 1…X and the second has ImageNumbers ranging 1…Y, when what you’d really want if you are going to have them analyzed together is 1…X then X+1…X+Y. This seems surmountable (make sure to do it on all the object tables too though, not just the image table!), but I’ve never personally tried anything like this before so I can’t vouch for the results.


Ok, sounds reasonable :slight_smile: 'll try to write a little script that concatenates the sqlite tables and increments the ImageNumbers. Thanks for your help!

CPA TypeError when plotting classifier results table

Please come back and let us know how it turns out! Good luck with it :slight_smile: