Problem with Grouping under ExportToDatabase



Hi all,

So I have been working with both CellProfiler and Cell Profiler Analyst. I was wondering if there was a way to group my images before I ran them through CPA so that I can tell which cells were treated with drugs and which cells were not treated. It’s hard to manually bin my images since the phenotypic differences are so subtle. I think the correct way about doing this is “Do you want to add groups” question under ExportToDatabase in Cell Profiler. However, in CPA I received this error. Any advice? Thanks!

An error occurred in the program:
DBException: ERROR: Group query failed for group “control”. Check the SQL syntax in your properties file.
Error was: "ERROR: Database query failed for connection “MainThread” and failed to reconnect
Query was: "SELECT Image_Metadata_Column_02 FROM MyExpt_Per_Image"
First exception was: no such column: Image_Metadata_Column_02
Second exception was: ERROR: Database query failed for connection "MainThread"
Query was: "SELECT Image_Metadata_Column_02 FROM MyExpt_Per_Image"
Exception was: no such column: Image_Metadata_Column_02


Traceback (most recent call last):
File “classifier.pyc”, line 581, in OnFetch
File “datamodel.pyc”, line 117, in GetRandomObjects
File “datamodel.pyc”, line 83, in _if_empty_populate
File “datamodel.pyc”, line 70, in PopulateModel
File “dbconnect.pyc”, line 683, in GetGroupMaps
File “dbconnect.pyc”, line 713, in group_map



The ExportToDatabase Add Groups setting simply makes the process of editing your CP Analyst properties file easier or possibly even unnecessary. So at this point, look at your properties file within the Groups section. Within that, there is apparently reference to “Image_Metadata_Column_02”, however if you view your database Per_Image table, apparently this column does not exist.

If the above pointers do not help, would you mind zipping and posting you properties file?



Below is my properties file. If there’s anything I need to change please let me know. Thanks!

#Tue Jul 28 17:33:26 2015


CellProfiler Analyst 2.0 properties file


==== Database Info ====

db_type = sqlite
db_sqlite_file = C:\Users\bs697\CtrlvsCytoD11(2).db

==== Database Tables ====

image_table = MyExpt_Per_Image
object_table = MyExpt_Per_Object

==== Database Columns ====

Specify the database column names that contain unique IDs for images and

objects (and optionally tables).

table_id (OPTIONAL): This field lets 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.

image_id: must be a foreign key column between your per-image and per-object


object_id: the object key column from your per-object table

image_id = ImageNumber
object_id = ObjectNumber
plate_id =
well_id =

Also specify the column names that contain X and Y coordinates for each

object within an image.

cell_x_loc = Cytoplasm_Location_Center_X
cell_y_loc = Cytoplasm_Location_Center_Y

==== Image Path and File Name Columns ====

Classifier needs to know where to find the images from your experiment.

Specify the column names from your per-image table that contain the image

paths and file names here.

Individual image files are expected to be monochromatic and represent a single

channel. However, any number of images may be combined by adding a new channel

path and filename column to the per-image table of your database and then

adding those column names here.

NOTE: These lists must have equal length!

image_path_cols = Image_PathName_Actin,Image_PathName_DNA
image_file_cols = Image_FileName_Actin,Image_FileName_DNA

CPA will now read image thumbnails directly from the database, if chosen in ExportToDatabase.

image_thumbnail_cols =

Give short names for each of the channels (respectively)…

image_names = Actin,DNA

Specify a default color for each of the channels (respectively)

Valid colors are: [red, green, blue, magenta, cyan, yellow, gray, none]

image_channel_colors =

==== Image Accesss Info ====

image_url_prepend =

==== Dynamic Groups ====

Here you can define groupings to choose from when classifier scores your experiment. (eg: per-well)

This is OPTIONAL, you may leave "groups = ".


group_XXX = MySQL select statement that returns image-keys and group-keys. This will be associated with the group name “XXX” from above.


