Flake_Finding_Querying.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "126401f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1fb1fcff",
   "metadata": {},
   "outputs": [],
   "source": [
    "### INPUT MAGNIFICATION AS \"_#x\"\n",
    "mag_using = \"_20x\"\n",
    "\n",
    "### CONNECT TO SQL DATABASE \"flake_characteristics\"\n",
    "con = sqlite3.connect('flake_characteristics'+mag_using)\n",
    "c = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27f48980",
   "metadata": {},
   "source": [
    "# Selective Querying\n",
    "## Characteristics to choose from:  \n",
    "    1. image                                   \n",
    "    2. mag                                     \n",
    "    3. thickness                               \n",
    "    4. l_side range: [50, 200]                                             \n",
    "    5. location (see below)\n",
    "\n",
    "## Locations:\n",
    "    1. UL = Upper Left     2. UM = Upper Middle    3. UR = Upper Right\n",
    "    4. ML = Middle Left    5. MM = Middle Middle   6. MR = Middle Right\n",
    "    7. LL = Lower Left     8. LM = Lower Middle    9. LR = Lower Right\n",
    "\n",
    "## Thicknesses:\n",
    "    1. mono\n",
    "    2. bi\n",
    "    3. few"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "679a62da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query all characteristics (*)\n",
    "df = pd.read_sql_query(\"SELECT * FROM flakes\", con)\n",
    "\n",
    "# print database\n",
    "#df.style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5cebb0f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query multiple characteristics (image, location -> characteristic 1, characteristic 2, ...)\n",
    "df = pd.read_sql_query(\"SELECT image, location FROM flakes\", con)\n",
    "\n",
    "# print database\n",
    "#df.style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ce522ea9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "</style>\n",
       "<table id=\"T_382fb\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th id=\"T_382fb_level0_col0\" class=\"col_heading level0 col0\" >image</th>\n",
       "      <th id=\"T_382fb_level0_col1\" class=\"col_heading level0 col1\" >mag</th>\n",
       "      <th id=\"T_382fb_level0_col2\" class=\"col_heading level0 col2\" >thickness</th>\n",
       "      <th id=\"T_382fb_level0_col3\" class=\"col_heading level0 col3\" >l_side</th>\n",
       "      <th id=\"T_382fb_level0_col4\" class=\"col_heading level0 col4\" >location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_382fb_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_382fb_row0_col0\" class=\"data row0 col0\" >Images/GRAPHENE/flg_3/flg_a_20x.jpg</td>\n",
       "      <td id=\"T_382fb_row0_col1\" class=\"data row0 col1\" >20</td>\n",
       "      <td id=\"T_382fb_row0_col2\" class=\"data row0 col2\" >bi</td>\n",
       "      <td id=\"T_382fb_row0_col3\" class=\"data row0 col3\" >116.504854</td>\n",
       "      <td id=\"T_382fb_row0_col4\" class=\"data row0 col4\" >UM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_382fb_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_382fb_row1_col0\" class=\"data row1 col0\" >Images/GRAPHENE/bl_4/bl_b_20x.jpg</td>\n",
       "      <td id=\"T_382fb_row1_col1\" class=\"data row1 col1\" >20</td>\n",
       "      <td id=\"T_382fb_row1_col2\" class=\"data row1 col2\" >bi</td>\n",
       "      <td id=\"T_382fb_row1_col3\" class=\"data row1 col3\" >166.990291</td>\n",
       "      <td id=\"T_382fb_row1_col4\" class=\"data row1 col4\" >ML</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_382fb_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_382fb_row2_col0\" class=\"data row2 col0\" >Images/GRAPHENE/ml_4/ml_a_20x_edgesize.jpg</td>\n",
       "      <td id=\"T_382fb_row2_col1\" class=\"data row2 col1\" >20</td>\n",
       "      <td id=\"T_382fb_row2_col2\" class=\"data row2 col2\" >mono</td>\n",
       "      <td id=\"T_382fb_row2_col3\" class=\"data row2 col3\" >131.553398</td>\n",
       "      <td id=\"T_382fb_row2_col4\" class=\"data row2 col4\" >UL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_382fb_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_382fb_row3_col0\" class=\"data row3 col0\" >Images/GRAPHENE/ml_4/ml_a_20x_contours.jpg</td>\n",
       "      <td id=\"T_382fb_row3_col1\" class=\"data row3 col1\" >20</td>\n",
       "      <td id=\"T_382fb_row3_col2\" class=\"data row3 col2\" >mono</td>\n",
       "      <td id=\"T_382fb_row3_col3\" class=\"data row3 col3\" >142.233010</td>\n",
       "      <td id=\"T_382fb_row3_col4\" class=\"data row3 col4\" >UL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7faad3c24790>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query by l_side length (l_side < # -> any 50 < # < 200)\n",
    "df = pd.read_sql_query(\"SELECT * FROM flakes WHERE l_side > 100\", con)\n",
    "\n",
    "# print database\n",
    "df.style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6408247c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "</style>\n",
       "<table id=\"T_4f40f\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th id=\"T_4f40f_level0_col0\" class=\"col_heading level0 col0\" >image</th>\n",
       "      <th id=\"T_4f40f_level0_col1\" class=\"col_heading level0 col1\" >mag</th>\n",
       "      <th id=\"T_4f40f_level0_col2\" class=\"col_heading level0 col2\" >thickness</th>\n",
       "      <th id=\"T_4f40f_level0_col3\" class=\"col_heading level0 col3\" >l_side</th>\n",
       "      <th id=\"T_4f40f_level0_col4\" class=\"col_heading level0 col4\" >location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_4f40f_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_4f40f_row0_col0\" class=\"data row0 col0\" >Images/GRAPHENE/bl_4/bl_b_20x.jpg</td>\n",
       "      <td id=\"T_4f40f_row0_col1\" class=\"data row0 col1\" >20</td>\n",
       "      <td id=\"T_4f40f_row0_col2\" class=\"data row0 col2\" >bi</td>\n",
       "      <td id=\"T_4f40f_row0_col3\" class=\"data row0 col3\" >166.990291</td>\n",
       "      <td id=\"T_4f40f_row0_col4\" class=\"data row0 col4\" >ML</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_4f40f_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_4f40f_row1_col0\" class=\"data row1 col0\" >Images/GRAPHENE/ml_4/ml_a_20x_dilate3.jpg</td>\n",
       "      <td id=\"T_4f40f_row1_col1\" class=\"data row1 col1\" >20</td>\n",
       "      <td id=\"T_4f40f_row1_col2\" class=\"data row1 col2\" >mono</td>\n",
       "      <td id=\"T_4f40f_row1_col3\" class=\"data row1 col3\" >55.825243</td>\n",
       "      <td id=\"T_4f40f_row1_col4\" class=\"data row1 col4\" >ML</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_4f40f_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_4f40f_row2_col0\" class=\"data row2 col0\" >Images/GRAPHENE/ml_4/ml_a_20x_dilate5.jpg</td>\n",
       "      <td id=\"T_4f40f_row2_col1\" class=\"data row2 col1\" >20</td>\n",
       "      <td id=\"T_4f40f_row2_col2\" class=\"data row2 col2\" >mono</td>\n",
       "      <td id=\"T_4f40f_row2_col3\" class=\"data row2 col3\" >55.825243</td>\n",
       "      <td id=\"T_4f40f_row2_col4\" class=\"data row2 col4\" >ML</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7faad4d1e400>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query by location (UL -> any location)\n",
    "df = pd.read_sql_query(\"SELECT * FROM flakes WHERE location = 'ML'\", con)\n",
    "\n",
    "# print database\n",
    "df.style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "b63bd944",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query by thickness (mono -> any thickness)\n",
    "df = pd.read_sql_query(\"SELECT * FROM flakes WHERE thickness = 'mono'\", con)\n",
    "\n",
    "# print database\n",
    "#df.style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "272830d4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}