{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "73fd11d9-e1ef-4901-acc6-bb33d3a7c9f8",
   "metadata": {},
   "source": [
    "# Object-Relational Mapping (ORM):<br>The `customer` Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2974b3e0-c870-44c6-ad5a-9e68940b14db",
   "metadata": {},
   "source": [
    "## Imports from `sqlalchemy`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6ebe353-effd-4add-8f5b-cfb5ec4a8583",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine, inspect\n",
    "from sqlalchemy import MetaData, Table, Column, Integer, String\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f294fdd-afc2-447c-a4ae-517108cfd1f6",
   "metadata": {},
   "source": [
    "## Create a session to interact with the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31b20878-5f90-4b25-a048-5c25f80a1934",
   "metadata": {},
   "outputs": [],
   "source": [
    "str = 'mysql+mysqlconnector://root:seekrit@localhost/customer'\n",
    "engine = create_engine(str)\n",
    "session = Session(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a9beff4-f37d-4703-81f7-7100bd42b787",
   "metadata": {},
   "source": [
    "## Prepare to map Python objects to database tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53cf9d4b-275b-4ff9-97a1-0139f3a65744",
   "metadata": {},
   "outputs": [],
   "source": [
    "base = automap_base()\n",
    "base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5a24610-7c5f-4f91-a065-ea6c74c68e36",
   "metadata": {},
   "source": [
    "## Drop the person table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d7b6727-9f0f-468a-8ea6-d0f02a18ac6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    base.classes.person.__table__.drop(engine)\n",
    "    print('Person table dropped.')\n",
    "except:\n",
    "    print('Person table did not exist.')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "729cc456-d65f-4470-a44d-2f905f8d915b",
   "metadata": {},
   "source": [
    "## Metadata for table creation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed2dced7-9ae2-4ab1-a2ba-04a258982b1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata = MetaData(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "602e99cc-57d6-4ada-8f06-b7ee6b9657a6",
   "metadata": {},
   "source": [
    "## Create the person table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19de85c8-c6a9-407c-b1a9-efcb25a44bc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "Table('person', metadata,\n",
    "      Column('id', Integer, primary_key=True, nullable=False),\n",
    "      Column('first_name', String(32), nullable=False),\n",
    "      Column('last_name', String(32), nullable=False),\n",
    "      Column('username', String(16), nullable=False),\n",
    "      Column('email', String(32), nullable=False),\n",
    "      Column('address', String(32), nullable=False),\n",
    "      Column('town', String(32), nullable=False)\n",
    "     )\n",
    "\n",
    "metadata.create_all()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b12dc065-96c9-4abb-9f1a-31f2660b9cf9",
   "metadata": {},
   "source": [
    "## Prepare to map Python objects to database tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19327e62-c29d-4ef6-9f1d-ff7c3965390a",
   "metadata": {},
   "outputs": [],
   "source": [
    "base = automap_base()\n",
    "base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59974ad9-bab0-4de3-85a0-a52c2e318414",
   "metadata": {},
   "source": [
    "## Map the `person` table to the `Person` class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4738d27b-eaa3-469f-9224-ad21cdf74343",
   "metadata": {},
   "outputs": [],
   "source": [
    "Person = base.classes.person"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "770189c3-4529-4135-8dd4-0418b98d1fb4",
   "metadata": {},
   "source": [
    "## Create two `Person` objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0db1c137-dafc-4c6b-b3d8-802c089fcea1",
   "metadata": {},
   "outputs": [],
   "source": [
    "person_1 = Person(first_name = 'Toby', \n",
    "                  last_name = 'Miller', \n",
    "                  username = 'tmiller', \n",
    "                  email = 'tmiller@example.com', \n",
    "                  address = '1662 Kinney Street',\n",
    "                  town = 'Wolfden'\n",
    "                 )\n",
    "\n",
    "person_2 = Person(first_name = 'Scott', \n",
    "                  last_name = 'Harvey', \n",
    "                  username = 'scottharvey', \n",
    "                  email = 'scottharvey@example.com', \n",
    "                  address = '424 Patterson Street',\n",
    "                  town = 'Beckinsdale'\n",
    "                 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18c5c6e8-499b-4fdf-b604-37d50b81ba19",
   "metadata": {},
   "outputs": [],
   "source": [
    "for p in [person_1, person_2]:\n",
    "    print(p.username, p.first_name, p.last_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92f40d40-da7b-424e-aa66-7025aff57695",
   "metadata": {},
   "source": [
    "## Insert the Person objects into table `person`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af6ba030-beae-4e33-a355-705dacef9968",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add_all([person_1, person_2])\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a189331-ddbd-4307-bdd3-b5200906f78b",
   "metadata": {},
   "source": [
    "## Close."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce920592-df23-46f1-bead-52e119c01112",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()\n",
    "engine.connect().close()\n",
    "engine.dispose()"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}