groups = Well, Gene, Well+Gene,

group_SQL_Well = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Per_Image_Table.well FROM Per_Image_Table

group_SQL_Gene = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Well_ID_Table.gene FROM Per_Image_Table, Well_ID_Table WHERE Per_Image_Table.well=Well_ID_Table.well

group_SQL_Well+Gene = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Well_ID_Table.well, Well_ID_Table.gene FROM Per_Image_Table, Well_ID_Table WHERE Per_Image_Table.well=Well_ID_Table.well

group_SQL_control = SELECT Image_Metadata_Column_02 FROM MyExpt_Per_Image
group_SQL_expt = SELECT Image_Metadata_Column_03 FROM MyExpt_Per_Image

==== Image Filters ====

Here you can define image filters to let you select objects from a subset of your experiment when training the classifier.


filter_SQL_XXX = MySQL select statement that returns image keys you wish to filter out. This will be associated with the filter name “XXX” from above.


filters = EMPTY, CDKs,

filter_SQL_EMPTY = SELECT TableNumber, ImageNumber FROM CPA_per_image, Well_ID_Table WHERE CPA_per_image.well=Well_ID_Table.well AND Well_ID_Table.Gene=“EMPTY”

filter_SQL_CDKs = SELECT TableNumber, ImageNumber FROM CPA_per_image, Well_ID_Table WHERE CPA_per_image.well=Well_ID_Table.well AND Well_ID_Table.Gene REGEXP ‘CDK.*’

==== Meta data ====

What are your objects called?


object_name = singular object name, plural object name,

object_name = cell, cells,

What size plates were used? 96, 384 or 5600? This is for use in the PlateViewer. Leave blank if none

plate_type =

==== Excluded Columns ====


Classifier uses columns in your per_object table to find rules. It will

automatically ignore ID columns defined in table_id, image_id, and object_id

as well as any columns that contain non-numeric data.

Here you may list other columns in your per_object table that you wish the

classifier to ignore when finding rules.

You may also use regular expressions here to match more general column names.

Example: classifier_ignore_columns = WellID, Meta_., ._Position

This will ignore any column named “WellID”, any columns that start with

“Meta_”, and any columns that end in “_Position”.

A more restrictive example:

classifier_ignore_columns = ImageNumber, ObjectNumber, .Parent., .Children., .Location_Center.,.Metadata.

classifier_ignore_columns = table_number_key_column, image_number_key_column, object_number_key_column

==== Other ====

Specify the approximate diameter of your objects in pixels here.

image_tile_size = 50

======== Auto Load Training Set ========


You may enter the full path to a training set that you would like Classifier

to automatically load when started.

training_set =

======== Area Based Scoring ========


You may specify a column in your per-object table which will be summed and

reported in place of object-counts when scoring. The typical use for this

is to report the areas of objects on a per-image or per-group basis.

area_scoring_column =

======== Output Per-Object Classes ========


Here you can specify a MySQL table in your Database where you would like

Classifier to write out class information for each object in the


class_table =

======== Check Tables ========


[yes/no] You can ask classifier to check your tables for anomalies such

as orphaned objects or missing column indices. Default is on.

This check is run when Classifier starts and may take up to a minute if

your object_table is extremely large.

check_tables = yes


I was able to find the groups section within the properties file. However, what do you mean by database Per_Image table? Where can I find this?


In your case the data are contained in a file called “C:\Users\bs697\CtrlvsCytoD11(2).db” and within that are database tables named [Something]Per_Image and [Something]Per_Object. Use a free tool like sqlite browser (Mac) or sqlyog (Windows) to view this. Within the Per_Image table, you’ll find the column names, and I suspect that you’ll find that there is no column named “Image_Metadata_Column_02” or “Image_Metadata_Column_03” even though you have defined Groups in your properties file with these names. I would inspect the Per_Image table, find the proper metadata column names, and substitute them into your Groups settings in the prop file.