2023-03-13-AP-unit2-4a.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Unit 2.4a Using Programs with Data, SQLAlchemy\n",
    "> Using Programs with Data is focused on SQL and database actions.  Part A focuses on SQLAlchemy and an OOP programming style,\n",
    "- toc: true\n",
    "- image: /images/python.png\n",
    "- categories: []\n",
    "- type: ap\n",
    "- week: 26"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Database and SQLAlchemy\n",
    "> In this blog we will explore using programs with data, focused on Databases.  We will use SQLite Database to learn more about using Programs with Data.  ***Use Debugging through these examples to examine Objects created in Code***.\n",
    "\n",
    "- College Board talks about ideas like \n",
    "    - Program Usage. \"iterative and interactive way when processing information\"\n",
    "    - Managing Data.  \"classifying data are part of the process in using programs\", \"data files in a Table\"\n",
    "    - Insight \"insight and knowledge can be obtained from ...  digitally represented information\"\n",
    "    - Filter systems. 'tools for finding information and recognizing patterns\"\n",
    "    - Application. \"the preserve has two databases\", \"an employee wants to count the number of book\"\n",
    "\n",
    "- PBL, Databases, Iterative/OOP\n",
    "    - Iterative. Refers to a sequence of instructions or code being repeated until a specific end result is achieved\n",
    "    - OOP. A computer programming model that organizes software design around data, or objects, rather than functions and logic\n",
    "    - SQL. Structured Query Language, abbreviated as SQL, is a language used in programming, managing, and structuring data \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports and Flask Objects\n",
    "> Defines and key object creations\n",
    "\n",
    "- Comment on where you have observed these working?  Provide a defintion of purpose.\n",
    "    1. Flask app object\n",
    "    2. SQLAlchemy db object\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "These imports define the key objects\n",
    "\"\"\"\n",
    "\n",
    "from flask import Flask\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "\n",
    "\"\"\"\n",
    "These object and definitions are used throughout the Jupyter Notebook.\n",
    "\"\"\"\n",
    "\n",
    "# Setup of key Flask object (app)\n",
    "app = Flask(__name__)\n",
    "# Setup SQLAlchemy object and properties for the database (db)\n",
    "database = 'sqlite:///files/sqlite.db'  # path and filename of database\n",
    "app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False\n",
    "app.config['SQLALCHEMY_DATABASE_URI'] = database\n",
    "app.config['SECRET_KEY'] = 'SECRET_KEY'\n",
    "db = SQLAlchemy()\n",
    "\n",
    "\n",
    "# This belongs in place where it runs once per project\n",
    "db.init_app(app)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Model Definition\n",
    "> Define columns, initialization, and CRUD methods for users table in sqlite.db\n",
    "\n",
    "- Comment on these items in the class, purpose and defintion.\n",
    "    - class User\n",
    "    - db.Model inheritance\n",
    "    - _init_ method\n",
    "    - ```@property```, ```@<column>.setter```\n",
    "    - create, read, update, delete methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\" database dependencies to support sqlite examples \"\"\"\n",
    "import datetime\n",
    "from datetime import datetime\n",
    "import json\n",
    "\n",
    "from sqlalchemy.exc import IntegrityError\n",
    "from werkzeug.security import generate_password_hash, check_password_hash\n",
    "\n",
    "\n",
    "''' Tutorial: https://www.sqlalchemy.org/library.html#tutorials, try to get into a Python shell and follow along '''\n",
    "\n",
    "# Define the User class to manage actions in the 'users' table\n",
    "# -- Object Relational Mapping (ORM) is the key concept of SQLAlchemy\n",
    "# -- a.) db.Model is like an inner layer of the onion in ORM\n",
    "# -- b.) User represents data we want to store, something that is built on db.Model\n",
    "# -- c.) SQLAlchemy ORM is layer on top of SQLAlchemy Core, then SQLAlchemy engine, SQL\n",
    "class User(db.Model):\n",
    "    __tablename__ = 'users'  # table name is plural, class name is singular\n",
    "\n",
    "    # Define the User schema with \"vars\" from object\n",
    "    id = db.Column(db.Integer, primary_key=True)\n",
    "    _name = db.Column(db.String(255), unique=False, nullable=False)\n",
    "    _uid = db.Column(db.String(255), unique=True, nullable=False)\n",
    "    _password = db.Column(db.String(255), unique=False, nullable=False)\n",
    "    _dob = db.Column(db.Date)\n",
    "\n",
    "    # constructor of a User object, initializes the instance variables within object (self)\n",
    "    def __init__(self, name, uid, password=\"123qwerty\", dob=datetime.today()):\n",
    "        self._name = name    # variables with self prefix become part of the object, \n",
    "        self._uid = uid\n",
    "        self.set_password(password)\n",
    "        if isinstance(dob, str):  # not a date type     \n",
    "            dob = date=datetime.today()\n",
    "        self._dob = dob\n",
    "\n",
    "    # a name getter method, extracts name from object\n",
    "    @property\n",
    "    def name(self):\n",
    "        return self._name\n",
    "    \n",
    "    # a setter function, allows name to be updated after initial object creation\n",
    "    @name.setter\n",
    "    def name(self, name):\n",
    "        self._name = name\n",
    "    \n",
    "    # a getter method, extracts uid from object\n",
    "    @property\n",
    "    def uid(self):\n",
    "        return self._uid\n",
    "    \n",
    "    # a setter function, allows uid to be updated after initial object creation\n",
    "    @uid.setter\n",
    "    def uid(self, uid):\n",
    "        self._uid = uid\n",
    "        \n",
    "    # check if uid parameter matches user id in object, return boolean\n",
    "    def is_uid(self, uid):\n",
    "        return self._uid == uid\n",
    "    \n",
    "    @property\n",
    "    def password(self):\n",
    "        return self._password[0:10] + \"...\" # because of security only show 1st characters\n",
    "\n",
    "    # update password, this is conventional method used for setter\n",
    "    def set_password(self, password):\n",
    "        \"\"\"Create a hashed password.\"\"\"\n",
    "        self._password = generate_password_hash(password, method='sha256')\n",
    "\n",
    "    # check password parameter against stored/encrypted password\n",
    "    def is_password(self, password):\n",
    "        \"\"\"Check against hashed password.\"\"\"\n",
    "        result = check_password_hash(self._password, password)\n",
    "        return result\n",
    "    \n",
    "    # dob property is returned as string, a string represents date outside object\n",
    "    @property\n",
    "    def dob(self):\n",
    "        dob_string = self._dob.strftime('%m-%d-%Y')\n",
    "        return dob_string\n",
    "    \n",
    "    # dob setter, verifies date type before it is set or default to today\n",
    "    @dob.setter\n",
    "    def dob(self, dob):\n",
    "        if isinstance(dob, str):  # not a date type     \n",
    "            dob = date=datetime.today()\n",
    "        self._dob = dob\n",
    "    \n",
    "    # age is calculated field, age is returned according to date of birth\n",
    "    @property\n",
    "    def age(self):\n",
    "        today = datetime.today()\n",
    "        return today.year - self._dob.year - ((today.month, today.day) < (self._dob.month, self._dob.day))\n",
    "    \n",
    "    # output content using str(object) is in human readable form\n",
    "    # output content using json dumps, this is ready for API response\n",
    "    def __str__(self):\n",
    "        return json.dumps(self.read())\n",
    "\n",
    "    # CRUD create/add a new record to the table\n",
    "    # returns self or None on error\n",
    "    def create(self):\n",
    "        try:\n",
    "            # creates a person object from User(db.Model) class, passes initializers\n",
    "            db.session.add(self)  # add prepares to persist person object to Users table\n",
    "            db.session.commit()  # SqlAlchemy \"unit of work pattern\" requires a manual commit\n",
    "            return self\n",
    "        except IntegrityError:\n",
    "            db.session.remove()\n",
    "            return None\n",
    "\n",
    "    # CRUD read converts self to dictionary\n",
    "    # returns dictionary\n",
    "    def read(self):\n",
    "        return {\n",
    "            \"id\": self.id,\n",
    "            \"name\": self.name,\n",
    "            \"uid\": self.uid,\n",
    "            \"dob\": self.dob,\n",
    "            \"age\": self.age,\n",
    "        }\n",
    "\n",
    "    # CRUD update: updates user name, password, phone\n",
    "    # returns self\n",
    "    def update(self, name=\"\", uid=\"\", password=\"\"):\n",
    "        \"\"\"only updates values with length\"\"\"\n",
    "        if len(name) > 0:\n",
    "            self.name = name\n",
    "        if len(uid) > 0:\n",
    "            self.uid = uid\n",
    "        if len(password) > 0:\n",
    "            self.set_password(password)\n",
    "        db.session.commit()\n",
    "        return self\n",
    "\n",
    "    # CRUD delete: remove self\n",
    "    # None\n",
    "    def delete(self):\n",
    "        db.session.delete(self)\n",
    "        db.session.commit()\n",
    "        return None\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initial Data\n",
    "> Uses SQLALchemy db.create_all() to initialize rows into sqlite.db\n",
    "\n",
    "- Comment on how these work?\n",
    "    1. Create All Tables from db Object\n",
    "    2. User Object Constructors\n",
    "    3. Try / Except \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Database Creation and Testing \"\"\"\n",
    "\n",
    "\n",
    "# Builds working data for testing\n",
    "def initUsers():\n",
    "    with app.app_context():\n",
    "        \"\"\"Create database and tables\"\"\"\n",
    "        db.create_all()\n",
    "        \"\"\"Tester data for table\"\"\"\n",
    "        u1 = User(name='Thomas Edison', uid='toby', password='123toby', dob=datetime(1847, 2, 11))\n",
    "        u2 = User(name='Nikola Tesla', uid='niko', password='123niko')\n",
    "        u3 = User(name='Alexander Graham Bell', uid='lex', password='123lex')\n",
    "        u4 = User(name='Eli Whitney', uid='whit', password='123whit')\n",
    "        u5 = User(name='Indiana Jones', uid='indi', dob=datetime(1920, 10, 21))\n",
    "        u6 = User(name='Marion Ravenwood', uid='raven', dob=datetime(1921, 10, 21))\n",
    "\n",
    "\n",
    "        users = [u1, u2, u3, u4, u5, u6]\n",
    "\n",
    "        \"\"\"Builds sample user/note(s) data\"\"\"\n",
    "        for user in users:\n",
    "            try:\n",
    "                '''add user to table'''\n",
    "                object = user.create()\n",
    "                print(f\"Created new uid {object.uid}\")\n",
    "            except:  # error raised if object nit created\n",
    "                '''fails with bad or duplicate data'''\n",
    "                print(f\"Records exist uid {user.uid}, or error.\")\n",
    "                \n",
    "initUsers()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check for given Credentials in users table in sqlite.db\n",
    "> Use of ORM Query object and custom methods to identify user to credentials uid and password\n",
    "\n",
    "- Comment on purpose of following\n",
    "    1. User.query.filter_by\n",
    "    2. user.password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQLAlchemy extracts single user from database matching User ID\n",
    "def find_by_uid(uid):\n",
    "    with app.app_context():\n",
    "        user = User.query.filter_by(_uid=uid).first()\n",
    "    return user # returns user object\n",
    "\n",
    "# Check credentials by finding user and verify password\n",
    "def check_credentials(uid, password):\n",
    "    # query email and return user record\n",
    "    user = find_by_uid(uid)\n",
    "    if user == None:\n",
    "        return False\n",
    "    if (user.is_password(password)):\n",
    "        return True\n",
    "    return False\n",
    "        \n",
    "#check_credentials(\"indi\", \"123qwerty\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a new User in table in Sqlite.db\n",
    "> Uses SQLALchemy and custom user.create() method to add row.\n",
    "\n",
    "- Comment on purpose of following\n",
    "    1. user.find_by_uid() and try/except\n",
    "    2. user = User(...)\n",
    "    3. user.dob and try/except\n",
    "    4. user.create() and try/except"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inputs, Try/Except, and SQLAlchemy work together to build a valid database object\n",
    "def create():\n",
    "    # optimize user time to see if uid exists\n",
    "    uid = input(\"Enter your user id:\")\n",
    "    user = find_by_uid(uid)\n",
    "    try:\n",
    "        print(\"Found\\n\", user.read())\n",
    "        return\n",
    "    except:\n",
    "        pass # keep going\n",
    "    \n",
    "    # request value that ensure creating valid object\n",
    "    name = input(\"Enter your name:\")\n",
    "    password = input(\"Enter your password\")\n",
    "    \n",
    "    # Initialize User object before date\n",
    "    user = User(name=name, \n",
    "                uid=uid, \n",
    "                password=password\n",
    "                )\n",
    "    \n",
    "    # create user.dob, fail with today as dob\n",
    "    dob = input(\"Enter your date of birth 'YYYY-MM-DD'\")\n",
    "    try:\n",
    "        user.dob = datetime.strptime(dob, '%Y-%m-%d').date()\n",
    "    except ValueError:\n",
    "        user.dob = datetime.today()\n",
    "        print(f\"Invalid date {dob} require YYYY-mm-dd, date defaulted to {user.dob}\")\n",
    "           \n",
    "    # write object to database\n",
    "    with app.app_context():\n",
    "        try:\n",
    "            object = user.create()\n",
    "            print(\"Created\\n\", object.read())\n",
    "        except:  # error raised if object not created\n",
    "            print(\"Unknown error uid {uid}\")\n",
    "        \n",
    "create()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading users table in sqlite.db\n",
    "> Uses SQLALchemy query.all method to read data\n",
    "\n",
    "- Comment on purpose of following\n",
    "    1. User.query.all\n",
    "    2. json_ready assignment, google List Comprehension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# SQLAlchemy extracts all users from database, turns each user into JSON\n",
    "def read():\n",
    "    with app.app_context():\n",
    "        table = User.query.all()\n",
    "    json_ready = [user.read() for user in table] # \"List Comprehensions\", for each user add user.read() to list\n",
    "    return json_ready\n",
    "\n",
    "read()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hacks\n",
    "- Add this Blog to you own Blogging site.  In the Blog add notes and observations on each code cell.\n",
    "- Change blog to your own database.\n",
    "- Add additional CRUD\n",
    "    - Add Update functionality to this blog.\n",
    "    - Add Delete functionality to this blog."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "8b82d5009c68ba5675978267e2b13a671f2a7143d61273c5a3813c97e0b2493d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}