Database Operations on SQLite using Python

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
7. Filtering Records Using the Where Clause in SQLite 
8. Grouping Records using the GROUP BY and Aggregate Functions in SQLite 
9. Sorting Records Using the Order By Statement in SQLite 

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 built-in 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.

Before you execute the remaining queries in this article, clear your products table and insert all the records from scratch. You will be using these records to perform the rest of the queries in this article.

The following query deletes all the records from the products table.

delete_records = """
DELETE from products
"""

db_cursor.execute(delete_records)
connection_object.commit()

And the following query inserts 5 new 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');
"""

db_cursor.execute(insert_products)
connection_object.commit()

Filtering Records Using the Where Clause in SQLite 

You saw the use of the WHERE clause in the section on SELECTING records from an SQLite Database table. You used the WHERE clause to filter records items that are priced at less than 500. In this section, you will see further uses of the WHERE clause for filtering records in a table.

Let’s see how you can use the WHERE clause to SELECT all the records from the products table where the product_category is Electronics.

select_where = "SELECT * from products WHERE product_category = 'Electronics' "

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

for product in all_products:
    print(product)

The above query will return the following results:

(6, 'Laptop', 1500, 'Electronics')
(8, 'Cell Phone', 500, 'Electronics')

You can see the items from the Electronics product category.

You can also use the WHERE operator in conjunction with the LIKE statement to SELECT records where a category contains text in a particular format. To do so,  have to use wild cards. For example, to filter all the records where the product name starts with the letter ‘s’, you can use the wildcard ‘s%’. Let’s see this with an example:

select_where = "SELECT * from products WHERE product_name LIKE 's%' "

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

for product in all_products:
    print(product)

In the output you should only see the records where the product name starts with the letter ‘s’, as shown below:

(7, 'Sofa', 200, 'Furniture')
(10, 'Scooter', 150, 'Vehicle')

Similarly, you can use the BETWEEN clause in conjunction with the WHERE clause to return all the items from a table where a numeric column contains values within a specific range. For instance, the following query returns all the records from the products table where the product_price column contains values between 200 and 1500 (inclusive).

select_where = "SELECT * from products WHERE product_price BETWEEN 200 and 1500 "

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

for product in all_products:
    print(product)

Here is the output of the above script:

(6, 'Laptop', 1500, 'Electronics')
(7, 'Sofa', 200, 'Furniture')
(8, 'Cell Phone', 500, 'Electronics')
(9, 'Bicycle', 300, 'Vehicle')

Grouping Records using the GROUP BY and Aggregate Functions in SQLite 

You can group records with the help of aggregate functions and the GROUP BY clause. With aggregate functions, you can execute queries like selecting the average product price for all categories, selecting the prices of the most expensive items from each category, and so on.

As a first example, let’s see how you can select the average price of all the products for each product category. Execute the following script:

select_where = """
SELECT product_category, AVG(product_price)
FROM products
GROUP BY product_category;
"""

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

for product in all_products:
    print(product)

In the output below, you can see the average of all the product prices for the three product categories in our dataset. The aggregate function used here is AVG().

('Electronics', 1000.0)
('Furniture', 200.0)
('Vehicle', 225.0)

You can group a column by minimum and maximum values using MIN() and MAX() aggregate functions, respectively. Similarly, you also group records by adding all values in a numeric column using the SUM() function. The following query returns the sum of all product prices for each product category in the products table.

select_where = """
SELECT product_category, SUM(product_price)
FROM products
GROUP BY product_category;
"""

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

for product in all_products:
    print(product)

Here is the output for the above query:

('Electronics', 2000)
('Furniture', 200)
('Vehicle', 450)

You can also filter records that are grouped using the aggregate functions. To do so, you have to use the HAVING clause. For instance, the following script groups all the records by product categories and return the product category name and the price of the most expensive item from that category. Next, the HAVING statement specifies that return only those records where the price of the most expensive item is greater than 400.

select_where = """
SELECT product_category, MAX(product_price)
FROM products

GROUP BY product_category

HAVING MAX(product_price) > 400;

"""

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

for product in all_products:
   print(product)

Since the ‘Electronics’ category is the only category where the price of the most expensive item is greater than 400, you will see the following output:

('Electronics', 2000)
('Furniture', 200)
('Vehicle', 450)

Sorting Records Using the Order By Statement in SQLite 

Finally, you can SELECT records in sorted order using the ORDER BY clause in SQLite. Records can be sorted in both ascending and descending order of values in a numerical column in an SQLite database table. The following script returns records from the products table in the ascending order of the values in the product_price column.

select_all_products = "SELECT * from products ORDER BY product_price"

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

for product in all_products:
    print(product)

The above query will return the following result:

(10, 'Scooter', 150, 'Vehicle')
(7, 'Sofa', 200, 'Furniture')
(9, 'Bicycle', 300, 'Vehicle')
(8, 'Cell Phone', 500, 'Electronics')
(6, 'Laptop', 1500, 'Electronics')

You can see that the items with the lowest price appear at the top.

Similarly, you can also sort items in the descending order of values in a specific column. To do so, you need to append the keyword DESC at the end of the column name that follows the ORDER BY clause. For example, the following query returns records from the products table in the descending order of the values in the product_price column.

select_all_products = "SELECT * from products ORDER BY product_price DESC"

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

for product in all_products:
    print(product)

Here is the output:

(6, 'Laptop', 1500, 'Electronics')
(8, 'Cell Phone', 500, 'Electronics')
(9, 'Bicycle', 300, 'Vehicle')
(7, 'Sofa', 200, 'Furniture')
(10, 'Scooter', 150, 'Vehicle')