Python programming language is widely used to create database applications. A database application consists of a database managed by a database management system (DBMS) such as MySQL, SQLite, MongoDB, SQL Server, etc. A database application may or not have a front end through which a user can interact with a database and perform operations like inserting, deleting, updating, and reading records from the database. The front-end application is often developed in languages like Python, Java, C++, etc.

In this article, you will see how to develop a very simple database application using the SQLite database with which a user can interact via Python programming language. You will see how to perform the following operations on the SQLite database from within a Python application.

  1. Creating a Connection with an SQLite Database
  2. Creating Tables in SQLite Database
  3. Inserting Records in an SQLite Database Table
  4. SELECT Records from an SQLite Database Table
  5. Updating Records in an SQLite Database Table
  6. Deleting Records from an SQLite Database Table

Creating Connection with an SQLite Database

Before you can perform any operation on an SQLite database, you first need to connect to it. Python contains a builtin library named sqlite3 which can be used to connect to an SQLite Database. The following script imports the library:

import sqlite3
from sqlite3 import Error

Next, to connect to an SQLite database, you can use the connect() function of the sqlite3 module. You need to pass the path to the database as a parameter. The connect() method returns a connection object which you can use to execute queries on the newly created database.

The following script creates connect_to_sqlite() method. The path to the database is passed as a parameter to this method. Inside the connet_to_sqlite() method, the path is passed to the connect() method which returns the connect object.

def connect_to_sqlite(db_path):
   connection_object = None
   try:
       connection_object = sqlite3.connect(db_path)
       print("Connection successfully established")
   except Error as err:
       print(f"Connection cannot occur because '{err}' occurred")

   return connection_object

 

Run the following script to create and connect to  pos_system.sqlite database located in the root of the G drive. You can change your database name and path.

connection_object = connect_to_sqlite("G:\\pos_system.sqlite")

Once you execute the above script, you should see an SQLite database named pos_system.sqlite in the root of your G drive.

Creating Tables in an SQLite Database

The pos_system.sqlite database is a fictional database for a point of sale system. We will create one table i.e. products in the database. The products table will contain information about the different products sold in a supermarket.  The following script creates  a string query to create the products table:

create_products_table = """
CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_name TEXT NOT NULL,
product_price INTEGER,
product_category TEXT
);
"""

The products table will have 4 columns: product_id, product_name, product_price, and product_category.

To execute the above query, you need a cursor object from the connection object that you just created. To create a cursor, call the cursor() method on the connection object as shown below:

db_cursor = connection_object.cursor()

Next, you need to pass the query string to the execute() method of the cursor object and then call commit() method on the connection object. Run the following script to create products table in the pos_system.sqlite database.

db_cursor.execute(create_products_table)
connection_object.commit()

Inserting Records in an SQLite Database Table

The process of inserting records in an SQLite table is similar to creating a table. You have to write the insert query in string format and then pass the query to the execute() method of the cursor object. Finally, you need to call the commit() method on the connection object to actually commit the changes in the database.

The following query string will insert 5 dummy records in the products table.

insert_products = """
INSERT INTO
products (product_name, product_price, product_category)
VALUES
('Laptop', 1500, 'Electronics'),
('Sofa', 200, 'Furniture'),
('Cell Phone', 500, 'Electronics'),
('Bicycle', 300, 'Vehicle'),
('Scooter', 150, 'Vehicle');
"""

The following script passes the above insert query to the execute() method of the cursor object and then calls the commit() method of the connection object to insert the records.

db_cursor.execute(insert_products)
connection_object.commit()

SELECT Records from an SQLite Database Table

To select records, you have to pass the select query in string format to the execute() function of the cursor object. Next, to return the list of selected records, you need to call the fetchall() method of the cursor object. Finally, you can iterate through the list of returned records and print each record line by line. The following script selects all the records from the products table and displays them line by line on the console using print() method.

select_all_products = "SELECT * from products"

db_cursor.execute(select_all_products)
all_products = db_cursor.fetchall()

for product in all_products:
    print(product)

Output:

(1, 'Laptop', 1500, 'Electronics')
(2, 'Sofa', 200, 'Furniture')
(3, 'Cell Phone', 500, 'Electronics')
(4, 'Bicycle', 300, 'Vehicle')
(5, 'Scooter', 150, 'Vehicle')

From the above output, you can see that all the records that you inserted in the products table are selected and displayed on the console.

Let’s see another example of the SELECT statement. The following script selects all the records from the products table where the product_price column has a value of less than 500.

select_all_products = "SELECT * from products WHERE product_price < 500"

db_cursor.execute(select_all_products)
all_products = db_cursor.fetchall()

for product in all_products:
print(product)

Output:

(2, 'Sofa', 200, 'Furniture')
(4, 'Bicycle', 300, 'Vehicle')
(5, 'Scooter', 150, 'Vehicle')

Updating Records in an SQLite Database Table

Updating records in an SQL table via sqlite3 module is similar to inserting records. You have to pass the query string for updating the table to the execute() method of the cursor object and then call the commit() method of the connection object to commit the changes. The following script updates the products table by reducing the prices of all the products in the Electronics category by 10 percent.

update_price = """
UPDATE products
SET product_price = product_price - (product_price * 0.1)
WHERE product_category = 'Electronics'
"""

db_cursor.execute(update_price)
connection_object.commit()

To see the updated prices, you can again use the SELECT query which selects all the records as shown below:

select_all_products = "SELECT * from products"

db_cursor.execute(select_all_products)
all_products = db_cursor.fetchall()

for product in all_products:
print(product)

Output:

(1, 'Laptop', 1350, 'Electronics')
(2, 'Sofa', 200, 'Furniture')
(3, 'Cell Phone', 450, 'Electronics')
(4, 'Bicycle', 300, 'Vehicle')
(5, 'Scooter', 150, 'Vehicle')

The above output shows that the price of electronics products has been reduced by 10 percent.

Deleting Records from an SQLite Database Table

Deleting a record is similar to updating and inserting a record. The following script deletes all the products that belong to the Furniture category:

delete_furniture = """
DELETE from products WHERE product_category = 'Furniture'
"""

db_cursor.execute(delete_furniture)
connection_object.commit()

Let’s select all the records again to see if the furniture products are actually removed:

select_all_products = "SELECT * from products"

db_cursor.execute(select_all_products)
all_products = db_cursor.fetchall()

for product in all_products:
print(product)

Output:

(1, 'Laptop', 1350, 'Electronics')
(3, 'Cell Phone', 450, 'Electronics')
(4, 'Bicycle', 300, 'Vehicle')
(5, 'Scooter', 150, 'Vehicle')

The above output shows that furniture products have been deleted.