Append experiments to existing database?

Hi there,
first: thank you for all your help to a CP newbie! you have already helped me a lot!!
second, I ran into a “new” problem:
is it possible to aggregate further, subsequent experiments to an existing database (preferably SQlite)? I have done a bit of research but could not come up with a straight forward approach to do so…
If so, could you roughly outline the steps necessary to do so?

Thanks, Benedikt

In short, yes.

Please follow this example for firsthand experience.

Download these images…test images

And then run the following pipelines sequentially, using the images above as inputs.

sqllite_test1.cppipe (8.8 KB)
sqllite_test2.cppipe (8.8 KB)

You can use this sqlite viewer to see the results.

If you mean you’d like to append data to a database, then yes I like Kyle’s answer - just create new tables within the database. If instead you want to append to an existing table, then that is harder for sqlite. Please see this: Merge SQlite database

FYI, for concatenating MySQL tables, there is CreateMasterTableWizard here:
though I don’t know that anyone have tested it lately.

Hi David,
thank you for your suggestion to use MySQL instead. Sadly, I do run into another problem there:

Traceback (most recent call last):
File “cellprofiler\pipeline.pyc”, line 2094, in prepare_run
File “cellprofiler\modules\exporttodatabase.pyc”, line 1647, in prepare_run
File “cellprofiler\modules\exporttodatabase.pyc”, line 2199, in create_database_tables
File “cellprofiler\modules\exporttodatabase.pyc”, line 262, in execute
File “MySQLdb\cursors.pyc”, line 174, in execute
File “MySQLdb\connections.pyc”, line 36, in defaulterrorhandler
ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘field(200)))’ at line 7”)

In case I decide to continue the pipeline I do run into the following error message:

Traceback (most recent call last):
File “cellprofiler\pipeline.pyc”, line 1934, in run_image_set
File “cellprofiler\modules\exporttodatabase.pyc”, line 1754, in run
File “cellprofiler\modules\exporttodatabase.pyc”, line 2917, in write_data_to_db
File “cellprofiler\modules\exporttodatabase.pyc”, line 262, in execute
File “MySQLdb\cursors.pyc”, line 174, in execute
File “MySQLdb\connections.pyc”, line 36, in defaulterrorhandler
ProgrammingError: (1146, “Table ‘cellprofiler_db.Per_Experiment’ doesn’t exist”)

Database connection is fine and Per_Image and Per_Object tables are created. Have you ever had this problem in CP2.1?

Hi Guys-

I am also interested in this question. Following up:

Using karhohs example, I am now writing multiple tables into my SQLite database file. My pipeline is producing multiple properties files. If I wanted to be able to access multiple tables from a single CPA session, how should I set up my properties file?

In the properties file, the instructions suggest that table_id can be used to “let Classifier handle multiple tables if you merge them into one and add a table_number column as a foreign key to your per-image and per-object tables.”

Is this possible in the SQLite format? If so, could someone provide some instructions?