File size: 2,909 Bytes
2689208 ea08a74 2689208 ea08a74 2689208 cb37243 2689208 ea08a74 2689208 072e991 2689208 072e991 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
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")
|