12_Working _With_Database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- SQLite in combination with Python's sqlite3 interface. \n",
    "- SQLite is a single file relational database bundled with most standard Python installs. \n",
    "- SQLite is often the technology of choice for small applications, \n",
    "- particularly those of embedded systems and devices like phones and tablets, smart appliances, and IOT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "#connect to database\n",
    "con = sqlite3.connect('PQRS.sqlite3')\n",
    "\n",
    "# generate a cursor\n",
    "cur = con.cursor()\n",
    "\n",
    "# define a SQL query string\n",
    "create_table_query = \"\"\" CREATE TABLE product (\n",
    "                        name text NOT NULL, \n",
    "                        price text NOT NULL\n",
    "                    )\"\"\"\n",
    "\n",
    "# execute the query\n",
    "cur.execute(create_table_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating a database\n",
    "import sqlite3  \n",
    "con = sqlite3.connect('PQRS.sqlite3')  \n",
    "cur = con.cursor()\n",
    "create_table_query = \"\"\" CREATE TABLE product (\n",
    "                    name text NOT NULL, \n",
    "                    price text NOT NULL\n",
    "                )\"\"\"\n",
    "\n",
    "# take user input\n",
    "name = input(\"Enter name\")\n",
    "price = input(\"Enter price\")\n",
    "\n",
    "# generate query based on user input\n",
    "add_data_query = \"\"\"INSERT INTO product (name, price) VALUES ('{}', '{}')\"\"\".format(name, price)\n",
    "\n",
    "# execute the query\n",
    "cur.execute(create_table_query)\n",
    "cur.execute(add_data_query)\n",
    "\n",
    "# commit or save the execution\n",
    "con.commit()\n",
    "\n",
    "#close the cursor\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3  \n",
    "con = sqlite3.connect('PQRS.sqlite3')  \n",
    "cur = con.cursor()\n",
    "add_data_query = \"\"\"INSERT INTO product (name, price) VALUES ('Test Product 123', '50000')\"\"\"\n",
    "cur.execute(add_data_query)\n",
    "con.commit()\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3  \n",
    "con = sqlite3.connect('PQRS.sqlite3')  \n",
    "cur = con.cursor()\n",
    "con.commit()\n",
    "cur.execute(\"SELECT * FROM product\")\n",
    "print(cur.fetchone())  #fetch just one item (this behaves as a generator)\n",
    "\n",
    "print(cur.fetchall()) # fetch all records in the database\n",
    "\n",
    "\n",
    "print(cur.fetchmany(1)) #Fetches several rows from the resultset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Further Reading: http://stackabuse.com/a-sqlite-tutorial-with-python/?utm_content=buffer24017&utm_medium=social&utm_source=facebook.com&utm_campaign=buffer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Storing our objects in the database\n",
    "\n",
    "***[Note: Use DB Browser for sqlite to browse the database]***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "class Product:\n",
    "    def __init__(self, name, price):\n",
    "        self.name = name\n",
    "        self.price = price\n",
    "\n",
    "conn = sqlite3.connect('product.db')\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "c.execute(\"\"\"CREATE TABLE products (\n",
    "            name text,\n",
    "            price float\n",
    "            )\"\"\")\n",
    "\n",
    "\n",
    "def insert_product(prod):\n",
    "    with conn: # work with context manager so that we don't have to commit everytime\n",
    "        # paramaterized SQL query\n",
    "        c.execute(\"INSERT INTO products VALUES (:name, :price)\", {'name': prod.name, 'price': prod.price})\n",
    "\n",
    "\n",
    "def get_product_by_name(name):\n",
    "    c.execute(\"SELECT * FROM products WHERE name=:name\", {'name': name})\n",
    "    return c.fetchall()\n",
    "\n",
    "\n",
    "def update_price(prod, price):\n",
    "    with conn:\n",
    "        c.execute(\"\"\"UPDATE products SET price = :price\n",
    "                    WHERE name = :name\"\"\",\n",
    "                  {'name': prod.name, 'price': price})\n",
    "\n",
    "\n",
    "def remove_product(prod):\n",
    "    with conn:\n",
    "        c.execute(\"DELETE from products WHERE name = :name\",\n",
    "                  {'name': prod.name})\n",
    "\n",
    "p1 = Product(\"DSLR Camera\", 50000)\n",
    "p2 = Product(\"Toshiba Laptop\", 750000)\n",
    "p3 = Product(\"ADATA External HDD 1TB\", 7500)\n",
    "\n",
    "insert_product(p1)\n",
    "insert_product(p2)\n",
    "insert_product(p3)\n",
    "\n",
    "prod = get_product_by_name('DSLR Camera')\n",
    "print(prod)\n",
    "\n",
    "update_price(p2, 70000)\n",
    "\n",
    "remove_product(p3)\n",
    "\n",
    "conn.close()"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}