How to read image thumbnails from SQLite DB

cellprofiler

#1

I am developing a C# program to process a large SQLite database (CellProfiler output).
I can access all data but I cannot convert “Image_Thumbnails” as byte[] into images.
Could you please let me know how it can be accomplished (VisualStudio2015 C#)?

Thanks,
Zygmunt


#2

Hi,

Based on this code, I think you need to use something along the lines of-

import PIL.Image as Image
from base64 import b64decode
from StringIO import StringIO

for wims in wells_and_images:
    try:
        ims = [Image.open(StringIO(im), 'r') for im in wims[1:]]
    except IOError, e:
        ims = [Image.open(StringIO(b64decode(im)), 'r') for im in wims[1:]]
    ims = [np.fromstring(im.tobytes(), dtype='uint8').reshape(im.size[1], im.size[0]).astype('float32') / 255
                       for im in ims]

#3

Thank you for your replay.

I read image thumbnails as a byte[]:

 string sql = "SELECT * FROM " + "Quantify_WBCsPer_Image";
            DataTable df = GDataTable(sql);
            foreach (DataRow row in df.Rows)
            {
                int d = row.ItemArray.GetLength(0);
                for (i = 0; i < d; i++)
                {
                    if (i == 55)
                    //column 55 - [Image_Thumbnail_APC]
                    {
                        MemoryStream ms = new MemoryStream((Byte[])row[i]);
                        Image image = Image.FromStream(ms);
//An unhandled exception of type 'System.ArgumentException' occurred in System.Drawing.dll

My questions:

  • where can I find a def of the p object?
  • what type is “wells_and_images”, “im” ( byte[] or string or char[])?
                         for well, im in wells_and_images:
                                imgs[well] = (im, )
                         for wims in wells_and_images:
                                for im in wims[1:] - wims is a list of im
                                imgs[wims[0]] =  ims
                         

db = dbconnect.DBConnect.getInstance()
        bmp = {}
        imgs = {}
        if self.well_disp == IMAGE:
            if p.plate_id:
                wells_and_images = db.execute('SELECT %s, %s FROM %s WHERE %s="%s" GROUP BY %s'%(
                    p.well_id, p.image_id, p.image_table, p.plate_id, self.plate, 
                    p.well_id))
            else:
                wells_and_images = db.execute('SELECT %s, %s FROM %s GROUP BY %s'%(
                    p.well_id, p.image_id, p.image_table, p.well_id))
                
            for well, im in wells_and_images:
                imgs[well] = (im, )
        elif self.well_disp == THUMBNAIL:
            assert p.image_thumbnail_cols, 'No thumbnail columns are defined in the database. Platemap cannot be drawn.'
            if p.plate_id:
                wells_and_images = db.execute('SELECT %s, %s FROM %s WHERE %s="%s" GROUP BY %s'%(
                    p.well_id, ','.join(p.image_thumbnail_cols), p.image_table, 
                    p.plate_id, self.plate, p.well_id))
            else:
                wells_and_images = db.execute('SELECT %s, %s FROM %s GROUP BY %s'%(
                    p.well_id, ','.join(p.image_thumbnail_cols), p.image_table, p.well_id))

            for wims in wells_and_images:
                try:
                    ims = [Image.open(StringIO(im), 'r') for im in wims[1:]]
                except IOError, e:
                    ims = [Image.open(StringIO(b64decode(im)), 'r') for im in wims[1:]]
                ims = [np.fromstring(im.tobytes(), dtype='uint8').reshape(im.size[1], im.size[0]).astype('float32') / 255
                       for im in ims]
                imgs[wims[0]] =  ims

Thanks,
Zygmunt


#4

Hi, sorry, I should have been more clear; I was just pointing you at the python equivalent of the code you’d need to write, I don’t know C# so I’m not sure how to specifically help you write that code. Here’s a stack overflow for converting python image stuff to C# which will hopefully get you started

Essentially I believe wims in this case is essentially the column containing all your thumbnail strings- for each one, you need to read it in, convert the bytes to numbers, scale it, then reshape it to the correct shape. p I believe is from the Properties.


#5

Hi,
You shouldn’t be sorry. Thank you for any info.
Maybe one more question regarding the query:
SELECT %s, %s FROM %s WHERE %s="%s" GROUP BY %s’%(
p.well_id, p.image_id, p.image_table, p.plate_id, self.plate,
p.well_id))
I am guessing that p.image_table is “Quantify_WBCsPer_Image” table (in SQLite file Quantify_WBCs.db)
but I cannot find:
image_id = ImageNumber
object_id = ObjectNumber
plate_id = Image_Metadata_FileLocation
well_id = Image_Metadata_well
series_id = Image_Group_Number
group_id = Image_Group_Number
timepoint_id = Image_Group_Index
Any advice?

Thanks,
Zygmunt


#6

ObjectNumber is only in Per_Object tables, and Image_Metadata_Well will only exist if you designated a piece of metadata called “Well” in your pipeline, but all the rest of those columns should definitely be in your image table.


#7

Hi, thank you for your email. Let’s forget C# and continue in Python only,
please.
wells_and_images = db.execute(‘SELECT %s, %s FROM %s WHERE %s="%s" GROUP
BY %s’%(
p.well_id, ‘,’.join(p.image_thumbnail_cols),
p.image_table,
p.plate_id, self.plate, p.well_id))
p.image_thumbnail_cols = "Image_Thumbnail_X"
Could you please explain how this join should work.

Thanks,
Zygmunt

https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=icon
Virus-free.
www.avast.com
https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link
<#m_2131676667738508471_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>


#8

Hi, thank you for your email. Let’s forget C# and continue in Python only, please.
wells_and_images = db.execute(‘SELECT %s, %s FROM %s WHERE %s="%s" GROUP BY %s’%(
p.well_id, ‘,’.join(p.image_thumbnail_cols), p.image_table,
p.plate_id, self.plate, p.well_id))
p.image_thumbnail_cols = "Image_Thumbnail_X"
Could you please explain how this join should work.

Thanks,
Zygmunt


#9

That join is allowing you to pull image thumbnails and group them by plate and and well, which is necessary for that particular implementation but not in general. The only python code you should need to pull all the images one-at-a-time should what I posted above, assuming wims is a column that has thumbnail data (and that the first row is the column name, hence the wims[1:])-

    try:
        ims = [Image.open(StringIO(im), 'r') for im in wims[1:]]
    except IOError, e:
        ims = [Image.open(StringIO(b64decode(im)), 'r') for im in wims[1:]]
    ims = [np.fromstring(im.tobytes(), dtype='uint8').reshape(im.size[1], im.size[0]).astype('float32') / 255
                       for im in ims]