05_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#default_exp database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# database\n",
    "> helpers to get and query a sqlalchemy engine for DB containing metadata on experiments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "from sqlalchemy import create_engine, text\n",
    "from sqlalchemy import Table, Column, Integer, String, MetaData, select\n",
    "\n",
    "import pandas as pd\n",
    "import getpass\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "def get_db_engine(username, password, ip_adress, model_name, rdbms=\"mysql\"):\n",
    "    \"\"\"\n",
    "    Creates a sqlalchemy engine to query a database.\n",
    "    \n",
    "    params:\n",
    "        - username: Username used to connect\n",
    "        - password: Password of the user\n",
    "        - ip_adress: IP adress of the database\n",
    "        - model_name: Name of the model of the database to connect to\n",
    "        - rdbms: Backend engine of the database\n",
    "        \n",
    "    return:\n",
    "        - A sqlalchemy engine connected to the database\n",
    "    \"\"\"\n",
    "    engine = create_engine(\"%s://%s:%s@%s/%s\" % (rdbms, username, password, ip_adress, model_name),echo = False)\n",
    "    test_query = \"SELECT * FROM Project\"\n",
    "    df_test = pd.DataFrame(engine.connect().execute(text(test_query)))\n",
    "    return engine\n",
    "\n",
    "def prompt_credentials(user=None, db_adress=None):\n",
    "    \"\"\"\n",
    "    Helper function to make a prompt for the password, and additonally the user and the database IP adress\n",
    "    if left to None.\n",
    "    \n",
    "    params:\n",
    "        - user: None to prompt or name of the user.\n",
    "        - db_adress: None to prompt or database adress\n",
    "        \n",
    "    return:\n",
    "        - username, password and database IP adress\n",
    "    \"\"\"\n",
    "    if user is None:\n",
    "        user = input(prompt='Username: ')\n",
    "    passwd = getpass.getpass(prompt='Password: ')\n",
    "    if db_adress is None:\n",
    "        db_adress = input(prompt='DB IP: ')\n",
    "    return user, passwd, db_adress"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "def get_record_essentials(engine, record_id):\n",
    "    \"\"\"\n",
    "    Retrieves the essential informations about a record.\n",
    "    \n",
    "    params:\n",
    "        - engine: Database engine\n",
    "        - record_id: ID of the record\n",
    "        \n",
    "    return:\n",
    "        - Pandas Dataframe of record essential informations\n",
    "    \"\"\"\n",
    "    q_select_record = \"SELECT * FROM Record WHERE id = %d\" % record_id\n",
    "    q_select_cell = \"SELECT * FROM Cell WHERE record_id = %d\" % record_id\n",
    "    \n",
    "    df_record = pd.DataFrame(engine.connect().execute(text(q_select_record)))\n",
    "    df_cell = pd.DataFrame(engine.connect().execute(text(q_select_cell)))\n",
    "    \n",
    "    experiment_id = df_record[\"experiment_id\"][0]\n",
    "    q_select_experiment = \"SELECT * FROM Experiment WHERE id = %d\" % experiment_id\n",
    "    df_experiment = pd.DataFrame(engine.connect().execute(text(q_select_experiment)))\n",
    "    \n",
    "    mouse_id = df_experiment[\"mouse_id\"][0]\n",
    "    q_select_mouse = \"SELECT * FROM Mouse WHERE id = %d\" % mouse_id\n",
    "    df_mouse = pd.DataFrame(engine.connect().execute(text(q_select_mouse)))\n",
    "    \n",
    "    tool_id = df_record[\"tool_id\"][0]\n",
    "    q_select_tool = \"SELECT * FROM Tool WHERE id = %d\" % tool_id\n",
    "    df_tool = pd.DataFrame(engine.connect().execute(text(q_select_tool)))\n",
    "    \n",
    "    q_select_map = \"SELECT * FROM Map WHERE tool_id = %d\" % tool_id\n",
    "    df_map = pd.DataFrame(engine.connect().execute(text(q_select_map)))\n",
    "    \n",
    "    res_dict = {\"record\": df_record, \"cell\": df_cell, \n",
    "                \"experiment\": df_experiment, \"mouse\": df_mouse,\n",
    "                \"tool\": df_tool, \"map\": df_map}\n",
    "    return res_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "def get_stim_params(engine, stim_hashes):   \n",
    "    \"\"\"\n",
    "    Retrieves the parameters of a stimulus specified by its hash key.\n",
    "    \n",
    "    params:\n",
    "        - engine: Database engine\n",
    "        - stim_hashes: Stimulus hash\n",
    "        \n",
    "    return:\n",
    "        - Pandas Dataframe of stimulus parameters\n",
    "    \"\"\"\n",
    "    #Writting the query speed up the function rather than querying all individual tables\n",
    "    # and filtering them all\n",
    "    if not isinstance(stim_hashes, list):\n",
    "        stim_hashes = [stim_hashes]\n",
    "    if len(stim_hashes)==1:\n",
    "        str_hashes = \"('\"+stim_hashes[0]+\"')\"\n",
    "    else:\n",
    "        str_hashes=str(tuple(stim_hashes))\n",
    "    query = \"\"\"SELECT Stim.name AS stim_name, description, barcode, stim_comment, stimulus_id,\n",
    "                screen_id, hash, date AS date_compiled, compiled_comment, compiled_id, parameter_id,\n",
    "                Parameter.name as param_name, value as param_value\n",
    "                FROM (SELECT Compiled.id as comp_id, name, description, barcode, Stimulus.comment AS stim_comment, stimulus_id, screen_id, hash, date, Compiled.comment AS compiled_comment FROM Stimulus \n",
    "                LEFT JOIN Compiled ON stimulus_id=Stimulus.id WHERE hash IN \"\"\"+str_hashes+\"\"\") AS Stim\n",
    "                LEFT JOIN Compiled_Parameter ON compiled_id = comp_id\n",
    "                LEFT JOIN Parameter ON parameter_id = Parameter.id\"\"\"\n",
    "    df_params = pd.DataFrame(engine.connect().execute(text(query)))\n",
    "    return df_params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "def get_table(engine, table_name):\n",
    "    \"\"\"\n",
    "    Return the entire content of a table in a pandas Dataframe.\n",
    "    \n",
    "    params:\n",
    "        - engine: Database engine\n",
    "        - table_name: Name of the table\n",
    "        \n",
    "    return:\n",
    "        - Pandas Dataframe of the whole table\n",
    "    \"\"\"\n",
    "    query = \"\"\"SELECT * FROM \"\"\"+str(table_name)\n",
    "    df_table = pd.DataFrame(engine.connect().execute(text(query)))\n",
    "    return df_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "def stim_param_to_dict(param_df, md5):\n",
    "    param_dict = {}\n",
    "    stim_mask = param_df[\"hash\"] == md5\n",
    "    for _, row in param_df[stim_mask][[\"param_name\", \"param_value\"]].iterrows():\n",
    "        try:\n",
    "            param = json.loads(row[\"param_value\"])\n",
    "        except:\n",
    "            param = row[\"param_value\"]\n",
    "        param_dict[row[\"param_name\"]] = param\n",
    "    return param_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Converted 00_core.ipynb.\n",
      "Converted 01_utils.ipynb.\n",
      "Converted 02_processing.ipynb.\n",
      "Converted 03_modelling.ipynb.\n",
      "Converted 04_plotting.ipynb.\n",
      "Converted 05_database.ipynb.\n",
      "Converted 06_eyetrack.ipynb.\n",
      "Converted 10_synchro.io.ipynb.\n",
      "Converted 11_synchro.extracting.ipynb.\n",
      "Converted 12_synchro.processing.ipynb.\n",
      "Converted 13_leddome.ipynb.\n",
      "Converted 99_testdata.ipynb.\n",
      "Converted index.ipynb.\n"
     ]
    }
   ],
   "source": [
    "#hide\n",
    "from nbdev.export import *\n",
    "notebook2script()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tor",
   "language": "python",
   "name": "tor"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}