{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting Started With Python and Postgresql\n",
    "This python package (snowexsql) is python package that provides easy access to the entire postgresql database. Here is how you establish a connection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the functions we need from the python snowexsql library\n",
    "from snowexsql.db import get_db\n",
    "\n",
    "# If you followed the install instructions the database name should be snowex\n",
    "db_name = 'db.snowexdata.org/snowex'\n",
    "\n",
    "# Using the function get_db, we receive 2 ways to interact with the database\n",
    "engine, session = get_db(db_name, credentials='./credentials.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using the Engine Object\n",
    "The `engine` object returned from the `get_db` function is not used much in the snowexsql library. It does allow you to use typical SQL \n",
    "strings to interact with the database. \n",
    "\n",
    "**Note**: Users who have used python + SQL before will likely be more familiar with this approach. Additionally those who don't know python but know SQL will also be more comfortable here.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8C18, 1C1, Open 6, 2S4, 2S7, 6N36, Skyway Open, 6S22, 2S6, 9S40, 9N30, 1N1, 9N47, 1N3, 9N29, 8N45, 9N39, 8C35, 1C5, 6N31, 3S33, 5N24, 3N53, 1C7, 7N40, SNB 2, 8N55, County Line Open, 6N18, 5C20, 5N10, 8N52, 2N21, 1N5, 6S32, GML, 1S1, 2C3, 8S41, JPL 1, Forest 14, 6S53, C1, 8C11, Open, 2C13, Open Flat, Joe Wright, 8N54, 7S23, 6N16, 8C31, Open 2, 5S42, Mesa West Open, 8N34, Upper, 6C37, 5S49, 9S39, 1S17, 2S35, 1C8, BA Flux Tower, 8N9, FL2A, 5S31, 8N38, 6S26, Caples Lake, Open 4, 2S46, 8S28, 8C36, 5N15, 2C33, 6N46, 3S14, 6S15, 8N35, Skyway Tree, TLSFL2A, 2N13, 3N26, 1S8, Saddle, Banner Open, 3S47, 3S52, 4N2, 2S9, 9S51, Trench 13, 6C24, Panorama Dome, 5C27, Senator Beck, 2S25, Swamp Angel, FL1B, 9N42, 1N6, JPL 2, 2S11, 2N8, 9N59, 1N7, 8C25, 3S5, 8N58, 9C28, 2S10, 2S45, 5C21, 5S24, 7S50, 2N49, 8C22, Forest 13, 2N14, 9C17, 5N19, 2C9, 5N50, 2N4, Mores Creek Summit, LDP Tree, 1C14, 2C2, CUES, SNB 1, 8S18, Michigan River, 7C15, Irwin Barn, 2S20, 1S12, 6S44, 2S48, 9C19, 9N43, 9N56, 9N44, 8S30, 8C26, 7N57, 3S38, 9C16, 5N11, 6S34, 4N27, Forest 12, 5S21, 2C12, 2N12, 9C23, 1S2, 3N22, 2N48, 2S3, 5S29, 8N25, 2C4, Forest North, 2C6, Forest, 2S37, 2S16, HQ Met Station, 1S13, Alta Collins, 4C30, County Line Tree, Bogus Upper, 2S36, 6S19, 8N37, Forest Flat, Atwater, LDP Open, 9N28, 1N23, Gothic, 6N17, 8N51, 1N20, Forest South, 5N41, 8C32, 5S43, 8N33, 5N32, 6C10, Tower 4, Banner Snotel, 2S27, Mesa West Trees, 6C34, Aspen, 8C29\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# First connect to the db\n",
    "conn = engine.connect()\n",
    "\n",
    "# Form a typical SQL query and use python to populate the table name\n",
    "qry = \"SELECT DISTINCT site_id FROM sites\"\n",
    "\n",
    "# Then we execute the sql command and collect the results\n",
    "results = conn.execute(qry)\n",
    "\n",
    "# Create a nice readable string to print the site names using python \n",
    "out = ', '.join((row['site_id'] for row in results))\n",
    "\n",
    "# Print it with a line return for readability\n",
    "print(out + '\\n')\n",
    "\n",
    "# Close your connections to avoid hanging transactions \n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using the Session Object\n",
    "The session object allows a user to interact with the database in a pure python form. This approach is called Object Relational Mapping (ORM). This is important because its super handy when your are googling for help. \n",
    "\n",
    "ORM *maps* the database tables and their columns to a python class and attributes. Here is how it works:\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8C18, 1C1, Open 6, 2S4, 2S7, 6N36, Skyway Open, 6S22, 2S6, 9S40, 9N30, 1N1, 9N47, 1N3, 9N29, 8N45, 9N39, 8C35, 1C5, 6N31, 3S33, 5N24, 3N53, 1C7, 7N40, SNB 2, 8N55, County Line Open, 6N18, 5C20, 5N10, 8N52, 2N21, 1N5, 6S32, GML, 1S1, 2C3, 8S41, JPL 1, Forest 14, 6S53, C1, 8C11, Open, 2C13, Open Flat, Joe Wright, 8N54, 7S23, 6N16, 8C31, Open 2, 5S42, Mesa West Open, 8N34, Upper, 6C37, 5S49, 9S39, 1S17, 2S35, 1C8, BA Flux Tower, 8N9, FL2A, 5S31, 8N38, 6S26, Caples Lake, Open 4, 2S46, 8S28, 8C36, 5N15, 2C33, 6N46, 3S14, 6S15, 8N35, Skyway Tree, TLSFL2A, 2N13, 3N26, 1S8, Saddle, Banner Open, 3S47, 3S52, 4N2, 2S9, 9S51, Trench 13, 6C24, Panorama Dome, 5C27, Senator Beck, 2S25, Swamp Angel, FL1B, 9N42, 1N6, JPL 2, 2S11, 2N8, 9N59, 1N7, 8C25, 3S5, 8N58, 9C28, 2S10, 2S45, 5C21, 5S24, 7S50, 2N49, 8C22, Forest 13, 2N14, 9C17, 5N19, 2C9, 5N50, 2N4, Mores Creek Summit, LDP Tree, 1C14, 2C2, CUES, SNB 1, 8S18, Michigan River, 7C15, Irwin Barn, 2S20, 1S12, 6S44, 2S48, 9C19, 9N43, 9N56, 9N44, 8S30, 8C26, 7N57, 3S38, 9C16, 5N11, 6S34, 4N27, Forest 12, 5S21, 2C12, 2N12, 9C23, 1S2, 3N22, 2N48, 2S3, 5S29, 8N25, 2C4, Forest North, 2C6, Forest, 2S37, 2S16, HQ Met Station, 1S13, Alta Collins, 4C30, County Line Tree, Bogus Upper, 2S36, 6S19, 8N37, Forest Flat, Atwater, LDP Open, 9N28, 1N23, Gothic, 6N17, 8N51, 1N20, Forest South, 5N41, 8C32, 5S43, 8N33, 5N32, 6C10, Tower 4, Banner Snotel, 2S27, Mesa West Trees, 6C34, Aspen, 8C29\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Import the table classes from our data module which is where our ORM classes are defined \n",
    "from  snowexsql.data import SiteData, PointData, LayerData, ImageData\n",
    "\n",
    "# Form the query to receive all the site_id from the sites table\n",
    "qry = session.query(SiteData.site_id).distinct()\n",
    "\n",
    "# Execute the query and collect the result\n",
    "results = qry.all()\n",
    "\n",
    "# Form a nice string for readability\n",
    "out = ', '.join([row[0] for row in list(results)])\n",
    "\n",
    "# Print it with a line return for readability\n",
    "print(out + '\\n')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close your session to avoid hanging transactions\n",
    "session.close()"
   ]
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}