def test_credential_injector_only_injects_mapped_credentials(
provider, credential_map):
def mock_func(*, user, db):
pass
with pytest.raises(TypeError):
credential_injector(credential_map, provider)(mock_func)()
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")
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 get_ophys_experiment_df():
api = (
credential_injector(LIMS_DB_CREDENTIAL_MAP)(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 test_credential_injector_only_injects_existing_kwargs(
provider, credential_map, expected):
def mock_func(*, user):
return user
assert (credential_injector(credential_map,
provider)(mock_func)() == expected)
def behavior_session_id_to_foraging_id(behavior_session_id):
'''maps behavior_session_id to foraging_id'''
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
query = '''select foraging_id from behavior_sessions where id = '{}';'''.format(
behavior_session_id)
return api.fetchone(query, strict=True)
def test_credential_injector_with_provider_update():
def mock_func(*, user):
return user
provider = EnvCredentialProvider({"LIMS_USER": "******"})
credential_map = {"user": "******"}
set_credential_provider(provider)
assert credential_injector(credential_map)(mock_func)() == "user"
def __init__(self, dbname=None, user=None, host=None, password=None,
port=None):
if any(map(lambda x: x is None, [dbname, user, host, password, port])):
# Currying is equivalent to decorator syntactic sugar
self.mtrain_db = (
credential_injector(MTRAIN_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
else:
self.mtrain_db = PostgresQueryMixin(
dbname=dbname, user=user, host=host, password=password,
port=port)
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 __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 db_connection_creator(
credentials: Optional[DbCredentials] = None,
fallback_credentials: Optional[dict] = None,
) -> PostgresQueryMixin:
"""Create a db connection using credentials. If credentials are not
provided then use fallback credentials (which attempt to read from
shell environment variables).
Note: Must provide one of either 'credentials' or 'fallback_credentials'.
If both are provided, 'credentials' will take precedence.
Parameters
----------
credentials : Optional[DbCredentials], optional
User specified credentials, by default None
fallback_credentials : dict
Fallback credentials to use for creating the DB connection in the
case that no 'credentials' are provided, by default None.
Fallback credentials will attempt to get db connection info from
shell environment variables.
Some examples of environment variables that fallback credentials
will try to read from can be found in allensdk.core.auth_config.
Returns
-------
PostgresQueryMixin
A DB connection instance which can execute queries to the DB
specified by credentials or fallback_credentials.
Raises
------
RuntimeError
If neither 'credentials' nor 'fallback_credentials' were provided.
"""
if credentials:
db_conn = PostgresQueryMixin(dbname=credentials.dbname,
user=credentials.user,
host=credentials.host,
port=credentials.port,
password=credentials.password)
elif fallback_credentials:
db_conn = (
credential_injector(fallback_credentials)(PostgresQueryMixin)())
else:
raise RuntimeError(
"Must provide either credentials or fallback credentials in "
"order to create a db connection!")
return db_conn
def get_value_from_table(search_key, search_value, target_table, target_key):
'''
a general function for getting a value from a LIMS table
'''
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)
(PostgresQueryMixin)())
query = '''
select {}
from {}
where {} = '{}'
'''
result = pd.read_sql(query.format(target_key, target_table, search_key, search_value), api.get_connection())
if len(result) == 1:
return result[target_key].iloc[0]
else:
return None
def get_all_ophys_lims_columns_names(self, table_name=u'ophys_experiments'):
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(PostgresQueryMixin)())
conn = api.get_connection()
cur = conn.cursor()
query = ' '.join((
"SELECT column_name ",
"FROM information_schema.columns",
"WHERE table_name = '{}'".format(table_name),
))
cur.execute(query)
lims_data = cur.fetchall()
conn.close()
return lims_data
def sql_lims_query(query):
'''
Performs SQL query to lims using any properly formated SQL query.
:param query (str): properly formatted SQL query
'''
# connect to LIMS using read only
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(PostgresQueryMixin)())
conn = api.get_connection()
cur = conn.cursor()
# execute query
cur.execute(query)
# fetch results
return (cur.fetchall())
def __init__(self, lims_id):
self.lims_id = lims_id
# We first gather all information from LIMS
try:
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(PostgresQueryMixin)())
conn = api.get_connection()
cur = conn.cursor()
query = ' '.join((
"SELECT oe.id, oe.name, oe.storage_directory, os.specimen_id",
", sp.external_specimen_name, os.date_of_acquisition, u.login as operator",
", e.name as rig, id.depth, st.acronym, os.parent_session_id, oe.workflow_state",
", im1.jp2, im2.jp2, p.code, os.stimulus_name, os.storage_directory, os.id",
"FROM ophys_experiments oe",
"JOIN ophys_sessions os ON oe.ophys_session_id = os.id",
"JOIN specimens sp ON sp.id=os.specimen_id",
"LEFT JOIN imaging_depths id ON id.id=os.imaging_depth_id",
"LEFT JOIN equipment e ON e.id=os.equipment_id",
"LEFT JOIN users u ON u.id=os.operator_id",
"LEFT JOIN structures st ON st.id=oe.targeted_structure_id",
"LEFT JOIN images im1 ON oe.averaged_surface_image_id = im1.id",
"LEFT JOIN images im2 ON oe.averaged_depth_image_id = im2.id",
"JOIN projects p ON p.id = os.project_id",
"WHERE oe.id='{}'",
))
cur.execute(query.format(self.lims_id))
lims_data = cur.fetchall()
if lims_data == []:
self.data_present = False
else:
self.data_pointer = lims_data[0]
self.data_present = True
conn.close()
except Exception as e:
logger.error("Unable to query LIMS database: {}".format(e))
self.data_present = False
def get_containers_df(only_passed=True):
api = (
credential_injector(LIMS_DB_CREDENTIAL_MAP)(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 __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 lims_query(query):
'''
execute a SQL query in LIMS
returns:
* the result if the result is a single element
* results in a pandas dataframe otherwise
Examples:
>> lims_query('select ophys_session_id from ophys_experiments where id = 878358326')
returns 877907546
>> lims_query('select * from ophys_experiments where id = 878358326')
returns a single line dataframe with all columns from the ophys_experiments table for ophys_experiment_id = 878358326
>> lims_query('select * from ophys_sessions where id in (877907546, 876522267, 869118259)')
returns a three line dataframe with all columns from the ophys_sessions table for ophys_session_id in the list [877907546, 876522267, 869118259]
>> lims_query('select * from ophys_sessions where specimen_id = 830901424')
returns all rows and columns in the ophys_sessions table for specimen_id = 830901424
'''
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(PostgresQueryMixin)())
conn = api.get_connection()
df = pd.read_sql(query, conn)
conn.close()
if df.shape == (1, 1):
# if the result is a single element, return only that element
return df.iloc[0][0]
else:
# otherwise return in dataframe format
return df
def get_specimen_reporter_line(self):
try:
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(PostgresQueryMixin)())
conn = api.get_connection()
cur = conn.cursor()
query = ' '.join((
"SELECT g.name as reporter_line",
"FROM ophys_experiments oe",
"JOIN ophys_sessions os ON oe.ophys_session_id = os.id",
"JOIN specimens sp ON sp.id=os.specimen_id",
"JOIN donors d ON d.id=sp.donor_id",
"JOIN donors_genotypes dg ON dg.donor_id=d.id",
"JOIN genotypes g ON g.id=dg.genotype_id",
"JOIN genotype_types gt ON gt.id=g.genotype_type_id AND gt.name = 'reporter'",
"WHERE oe.id=%s",
))
cur.execute(query, [self.lims_id])
genotype_data = cur.fetchall()
final_genotype = ''
link_string = ''
for local_text in genotype_data:
local_gene = local_text[0]
final_genotype = final_genotype + link_string + local_gene
link_string = ';'
conn.close()
except Exception as e:
logger.error("cannot query specimen reporter line: {}".format(e))
final_genotype = ''
return final_genotype
def test_credential_injector(provider, credential_map, expected):
def mock_func(*, user, password):
return (user, password)
assert (credential_injector(credential_map,
provider)(mock_func)() == expected)
def test_credential_injector_preserves_function_args(provider, credential_map):
def mock_func(arg1, kwarg1=None, *, user, password):
return (arg1, kwarg1, user, password)
assert (credential_injector(credential_map, provider)(mock_func)(
"arg1", kwarg1="kwarg1") == ("arg1", "kwarg1", "user", "1234"))
def get_psql_dict_cursor():
"""Set up a connection to a psql db server with a dict cursor"""
api = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(PostgresQueryMixin)())
con = api.get_connection()
con.set_session(readonly=True, autocommit=True)
return con.cursor(cursor_factory=extras.RealDictCursor)
