S12-Python-for-Interacting-with-SQLite-and-PostGreSQL-Databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Python Mega Course: Build 10 Real World Applications\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook contains the source code for the video lectures of Section 12 of [The Python Mega Course: Build 10 Real World Applciations](https://www.udemy.com/the-python-mega-course/?couponCode=GITHEADSECTION)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Section 12: Python for Interacting with SQLite and PostGreSQL Databases\n",
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Introduction to \"Python with Databases\"](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775356?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An introduction on how Python is used in interaction with databases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Connecting and Inserting Data to SQLite via Python](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775360?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code creates a `lite.db` database file, and a `store` table in that database. Then, it inserts some data in `store` via the `insert` function and then it prints out those data via the `view` function. If the `lite.db` exists, then no new database is created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Water Glass', 10, 5.0), ('Water Glass', 10, 5.0), ('Water Glass', 10, 5.0), ('Water Glass', 10, 5.0)]\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "def create_table():\n",
    "    conn = sqlite3.connect(\"lite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)\")\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def insert(item, quantity, price):\n",
    "    conn = sqlite3.connect(\"lite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"INSERT INTO store VALUES (?,?,?)\",(item, quantity, price))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def view():\n",
    "    conn = sqlite3.connect(\"lite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM store\")\n",
    "    rows = cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "    \n",
    "create_table()    \n",
    "insert(\"Water Glass\", 10, 5)\n",
    "print(view())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: Anytime you execute the above code, a new row will be inserted by the `insert` function into the database table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Selecting, Inserting, Deleting, and Updating SQLite Records](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775362?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code below connects to a sqlite3 database, and does various operations with the database table such as updating and deleting rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First print: [('Water Glass', 10, 5.0), ('Milk', 11, 6.0)]\n",
      "Second print: [('Water Glass', 10, 5.0), ('Milk', 99, 33.0)]\n",
      "Third print: [('Milk', 99, 33.0)]\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "def create_table():\n",
    "    conn = sqlite3.connect(\"newlite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)\")\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def insert(item, quantity, price):\n",
    "    conn = sqlite3.connect(\"newlite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"INSERT INTO store VALUES (?,?,?)\",(item, quantity, price))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def view():\n",
    "    conn = sqlite3.connect(\"newlite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM store\")\n",
    "    rows = cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def delete(item):\n",
    "    conn = sqlite3.connect(\"newlite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"DELETE FROM store WHERE item=?\",(item,))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    \n",
    "def update(quantity, price, item):\n",
    "    conn = sqlite3.connect(\"newlite.db\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"UPDATE store SET quantity=?, price=? WHERE item=?\", (quantity, price, item))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    \n",
    "create_table()    \n",
    "insert(\"Water Glass\", 10, 5)\n",
    "insert(\"Milk\", 11, 6)\n",
    "print(\"First print:\", view())\n",
    "update(99,33,\"Milk\")\n",
    "print(\"Second print:\", view())\n",
    "delete(\"Water Glass\")\n",
    "print(\"Third print:\", view())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Introduction to PostGreSQL Psycopg2](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775364?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An introduction to using PostGreSQL in interaction with Python. The interaction is possible via the Python psycopg2 library which can be installed with `pip install psycopg2`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Selecting, Inserting, Deleting, and Updating PostGreSQL Records](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775374?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code does various operations with a PostGreSQL datbase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE**: For the code to work you need to have PostGreSQL installed, and you should create a database there. Then, update the string inside psycopg2.connect() to reflect your credentials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First print: [('Water Glass', 10, 5.0), ('Milk', 11, 6.0)]\n",
      "Second print: [('Water Glass', 10, 5.0), ('Milk', 99, 33.0)]\n",
      "Third print: [('Milk', 99, 33.0)]\n"
     ]
    }
   ],
   "source": [
    "import psycopg2\n",
    "\n",
    "def create_table():\n",
    "    conn = psycopg2.connect(\"dbname='database1' user='postgres' password='postgres123' host='localhost' port='5432'\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)\")\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def insert(item, quantity, price):\n",
    "    conn = psycopg2.connect(\"dbname='database1' user='postgres' password='postgres123' host='localhost' port='5432'\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"INSERT INTO store VALUES ('%s','%s','%s')\" % (item, quantity, price))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def view():\n",
    "    conn = psycopg2.connect(\"dbname='database1' user='postgres' password='postgres123' host='localhost' port='5432'\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM store\")\n",
    "    rows = cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def delete(item):\n",
    "    conn = psycopg2.connect(\"dbname='database1' user='postgres' password='postgres123' host='localhost' port='5432'\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"DELETE FROM store WHERE item=%s\",(item,))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    \n",
    "def update(quantity, price, item):\n",
    "    conn = psycopg2.connect(\"dbname='database1' user='postgres' password='postgres123' host='localhost' port='5432'\")\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"UPDATE store SET quantity=%s, price=%s WHERE item=%s\", (quantity, price, item))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    \n",
    "create_table()    \n",
    "insert(\"Water Glass\", 10, 5)\n",
    "insert(\"Milk\", 11, 6)\n",
    "print(\"First print:\", view())\n",
    "update(99,33,\"Milk\")\n",
    "print(\"Second print:\", view())\n",
    "delete(\"Water Glass\")\n",
    "print(\"Third print:\", view())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}