Introduction to databases

In many programs it is necessary to store information so that it can be used later - it might be the scores from this week's football matches (so you can work out the position of each team in the league) or the amount of each drink type that was sold on a particular day (so you can see which drink is the most popular at different times of the year).

You are already aware that it is possible to store data in text files and you use could use this method to store the above information. However, once the amount of data you want to store increases you will quickly run into some problems:

  • What is the best way to structure the data so that it can be retrieved easily?
  • What is the best way to search and sort the data?
  • How will changes to the structure of the data impact on the program?

Databases help to resolve these problems by providing us with guidelines on how the data should be structured and by providing mechanisms to enable searching and sorting without having to worry about how these methods work.

In essence, a database is a organised body of related information that you can access in a clearly defined way. A database management system controls your access to the database and enables you to define, create and maintain that database.

SQLite

The database management system that we will using is called SQLite. It is used widely by companies as diverse as Apple, Airbus, General Electric and Mozilla to provide the database functionality in some of their applications.

We will use SQLite as it is provided as part of the standard Python library and provides us with all of the functionality that is necessary to teach databases at A-Level. Whilst it lacks some of the advance features of systems such as MySQL or PostgreSQL it is much easier to use as it requires no additional configuration or installation.

Creating a new database in Python

Creating a new, blank database in Python using SQLite is very simple:

1
2
3
4
import sqlite3

with sqlite3.connect('example.db') as db:
    pass

The above statement will connect to the database with the provided name. If there is no file with the given name then it will create a new file with this name, otherwise it will open the existing file at this location.

In order for our database to be useful we must define its structure, therefore in the next section we will turn our attention to designing the data model for our database.