Let's now build a simple phone book application that allows the user to store names and phone numbers.

The main learning objective for this project relates to being able to use a relational database with Tkinter to store and manipulate records. We have already seen some basic examples of object persistence with serialization. Relational databases extend this persistence using rules of relational algebra to store data in tables.

Python provides database interfaces for a wide range of database engines. Some of the commonly used database engines include MySQL, SQLite, PostgreSQL, Oracle, Ingres, SAP DB, Informix, Sybase, Firebird, IBM DB2, Microsoft SQL Server, and Microsoft Access.

We will use SQLite to store data for our phone book application.

SQLite is a serverless, zero-configuration, self-contained SQL database engine suitable for developing embedded applications. The source code for SQLite is in the public domain, which makes it freely available for use in all sorts of commercial and non-commercial projects.

Unlike many other SQL databases, SQLite does not require running a separate server process. Instead, SQLite stores all the data directly onto flat files that get stored on a computer disk. These files are easily portable across different platforms, making it a very popular choice for smaller and simpler database implementation requirements.

Python comes with a built-in standard library for SQLite3 support. However, we need to download the SQLite3 command-line tool that lets us create, modify, and access the database using a command line. The command-line shell for Windows, Linux, and macOS can be downloaded from http://sqlite.org/download.html.

Following the instructions on the website, install the SQLite command shell into any location of your choice.

Let's now implement our phone book application. The application will look as follows:

The application will demonstrate some of the common operations involved in database programming. The user should be able to create new records, read existing records, update existing records, and delete records from the database using this application. Together, these activities constitute what are known as CRUD (Create, Read, Update, and Delete) operations on a database.

In order to create the database, we open the command-line tool of our operating system. Within the command line, we first navigate to the directory where we need to create the new database file. In order to create the database, we simply use this command:

sqlite3 phonebook.db

This creates a database file named phonebook.db in the folder from which we execute the command. It also displays a message similar to the following: 

SQLite version 3.7.17 2018-01-31 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

We have now created a database named phonebook.db. However, the database file is currently empty. It does not contain any tables or any data. So, we get no results if we run this command:

sqlite> .tables

For now, let's exit the command-line tool by typing this:

sqlite> .exit

We want to store contacts in our database, and that is why we will create the contacts table. Our database table should store a person's name and phone number. In addition, it is good practice to keep a unique identification number for each person or each entry in the table. This is because multiple people might have the same name or same contact number.

To create a table within our phonebook.db database, we again open the command-line tool and navigate to the directory where we had created the database. We again get into the SQLite3 terminal by typing this:

sqlite3 phonebook.db

This time, a new database is not created. Rather, the command now opens the existing phonebook.db database because it is already present on the disk.

Next, we create a table named contacts, and add three columns to the table from the command line:

sqlite> CREATE TABLE contacts
(
contactid INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL,
contactnumber INTEGER NOT NULL
);

You can verify the contacts table was created by typing the following command:

sqlite>.table

This prints the name of all the tables present in the currently open database. You will get the following output:

sqlite>.table
contacts

Let's first create a basic GUI that will let us add, view, delete, and modify the records. We create a class named phoneBook and create all GUI widgets within it.

We do not discuss the entire code that creates the GUI as we have coded similar structures in the past. However, we use a new ttk widget named Treeview. The code for creating Treeview is as follows (9.11_phonebook.py):

def create_tree_view(self):
 self.tree = ttk.Treeview(height=5, columns=2)
 self.tree.grid(row=4, column=0, columnspan=2)
 self.tree.heading('#0', text='Name', anchor=W)
 self.tree.heading(2, text='Phone Number', anchor=W)

To add items to Treeview, we use the following code:

self.tree.insert('', 0, text=row[1], values=row[2])

To get all items in Treeview, we use the code:

items = self.tree.get_children()

To delete items from Treeview, we use the code:

self.tree.delete(item)

Next, let's prepare the code to query our database:

db_filename = 'phonebook.db'

def execute_db_query(self, query, parameters=()):
  with sqlite3.connect(self.db_filename) as conn:
    cursor = conn.cursor()
    query_result = cursor.execute(query, parameters)
    conn.commit()
  return query_result

The code description is as follows:

• The method establishes a connection to the phonebook.db database we created earlier.
• The next line, cursor = conn.cursor(), creates a cursor object. The cursor is a control structure that is required as per SQL standards, and it enables us to traverse the records in a database.
• The next line, cursor.execute(query), executes the query against the database.
• The line conn.commit() actually commits/saves these changes to the database.

We can now use the preceding method to execute CRUD queries on the database.