Python and SQLite Introduction

SQLite is a database that can be used in conjunction with Python to create desktop, web and console based application. It can be used to store data, as well as configuration information. Python, together with SQL, can be used to query the data stored within an SQLite database, as well as insert, update and delete the data.

Connecting to an SQLite Database

In order to access data within an SQLite database using Python, a connection to the database must first be established. Below is an example of how this can be achieved. Unlike other databases, such as MySQL, SQLite does not require a separate server process, but instead exists as a standalone file, that can be embedded directly into an application. A username and password are also not required to access the database, instead file permissions are used to limit access to it.

import os.path
import sqlite3

# Database.
database = 'testDB.db'
connect = None

# Check if database file exists.
if not os.path.isfile(database):

    # Message confirming incorrect database location.
    print("Error locating database.")

    # Stop program execution.
    quit()

try:

    # Connect to database.
    connect = sqlite3.connect(database)

    # Message confirming successful database connection.
    print("Database connection successful.")

except sqlite3.DatabaseError as e:

    # Message confirming unsuccessful database connection.
    print("Database connection unsuccessful.")

    # Stop program execution.
    quit()

Firstly, in this example, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. Note that here, the database resides in the same location as the program. If it didn’t, the database variable would need to include the file path, as well as its name. If successfully found, an attempt is made to connect to the database, which is enclosed in a ‘try-except’ block. The ‘try-except’ block is used to catch any exceptions that arise in trying to connect to the database and deal with them in a user friendly manner. Again, a ‘quit’ statement is used after the confirmation of an unsuccessful database connection, which stops execution of the program completely.