In this article, we will start learning to work with a Database using Python. Every useful application either, it is a desktop application, mobile application, or web application, uses some sort of database for the storage and retrieval of the data. In our previous article, we saw that we can achieve data persistence using files but it is not the most efficient way. There are many database choices available for Python, but for this series, we will be going to use sqlite3 for a number of reasons:

sqlite3 comes with Python. If you have Python installed, it means you have sqlite3. That makes it easy for following the article.
sqlite3 is a perfect choice for a wide variety of applications.
sqlite3 is reliable, simple, does not require separate database engine
sqlite3 is a self-contained, server-less, zero-configuration and fully capable database.
Easy to use

What is Database?

A database is a file that is organized for storing the data. It is an organized collection of data used for storing, managing and retrieving information. It is used to persist the data. It is stored on permanent storage.

A database looks like a general spreadsheet with multiple sheets. Three main structures are there:

Tables/Relations
Rows/Tuples
Columns/Attributes

The yellow highlighted part is called row or tuple.

Creating a Database Table

When we create a database table, we must inform the database in advance the following:

The name of the table
The names of each of the columns in the table
The data type of each of the column in the table

You may see the various available datatypes supported by SQLite by visiting www.sqlite.org/datatypes.html

We will be creating a simple database for this article. The database will keep a record of an Employee. We will capture the employee id, employee name and designation attributes. Let’s start writing the code.

To create a database file and a table named employee with three columns in the database is as follows:

#First of all import sqlite3
import sqlite3
conn = sqlite3.connect('company.sqlite ')

The connect function makes a connection to the database stored in a file named company.sqlite3 in the current directory. If the file with the same name does not exist in the current directory, then it will be created. For example, after the execution of the above-given statement, the following file will be created.

cur = conn.cursor()
# BEFORE CREATING THE TABLE, DROP IT IF EXISTS
cur.execute('DROP TABLE IF EXISTS employee')
cur.execute('CREATE TABLE employee (empid INT, ename TEXT, designation TEXT) ')
conn.close()

A cursor is just like a file handler that we can use to perform operations on the database. Python provides cursor() function for this purpose to create a cursor for the connected database.

Cursor object has the following four major operations:

xecute()
fetchone()
fetchall()
close()

Once we have the cursor, we can then execute the commands on the database by using the execute() method.

In the above-given code, we are executing 2 SQL (Structured Query Language) commands in our database.

The first command removes the table employee from the database if it already exists.

cur.execute('DROP TABLE IF EXISTS employee')

The second command creates a new table named employees with one INT column and two TEXT columns.

cur.execute('CREATE TABLE employee (empid INT, ename TEXT, designation TEXT) ')

Let’s now create a file named create_database.py that contains the above-given script.

import sqlite3
def main():
	conn = sqlite.connect('company.sqlite')
	cur = conn.cursor()
	cur.execute('DROP TABLE IF EXISTS employee')
	cur.execute('CREATE TABLE employee (empid INT, ename TEXT, designation TEXT) ')
	conn.close()
if __name__ == '__main__': main()

Now that we have created a database with one table. Let’s insert some data in it.

Inserting Data in Database Table

In order to insert data in the table of a database, we will use the SQL INSERT command. The INSERT command is used to insert data (a row/tuple) into a table.

The basic format of INSERT command is:

INSERT INTO tablename (columns) VALUES (placeholders), (actualvalues)

The INSERT command indicates which table we are using then defines a new row by specifying the fields we want to include followed by the values we want to pass in those fields. We specify the values as a question mark (?,….,?) to indicate that the actual values will be passed as a tuple (value1,…,valueN).

Create a file named insert_data.py

import sqlite3
def main():
	conn = sqlite3.connect('company.sqlite')
	cur = conn.cursor()
	cur.execute('INSERT INTO employee (empid, ename, designation) VALUES (?,?,?)', (210, 'Ahmed', 'Instructor' ))
	
	cur.execute('INSERT INTO employee (empid, ename, designation) VALUES (?,?,?)', (211, 'Kamal', 'Instructor' ))

	cur.execute('INSERT INTO employee (empid, ename, designation) VALUES (?,?,?)', (212, 'Zeeshan', 'Instructor' ))

	cur.execute('INSERT INTO employee (empid, ename, designation) VALUES (?,?,?)', (213, 'Zunaina', 'Instructor' ))

	cur.execute('INSERT INTO employee (empid, ename, designation) VALUES (?,?,?)', (214, 'Qaiser', 'Professor' ))

	cur.execute('INSERT INTO employee (empid, ename, designation) VALUES (?,?,?)', (215, 'Shakeel', 'Assistant Professor' ))
	conn.commit()
	conn.close()

if __name__ == '__main__': main()

In the above piece of code, we have INSERT six rows into our table and use commit() to enforce the data to be written to the database table. After the execution of the above-given piece of code, the table would look like:

Displaying All Data from a Database Table

In order to display data from a table in a database, SELECT command is used.

SELECT command is used to retrieve the data from a table in the database. It retrieves the rows. Following is the basic structure of SELECT command

SELECT * FROM tablename

The above given general form is used to retrieve all rows with all columns. However, we can choose rows with specific columns to be retrieved for that purpose, the general form is like the following:

SELECT column1,column2,…,columnN FROM tablename

Create a file named, display_data.py in which we will display all rows with all columns.

import sqlite3
def main():
    conn = sqlite3.connect('company.sqlite')
    cur = conn.cursor()
    print('Data')
    cur.execute('SELECT * FROM employee')
    for row in cur:
            print(row)
    conn.close()
if __name__ == '__main__': main()

The output of the above piece of code will be:

Create another file named display_idnames.py. In this, we will write a piece of code to display specific columns.

import sqlite3
def main():
    conn = sqlite3.connect('company.sqlite')
    cur = conn.cursor()
    print('Data')
    cur.execute('SELECT empid,ename FROM employee')
    for row in cur:
            print(row)
    conn.close()
if __name__ == '__main__': main()

The output of the above piece of code will be:

In the upcoming article related to database, we will see some more concepts like constraints, projection, selection, where clauses, group functions etc. Stay tuned.

Next Part

Here’re some more Articles, you might be interested:

— File I/O in Python – Read and Write Files Tutorial

— Developing Chat Application in Python with Source Code

— Top 5 Python Web Frameworks to Learn