{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fe8df487-4770-471d-ab27-a07a5e5f02f5",
   "metadata": {},
   "source": [
    "# Connect to a Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c2769b7-ab08-4ad8-af36-6257935e2508",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from configparser import ConfigParser\n",
    "from mysql.connector import MySQLConnection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e61558a-df66-435a-8078-f9d41157f72b",
   "metadata": {},
   "source": [
    "### Bad practice!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ade513f4-2cc9-42c0-92e3-eb81214cfb12",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = MySQLConnection(host='localhost',\n",
    "                       database='school',\n",
    "                       user='root',\n",
    "                       password='seekrit')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b8f379d-d12f-40b5-a944-7ccc7a07eff5",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4570b06b-0b03-49a8-9b81-1534fda336c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f918f38-d76a-420b-9d89-6cc785e53915",
   "metadata": {},
   "source": [
    "### Instead, use a configuration file.\n",
    "\n",
    "```\n",
    "[mysql]\n",
    "host = localhost\n",
    "database = school\n",
    "user = root\n",
    "password = seekrit\n",
    "```\n",
    "**`school.ini`**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcdf6e11-2623-4500-ae3f-9fa35581aaf3",
   "metadata": {},
   "source": [
    "### Use the configuration parser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92a25dcc-def5-4889-89ea-769317435b24",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_config(config_file = 'config.ini', section = 'mysql'):\n",
    "    \"\"\"\n",
    "    Read a configuration file config_file and the given section. \n",
    "    If successful, return the configuration as a dictionary,\n",
    "    else raise an exception. \n",
    "    \"\"\"\n",
    "    parser = ConfigParser()\n",
    "    \n",
    "    # Does the configuration file exist?\n",
    "    if os.path.isfile(config_file):\n",
    "        parser.read(config_file)\n",
    "    else:\n",
    "        raise Exception(f\"Configuration file '{config_file}' \"\n",
    "                        \"doesn't exist.\")\n",
    "    \n",
    "    config = {}\n",
    "    \n",
    "    # Does it have the right section?\n",
    "    if parser.has_section(section):\n",
    "        \n",
    "        # Parse the configuration file.\n",
    "        items = parser.items(section)\n",
    "        \n",
    "        # Construct the parameter dictionary.\n",
    "        for item in items:\n",
    "            config[item[0]] = item[1]\n",
    "            \n",
    "    else:\n",
    "        raise Exception(f\"Section '{section}' missing \"\n",
    "                        f\"in configuration file '{config_file}'.\")\n",
    "    \n",
    "    return config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c47ee4dc-7edc-43e9-9d13-bf58202492c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_config = read_config('school.ini')\n",
    "db_config"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8abfafa0-66e9-4761-9517-32a70189f80a",
   "metadata": {},
   "source": [
    "### Then the connection code is simpler and doesn’t expose the password."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f771bb7-d8a6-43be-a48b-109a48ef607b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_connection(config_file = 'config.ini', section = 'mysql'):\n",
    "    \"\"\"\n",
    "    Make a connection to a database with the configuration file\n",
    "    config_file and the given section. If successful, return \n",
    "    the connection, else raise an exception.\n",
    "    \"\"\"\n",
    "    try:\n",
    "        db_config = read_config(config_file, section)            \n",
    "        conn = MySQLConnection(**db_config)\n",
    "        \n",
    "        if conn.is_connected():\n",
    "            return conn\n",
    "                \n",
    "    except Error as e:\n",
    "        raise Exception(f'Connection failed.\\n{e}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9c58069-a218-4f8a-9a9f-8e2a69b6a5dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('school.ini')\n",
    "conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "236ec312-0a65-4f43-9183-3272b8ad4133",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62abbd4b-1ed0-4bb8-846d-0566024b425a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM class\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "rows  = cursor.fetchall()\n",
    "count = cursor.rowcount\n",
    "\n",
    "print(f'Fetched {count} rows.')\n",
    "print()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a57b683-250a-4219-8947-f320a9a48b70",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66da942c-1e7e-4bf3-beb9-d6c817066aa9",
   "metadata": {},
   "source": [
    "### It will be easy to switch to another database, even on a different server.\n",
    "\n",
    "```\n",
    "[mysql]\n",
    "host = IES-ADS-ClassDB.sjsu.edu\n",
    "database = school\n",
    "user = student\n",
    "password = seekrit\n",
    "```\n",
    "\n",
    "**`school-remote.ini`**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28817a8c-9549-4977-a25a-dd3e6344e508",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('school-remote.ini')\n",
    "conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c11e92f-17ee-4cf6-96cc-ee5a92d0fc08",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38cc2a95-de4a-4443-a7d5-9cf93fe057bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM student\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "rows  = cursor.fetchall()\n",
    "count = cursor.rowcount\n",
    "\n",
    "print(f'Fetched {count} rows.')\n",
    "print()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "470614bf-0202-4e8f-9491-8eeb0d47fefe",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc8a4a20-88a3-4910-b306-25d19f48ab99",
   "metadata": {},
   "source": [
    "### Using a configuration file to make a database connection is code we’ll use often, so let’s put it into a separate Python database utilities file **`DATA225utils.py`** containing the two functions. We can then import the file whenever necessary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d7b23b5-d1bf-47d6-b58e-3412b02aaf7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copyright (c) 2023 by Ronald Mak"
   ]
  }
 ],
 "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
}