{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "This is a brief introduction to the [refractivesqlite package](https://github.com/HugoGuillen/refractiveindex.info-sqlite) developed by [Hugo Guillén](https://github.com/HugoGuillen). This package is a wrapper in Python 3 for the database of optical constants [refractiveindex.info](http://refractiveindex.info/) developed by [Mikhail Polyanskiy](https://github.com/polyanskiy). \n",
    "\n",
    "For better visualization, this tutorial can be downloaded as an interactive Jupyter Notebook [here](https://github.com/HugoGuillen/refractiveindex.info-sqlite/blob/master/Tutorial.ipynb).\n",
    "\n",
    "## Features\n",
    "The principal functionality of this package is to allow offline/programmatically queries to the database such as:\n",
    "- Create a local SQLite database from the refractiveindex yml folder.\n",
    "- Create a local SQLite database from the refractiveindex.zip url.\n",
    "- Search the local database pages by approximate or exact terms.\n",
    "- Search material data (refractiveindex, extinctioncoefficient) by intervals.\n",
    "- Execute custom SQL queries on the database.\n",
    "- Export material data (refractiveindex, extinctioncoefficient) to numpy arrays or csv files.\n",
    "- Get data (refractiveindex, extinctioncoefficient) at specified wavelengths.\n",
    "\n",
    "## Scheme\n",
    "The package creates a database with the [following scheme](https://github.com/HugoGuillen/refractiveindex.info-sqlite/blob/master/ER.PNG).\n",
    "\n",
    "![Scheme](ER.PNG \"Scheme\")\n",
    "\n",
    "The table ``pages`` contains information about the materials; ``refractiveindex``  and ``extcoeff`` are self explanatory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Features\n",
    "## Create the database\n",
    "\n",
    "Note: once created, you don't need to create the DB again!\n",
    "In both methods you can specify the interpolation_points for the case when *n* is expressed as a formula and *k* is not defined."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From url\n",
    "You can use the default database, or specify the link of the version you want to load (See 2.1.3). \n",
    "\n",
    "*Note: you may see some LOG warnings, that's perfectly normal.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from refractivesqlite import dboperations as DB\n",
    "\n",
    "dbpath = \"refractive.db\"\n",
    "db = DB.Database(dbpath)\n",
    "db.create_database_from_url()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check URL version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database file found at refractive.db\n",
      "http://refractiveindex.info/download/database/rii-database-2016-01-31.zip\n"
     ]
    }
   ],
   "source": [
    "from refractivesqlite import dboperations as DB\n",
    "\n",
    "dbpath = \"refractive.db\"\n",
    "db = DB.Database(dbpath)\n",
    "db.check_url_version()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create from a custom URL \n",
    "You can find the .zip file of a version [here](http://refractiveindex.info/download.php). For example, to load the version 2016-05-25:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from refractivesqlite import dboperations as DB\n",
    "\n",
    "dbpath = \"refractive.db\"\n",
    "db = DB.Database(dbpath)\n",
    "db.create_database_from_url(riiurl=\"http://refractiveindex.info/download/database/rii-database-2016-05-25.zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From local yml folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from refractivesqlite import dboperations as DB\n",
    "\n",
    "dbpath = \"refractive.db\"\n",
    "ymlpath = \"database\"\n",
    "db = DB.Database(dbpath)\n",
    "db.create_database_from_folder(ymlpath,interpolation_points=200)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load existing database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database file found at refractive.db\n"
     ]
    }
   ],
   "source": [
    "from refractivesqlite import dboperations as DB\n",
    "\n",
    "dbpath = \"refractive.db\"\n",
    "db = DB.Database(dbpath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Searches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List all pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db.search_pages()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search pages by term"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 results found.\n",
      "pageid\tshelf\tbook\tpage\tfilepath\thasrefractive\thasextinction\trangeMin\trangeMax\tpoints\n",
      "467\torganic\tethylene_glycol\tSani_and_Otanicar\torganic\\C2H6O2 - ethylene glycol\\Sani_and_Otanicar.yml\t1\t1\t0.185\t2.8\t66\n",
      "470\torganic\tpropylene_glycol\tOtanicar\torganic\\C3H8O2 - propylene glycol\\Otanicar.yml\t1\t1\t0.434\t0.656\t66\n",
      "1561\tother\tTherminolVP-1\tOtanicar\theat transfer fluids\\Therminol VP-1\\Otanicar.yml\t0\t1\t0.2\t1.5\t66\n"
     ]
    }
   ],
   "source": [
    "db.search_pages(\"otanicar\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search pages by exact term"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "15 results found.\n",
      "pageid\tshelf\tbook\tpage\tfilepath\thasrefractive\thasextinction\trangeMin\trangeMax\tpoints\n",
      "27\tmain\tAu\tRakic\tmain\\Au\\Rakic.yml\t1\t1\t0.2066\t12.4\t200\n",
      "28\tmain\tAu\tOlmon-ev\tmain\\Au\\Olmon-ev.yml\t1\t1\t0.3\t24.93\t448\n",
      "29\tmain\tAu\tOlmon-sc\tmain\\Au\\Olmon-sc.yml\t1\t1\t0.3\t24.93\t448\n",
      "30\tmain\tAu\tOlmon-ts\tmain\\Au\\Olmon-ts.yml\t1\t1\t0.3\t24.93\t448\n",
      "31\tmain\tAu\tMcPeak\tmain\\Au\\McPeak.yml\t1\t1\t0.3\t1.7\t141\n",
      "32\tmain\tAu\tBabar\tmain\\Au\\Babar.yml\t1\t1\t0.2066\t12.4\t69\n",
      "33\tmain\tAu\tOrdal\tmain\\Au\\Ordal.yml\t1\t1\t0.667\t286.0\t52\n",
      "34\tmain\tAu\tWindt\tmain\\Au\\Windt.yml\t1\t1\t0.00236\t0.12157\t36\n",
      "35\tmain\tAu\tHagemann\tmain\\Au\\Hagemann.yml\t1\t1\t8.266e-06\t248.0\t149\n",
      "36\tmain\tAu\tHagemann-2\tmain\\Au\\Hagemann-2.yml\t1\t1\t0.003542\t0.8266\t124\n",
      "37\tmain\tAu\tJohnson\tmain\\Au\\Johnson.yml\t1\t1\t0.1879\t1.937\t49\n",
      "38\tmain\tAu\tLemarchand\tmain\\Au\\Lemarchand-3.96nm.yml\t1\t1\t0.35\t1.8\t291\n",
      "39\tmain\tAu\tLemarchand\tmain\\Au\\Lemarchand-4.62nm.yml\t1\t1\t0.35\t1.8\t291\n",
      "40\tmain\tAu\tLemarchand\tmain\\Au\\Lemarchand-5.77nm.yml\t1\t1\t0.35\t1.8\t291\n",
      "41\tmain\tAu\tLemarchand\tmain\\Au\\Lemarchand-11.7nm.yml\t1\t1\t0.35\t1.8\t291\n"
     ]
    }
   ],
   "source": [
    "db.search_pages(\"au\",exact=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search by PageId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pageid\tshelf\tbook\tpage\tfilepath\thasrefractive\thasextinction\trangeMin\trangeMax\tpoints\n",
      "1542\tother\tMLC-9200-000\tLi-o\tliquid crystals\\MLC-9200-000\\Li-o.yml\t1\t0\t0.45\t0.656\t100\n"
     ]
    }
   ],
   "source": [
    "db.search_id(1542)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search materials by refractive index (*n*) interval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*Search n = 0.3 delta_n = 0.0001\n",
      "12 results found.\n",
      "pageid|shelf|book|page|wavelength|n\n",
      "(4, 'main', 'Ag', 'Hagemann', 0.3351, 0.3)\n",
      "(41, 'main', 'Au', 'Lemarchand', 0.705, 0.30001489)\n",
      "(102, 'main', 'Cu', 'Johnson', 0.6168, 0.3)\n",
      "(102, 'main', 'Cu', 'Johnson', 0.892, 0.3)\n",
      "(199, 'main', 'Nb', 'Golovashkin-4.2', 2.5, 0.3)\n",
      "(296, 'main', 'Pb', 'Golovashkin-4.2', 1.6, 0.3)\n",
      "(1475, 'other', 'Au-Ag', 'Rioux-Au70Ag30', 0.608, 0.29996)\n",
      "(1475, 'other', 'Au-Ag', 'Rioux-Au70Ag30', 0.676, 0.29992)\n",
      "(1477, 'other', 'Au-Ag', 'Rioux-Au50Ag50', 0.573, 0.30004)\n",
      "(1477, 'other', 'Au-Ag', 'Rioux-Au50Ag50', 0.637, 0.29994)\n",
      "(1478, 'other', 'Au-Ag', 'Rioux-Au40Ag60', 0.71, 0.29996)\n",
      "(1480, 'other', 'Au-Ag', 'Rioux-Au20Ag80', 0.928, 0.2999)\n"
     ]
    }
   ],
   "source": [
    "db.search_n(n=0.3,delta_n=.0001)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search materials by extinction coefficient (*k*) interval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*Search k = 0.3 delta_k = 0.0001\n",
      "7 results found.\n",
      "pageid|shelf|book|page|wavelength|k\n",
      "(4, 'main', 'Ag', 'Hagemann', 0.0253, 0.3)\n",
      "(74, 'main', 'C', 'Djurisic-e', 0.057251, 0.30008)\n",
      "(228, 'main', 'H2O', 'Warren', 3.195, 0.3)\n",
      "(251, 'main', 'Ta2O5', 'Bright-amorphous', 10.3093, 0.300047)\n",
      "(323, 'main', 'CdS', 'Treharne', 0.46592328, 0.29994)\n",
      "(418, 'main', 'Tm', 'Vidal-Dasilva', 0.032843493, 0.30004735)\n",
      "(1584, '3d', 'crystals', 'ice', 3.195, 0.3)\n"
     ]
    }
   ],
   "source": [
    "db.search_k(k=0.3,delta_k=.0001)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search materials by *n,k* interval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*Search n = 0.3 delta_n = 0.1 k = 0.3 delta_k = 0.1\n",
      "42 results found.\n",
      "pageid|shelf|book|page|wavelength|n|k\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042069, 0.399493, 0.262777)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042126, 0.397854, 0.265747)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042183, 0.396244, 0.26875)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.04224, 0.394662, 0.271782)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042298, 0.39311, 0.274845)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042355, 0.391588, 0.277937)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042413, 0.390097, 0.281058)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042471, 0.388638, 0.284207)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042529, 0.387211, 0.287382)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042588, 0.385817, 0.290584)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042646, 0.384457, 0.293811)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042705, 0.383131, 0.297063)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042763, 0.38184, 0.300338)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042822, 0.380584, 0.303635)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042881, 0.379364, 0.306955)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.042941, 0.37818, 0.310295)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043, 0.377032, 0.313655)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043059, 0.375921, 0.317034)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043119, 0.374848, 0.320431)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043179, 0.373812, 0.323845)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043239, 0.372813, 0.327275)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043299, 0.371852, 0.330719)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.04336, 0.37093, 0.334178)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.04342, 0.370045, 0.337649)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043481, 0.369199, 0.341133)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043542, 0.368391, 0.344628)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043603, 0.367621, 0.348132)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043664, 0.366889, 0.351646)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043726, 0.366195, 0.355167)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043787, 0.365539, 0.358696)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043849, 0.364921, 0.362231)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043911, 0.364341, 0.365772)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.043973, 0.363798, 0.369317)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044035, 0.363293, 0.372865)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044098, 0.362824, 0.376416)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.04416, 0.362392, 0.379969)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044223, 0.361996, 0.383522)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044286, 0.361636, 0.387076)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044349, 0.361312, 0.390629)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044413, 0.361024, 0.394181)\n",
      "(73, 'main', 'C', 'Djurisic-o', 0.044476, 0.36077, 0.397731)\n",
      "(128, 'main', 'SrF2', 'Rodriguez-de_Marcos', 26.34640693, 0.330467574, 0.383771957)\n"
     ]
    }
   ],
   "source": [
    "db.search_nk(n=0.3, delta_n=0.1,k=0.3,delta_k=0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Custom SQL search\n",
    "You can search for custom sql queries (at your own risk). The function prints the number of results, and returns a list with n-tuples ordered by the fields on the select statement. Check the Section 1.2 for the scheme."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 results found.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(0, 'main', 'Ag', 'Rakic', 'main\\\\Ag\\\\Rakic.yml', 1, 1, 0.2066, 12.4, 200),\n",
       " (1, 'main', 'Ag', 'McPeak', 'main\\\\Ag\\\\McPeak.yml', 1, 1, 0.3, 1.7, 141)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get any page in main/Ag/ that its name contains a letter 'k'.\n",
    "db.search_custom('select * from pages where shelf=\"main\" and book=\"Ag\" '+\n",
    "                 'and page LIKE \"%k%\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11 results found.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(0.3, 0.972336834),\n",
       " (0.31, 0.572501372),\n",
       " (0.32, 0.331538655),\n",
       " (0.33, 0.585441289),\n",
       " (0.34, 1.052042671),\n",
       " (0.35, 1.326866905),\n",
       " (0.36, 1.533000885),\n",
       " (0.37, 1.708000982),\n",
       " (0.38, 1.858729622),\n",
       " (0.39, 1.99546181),\n",
       " (0.4, 2.122943979)]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get the wavelength and extinction coefficient from the page with id 1 \n",
    "#in the interval [0.3, 0.4].\n",
    "db.search_custom('''select wave,coeff \n",
    "                    from extcoeff \n",
    "                    where pageid = 1 and wave between 0.3 and 0.4''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11 results found.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(0.3, 1.646857286),\n",
       " (0.31, 1.455629699),\n",
       " (0.32, 0.920058628),\n",
       " (0.33, 0.233797996),\n",
       " (0.34, 0.103893124),\n",
       " (0.35, 0.07599858),\n",
       " (0.36, 0.063180003),\n",
       " (0.37, 0.056279242),\n",
       " (0.38, 0.050752406),\n",
       " (0.39, 0.048660415),\n",
       " (0.4, 0.04572895)]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get the wavelength and refractive index from the page with id 1 \n",
    "#in the interval [0.3, 0.4].\n",
    "db.search_custom('''select wave,refindex \n",
    "                    from refractiveindex \n",
    "                    where pageid = 1 and wave between 0.3 and 0.4''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23 results found.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('main\\\\Co\\\\Johnson.yml', 0.301, 1.44, 2.25),\n",
       " ('main\\\\Cr\\\\Johnson.yml', 0.301, 1.53, 2.34),\n",
       " ('main\\\\Fe\\\\Johnson.yml', 0.301, 1.67, 2.0),\n",
       " ('main\\\\Mn\\\\Johnson.yml', 0.301, 1.86, 2.19),\n",
       " ('main\\\\Ni\\\\Johnson.yml', 0.301, 2.02, 2.18),\n",
       " ('main\\\\Pd\\\\Johnson.yml', 0.301, 1.2, 2.29),\n",
       " ('main\\\\Ti\\\\Johnson.yml', 0.301, 1.45, 2.46),\n",
       " ('main\\\\V\\\\Johnson.yml', 0.301, 2.07, 2.95),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au100Ag0.yml', 0.301, 1.60958, 1.92549),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au90Ag10.yml', 0.301, 1.56306, 1.85315),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au80Ag20.yml', 0.301, 1.52131, 1.78689),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au70Ag30.yml', 0.301, 1.48332, 1.72633),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au60Ag40.yml', 0.301, 1.44868, 1.67012),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au50Ag50.yml', 0.301, 1.41765, 1.61551),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au40Ag60.yml', 0.301, 1.39132, 1.55779),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au30Ag70.yml', 0.301, 1.37194, 1.48921),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au20Ag80.yml', 0.301, 1.36413, 1.39552),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au10Ag90.yml', 0.301, 1.37929, 1.24231),\n",
       " ('alloys\\\\Au-Ag\\\\Rioux-Au0Ag100.yml', 0.301, 1.45664, 0.89431),\n",
       " ('perovskite\\\\CH3NH3PbI3\\\\Leguy.yml', 0.301, 1.7499, 0.9508),\n",
       " ('perovskite\\\\CH3NH3PbI3\\\\Leguy-hydrated.yml', 0.301, 2.7371, 1.0209),\n",
       " ('main\\\\Fe\\\\Johnson.yml', 0.301, 1.67, 2.0),\n",
       " ('main\\\\Ti\\\\Johnson.yml', 0.301, 1.45, 2.46)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get the path of the yml file, wavelength, refractive index and extinction coefficient \n",
    "#of all the pages with info on wavelength = 0.301\n",
    "db.search_custom('''select p.filepath, r.wave,refindex,coeff \n",
    "                    from refractiveindex r inner join extcoeff e on r.pageid = e.pageid \n",
    "                    and r.wave = e.wave inner join pages p on r.pageid = p.pageid\n",
    "                    where r.wave = .301''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get data in a numpy.array from a PageId"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get material refractive index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Material main\\Ag\\Johnson.yml loaded.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([[ 0.1879,  1.07  ],\n",
       "       [ 0.1916,  1.1   ],\n",
       "       [ 0.1953,  1.12  ],\n",
       "       [ 0.1993,  1.14  ],\n",
       "       [ 0.2033,  1.15  ],\n",
       "       [ 0.2073,  1.18  ],\n",
       "       [ 0.2119,  1.2   ],\n",
       "       [ 0.2164,  1.22  ],\n",
       "       [ 0.2214,  1.25  ],\n",
       "       [ 0.2262,  1.26  ],\n",
       "       [ 0.2313,  1.28  ],\n",
       "       [ 0.2371,  1.28  ],\n",
       "       [ 0.2426,  1.3   ],\n",
       "       [ 0.249 ,  1.31  ],\n",
       "       [ 0.2551,  1.33  ],\n",
       "       [ 0.2616,  1.35  ],\n",
       "       [ 0.2689,  1.38  ],\n",
       "       [ 0.2761,  1.41  ],\n",
       "       [ 0.2844,  1.41  ],\n",
       "       [ 0.2924,  1.39  ],\n",
       "       [ 0.3009,  1.34  ],\n",
       "       [ 0.3107,  1.13  ],\n",
       "       [ 0.3204,  0.81  ],\n",
       "       [ 0.3315,  0.17  ],\n",
       "       [ 0.3425,  0.14  ],\n",
       "       [ 0.3542,  0.1   ],\n",
       "       [ 0.3679,  0.07  ],\n",
       "       [ 0.3815,  0.05  ],\n",
       "       [ 0.3974,  0.05  ],\n",
       "       [ 0.4133,  0.05  ],\n",
       "       [ 0.4305,  0.04  ],\n",
       "       [ 0.4509,  0.04  ],\n",
       "       [ 0.4714,  0.05  ],\n",
       "       [ 0.4959,  0.05  ],\n",
       "       [ 0.5209,  0.05  ],\n",
       "       [ 0.5486,  0.06  ],\n",
       "       [ 0.5821,  0.05  ],\n",
       "       [ 0.6168,  0.06  ],\n",
       "       [ 0.6595,  0.05  ],\n",
       "       [ 0.7045,  0.04  ],\n",
       "       [ 0.756 ,  0.03  ],\n",
       "       [ 0.8211,  0.04  ],\n",
       "       [ 0.892 ,  0.04  ],\n",
       "       [ 0.984 ,  0.04  ],\n",
       "       [ 1.088 ,  0.04  ],\n",
       "       [ 1.216 ,  0.09  ],\n",
       "       [ 1.393 ,  0.13  ],\n",
       "       [ 1.61  ,  0.15  ],\n",
       "       [ 1.937 ,  0.24  ]])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.get_material_n_numpy(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get material extinction coefficients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Material main\\Ag\\Johnson.yml loaded.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([[  0.1879,   1.212 ],\n",
       "       [  0.1916,   1.232 ],\n",
       "       [  0.1953,   1.255 ],\n",
       "       [  0.1993,   1.277 ],\n",
       "       [  0.2033,   1.296 ],\n",
       "       [  0.2073,   1.312 ],\n",
       "       [  0.2119,   1.325 ],\n",
       "       [  0.2164,   1.336 ],\n",
       "       [  0.2214,   1.342 ],\n",
       "       [  0.2262,   1.344 ],\n",
       "       [  0.2313,   1.357 ],\n",
       "       [  0.2371,   1.367 ],\n",
       "       [  0.2426,   1.378 ],\n",
       "       [  0.249 ,   1.389 ],\n",
       "       [  0.2551,   1.393 ],\n",
       "       [  0.2616,   1.387 ],\n",
       "       [  0.2689,   1.372 ],\n",
       "       [  0.2761,   1.331 ],\n",
       "       [  0.2844,   1.264 ],\n",
       "       [  0.2924,   1.161 ],\n",
       "       [  0.3009,   0.964 ],\n",
       "       [  0.3107,   0.616 ],\n",
       "       [  0.3204,   0.392 ],\n",
       "       [  0.3315,   0.829 ],\n",
       "       [  0.3425,   1.142 ],\n",
       "       [  0.3542,   1.419 ],\n",
       "       [  0.3679,   1.657 ],\n",
       "       [  0.3815,   1.864 ],\n",
       "       [  0.3974,   2.07  ],\n",
       "       [  0.4133,   2.275 ],\n",
       "       [  0.4305,   2.462 ],\n",
       "       [  0.4509,   2.657 ],\n",
       "       [  0.4714,   2.869 ],\n",
       "       [  0.4959,   3.093 ],\n",
       "       [  0.5209,   3.324 ],\n",
       "       [  0.5486,   3.586 ],\n",
       "       [  0.5821,   3.858 ],\n",
       "       [  0.6168,   4.152 ],\n",
       "       [  0.6595,   4.483 ],\n",
       "       [  0.7045,   4.838 ],\n",
       "       [  0.756 ,   5.242 ],\n",
       "       [  0.8211,   5.727 ],\n",
       "       [  0.892 ,   6.312 ],\n",
       "       [  0.984 ,   6.992 ],\n",
       "       [  1.088 ,   7.795 ],\n",
       "       [  1.216 ,   8.828 ],\n",
       "       [  1.393 ,  10.1   ],\n",
       "       [  1.61  ,  11.85  ],\n",
       "       [  1.937 ,  14.08  ]])"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.get_material_k_numpy(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get material data as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Material main\\Ag\\Johnson.yml loaded.\n",
      "Wrote 5,main,Ag,Johnson(nk).csv\n"
     ]
    }
   ],
   "source": [
    "db.get_material_csv(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get ALL CSVs *(Be careful)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db.get_material_csv_all(outputfolder=\"all\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interact with materials\n",
    "You need to *load* a material to interact with it. Once loaded, you can make any number of operations with it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load material by Id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Material main\\Ag\\Johnson.yml loaded.\n"
     ]
    }
   ],
   "source": [
    "mat = db.get_material(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get material info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrderedDict([('pageid', 5), ('shelf', 'main'), ('book', 'Ag'), ('page', 'Johnson'), ('filepath', 'main\\\\Ag\\\\Johnson.yml'), ('hasrefractive', 1), ('hasextinction', 1), ('rangeMin', 0.1879), ('rangeMax', 1.937), ('points', 49)])\n"
     ]
    }
   ],
   "source": [
    "print(mat.get_page_info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get all material refractive index data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0.1879, 1.07], [0.1916, 1.1], [0.1953, 1.12], [0.1993, 1.14], [0.2033, 1.15], [0.2073, 1.18], [0.2119, 1.2], [0.2164, 1.22], [0.2214, 1.25], [0.2262, 1.26], [0.2313, 1.28], [0.2371, 1.28], [0.2426, 1.3], [0.249, 1.31], [0.2551, 1.33], [0.2616, 1.35], [0.2689, 1.38], [0.2761, 1.41], [0.2844, 1.41], [0.2924, 1.39], [0.3009, 1.34], [0.3107, 1.13], [0.3204, 0.81], [0.3315, 0.17], [0.3425, 0.14], [0.3542, 0.1], [0.3679, 0.07], [0.3815, 0.05], [0.3974, 0.05], [0.4133, 0.05], [0.4305, 0.04], [0.4509, 0.04], [0.4714, 0.05], [0.4959, 0.05], [0.5209, 0.05], [0.5486, 0.06], [0.5821, 0.05], [0.6168, 0.06], [0.6595, 0.05], [0.7045, 0.04], [0.756, 0.03], [0.8211, 0.04], [0.892, 0.04], [0.984, 0.04], [1.088, 0.04], [1.216, 0.09], [1.393, 0.13], [1.61, 0.15], [1.937, 0.24]]\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "n = mat.get_complete_refractive()\n",
    "print(n)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get all material refractive index data as a numpy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 0.1879  1.07  ]\n",
      " [ 0.1916  1.1   ]\n",
      " [ 0.1953  1.12  ]\n",
      " [ 0.1993  1.14  ]\n",
      " [ 0.2033  1.15  ]\n",
      " [ 0.2073  1.18  ]\n",
      " [ 0.2119  1.2   ]\n",
      " [ 0.2164  1.22  ]\n",
      " [ 0.2214  1.25  ]\n",
      " [ 0.2262  1.26  ]\n",
      " [ 0.2313  1.28  ]\n",
      " [ 0.2371  1.28  ]\n",
      " [ 0.2426  1.3   ]\n",
      " [ 0.249   1.31  ]\n",
      " [ 0.2551  1.33  ]\n",
      " [ 0.2616  1.35  ]\n",
      " [ 0.2689  1.38  ]\n",
      " [ 0.2761  1.41  ]\n",
      " [ 0.2844  1.41  ]\n",
      " [ 0.2924  1.39  ]\n",
      " [ 0.3009  1.34  ]\n",
      " [ 0.3107  1.13  ]\n",
      " [ 0.3204  0.81  ]\n",
      " [ 0.3315  0.17  ]\n",
      " [ 0.3425  0.14  ]\n",
      " [ 0.3542  0.1   ]\n",
      " [ 0.3679  0.07  ]\n",
      " [ 0.3815  0.05  ]\n",
      " [ 0.3974  0.05  ]\n",
      " [ 0.4133  0.05  ]\n",
      " [ 0.4305  0.04  ]\n",
      " [ 0.4509  0.04  ]\n",
      " [ 0.4714  0.05  ]\n",
      " [ 0.4959  0.05  ]\n",
      " [ 0.5209  0.05  ]\n",
      " [ 0.5486  0.06  ]\n",
      " [ 0.5821  0.05  ]\n",
      " [ 0.6168  0.06  ]\n",
      " [ 0.6595  0.05  ]\n",
      " [ 0.7045  0.04  ]\n",
      " [ 0.756   0.03  ]\n",
      " [ 0.8211  0.04  ]\n",
      " [ 0.892   0.04  ]\n",
      " [ 0.984   0.04  ]\n",
      " [ 1.088   0.04  ]\n",
      " [ 1.216   0.09  ]\n",
      " [ 1.393   0.13  ]\n",
      " [ 1.61    0.15  ]\n",
      " [ 1.937   0.24  ]]\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "n = np.array(mat.get_complete_refractive())\n",
    "print(n)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get all material extinction coefficient data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0.1879, 1.212], [0.1916, 1.232], [0.1953, 1.255], [0.1993, 1.277], [0.2033, 1.296], [0.2073, 1.312], [0.2119, 1.325], [0.2164, 1.336], [0.2214, 1.342], [0.2262, 1.344], [0.2313, 1.357], [0.2371, 1.367], [0.2426, 1.378], [0.249, 1.389], [0.2551, 1.393], [0.2616, 1.387], [0.2689, 1.372], [0.2761, 1.331], [0.2844, 1.264], [0.2924, 1.161], [0.3009, 0.964], [0.3107, 0.616], [0.3204, 0.392], [0.3315, 0.829], [0.3425, 1.142], [0.3542, 1.419], [0.3679, 1.657], [0.3815, 1.864], [0.3974, 2.07], [0.4133, 2.275], [0.4305, 2.462], [0.4509, 2.657], [0.4714, 2.869], [0.4959, 3.093], [0.5209, 3.324], [0.5486, 3.586], [0.5821, 3.858], [0.6168, 4.152], [0.6595, 4.483], [0.7045, 4.838], [0.756, 5.242], [0.8211, 5.727], [0.892, 6.312], [0.984, 6.992], [1.088, 7.795], [1.216, 8.828], [1.393, 10.1], [1.61, 11.85], [1.937, 14.08]]\n"
     ]
    }
   ],
   "source": [
    "k = mat.get_complete_extinction()\n",
    "print(k)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get all material extinction coefficient data as a numpy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[  0.1879   1.212 ]\n",
      " [  0.1916   1.232 ]\n",
      " [  0.1953   1.255 ]\n",
      " [  0.1993   1.277 ]\n",
      " [  0.2033   1.296 ]\n",
      " [  0.2073   1.312 ]\n",
      " [  0.2119   1.325 ]\n",
      " [  0.2164   1.336 ]\n",
      " [  0.2214   1.342 ]\n",
      " [  0.2262   1.344 ]\n",
      " [  0.2313   1.357 ]\n",
      " [  0.2371   1.367 ]\n",
      " [  0.2426   1.378 ]\n",
      " [  0.249    1.389 ]\n",
      " [  0.2551   1.393 ]\n",
      " [  0.2616   1.387 ]\n",
      " [  0.2689   1.372 ]\n",
      " [  0.2761   1.331 ]\n",
      " [  0.2844   1.264 ]\n",
      " [  0.2924   1.161 ]\n",
      " [  0.3009   0.964 ]\n",
      " [  0.3107   0.616 ]\n",
      " [  0.3204   0.392 ]\n",
      " [  0.3315   0.829 ]\n",
      " [  0.3425   1.142 ]\n",
      " [  0.3542   1.419 ]\n",
      " [  0.3679   1.657 ]\n",
      " [  0.3815   1.864 ]\n",
      " [  0.3974   2.07  ]\n",
      " [  0.4133   2.275 ]\n",
      " [  0.4305   2.462 ]\n",
      " [  0.4509   2.657 ]\n",
      " [  0.4714   2.869 ]\n",
      " [  0.4959   3.093 ]\n",
      " [  0.5209   3.324 ]\n",
      " [  0.5486   3.586 ]\n",
      " [  0.5821   3.858 ]\n",
      " [  0.6168   4.152 ]\n",
      " [  0.6595   4.483 ]\n",
      " [  0.7045   4.838 ]\n",
      " [  0.756    5.242 ]\n",
      " [  0.8211   5.727 ]\n",
      " [  0.892    6.312 ]\n",
      " [  0.984    6.992 ]\n",
      " [  1.088    7.795 ]\n",
      " [  1.216    8.828 ]\n",
      " [  1.393   10.1   ]\n",
      " [  1.61    11.85  ]\n",
      " [  1.937   14.08  ]]\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "k = np.array(mat.get_complete_extinction())\n",
    "print(k)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get material data as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wrote mat1(nk).csv\n"
     ]
    }
   ],
   "source": [
    "mat.to_csv(output=\"mat1.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Get refractive index at a specified wavelength"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(0.04004878048780487)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat.get_refractiveindex(451)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get extinction coefficient at a specified wavelength"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(2.658034146341463)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat.get_extinctioncoefficient(451)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}