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)