Per_Object table error

Hi, I am using Cell Profiler 2.0 to analyse a collection of images and create a database (SQLite) and property file for CPA 2.0 using the ExporttoDatabase module.
I have various issues.
First the CSV files generated don’t have the same name that in the property file. This does not seem to be a problem for the Image table but the Per_Object table fail to load, even changing the name to match. I get the following error:

=========================================================
An error occurred in the program:
DBException: ERROR: Database query failed for connection "MainThread"
SELECT * FROM Per_Object LIMIT 1
no such table: Per_Object

Traceback (most recent call last):
File “plateviewer.pyc”, line 356, in UpdateMeasurementChoice
File “plateviewer.pyc”, line 751, in get_non_blob_types_from_table
File “dbconnect.pyc”, line 760, in GetColumnNames
File “dbconnect.pyc”, line 64, in fn
File “dbconnect.pyc”, line 505, in execute

============================================================
Also I am only able to see some measurements from the ones I am acquiring with Cell profiler altough they are all in the tables.

I have used CPA succesfully in the past and I was wondering if there are any changes in the latest release that could cause this or there is something in the export to database that I am missing.

Thanks.

I’m going to need some more info here. There isn’t anything new in CPA that I’m aware of that would cause such a problem. Off the top of my head, did you make sure the case of the tablename is right (ie: per_object vs Per_Object) in your props file?

What CPA version are you running?
Can you paste your properties file?
When you say you can only see some of the measurements, where do you see them? How did you verify that they are in the tables?
Can you verify the table names in your active database in CPA? (open tableviewer and choose file > “open from database” to get a list)

Another possible explanation that just came to mind is that CPA may have created the SQLite database when you had the wrong tablename entered and simply didn’t import the per_object table. At the moment I’m not sure if changing the tablenames in the properties file would cause CPA to recreate the database. It should, and I think it does, but if it doesn’t then you’d have delete the db file by hand and try running CPA again. You can find this file by looking in the terminal output of CPA once you’ve fetched some data. Run CPA, open scatterplot and click “update”… go back to the CPA output window and scroll up, it should say something like this:

Logging level: DEBUG Skipping table checking step for sqlite [MainThread] Connecting to the database... [MainThread] SQLite file: /Users/afraser/CPA/CPA_DB_24dbc308f2e6982bde27efa88f46ca03.db ...

Yes, but actually I had to change them as they did not match when coming out of the cell profiler (In cell profiler the csv. files where named Images and Cells)

I am running CPA 2.0 for Windows (r11306). I used it in a 64x computer.

My properties file is:

==============================================

CellProfiler Analyst 2.0 properties file

==============================================

==== Database Info ====

db_type = sqlite
db_sqlite_file = C:\Users\fvega\Desktop\Cell ProfilerProcessing Images\OUT\dabase\testHomeDB.db

==== Database Tables ====

image_table = ExptTPA_Per_Image
object_table = ExptTPA_Per_Object

#image_csv_file = C:\Users\fvega\Desktop\Cell ProfilerProcessing Images\OUT\dabase\Image.csv
#object_csv_file = C:\Users\fvega\Desktop\Cell ProfilerProcessing Images\OUT\dabase\Object.csv

==== 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

tables

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

image_id = ImageNumber
object_id = ObjectNumber
plate_id = Image_Metadata_Plate
well_id = Image_Metadata_Well

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

object within an image.

cell_x_loc = Nuclei_Location_Center_X
cell_y_loc = Nuclei_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_OrigTubulin,Image_PathName_OrigActin,Image_PathName_OrigDAPI
image_file_cols = Image_FileName_OrigTubulin,Image_FileName_OrigActin,Image_FileName_OrigDAPI

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 = Tubulin,Actin,DAPI

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

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

image_channel_colors = green, red, blue

==== 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 = ".

FORMAT:

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

EXAMPLE GROUPS:

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_ByWell = SELECT ImageNumber, Image_Metadata_Plate, Image_Metadata_Well FROM ExptTPA_Per_Image
group_SQL_ByExperiment = SELECT ImageNumber, Image_Metadata_experiment, Image_Metadata_Plate, Image_Metadata_Well FROM ExptTPA_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.

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

FORMAT:

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.

EXAMPLE FILTERS:

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?

FORMAT:

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 = 384

==== Excluded Columns ====

OPTIONAL

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, Metadata_., Location_., Neighbors_., Number_.,

==== Other ====

Specify the approximate diameter of your objects in pixels here.

image_tile_size = 100

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

OPTIONAL

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 ========

OPTIONAL

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 ========

OPTIONAL

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

object_table

class_table =

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

OPTIONAL

[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

========================================================================================

They are on the tables when I open the csv files with excell for example, but I can not display them all in any tool in CPA like plate viewer or dotplot.

I will try your advise. I am using a Exporttospreadsheet module before the Exporttodatabase one in CP. Could this create problems with the csv files generated and how they are read by the DB?

This is likely confusing things quite a bit.

Let me clarify something first:
The lines below tell me that ExportToDatabase was probably configured to write out an SQLite database directly. These two lines alone should be sufficient to connect CPA to your database:

db_type = sqlite db_sqlite_file = C:\Users\fvega\Desktop\Cell ProfilerProcessing Images\OUT\dabase\testHomeDB.db
The lines below are commented out so CPA doesn’t pay attention to them… still it sounds like you changed them and like you think that CPA is reading in CSV files. In any case, they should probably be removed completely. If these files were generated by ExportToSpreadsheet, CPA probably won’t be able to read them without tweaking anyway.

#image_csv_file = C:\Users\fvega\Desktop\Cell ProfilerProcessing Images\OUT\dabase\Image.csv #object_csv_file = C:\Users\fvega\Desktop\Cell ProfilerProcessing Images\OUT\dabase\Object.csv
You may want to post your ExportToSpreadsheet and ExportToDatabase configurations to the CP Help forum to get some advice on whether there’s a simpler way to do what you want.

Finally it looks like using a ExporttoSpreadsheet and ExporttoDatabase modules at the same time was causing the problem.

Thanks for the help!

Ah, I’m glad you were able to work it out. Indeed, using those two modules together is a recipe for confusion since their output is similar and redundant. In general, I think ExportToSpreadsheet should be used when you want to look at the data in Excel or some other csv-friendly application. ExportToDatabase is better if you plan on looking at your data in CPA, from there you can output CSVs of per_image, per_object and any results tables as you like.