def from_db(cls,
full_path=None,
blind_trial_id=None,
reviewer_id=None,
trial_id=None,
testing=False,
postgresql=None):
if blind_trial_id is None and reviewer_id is None and trial_id is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db(cursor, full_path)
else:
with Cursor() as cursor:
return cls.__from_db(cursor, full_path)
elif full_path is None and reviewer_id is None and trial_id is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_blind_trial_id(cursor, blind_trial_id)
else:
with Cursor() as cursor:
return cls.__from_db_blind_trial_id(cursor, blind_trial_id)
elif reviewer_id is not None and trial_id is not None and full_path is None and blind_trial_id is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_reviewer_trial_id(
cursor, reviewer_id, trial_id)
else:
with Cursor() as cursor:
return cls.__from_db_reviewer_trial_id(
cursor, reviewer_id, trial_id)
def from_db(cls,
reviewer_fullname=None,
scored_dir=None,
reviewer_id=None,
testing=False,
postgresql=None):
if reviewer_id is None and reviewer_fullname is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_scored_dir(cursor, scored_dir)
else:
with Cursor() as cursor:
return cls.__from_db_by_scored_dir(cursor, scored_dir)
elif scored_dir is None and reviewer_fullname is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_reviewer_id(cursor, reviewer_id)
else:
with Cursor() as cursor:
return cls.__from_db_by_reviewer_id(cursor, reviewer_id)
elif reviewer_id is None and scored_dir is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_reviewer_fullname(
cursor, reviewer_fullname)
else:
with Cursor() as cursor:
return cls.__from_db_by_reviewer_fullname(
cursor, reviewer_fullname)
def from_db(cls, trial_dir=None, trial_id=None, testing=False, postgresql=None):
if trial_id is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_dir(cursor, trial_dir)
else:
with Cursor() as cursor:
return cls.__from_db_by_dir(cursor, trial_dir)
elif trial_dir is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_id(cursor, trial_id)
else:
with Cursor() as cursor:
return cls.__from_db_by_id(cursor, trial_id)
def delete_from_db(self, testing=False, postgresql=None):
if testing:
with TestingCursor(postgresql) as cursor:
self.__delete_from_db(cursor)
else:
with Cursor() as cursor:
self.__delete_from_db(cursor)
def from_db(cls, experiment_name, testing=False, postgresql=None):
experiment_name = utils.prep_string_for_db(experiment_name)
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db(cursor, experiment_name)
else:
with Cursor() as cursor:
return cls.__from_db(cursor, experiment_name)
def list_trial_dir_for_folder(cls, folder_id, testing=False, postgresql=None):
if testing:
with TestingCursor(postgresql) as cursor:
cursor.execute("SELECT trial_dir FROM trials WHERE folder_id = %s;", (folder_id,))
return list(item for tup in cursor.fetchall() for item in tup)
else:
with Cursor() as cursor:
cursor.execute("SELECT trial_dir FROM trials WHERE folder_id = %s;", (folder_id,))
return list(item for tup in cursor.fetchall() for item in tup)
def from_db(cls,
blind_name=None,
blind_folder_id=None,
testing=False,
postgresql=None):
if blind_folder_id is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_blind_name(cursor, blind_name)
else:
with Cursor() as cursor:
return cls.__from_db_by_blind_name(cursor, blind_name)
elif blind_name is None:
if testing:
with TestingCursor(postgresql) as cursor:
return cls.__from_db_by_id(cursor, blind_folder_id)
else:
with Cursor() as cursor:
return cls.__from_db_by_id(cursor, blind_folder_id)
def delete_from_db(self, testing=False, postgresql=None):
def __delete_from_db(cursor, experiment_id):
cursor.execute("DELETE FROM experiments WHERE experiment_id = %s",
(experiment_id, ))
if testing:
with TestingCursor(postgresql) as cursor:
__delete_from_db(cursor, self.experiment_id)
else:
with Cursor() as cursor:
__delete_from_db(cursor, self.experiment_id)
def list_all_blind_names(cls, testing=False, postgresql=None):
def main_list_all_blind_names(a_cursor):
a_cursor.execute("SELECT blind_name FROM blind_folders;")
return list(item for tup in a_cursor.fetchall() for item in tup)
if testing:
with TestingCursor(postgresql) as cursor:
return main_list_all_blind_names(cursor)
else:
with Cursor() as cursor:
return main_list_all_blind_names(cursor)
def save_to_db(self, testing=False, postgresql=None):
def save_to_db_main(scored_dir, a_cursor):
if scored_dir not in list_all_scored_dirs(a_cursor):
self.__save_to_db(a_cursor)
return self.__from_db_by_scored_dir(a_cursor, scored_dir)
if testing:
with TestingCursor(postgresql) as cursor:
return save_to_db_main(self.scored_dir, cursor)
else:
with Cursor() as cursor:
return save_to_db_main(self.scored_dir, cursor)
def save_to_db(self, testing=False, postgresql=None):
def save_to_db_main(blind_name, a_cursor):
if blind_name not in list_all_blind_names(a_cursor):
self.__save_to_db(a_cursor)
return self.__from_db_by_blind_name(a_cursor,
blind_name=blind_name)
if testing:
with TestingCursor(postgresql) as cursor:
return save_to_db_main(self.blind_name, cursor)
else:
with Cursor() as cursor:
return save_to_db_main(self.blind_name, cursor)
def save_to_db(self, testing=False, postgresql=None):
def save_to_db_main(a_cursor):
if self.experiment_id not in list_all_experiment_ids(a_cursor):
self.__save_to_db(a_cursor)
else:
self.__update_experiment(a_cursor)
return self.__from_db(a_cursor, self.experiment_name)
if testing:
with TestingCursor(postgresql) as cursor:
return save_to_db_main(cursor)
else:
with Cursor() as cursor:
return save_to_db_main(cursor)
def list_participants(experiment_name='all'):
if experiment_name == 'all':
all_participants = dict()
with Cursor() as cursor:
all_experiments = list_all_experiments(cursor)
for experiment_name in all_experiments:
all_participants[experiment_name] = sorted(set(
(Mouse.from_db_by_id(mouse_id).eartag
for mouse_id in list_participants(experiment_name))),
key=int)
return all_participants
experiment_participant_details = ParticipantDetails.list_participants(
experiment_name)
return sorted(set(experiment_participant_details), key=int)
def save_to_db(self, testing=False, postgresql=None):
def main(a_cursor, trial_dir):
cursor.execute(
"INSERT INTO trials (experiment_id, folder_id, trial_dir, trial_date) VALUES (%s, %s, %s, %s);",
(self.experiment_id, self.folder_id, self.trial_dir,
utils.convert_date_int_yyyymmdd(self.trial_date)))
return self.__from_db_by_dir(a_cursor, trial_dir)
if testing:
with TestingCursor(postgresql) as cursor:
return main(cursor, self.trial_dir)
else:
with Cursor() as cursor:
return main(cursor, self.trial_dir)
def from_db(cls, eartag, experiment_name, testing=False, postgresql=None):
if testing:
with TestingCursor(postgresql) as cursor:
mouse = Mouse.from_db(eartag,
testing=testing,
postgresql=postgresql)
experiment = Experiment.from_db(experiment_name,
testing=testing,
postgresql=postgresql)
return cls.__from_db(cursor, mouse, experiment)
else:
with Cursor() as cursor:
mouse = Mouse.from_db(eartag)
experiment = Experiment.from_db(experiment_name)
return cls.__from_db(cursor, mouse, experiment)
def save_to_db(self, testing=False, postgresql=None):
def main(a_cursor, full_path):
try:
cursor.execute(
"INSERT INTO blind_trials (trial_id, folder_id, full_path) VALUES (%s, %s, %s);",
(self.trial_id, self.folder_id, self.full_path))
except:
print("Already in db")
return self.__from_db(a_cursor, full_path)
if testing:
with TestingCursor(postgresql) as cursor:
return main(cursor, self.full_path)
else:
with Cursor() as cursor:
return main(cursor, self.full_path)
def list_participants(cls, experiment_name, testing=False, postgresql=None):
def main(a_cursor, experiment_id):
a_cursor.execute("SELECT eartag FROM all_participants_all_trials "
"WHERE experiment_id = %s;", (experiment_id,))
no_dups = sorted(set(utils.list_from_cursor(cursor.fetchall())), key=int)
return no_dups
experiment = Experiment.from_db(experiment_name, testing, postgresql)
if testing:
with TestingCursor(postgresql) as cursor:
return main(cursor, experiment.experiment_id)
else:
with Cursor() as cursor:
return main(cursor, experiment.experiment_id)
def from_db_by_id(cls, experiment_id, testing=False, postgresql=None):
def main(a_cursor, exp_id):
a_cursor.execute(
"SELECT * FROM experiments WHERE experiment_id = %s;",
(exp_id, ))
exp = cursor.fetchone()
if exp is None:
print(f"No experiment in the database with id {exp_id}")
return None
return cls(experiment_name=exp[2],
experiment_dir=exp[1],
experiment_id=exp[0])
if testing:
with TestingCursor(postgresql) as cursor:
return main(cursor, experiment_id)
else:
with Cursor() as cursor:
return main(cursor, experiment_id)
def get_id(cls, experiment_name, testing=False, postgresql=None):
experiment_name = utils.prep_string_for_db(experiment_name)
def main(a_cursor, exp_name):
a_cursor.execute(
"SELECT experiment_id FROM experiments WHERE experiment_name = %s;",
(exp_name, ))
exp_id = cursor.fetchall()
if exp_id is None:
print(f"No experiment in the database with name {exp_name}")
return None
return utils.list_from_cursor(exp_id)
if testing:
with TestingCursor(postgresql) as cursor:
return main(cursor, experiment_name)
else:
with Cursor() as cursor:
return main(cursor, experiment_name)
def list_all_blind_folders(cls,
experiment_id,
testing=False,
postgresql=None):
def main_list_all_blind_folders(a_cursor):
a_cursor.execute(
"SELECT blind_folder_id FROM blind_folders_all_upstream_ids WHERE experiment_id = %s",
(experiment_id, ))
all_folder_ids = a_cursor.fetchall()
return [
cls.from_db(blind_folder_id=blind_folder_id[0])
for blind_folder_id in all_folder_ids
]
if testing:
with TestingCursor(postgresql) as cursor:
return main_list_all_blind_folders(cursor)
else:
with Cursor() as cursor:
return main_list_all_blind_folders(cursor)
def list_all_sessions(cls,
mouse,
experiment,
testing=False,
postgresql=None):
def main_list_all_sessions(a_cursor, mouse_id, experiment_id):
a_cursor.execute(
"SELECT session_dir FROM sessions WHERE mouse_id = %s AND experiment_id = %s",
(mouse_id, experiment_id))
return list(
Session.from_db(session_dir=item)
for tup in a_cursor.fetchall() for item in tup)
if testing:
with TestingCursor(postgresql) as cursor:
return main_list_all_sessions(cursor, mouse.mouse_id,
experiment.experiment_id)
else:
with Cursor() as cursor:
return main_list_all_sessions(cursor, mouse.mouse_id,
experiment.experiment_id)
def list_participants(cls,
experiment_name,
testing=False,
postgresql=None):
def list_participants_main(a_cursor, exp_id):
a_cursor.execute(
"SELECT mouse_id FROM all_participants_all_experiments WHERE experiment_id = %s;",
(exp_id, ))
return utils.list_from_cursor(cursor.fetchall())
experiment_id = Experiment.get_id(experiment_name, testing, postgresql)
if len(experiment_id) == 1:
experiment_id = experiment_id[0]
else:
warnings.warn(
"Multiple experiments in the database with the same name.")
if testing:
with TestingCursor(postgresql) as cursor:
return list_participants_main(cursor, experiment_id)
else:
with Cursor() as cursor:
return list_participants_main(cursor, experiment_id)
def save_to_db(self, testing=False, postgresql=None):
def save_to_db(a_cursor, mouse_id, experiment_id, start_date, end_date,
exp_spec_details, participant_dir):
a_cursor.execute(
"INSERT INTO participant_details "
"(mouse_id, experiment_id, start_date, end_date, exp_spec_details, participant_dir) "
"VALUES (%s, %s, %s, %s, %s, %s);",
(mouse_id, experiment_id, start_date, end_date,
Json(exp_spec_details), participant_dir))
def update_details(a_cursor, start_date, end_date, exp_spec_details,
participant_dir, detail_id):
a_cursor.execute(
"UPDATE participant_details "
"SET (start_date, end_date, exp_spec_details, participant_dir) = (%s, %s, %s, %s) "
"WHERE detail_id = %s;",
(start_date, end_date, Json(exp_spec_details), participant_dir,
detail_id))
def save_to_db_main(a_cursor):
if self.detail_id not in list_all_detail_ids(a_cursor):
save_to_db(a_cursor, self.mouse.mouse_id,
self.experiment.experiment_id, self.start_date,
self.end_date, self.exp_spec_details,
self.participant_dir)
else:
update_details(a_cursor, self.start_date, self.end_date,
self.exp_spec_details, self.participant_dir,
self.detail_id)
return self.__from_db(a_cursor, self.mouse, self.experiment)
if testing:
with TestingCursor(postgresql) as cursor:
return save_to_db_main(cursor)
else:
with Cursor() as cursor:
return save_to_db_main(cursor)
import database.create_database.create_tables as create_tables
import database.create_database.create_views as create_views
from database.cursors import Cursor
from database.database import Database
from data.constants import dbDetails, dbUser_Krista
Database.initialize(**dbDetails, **dbUser_Krista)
with Cursor() as cursor:
cursor.execute("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";")
create_tables.create_mouse_table(cursor)
create_tables.create_experiments_table(cursor)
create_tables.create_participant_details_table(cursor)
create_tables.create_sessions_table(cursor)
create_tables.create_folders_table(cursor)
create_tables.create_trials_table(cursor)
create_tables.create_reviewers_table(cursor)
create_tables.create_blind_folders_table(cursor)
create_tables.create_blind_trials_table(cursor)
create_views.create_view_all_participants_all_experiments(cursor)
create_views.create_view_folders_all_upstream_ids(cursor)
create_views.create_view_trials_all_upstream_ids(cursor)