Creating SQLite indexes from Python

Overview:

Database systems are used virtually in all types of business domains by the software applications they employ. The success of any software application is defined by how fast the information is retrieved. To make search faster, database management systems use indexes. SQLite too uses indexes on columns of the tables to make searches on SQLite tables work faster.

SQLite Indexes:

SQLite indexes are B-Tree based.
As many indexes can be created on the columns of an SQLite table as the implementation supports. However, care needs to be taken to define indexes based on the needs as indexes occupy finite space in disk along with table data.
SQLite supports partial indexes. In a partial index only a specific set of rows as specified by the where clause is indexed.
SQLite also supports indexing based on expressions. Such expressions should only use functions that are deterministic, that is the output of the function solely depends on the input.
When an SQLite table is created, if the table has a primary key defined on it, then SQLite creates an index based on the primary key automatically. This can be verified by querying the table SQLite_master in SQLite. The name of the index is auto generated by SQLite and typically of the form sqlite_autoindex_<tablename>_<indexcount>
Using the SQLite provided SQL statement CREATE INDEX, indexes can be defined on any column of the SQLite table.
A primary index is an index that is created on the primary key column and the secondary index is an index that is created on the non-primary key columns.

Creating an Index on SQLite table using Python:

Almost all the time except the efforts spent on testing and defect fixing, the access to a database is purely applications written in languages like Python.
The SQLite3 module provides the Python interface to connect to an SQLite database from a Python program.
Once the SQLite3 module is imported within a Python program, the connect() method of the module can be invoked by specifying the database file name.
Once a connection object is obtained using the connect() method of SQLite3 module, a cursor object can as well be obtained to execute any SQL command including the CREATE INDEX command on to the SQLite database.
A sample Python program given here creates a primary index as well as a secondary index on a SQLite table.

Example:

#---- Sample Python program that creates primary and secondary indexes on a SQLite Table----

import sqlite3

# Connection to a SQLite database

sqliteConnection = sqlite3.connect("./main.db")

# Cursor object

sqliteCursor = sqliteConnection.cursor()

sqliteDropTable = "DROP TABLE IF EXISTS parts"

# Create a SQLite table with primary key

sqliteCreateTable = "CREATE TABLE parts(id int PRIMARY KEY, type varchar(4), name varchar(24))"

sqliteCursor.execute(sqliteDropTable)

# Execute the create table statement

sqliteCursor.execute(sqliteCreateTable)

# Create a secondary key on the name column

createSecondaryIndex = "CREATE INDEX index_part_name ON parts(name)"

sqliteCursor.execute(createSecondaryIndex)

# Query the SQLite master table to see the list of indexes

sqlQuery = "Select * from SQLite_master"

sqliteCursor.execute(sqlQuery)

masterRecords = sqliteCursor.fetchall()

# Print the SQLite master table contents

print(masterRecords)

Output:

The output has both the primary index 'sqlite_autoindex_parts_1 and the secondary index 'index_part_name' listed.

[('table', 'parts', 'parts', 2, 'CREATE TABLE parts(id int PRIMARY KEY, type varchar(4), name varchar(24))'), ('index', 'sqlite_autoindex_parts_1', 'parts', 3, None), ('index', 'index_part_name', 'parts', 4, 'CREATE INDEX index_part_name ON parts(name)')]