PlateViewer error: Database query failed for connection

plateviewer
exporttodatabase

#1

Hey guys!

I’m new to CPA and could use some help with what may be a simple problem. Upon trying to open PlateViewer, I get the following error message:

An error occurred in the program:
DBException: ERROR: Database query failed for connection “MainThread” and failed to reconnect
Query was: "SELECT * FROM my_tableimg LIMIT 1"
First exception was: no such table: my_tableimg
Second exception was: ERROR: Database query failed for connection "MainThread"
Query was: "SELECT * FROM my_tableimg LIMIT 1"
Exception was: no such table: my_tableimg

Traceback (most recent call last):
File “cpa.py”, line 266, in launch_plate_map_browser
File “plateviewer.pyc”, line 61, in init
File “plateviewer.pyc”, line 739, in get_non_blob_types_from_table
File “dbconnect.pyc”, line 782, in GetColumnNames
File “dbconnect.pyc”, line 64, in fn
File “dbconnect.pyc”, line 512, in execute

I’ve spent so much time trying to debug this but I don’t think I fully understand what’s happening. Does anyone have any suggestions?

My properties file reads as follows:

    #Thu Aug 11 18:25:55 2016
# ==============================================
#
# CellProfiler Analyst 2.0 properties file
#
# ==============================================

# ==== Database Info ====
db_type          =  sqlite
image_csv_file   =  T:\ActiveProjects\Bill Daly\Cellprofiler\20160804 CDI Neurons May Plate 2\ForCellprofilerAnalyst\SQL_forebrain1_384_Image.csv
object_csv_file  =  T:\ActiveProjects\Bill Daly\Cellprofiler\20160804 CDI Neurons May Plate 2\ForCellprofilerAnalyst\SQL_forebrain1_384_Object.csv

# ==== Database Tables ====
image_table   = my_tableimg
object_table  = my_tableobj

# ==== 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      = I
well_id       = V

# 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_Dapi,Image_PathName_Rhodamine
image_file_cols = Image_FileName_Dapi,Image_FileName_Rhodamine

# 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 = Dapi,Rhodamine

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



# ==== Image Filters ====
# Here you can define image filters to let you select objects from a subset of your experiment when training the classifier.
# 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

# ==== Other ====
# Specify the approximate diameter of your objects in pixels here.
image_tile_size   =  50

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

#2

I think you’ve probably got at least a couple of errors in the configuration of your properties file here.

The specific error that it’s saying is that it can’t find your “my_tableimg” table. When you run a properties files based on CSVs, it’s supposed to create an SQLite database for you- have you checked that A) it did that and B) that the name of your image table is actually “my_tableimg”? That doesn’t sound like standard CP designation to me. The plate_id and well_id columns also don’t sound like standard CP designation.

If you’re new to CPA and/or databases in general, my best advice would be to run your pipeline that created these measurements again and use ExportToDatabase to create the SQLite database and properties file directly for you- that’ll save a lot of time in trying to get it manually configured. Just my $0.02.