SQL controller

This is the base sql class. It is mostly used per animal, so the init function needs an animal passed to the constructor It also needs for the animal, histology and scan run tables to be filled out for each animal to use

class library.controller.sql_controller.SqlController(animal)

Bases: AnnotationSessionController, AnimalController, ElastixController, HistologyController, ScanRunController, SectionsController, SlideCZIToTifController

This is the base controller class for all things SQL. Each parent class of SqlController would correspond to one table in the database, and include all the methods to interact with that table

add_row(data)

adding a row to a table

Parameters:

data – (data to be added ): instance of sqalchemy ORMs

delete_row(model, search_dictionary)

Deletes a row from the database based on the given search criteria.

Args:

search_dictionary (dict): A dictionary containing the search criteria to find the row to delete. model (Base): The SQLAlchemy model class representing the table from which the row will be deleted.

Returns:

None

get_resolution(animal)

Returns the resolution for an animal

Parameters:

animal – string primary key

Return numpy array:

of the resolutions

get_row(search_dictionary: dict, model: object) → object

look for a specific row in the database and return the result

Parameters:

  • search_dictionary – (dict): field and value of the search

  • model – (sqalchemy ORM): the sqalchemy ORM in question

Returns:

sql alchemy query

query_one_with_filters_and_sum(model, filters: dict, sum_columns: list, group_by_columns: list)

Query a single row with multiple filters, sum multiple columns, and group by multiple columns.

Parameters:

  • model – SQLAlchemy model class

  • filters – Dictionary of filters {column: value}

  • sum_columns – List of columns to sum

  • group_by_columns – List of columns to group by

Returns:

Single row result or None

query_row(model, search_dictionary)

Queries a single row from the database based on the provided search criteria.

Args:

search_dictionary (dict): A dictionary where the keys are the column names and the values are the values to search for. model (Base): The SQLAlchemy model class to query.

Returns:

model: The first row that matches the search criteria, or None if no match is found.

row_exists(search_dictionary, model)

check if a specific row exist in a table

Parameters:

  • search_dictionary – (dict): field and value for the search

  • model – (sqalchemy ORM): sqalchemy ORM

Return boolean:

whether the row exists

update_row(model, row, update_dict: dict) → None

Update the table with the given ID using the provided update dictionary.

Args:

id (int): The ID of the scan run to update. update_dict (dict): A dictionary containing the fields to update and their new values.

Returns:

None