S13-App-5-Build-a-Desktop-Database-Application.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 13 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 13: Application 5: Build a Desktop Database Application\n",
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Program Demonstration](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775394?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This video lecture shows the finished version of the website running on a browser."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [User Interface Design](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775396?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This lecture shows a drawing of how the graphical interface is going to look like. Having a sketch helps later in the coding phase."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Frontend Interface](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775402?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is the code that builds the GUI. Note that the GUI doesn't have any functionality yet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from tkinter import *\n",
    "\n",
    "window=Tk()\n",
    "\n",
    "window.wm_title(\"BookStore\")\n",
    "\n",
    "l1=Label(window,text=\"Title\")\n",
    "l1.grid(row=0,column=0)\n",
    "\n",
    "l2=Label(window,text=\"Author\")\n",
    "l2.grid(row=0,column=2)\n",
    "\n",
    "l3=Label(window,text=\"Year\")\n",
    "l3.grid(row=1,column=0)\n",
    "\n",
    "l4=Label(window,text=\"ISBN\")\n",
    "l4.grid(row=1,column=2)\n",
    "\n",
    "title_text=StringVar()\n",
    "e1=Entry(window,textvariable=title_text)\n",
    "e1.grid(row=0,column=1)\n",
    "\n",
    "author_text=StringVar()\n",
    "e2=Entry(window,textvariable=author_text)\n",
    "e2.grid(row=0,column=3)\n",
    "\n",
    "year_text=StringVar()\n",
    "e3=Entry(window,textvariable=year_text)\n",
    "e3.grid(row=1,column=1)\n",
    "\n",
    "isbn_text=StringVar()\n",
    "e4=Entry(window,textvariable=isbn_text)\n",
    "e4.grid(row=1,column=3)\n",
    "\n",
    "list1=Listbox(window, height=6,width=35)\n",
    "list1.grid(row=2,column=0,rowspan=6,columnspan=2)\n",
    "\n",
    "sb1=Scrollbar(window)\n",
    "sb1.grid(row=2,column=2,rowspan=6)\n",
    "\n",
    "list1.configure(yscrollcommand=sb1.set)\n",
    "sb1.configure(command=list1.yview)\n",
    "\n",
    "b1=Button(window,text=\"View all\", width=12)\n",
    "b1.grid(row=2,column=3)\n",
    "\n",
    "b2=Button(window,text=\"Search entry\", width=12)\n",
    "b2.grid(row=3,column=3)\n",
    "\n",
    "b3=Button(window,text=\"Add entry\", width=12)\n",
    "b3.grid(row=4,column=3)\n",
    "\n",
    "b4=Button(window,text=\"Update selected\", width=12)\n",
    "b4.grid(row=5,column=3)\n",
    "\n",
    "b5=Button(window,text=\"Delete selected\", width=12)\n",
    "b5.grid(row=6,column=3)\n",
    "\n",
    "b6=Button(window,text=\"Close\", width=12)\n",
    "b6.grid(row=7,column=3)\n",
    "\n",
    "window.mainloop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Backend](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775410?start=765)\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'The Sun', 'John Smith', 1918, 913123132)]\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "def connect():\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)\")\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def insert(title, author, year, isbn):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"INSERT INTO book VALUES (NULL,?,?,?,?)\",(title,author,year,isbn))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    view()\n",
    "\n",
    "def view():\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM book\")\n",
    "    rows=cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def search(title=\"\", author=\"\", year=\"\", isbn=\"\"):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?\", (title,author,year,isbn))\n",
    "    rows=cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def delete(id):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"DELETE FROM book WHERE id=?\",(id,))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def update(id, title, author, year, isbn):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"UPDATE book SET title=?, author=?, year=?, isbn=? WHERE id=?\",(title,author,year,isbn,id))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "connect()\n",
    "insert(\"The Sun\", \"John Smith\", 1918, 913123132)\n",
    "print(view())\n",
    "\n",
    "# Other things you can try:\n",
    "#delete(3)\n",
    "#update(4,\"The moon\",\"John Smooth\",1917,99999)\n",
    "#print(search(author=\"John Smooth\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Connecting the Frontend to the Backend, Part 1](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775412?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This lecture covers the part of the process of connecting the frontend to the backend. The code is completed in the next lecture. Please see the complete version of the code in the next lecture."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Connecting the Frontend to the Backend, Part 2](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/5170648?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's the completed `frontend.py` code. To try it, put the `frontend.py` and `backend.py` (provided further below) in the same directory and run `frontend.py` with python `frontend.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# frontend.py \n",
    "\n",
    "from tkinter import *\n",
    "\n",
    "import backend\n",
    "\n",
    "def get_selected_row(event):\n",
    "    global selected_tuple\n",
    "    index = list1.curselection()[0]\n",
    "    selected_tuple = list1.get(index)\n",
    "    e1.delete(0, END)\n",
    "    e1.insert(END, selected_tuple[1])\n",
    "    e2.delete(0, END)\n",
    "    e2.insert(END, selected_tuple[2])\n",
    "    e3.delete(0, END)\n",
    "    e3.insert(END, selected_tuple[3])\n",
    "    e4.delete(0, END)\n",
    "    e4.insert(END, selected_tuple[4])\n",
    "\n",
    "def view_command():\n",
    "    list1.delete(0, END)\n",
    "    for row in backend.view():\n",
    "        print(row)\n",
    "        list1.insert(END, row)\n",
    "\n",
    "def search_command():\n",
    "    list1.delete(0, END)\n",
    "    for row in backend.search(title_text.get(), author_text.get(), year_text.get(), isbn_text.get()):\n",
    "        list1.insert(END, row)\n",
    "\n",
    "def add_command():\n",
    "    backend.insert(title_text.get(), author_text.get(), year_text.get(), isbn_text.get())\n",
    "    list1.delete(0, END)\n",
    "    list1.insert(END,(title_text.get(), author_text.get(), year_text.get(), isbn_text.get()))\n",
    "\n",
    "def delete_command():\n",
    "    backend.delete(selected_tuple[0])\n",
    "\n",
    "def update_command():\n",
    "    backend.update(selected_tuple[0], title_text.get(), author_text.get(), year_text.get(), isbn_text.get())\n",
    "\n",
    "window=Tk()\n",
    "\n",
    "window.wm_title(\"BookStore\")\n",
    "\n",
    "l1=Label(window, text = \"Title\")\n",
    "l1.grid(row=0, column=0)\n",
    "\n",
    "l2=Label(window, text=\"Author\")\n",
    "l2.grid(row=0, column=2)\n",
    "\n",
    "l3=Label(window, text=\"Year\")\n",
    "l3.grid(row=1, column=0)\n",
    "\n",
    "l4=Label(window, text=\"ISBN\")\n",
    "l4.grid(row=1, column=2)\n",
    "\n",
    "title_text = StringVar()\n",
    "e1=Entry(window, textvariable = title_text)\n",
    "e1.grid(row=0, column=1)\n",
    "\n",
    "author_text = StringVar()\n",
    "e2=Entry(window, textvariable=author_text)\n",
    "e2.grid(row=0, column=3)\n",
    "\n",
    "year_text = StringVar()\n",
    "e3=Entry(window, textvariable=year_text)\n",
    "e3.grid(row=1,column=1)\n",
    "\n",
    "isbn_text = StringVar()\n",
    "e4=Entry(window, textvariable=isbn_text)\n",
    "e4.grid(row=1, column=3)\n",
    "\n",
    "list1=Listbox(window, height=6, width=35)\n",
    "list1.grid(row=2, column=0, rowspan=6, columnspan=2)\n",
    "\n",
    "sb1=Scrollbar(window)\n",
    "sb1.grid(row=2, column=2, rowspan=6)\n",
    "\n",
    "list1.configure(yscrollcommand=sb1.set)\n",
    "sb1.configure(command=list1.yview)\n",
    "\n",
    "list1.bind('<<ListboxSelect>>', get_selected_row)\n",
    "\n",
    "b1=Button(window,text=\"View all\", width=12, command=view_command)\n",
    "b1.grid(row=2, column=3)\n",
    "\n",
    "b2=Button(window,text=\"Search entry\", width=12,command=search_command)\n",
    "b2.grid(row=3,column=3)\n",
    "\n",
    "b3=Button(window, text=\"Add entry\", width=12, command=add_command)\n",
    "b3.grid(row=4, column=3)\n",
    "\n",
    "b4=Button(window, text=\"Update selected\", width=12, command=update_command)\n",
    "b4.grid(row=5, column=3)\n",
    "\n",
    "b5=Button(window, text=\"Delete selected\", width=12, command=delete_command)\n",
    "b5.grid(row=6, column=3)\n",
    "\n",
    "b6=Button(window, text=\"Close\", width=12, command=window.destroy)\n",
    "b6.grid(row=7, column=3)\n",
    "\n",
    "window.mainloop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here's the completed `backend.py` code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# backend.py\n",
    "\n",
    "import sqlite3\n",
    "\n",
    "def connect():\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)\")\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def insert(title, author, year, isbn):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"INSERT INTO book VALUES (NULL,?,?,?,?)\",(title, author, year, isbn))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    view()\n",
    "\n",
    "def view():\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM book\")\n",
    "    rows=cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def search(title=\"\", author=\"\", year=\"\", isbn=\"\"):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?\", (title, author, year, isbn))\n",
    "    rows=cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def delete(id):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"DELETE FROM book WHERE id=?\",(id,))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def update(id, title, author, year, isbn):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"UPDATE book SET title=?, author=?, year=?, isbn=? WHERE id=?\",(title, author, year, isbn, id))\n",
    "    conn.commit()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Practice:** [Fixing the Bug](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/8174886?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you haven't noticed it already, the program has a bug. When the list box is empty and the user clicks it, an `IndexError` is generated in the terminal."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why that happens?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That happens because when the user clicks the list box this code is executed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list1.bind('<<ListboxSelect>>',get_selected_row) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That line calls the `get_selected_row`  function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_selected_row(event):\n",
    "    global selected_tuple\n",
    "    index=list1.curselection()[0]\n",
    "    selected_tuple=list1.get(index)\n",
    "    e1.delete(0,END)\n",
    "    e1.insert(END,selected_tuple[1])\n",
    "    e2.delete(0,END)\n",
    "    e2.insert(END,selected_tuple[2])\n",
    "    e3.delete(0,END)\n",
    "    e3.insert(END,selected_tuple[3])\n",
    "    e4.delete(0,END)\n",
    "    e4.insert(END,selected_tuple[4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the listbox is empty, then `list1.curselection()`  will be an empty list with no items. Trying to access the first item of that list with `[0]`  in line 3 will throw an error since there is no first item in the list. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please try to fix that bug. The next lecture contains the solution."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Solution:** [Fixing the Bug](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/8174962?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Changing the `def_selected_row` function to this one below fixes the issue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_selected_row(event):\n",
    "    try:\n",
    "        global selected_tuple\n",
    "        index=list1.curselection()[0]\n",
    "        selected_tuple=list1.get(index)\n",
    "        e1.delete(0,END)\n",
    "        e1.insert(END,selected_tuple[1])\n",
    "        e2.delete(0,END)\n",
    "        e2.insert(END,selected_tuple[2])\n",
    "        e3.delete(0,END)\n",
    "        e3.insert(END,selected_tuple[3])\n",
    "        e4.delete(0,END)\n",
    "        e4.insert(END,selected_tuple[4])\n",
    "    except Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Explanation:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see the error was fixed by simply implementing a try  and except  block. When the get_selected_row  function is called, Python will try to execute the indented block under try . If there is an IndexError none of the lines under try  will be executed. Instead the line under except  will be executed which is pass . The pass  stetement means do nothing. So the function will do nothing when there's an empty listbox."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Creating a Standalone Executable Version of the Program](https://www.udemy.com/the-python-mega-course/learn/v4/t/lecture/4775418?start=0)\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This lecture covers the process of how to convert your .py files into executable files that can run on any computer."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lecture:** [Final Code of Application 5]()\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# frontend.py \n",
    "\n",
    "from tkinter import *\n",
    "\n",
    "import backend\n",
    "\n",
    "def get_selected_row(event):\n",
    "    try:\n",
    "        global selected_tuple\n",
    "        index=list1.curselection()[0]\n",
    "        selected_tuple=list1.get(index)\n",
    "        e1.delete(0,END)\n",
    "        e1.insert(END,selected_tuple[1])\n",
    "        e2.delete(0,END)\n",
    "        e2.insert(END,selected_tuple[2])\n",
    "        e3.delete(0,END)\n",
    "        e3.insert(END,selected_tuple[3])\n",
    "        e4.delete(0,END)\n",
    "        e4.insert(END,selected_tuple[4])\n",
    "    except Index\n",
    "\n",
    "def view_command():\n",
    "    list1.delete(0, END)\n",
    "    for row in backend.view():\n",
    "        print(row)\n",
    "        list1.insert(END, row)\n",
    "\n",
    "def search_command():\n",
    "    list1.delete(0, END)\n",
    "    for row in backend.search(title_text.get(), author_text.get(), year_text.get(), isbn_text.get()):\n",
    "        list1.insert(END, row)\n",
    "\n",
    "def add_command():\n",
    "    backend.insert(title_text.get(), author_text.get(), year_text.get(), isbn_text.get())\n",
    "    list1.delete(0, END)\n",
    "    list1.insert(END,(title_text.get(), author_text.get(), year_text.get(), isbn_text.get()))\n",
    "\n",
    "def delete_command():\n",
    "    backend.delete(selected_tuple[0])\n",
    "\n",
    "def update_command():\n",
    "    backend.update(selected_tuple[0], title_text.get(), author_text.get(), year_text.get(), isbn_text.get())\n",
    "\n",
    "window=Tk()\n",
    "\n",
    "window.wm_title(\"BookStore\")\n",
    "\n",
    "l1=Label(window, text = \"Title\")\n",
    "l1.grid(row=0, column=0)\n",
    "\n",
    "l2=Label(window, text=\"Author\")\n",
    "l2.grid(row=0, column=2)\n",
    "\n",
    "l3=Label(window, text=\"Year\")\n",
    "l3.grid(row=1, column=0)\n",
    "\n",
    "l4=Label(window, text=\"ISBN\")\n",
    "l4.grid(row=1, column=2)\n",
    "\n",
    "title_text = StringVar()\n",
    "e1=Entry(window, textvariable = title_text)\n",
    "e1.grid(row=0, column=1)\n",
    "\n",
    "author_text = StringVar()\n",
    "e2=Entry(window, textvariable=author_text)\n",
    "e2.grid(row=0, column=3)\n",
    "\n",
    "year_text = StringVar()\n",
    "e3=Entry(window, textvariable=year_text)\n",
    "e3.grid(row=1,column=1)\n",
    "\n",
    "isbn_text = StringVar()\n",
    "e4=Entry(window, textvariable=isbn_text)\n",
    "e4.grid(row=1, column=3)\n",
    "\n",
    "list1=Listbox(window, height=6, width=35)\n",
    "list1.grid(row=2, column=0, rowspan=6, columnspan=2)\n",
    "\n",
    "sb1=Scrollbar(window)\n",
    "sb1.grid(row=2, column=2, rowspan=6)\n",
    "\n",
    "list1.configure(yscrollcommand=sb1.set)\n",
    "sb1.configure(command=list1.yview)\n",
    "\n",
    "list1.bind('<<ListboxSelect>>', get_selected_row)\n",
    "\n",
    "b1=Button(window,text=\"View all\", width=12, command=view_command)\n",
    "b1.grid(row=2, column=3)\n",
    "\n",
    "b2=Button(window,text=\"Search entry\", width=12,command=search_command)\n",
    "b2.grid(row=3,column=3)\n",
    "\n",
    "b3=Button(window, text=\"Add entry\", width=12, command=add_command)\n",
    "b3.grid(row=4, column=3)\n",
    "\n",
    "b4=Button(window, text=\"Update selected\", width=12, command=update_command)\n",
    "b4.grid(row=5, column=3)\n",
    "\n",
    "b5=Button(window, text=\"Delete selected\", width=12, command=delete_command)\n",
    "b5.grid(row=6, column=3)\n",
    "\n",
    "b6=Button(window, text=\"Close\", width=12, command=window.destroy)\n",
    "b6.grid(row=7, column=3)\n",
    "\n",
    "window.mainloop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here's the completed `backend.py` code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# backend.py\n",
    "\n",
    "import sqlite3\n",
    "\n",
    "def connect():\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)\")\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def insert(title, author, year, isbn):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"INSERT INTO book VALUES (NULL,?,?,?,?)\",(title, author, year, isbn))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    view()\n",
    "\n",
    "def view():\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM book\")\n",
    "    rows=cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def search(title=\"\", author=\"\", year=\"\", isbn=\"\"):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?\", (title, author, year, isbn))\n",
    "    rows=cur.fetchall()\n",
    "    conn.close()\n",
    "    return rows\n",
    "\n",
    "def delete(id):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"DELETE FROM book WHERE id=?\",(id,))\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "\n",
    "def update(id, title, author, year, isbn):\n",
    "    conn=sqlite3.connect(\"books.db\")\n",
    "    cur=conn.cursor()\n",
    "    cur.execute(\"UPDATE book SET title=?, author=?, year=?, isbn=? WHERE id=?\",(title, author, year, isbn, id))\n",
    "    conn.commit()\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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}