SQLite3.ipynb

{
 "metadata": {
  "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.8.6-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Python version: 3.8.6\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "import sqlite3\n",
    "print(\"Python version:\", sys.version[:5])"
   ]
  },
  {
   "source": [
    "# Creating a table in SQLite3"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open connection\n",
    "con = sqlite3.connect('db/base.db')\n",
    "\n",
    "# Defining cursor\n",
    "cur = con.cursor()\n",
    "\n",
    "# Defining query\n",
    "sql = \"\"\"\n",
    "CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n",
    "                    name TEXT NOT NULL,\n",
    "                    phone TEXT NOT NULL,\n",
    "                    email TEXT UNIQUE NOT NULL)\"\"\"\n",
    "# Attach sql to cursor                   \n",
    "cur.execute(sql)\n",
    "\n",
    "# Executes the sql in the db\n",
    "con.commit()\n",
    "\n",
    "# Close connection\n",
    "con.close()\n"
   ]
  },
  {
   "source": [
    "# Insert"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect('db/base.db')\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "sql = \"\"\"\n",
    "INSERT INTO users (name, phone, email)\n",
    "    VALUES(\"João Sousa\", \"21900000000\", \"joaovfsousa@live.com\")\n",
    "\"\"\"\n",
    "\n",
    "cur.execute(sql)\n",
    "\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "source": [
    "# Defining some function to help"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def db_insert(name, phone, email):\n",
    "    return f\"\"\"\n",
    "    INSERT INTO users (name, phone, email)\n",
    "        VALUES(\"{name}\", \"{phone}\", \"{email}\")\n",
    "    \"\"\"\n",
    "\n",
    "con = sqlite3.connect('db/base.db')\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "cur.execute(db_insert(\"marivaldo\", \"999999998\", \"marivaldo@gmail.com\"))\n",
    "\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "source": [
    "# Update"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def db_update(name, email):\n",
    "    return f\"\"\"\n",
    "           UPDATE users SET name = \"{name}\" WHERE email = \"{email}\"\n",
    "           \"\"\"\n",
    "\n",
    "con = sqlite3.connect('db/base.db')\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "cur.execute(db_update(\"Matheus\", \"joaovfsousa@live.com\"))\n",
    "\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "source": [
    "# Delete"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def db_delete(email):\n",
    "    return f\"\"\"\n",
    "            DELETE FROM users WHERE email=\"{email}\"\n",
    "            \"\"\"\n",
    "\n",
    "con = sqlite3.connect('db/base.db')\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "cur.execute(db_delete(\"joaovfsousa@live.com\"))\n",
    "\n",
    "con.commit()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "source": [
    "# Select"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(2, 'João', '21994677053', 'joaovfsousa@gmail.com')\n"
     ]
    }
   ],
   "source": [
    "def db_select(data, field):\n",
    "    return f\"\"\"\n",
    "            SELECT id, name, phone, email\n",
    "            FROM users\n",
    "            WHERE {field}={data}\n",
    "            \"\"\"\n",
    "\n",
    "con = sqlite3.connect('db/base.db')\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "cur.execute(db_select(\"2\", \"id\"))\n",
    "\n",
    "#con.commit() not used once we want to return a value\n",
    "\n",
    "#returns one line from the result of the query\n",
    "data = cur.fetchone()\n",
    "\n",
    "con.close()\n",
    "\n",
    "print(data)\n"
   ]
  },
  {
   "source": [
    "# Aplying decorators"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def commit_close(func):\n",
    "    def decorator(*args):\n",
    "        con = sqlite3.connect('db/base.db')\n",
    "        cur = con.cursor()\n",
    "        sql = func(*args)\n",
    "        cur.execute(sql)\n",
    "        con.commit()\n",
    "        con.close()\n",
    "    return decorator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "@commit_close\n",
    "def db_insert(name, phone, email):\n",
    "    return f\"\"\"\n",
    "    INSERT INTO users (name, phone, email)\n",
    "        VALUES(\"{name}\", \"{phone}\", \"{email}\")\n",
    "    \"\"\"\n",
    "@commit_close\n",
    "def db_update(name, email):\n",
    "    return f\"\"\"\n",
    "           UPDATE users SET name = \"{name}\" WHERE email = \"{email}\"\n",
    "           \"\"\"\n",
    "@commit_close\n",
    "def db_delete(email):\n",
    "    return f\"\"\"\n",
    "            DELETE FROM users WHERE email=\"{email}\"\n",
    "            \"\"\"\n",
    "\n",
    "def db_select(data, field):\n",
    "    sql = f\"\"\"\n",
    "            SELECT id, name, phone, email\n",
    "            FROM users\n",
    "            WHERE {field}={data}\n",
    "            \"\"\"\n",
    "\n",
    "    con = sqlite3.connect('db/base.db')\n",
    "    cur = con.cursor()\n",
    "    cur.execute(sql)\n",
    "    data = cur.fetchall()\n",
    "    con.close()\n",
    "    return data\n"
   ]
  },
  {
   "source": [
    "# Using the decorated functions"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_insert(\"Regis\", \"98989898\", \"regis_@gmail.com\")\n",
    "db_insert(\"Fabricio\", \"98989898\", \"fabricio_@gmail.com\")\n",
    "db_insert(\"Mazinho\", \"98989898\", \"mazinho_@gmail.com\")\n",
    "db_insert(\"Diego\", \"98989898\", \"diego_@gmail.com\")\n",
    "db_insert(\"Ricardo\", \"98989898\", \"ricardo_@gmail.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(5, 'Regis', '98989898', 'regis_@gmail.com'),\n",
       " (6, 'Fabricio', '98989898', 'fabricio_@gmail.com'),\n",
       " (7, 'Mazinho', '98989898', 'mazinho_@gmail.com'),\n",
       " (8, 'Diego', '98989898', 'diego_@gmail.com'),\n",
       " (9, 'Ricardo', '98989898', 'ricardo_@gmail.com')]"
      ]
     },
     "metadata": {},
     "execution_count": 30
    }
   ],
   "source": [
    "db_select(\"98989898\", \"phone\")"
   ]
  }
 ]
}