def get_ophys_experiment_df(): api = PostgresQueryMixin() query = ''' SELECT oec.visual_behavior_experiment_container_id as container_id, oec.ophys_experiment_id, oe.workflow_state, d.full_genotype as full_genotype, id.depth as imaging_depth, st.acronym as targeted_structure, os.name as session_name, equipment.name as equipment_name FROM ophys_experiments_visual_behavior_experiment_containers oec LEFT JOIN ophys_experiments oe ON oe.id = oec.ophys_experiment_id LEFT JOIN ophys_sessions os ON oe.ophys_session_id = os.id LEFT JOIN specimens sp ON sp.id=os.specimen_id LEFT JOIN donors d ON d.id=sp.donor_id LEFT JOIN imaging_depths id ON id.id=os.imaging_depth_id LEFT JOIN structures st ON st.id=oe.targeted_structure_id LEFT JOIN equipment ON equipment.id=os.equipment_id ''' return pd.read_sql(query, api.get_connection())
def __init__(self, behavior_session_id: int, lims_credentials: Optional[DbCredentials] = None, mtrain_credentials: Optional[DbCredentials] = None): super().__init__() if mtrain_credentials: self.mtrain_db = PostgresQueryMixin( dbname=mtrain_credentials.dbname, user=mtrain_credentials.user, host=mtrain_credentials.host, port=mtrain_credentials.port, password=mtrain_credentials.password) else: self.mtrain_db = (credential_injector(MTRAIN_DB_CREDENTIAL_MAP) (PostgresQueryMixin)()) if lims_credentials: self.lims_db = PostgresQueryMixin( dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, port=lims_credentials.port, password=lims_credentials.password) else: self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP) (PostgresQueryMixin)()) self.behavior_session_id = behavior_session_id ids = self._get_ids() self.ophys_experiment_ids = ids.get("ophys_experiment_ids") self.ophys_session_id = ids.get("ophys_session_id") self.behavior_training_id = ids.get("behavior_training_id") self.foraging_id = ids.get("foraging_id") self.ophys_container_id = ids.get("ophys_container_id")
class LimsApi(): def __init__(self, lims_credentials: Optional[DbCredentials] = None): if lims_credentials: self.lims_db = PostgresQueryMixin( dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, password=lims_credentials.password, port=lims_credentials.port) else: # Currying is equivalent to decorator syntactic sugar self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP)( PostgresQueryMixin)()) def get_experiment_id(self): return self.experiment_id def get_behavior_tracking_video_filepath_df(self): query = ''' SELECT wkf.storage_directory || wkf.filename AS raw_behavior_tracking_video_filepath, attachable_type FROM well_known_files wkf WHERE wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'RawBehaviorTrackingVideo') ''' return pd.read_sql(query, self.lims_db.get_connection()) def get_eye_tracking_video_filepath_df(self): query = ''' SELECT wkf.storage_directory || wkf.filename AS raw_behavior_tracking_video_filepath, attachable_type FROM well_known_files wkf WHERE wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'RawEyeTrackingVideo') ''' return pd.read_sql(query, self.lims_db.get_connection())
def default( cls, lims_credentials: Optional[DbCredentials] = None, mtrain_credentials: Optional[DbCredentials] = None, app_kwargs: Optional[Dict[str, Any]] = None) -> \ "BehaviorProjectLimsApi": """Construct a BehaviorProjectLimsApi instance with default postgres and app engines. Parameters ---------- lims_credentials: Optional[DbCredentials] Credentials to pass to the postgres connector to the lims database. If left unspecified, will check environment variables for the appropriate values. mtrain_credentials: Optional[DbCredentials] Credentials to pass to the postgres connector to the mtrain database. If left unspecified, will check environment variables for the appropriate values. app_kwargs: Dict Dict of arguments to pass to the app engine. Currently unused. Returns ------- BehaviorProjectLimsApi """ _app_kwargs = {"scheme": "http", "host": "lims2"} if app_kwargs: _app_kwargs.update(app_kwargs) if lims_credentials: lims_engine = PostgresQueryMixin( dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, password=lims_credentials.password, port=lims_credentials.port) else: # Currying is equivalent to decorator syntactic sugar lims_engine = (credential_injector(LIMS_DB_CREDENTIAL_MAP)( PostgresQueryMixin)()) if mtrain_credentials: mtrain_engine = PostgresQueryMixin( dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, password=lims_credentials.password, port=lims_credentials.port) else: # Currying is equivalent to decorator syntactic sugar mtrain_engine = (credential_injector(MTRAIN_DB_CREDENTIAL_MAP)( PostgresQueryMixin)()) app_engine = HttpEngine(**_app_kwargs) return cls(lims_engine, mtrain_engine, app_engine)
def __init__(self, ophys_experiment_id: int, lims_credentials: Optional[DbCredentials] = None): self.ophys_experiment_id = ophys_experiment_id if lims_credentials: self.lims_db = PostgresQueryMixin( dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, password=lims_credentials.password, port=lims_credentials.port) else: # Currying is equivalent to decorator syntactic sugar self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP) (PostgresQueryMixin)())
def __init__(self, behavior_session_id): super().__init__() # TODO: this password has been exposed in code but we really need # to move towards using a secrets database self.mtrain_db = PostgresQueryMixin(dbname="mtrain", user="******", host="prodmtrain1", port=5432, password="******") self.behavior_session_id = behavior_session_id ids = self._get_ids() self.ophys_experiment_ids = ids.get("ophys_experiment_ids") self.ophys_session_id = ids.get("ophys_session_id") self.behavior_training_id = ids.get("behavior_training_id") self.foraging_id = ids.get("foraging_id") self.ophys_container_id = ids.get("ophys_container_id")
def _get_behavior_stage_table( self, behavior_session_ids: Optional[List[int]] = None, mtrain_db: Optional[PostgresQueryMixin] = None): # Select fewer rows if possible via behavior_session_id if behavior_session_ids: foraging_ids = self._get_foraging_ids_from_behavior_session( behavior_session_ids) # Otherwise just get the full table from mtrain else: foraging_ids = None foraging_ids_query = self._build_in_list_selector_query( "bs.id", foraging_ids) # TODO: this password has already been exposed in code but we really # need to move towards using a secrets database if not mtrain_db: mtrain_db = PostgresQueryMixin(dbname="mtrain", user="******", host="prodmtrain1", port=5432, password="******") query = f""" SELECT stages.name as session_type, bs.id AS foraging_id FROM behavior_sessions bs JOIN stages ON stages.id = bs.state_id {foraging_ids_query}; """ return mtrain_db.select(query)
def default( cls, pg_kwargs: Optional[Dict[str, Any]] = None, app_kwargs: Optional[Dict[str, Any]] = None) -> \ "BehaviorProjectLimsApi": """Construct a BehaviorProjectLimsApi instance with default postgres and app engines. :param pg_kwargs: dict of keyword arguments to pass to the PostgresQueryMixin class instance. Valid arguments include: "dbname", "user", "host", "password", "port". Will use defaults in PostGresQueryMixin.__init__ if unspecified. :type pg_kwargs: dict :param app_kwargs: dict of keyword arguments to pass to the HTTPEngine class instance. Valid arguments include: "scheme", "host". Will default to scheme=http, host=lims2 if left unspecified. :type app_kwargs: dict :rtype: BehaviorProjectLimsApi """ _pg_kwargs = pg_kwargs or dict() _app_kwargs = {"scheme": "http", "host": "lims2"} if app_kwargs: _app_kwargs.update(app_kwargs) pg_engine = PostgresQueryMixin(**_pg_kwargs) app_engine = HttpEngine(**_app_kwargs) return cls(pg_engine, app_engine)
def from_lims(cls, ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> "OphysSessionId": query = """ SELECT oe.ophys_session_id FROM ophys_experiments oe WHERE id = {}; """.format(ophys_experiment_id) session_id = lims_db.fetchone(query, strict=False) return cls(session_id=session_id)
def from_lims(cls, ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> "ExperimentContainerId": query = """ SELECT visual_behavior_experiment_container_id FROM ophys_experiments_visual_behavior_experiment_containers WHERE ophys_experiment_id = {}; """.format(ophys_experiment_id) container_id = lims_db.fetchone(query, strict=False) return cls(experiment_container_id=container_id)
def from_lims( cls, db: PostgresQueryMixin, behavior_session_id: Union[int, str] ) -> "StimulusFile": query = STIMULUS_FILE_QUERY_TEMPLATE.format( behavior_session_id=behavior_session_id ) filepath = db.fetchone(query, strict=True) return cls(filepath=filepath)
def from_lims( cls, db: PostgresQueryMixin, ophys_experiment_id: Union[int, str] ) -> "SyncFile": query = SYNC_FILE_QUERY_TEMPLATE.format( ophys_experiment_id=ophys_experiment_id ) filepath = db.fetchone(query, strict=True) return cls(filepath=filepath)
def _get_targeted_structure_from_lims(ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> str: query = """ SELECT st.acronym FROM ophys_experiments oe LEFT JOIN structures st ON st.id = oe.targeted_structure_id WHERE oe.id = {}; """.format(ophys_experiment_id) targeted_structure = lims_db.fetchone(query, strict=True) return targeted_structure
def from_lims(cls, behavior_session_id: int, lims_db: PostgresQueryMixin) -> "Equipment": query = f""" SELECT e.name AS device_name FROM behavior_sessions bs JOIN equipment e ON e.id = bs.equipment_id WHERE bs.id = {behavior_session_id}; """ equipment_name = lims_db.fetchone(query, strict=True) return cls(equipment_name=equipment_name)
def from_lims(cls, behavior_session_id: int, lims_db: PostgresQueryMixin) -> "FullGenotype": query = f""" SELECT d.full_genotype FROM behavior_sessions bs JOIN donors d ON d.id=bs.donor_id WHERE bs.id= {behavior_session_id}; """ genotype = lims_db.fetchone(query, strict=True) return cls(full_genotype=genotype)
def from_lims(cls, ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> "DateOfAcquisitionOphys": query = f""" SELECT os.date_of_acquisition FROM ophys_experiments oe JOIN ophys_sessions os ON oe.ophys_session_id = os.id WHERE oe.id = {ophys_experiment_id}; """ doa = lims_db.fetchone(query=query) doa = cls._postprocess_lims_datetime(datetime=doa) return DateOfAcquisitionOphys(date_of_acquisition=doa)
def get_containers_df(only_passed=True): api = PostgresQueryMixin() if only_passed is True: query = ''' SELECT * FROM visual_behavior_experiment_containers vbc WHERE workflow_state IN ('container_qc','publish'); ''' else: query = ''' SELECT * FROM visual_behavior_experiment_containers vbc ''' return pd.read_sql( query, api.get_connection()).rename(columns={'id': 'container_id'})[[ 'container_id', 'specimen_id', 'workflow_state' ]]
def default(cls, lims_credentials: Optional[DbCredentials] = None, app_kwargs=None, asynchronous=True): """ Construct a "straightforward" lims api that can fetch data from lims2. Parameters ---------- lims_credentials : DbCredentials Credentials and configuration for postgres queries against the LIMS database. If left unspecified will attempt to provide credentials from environment variables. app_kwargs : dict High-level configuration for http requests. See allensdk.brain_observatory.ecephys.ecephys_project_api.http_engine.HttpEngine and AsyncHttpEngine for details. asynchronous : bool If true, (http) queries will be made asynchronously. Returns ------- EcephysProjectLimsApi """ _app_kwargs = { "scheme": "http", "host": "lims2", "asynchronous": asynchronous } if app_kwargs is not None: if "asynchronous" in app_kwargs: raise TypeError( "please specify asynchronicity option at the api level rather than for the http engine" ) _app_kwargs.update(app_kwargs) app_engine_cls = AsyncHttpEngine if _app_kwargs[ "asynchronous"] else HttpEngine app_engine = app_engine_cls(**_app_kwargs) if lims_credentials is not None: pg_engine = PostgresQueryMixin(dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, password=lims_credentials.password, port=lims_credentials.port) else: # Currying is equivalent to decorator syntactic sugar pg_engine = (credential_injector(LIMS_DB_CREDENTIAL_MAP)( PostgresQueryMixin)()) return cls(pg_engine, app_engine)
def from_lims(cls, behavior_session_id: int, lims_db: PostgresQueryMixin) -> "Sex": query = f""" SELECT g.name AS sex FROM behavior_sessions bs JOIN donors d ON bs.donor_id = d.id JOIN genders g ON g.id = d.gender_id WHERE bs.id = {behavior_session_id}; """ sex = lims_db.fetchone(query, strict=True) return cls(sex=sex)
def from_lims(cls, ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> "FieldOfViewShape": query = f""" SELECT oe.movie_width as width, oe.movie_height as height FROM ophys_experiments oe WHERE oe.id = {ophys_experiment_id}; """ df = lims_db.select(query=query) height = df.iloc[0]['height'] width = df.iloc[0]['width'] return cls(height=height, width=width)
def from_lims(cls, ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> "ImagingDepth": query = """ SELECT id.depth FROM ophys_experiments oe JOIN ophys_sessions os ON oe.ophys_session_id = os.id LEFT JOIN imaging_depths id ON id.id = oe.imaging_depth_id WHERE oe.id = {}; """.format(ophys_experiment_id) imaging_depth = lims_db.fetchone(query, strict=True) return cls(imaging_depth=imaging_depth)
def from_lims(cls, behavior_session_id: int, lims_db: PostgresQueryMixin) -> "Age": query = f""" SELECT a.name AS age FROM behavior_sessions bs JOIN donors d ON d.id = bs.donor_id JOIN ages a ON a.id = d.age_id WHERE bs.id = {behavior_session_id}; """ age = lims_db.fetchone(query, strict=True) age = cls._age_code_to_days(age=age) return cls(age=age)
def from_lims(cls, behavior_session_id: int, lims_db: PostgresQueryMixin) -> "DateOfAcquisition": query = """ SELECT bs.date_of_acquisition FROM behavior_sessions bs WHERE bs.id = {}; """.format(behavior_session_id) experiment_date = lims_db.fetchone(query, strict=True) experiment_date = cls._postprocess_lims_datetime( datetime=experiment_date) return cls(date_of_acquisition=experiment_date)
def from_lims(cls, db: PostgresQueryMixin, ophys_experiment_id: Union[int, str]) -> "EyeTrackingFile": query = f""" SELECT wkf.storage_directory || wkf.filename AS eye_tracking_file FROM ophys_experiments oe LEFT JOIN well_known_files wkf ON wkf.attachable_id = oe.ophys_session_id JOIN well_known_file_types wkft ON wkf.well_known_file_type_id = wkft.id WHERE wkf.attachable_type = 'OphysSession' AND wkft.name = 'EyeTracking Ellipses' AND oe.id = {ophys_experiment_id}; """ # noqa E501 filepath = db.fetchone(query, strict=True) return cls(filepath=filepath)
def default(cls, pg_kwargs=None, app_kwargs=None): _pg_kwargs = {} if pg_kwargs is not None: _pg_kwargs.update(pg_kwargs) _app_kwargs = {"scheme": "http", "host": "lims2"} if app_kwargs is not None: _app_kwargs.update(app_kwargs) pg_engine = PostgresQueryMixin(**_pg_kwargs) app_engine = HttpEngine(**_app_kwargs) return cls(pg_engine, app_engine)
def __init__(self, behavior_experiment_id: int, lims_credentials: Optional[DbCredentials] = None): """ Notes ----- - behavior_experiment_id is the same as behavior_session_id which is in lims - behavior_experiment_id is associated with foraging_id in lims - foraging_id in lims is the same as behavior_session_uuid in mtrain which is the same as session_uuid in the pickle returned by behavior_stimulus_file """ self.behavior_experiment_id = behavior_experiment_id if lims_credentials: self.lims_db = PostgresQueryMixin( dbname=lims_credentials.dbname, user=lims_credentials.user, host=lims_credentials.host, password=lims_credentials.password, port=lims_credentials.port) else: # Use default credentials from provider # Currying is equivalent to decorator syntactic sugar self.lims_db = ( credential_injector(LIMS_DB_CREDENTIAL_MAP) (PostgresQueryMixin)())
def from_lims(cls, behavior_session_id: int, lims_db: PostgresQueryMixin) -> "ForagingId": query = f""" SELECT foraging_id FROM behavior_sessions WHERE behavior_sessions.id = {behavior_session_id}; """ foraging_id = lims_db.fetchone(query, strict=True) foraging_id = uuid.UUID(foraging_id) return cls(foraging_id=foraging_id)
def from_lims(cls, ophys_experiment_id: int, lims_db: PostgresQueryMixin) -> "ProjectCode": query = f""" SELECT projects.code AS project_code FROM ophys_sessions JOIN projects ON projects.id = ophys_sessions.project_id WHERE ophys_sessions.id = ( SELECT oe.ophys_session_id FROM ophys_experiments oe WHERE oe.id = {ophys_experiment_id} ) """ project_code = lims_db.fetchone(query, strict=True) return cls(project_code=project_code)
def from_lims( cls, db: PostgresQueryMixin, ophys_experiment_id: Union[int, str] ) -> "EventDetectionFile": query = f''' SELECT wkf.storage_directory || wkf.filename AS event_detection_filepath FROM ophys_experiments oe LEFT JOIN well_known_files wkf ON wkf.attachable_id = oe.id JOIN well_known_file_types wkft ON wkf.well_known_file_type_id = wkft.id WHERE wkft.name = 'OphysEventTraceFile' AND oe.id = {ophys_experiment_id}; ''' # noqa E501 filepath = safe_system_path(db.fetchone(query, strict=True)) return cls(filepath=filepath)
def from_lims(cls, db: PostgresQueryMixin, ophys_experiment_id: Union[int, str]) -> "DemixFile": query = """ SELECT wkf.storage_directory || wkf.filename AS demix_file FROM ophys_experiments oe JOIN well_known_files wkf ON wkf.attachable_id = oe.id JOIN well_known_file_types wkft ON wkft.id = wkf.well_known_file_type_id WHERE wkf.attachable_type = 'OphysExperiment' AND wkft.name = 'DemixedTracesFile' AND oe.id = {}; """.format(ophys_experiment_id) filepath = db.fetchone(query, strict=True) return cls(filepath=filepath)