annotation-preprocessing/0_fetch_from_database.py

import mysql.connector
import pandas as pd
import os
from dotenv import load_dotenv

BASE_OBJECT_SQL = """
FROM UniqueGroundTruth 
 JOIN DetectedObject on DetectedObject.id = UniqueGroundTruth.object_id
 JOIN Image on Image.id = DetectedObject.image_id 
 JOIN FocusStack on FocusStack.id = Image.focus_stack_id
 JOIN Scan on Scan.id = FocusStack.scan_id
 JOIN Slide on Slide.id = Scan.slide_id 
 JOIN ObjectType on ObjectType.id = UniqueGroundTruth.object_type_id 
 WHERE metaclass_id = 1 -- only select eggs;
 AND study_id = 31
 ORDER BY UniqueGroundTruth.focus_stack_id
"""

def fetch_objects_from_datase(db):
 cursor = db.cursor()

 cursor.execute("""SELECT
 UniqueGroundTruth.focus_stack_id,
 UniqueGroundTruth.x_min, 
 UniqueGroundTruth.y_min,
 UniqueGroundTruth.x_max, 
 UniqueGroundTruth.y_max,
 UniqueGroundTruth.object_type_id,
 ObjectType.name,
 Image.add_date""" + BASE_OBJECT_SQL)

 result = cursor.fetchall()
 return result

def fetch_focus_stacks_from_database(db):
 cursor = db.cursor()

 cursor.execute("""SELECT 
 FocusStack.id as foucs_stack_id, 
 CONCAT (study_id, "/", uuid, "/", file_name) as file_path, 
 file_name,
 uuid,
 study_id,
 Image.pos_z,
 Image.focus_value,
 Image.add_date
 FROM FocusStack
 JOIN Scan on Scan.id = FocusStack.scan_id
 JOIN Slide on Slide.id = Scan.slide_id 
 JOIN Study on Study .id = Slide.study_id 
 JOIN Image on Image.focus_stack_id = FocusStack.id
 WHERE 
 FocusStack.id IN( -- get all focus stacks that have objects in them;
 SELECT DISTINCT
 UniqueGroundTruth.focus_stack_id
 """ + BASE_OBJECT_SQL
 + """
 )
 ORDER BY FocusStack.id DESC, focus_value, focus_level
 """)
 result = cursor.fetchall()
 return result



if __name__ == "__main__":
 load_dotenv()

 db = mysql.connector.connect(
 host=os.getenv('DB_HOST'),
 user=os.getenv('DB_USER'),
 password=os.getenv('DB_PASSWORD'),
 database=os.getenv('DB_NAME')
 )

 print("Querring objects...")
 df_objects = pd.DataFrame(fetch_objects_from_datase(db))
 print("Querring stacks...")
 df_stacks = pd.DataFrame(fetch_focus_stacks_from_database(db))

 df_objects.columns = ['stack_id', 'x_min', 'y_min', 'x_max', 'y_max', 'object_type_id', 'name', 'add_date']
 df_stacks.columns = ['stack_id', 'file_path', 'file_name',
 'uuid', 'study_id', 'pos_z', 'focus_value', 'add_date']

 print("Writing objects to file...")
 df_objects.to_csv("out/objects.csv")
 print("Writing stacks to file...")
 df_stacks.to_csv("out/stacks.csv")