Python and SQLite - Selecting Data

When performing an operation on an SQLite database, such as selecting, inserting, updating and deleting data, the first thing that needs to be done is to connect to the database, as previously described.

In order to retrieve data, as well as insert, update and delete data, from an SQLite database, SQL, or Structured Query Language needs to be used, more details of which can be found here. Retrieving data is done via the ‘Select’ statement.

The following table of data, called ‘person’, will be used in the example below for selecting data.

Note that, in an SQLite database there is no specific datatype for storing dates. One option, although not the only option, is to store them as text in the format YYYY-MM-DD (four digit year, two digit month and two digit day), which allows them to be sorted if necessary. Once the date has been extracted from the database, it can be manipulated programmatically in to the desired format.

The example below selects four items of data from the ‘person’ table, in last name, first name and date of birth order. The resulting data is stored in a variable, which is then used in a ‘for’ loop to output details of each record to the console in the format: “id: lastname, firstname (dob)”. Slices are used to extract the individual parts of the date of birth and format it as required. Note that the second ‘try-except’ block has a ‘finally’ section that closes the database connection, regardless of whether the data retrieval is successful or not.

import os.path
import sqlite3

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

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

    # Confirm incorrect database location and stop program execution.
    print("Error locating database.")
    quit()

try:

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

except sqlite3.DatabaseError as e:

    # Confirm unsuccessful connection and quit.
    print("Database connection unsuccessful.")
    quit()

# Cursor to execute query.
cursor = connect.cursor()

# SQL to select data from the person table.
sqlSelect = \
    "SELECT id, firstname, lastname, dob \
     FROM person \
     ORDER BY lastname, firstname, dob"

try:

    # Execute query.
    cursor.execute(sqlSelect)

    # Fetch the data returned.
    results = cursor.fetchall()

    # Display person information in the console.
    for row in results:

        print("{}: {}, {} ({}/{}/{})".format(
            row[0], row[2], row[1],
            row[3][8:], row[3][5:7], row[3][:4]))

except sqlite3.DatabaseError as e:

    # Confirm error retrieving person information and quit.
    print("Error retrieving person information.")
    quit()

finally:

    # Close database connection.
    connect.close()

The resulting output to the console is as follows.

3: Bloggs, Fred (07/05/1975)
2: Jones, George (15/12/1997)
1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)

Often it isn’t necessary to return all records from a database table. Where this is the case, parameters need to be introduced into the query. In the following example, the records returned are limited to those with a date of birth between 1 January 1980 and 31 December 1989. Within the SQL, question marks are used to signify that parameters need to be incorporated. The parameter values are then bound into the SQL statement when it is executed. Binding the parameters in this way helps prevent SQL injection, where hackers try to insert malicious code to either do damage to the database or access more data than should be allowed.

import os.path
import sqlite3

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

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

    # Confirm incorrect database location and stop program execution.
    print("Error locating database.")
    quit()

try:

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

except sqlite3.DatabaseError as e:

    # Confirm unsuccessful connection and quit.
    print("Database connection unsuccessful.")
    quit()

# Cursor to execute query.
cursor = connect.cursor()

# Query parameters.
dobLower = "1980-01-01"
dobUpper = "1989-12-31"

# SQL to select data from the person table.
sqlSelect = \
    "SELECT id, firstname, lastname, dob \
     FROM person \
     WHERE dob BETWEEN ? AND ? \
     ORDER BY lastname, firstname, dob"

try:

    # Execute query.
    cursor.execute(sqlSelect, (dobLower, dobUpper))

    # Fetch the data returned.
    results = cursor.fetchall()

    # Display person information in the console.
    for row in results:

        print("{}: {}, {} ({}/{}/{})".format(
            row[0], row[2], row[1],
            row[3][8:], row[3][5:7], row[3][:4]))

except sqlite3.DatabaseError as e:

    # Confirm error retrieving person information and quit.
    print("Error retrieving person information.")
    quit()

finally:

    # Close database connection.
    connect.close()

The resulting output to the console is as follows.

1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)