Simple data storage
One of the most basic requirements of any program is to be able to store data.
This data might be the name of a player playing a game, or their score, or how much of the game has been completed. This data could be stored in variables, in Python for example:
playerOneName = input("Player 1, enter your name:")
score = score + 10
trainingLevelCompleted = True
More flexible ways to store data
Most programming languages have more powerful ways of storing data which a programmer can use in more complex situations. You can think of these as containers for data, and we call these containers data structures.
The most common data structure is the array. A list is a more powerful version of an array, and because Python only supports lists, we will talk about lists in the examples below.
An example of a situation where we might want to use a list is to store details of the members of a club.
For example:
memberList = []
memberList.append("Silvia")
memberList.append("Piotr")
memberList.append("Ursula")
memberList.append("Ronnie")
memberList.append("Sonia")
print(memberList)
File storage
Whilst almost all programs use variables and data structures to store data whilst the program is running, when the program ends all the data is usually lost. This is because data being used in a running program is usually stored in a type of memory called Random Access Memory (RAM) that loses its contents completely when electrical power is removed (i.e. the circuit containing the RAM is switched off). To get around this, it is possible to store data in files on some sort of secondary storage device, for example a hard disk or flash memory card. This is very common for all but the most simple of applications. Variables and data structures can be stored away before the program stops running and when the program is run again later, the data previously stored can be loaded into memory again.
Whilst this works fine, there are some downsides. Firstly the programmer is responsible for writing all the code that stores the data, and also the code that reads back the data. This creates an extra burden of work, and could also lead to more bugs being introduced. Secondly, the format of the file that is written to the disk is up to the programmer and it means the data in the file cannot directly be read by other programs. In general it is a good thing to be able to move data between different programs and systems easily. This is called data portability.
Databases
For an application where data is frequently being stored, read, changed and sorted, a popular technique programmers use is to write a program that works alongside a database. The program contains all the logic for the application, and the database is used to store the data being generated and used by the application.
The way this works is that the program “connects” to an associated database, and the program sends commands to the database which then runs the commands if it can. At any time the program can stop running, and the database will have safely stored all the data. The commands sent might ask the database to return data to the program, add some data, delete some data, or change some data. You can even send commands that will create the structure of the database in the first place.
The beauty of this sort of approach, is that the programmer does not have to worry about writing all the code to store away and read back data in files as the database does that automatically. Also the data can easily be sorted and selected whilst for numeric data, totals and other calculated data like averages can be easily generated. It has the further advantage that the database files can be read by other programs if needed and if permission is given, making the data highly portable between different systems.
SQL
The commands that we send to the database from a program are written in a special language designed for the purpose. This is called Structured Query Language, or SQL for short (pronounced see-kwull).
Below is a statement written in SQL:
SELECT firstname,form FROM students ORDER BY formIf we ran the above command in a database, it would look for a table called students within the database, and then return the data requested from that table using the rules specified in the command. In this case it will return two fields: firstname and form, and the whole thing will be alphabetically sorted in order of form.
Connecting your program to the database
Programs can connect to and manipulate databases using library code built into the programming language being used. The way a programmer connects to the database depends on whether the database is embedded (i.e. closely coupled to the program and running as if it was part of the program) or running on a server. Most databases run on a server, i.e. a separate program, usually running on a different machine. The downside with running on a separate server is that someone needs to setup, configure and manage that server, and you also need to send commands and data backwards and forwards to the server via some sort of communications protocol (usually TCP/IP). This will slow things down. An embedded database doesn’t need a separate server, and doesn’t need setting up. Your program communicates directly with it via library code.
We are going to use an embedded database called SQLite – a database that we communicate with directly using SQL commands and a Python library. SQLite is used in countless different programs, for example in iTunes where it might be used to organise the music and video content you own. It is also used in Dropbox, where it would be used to keep track of the files in all of your folders.
The first step in connecting to an embedded SQLite database is to import the library. The following line at the top of a Python module achieves this:
import sqlite3Various Python objects within that libary allow us to pass commands to the database, and receive data back into the program for us to use. We can even send commands to create and setup the database itself. We will learn how to do this through the various exercises created to guide you through the process. These use the PRIMM methodology which encourages you to read code made by someone else, investigate it further, make some modifications, and apply your learning by creating your own program.
Cursor
The final concept to grasp before commencing work on the exercises is that of the cursor. This is a means of sending the SQL command to the database, and returning the records to the program, without having to keep track of where exactly we have read up to. Lets say we have a query which returns 8 records. You can either:
- read all the records at once: fetchall()
{record 1} {record 2} {record 3} {record 4} {record 5} {record 6} {record 7} {record 8}
- read a single record at a time: fetchone()
{record 1} {record 2} {record 3} {record 4} {record 5} {record 6} {record 7} {record 8}
- read a specific number of records: fetchmany(n)
{record 1} {record 2} {record 3} {record 4} {record 5} {record 6} {record 7} {record 8}
The name cursor is deliberate. Think about the flashing cursor that appears when you are editing a document or program: it shows the position where your typing will be inserted. You don’t have to do the job of managing the updating of the cursor, the system does it as you type.
Getting started
Copy and extract these required files to your own computer hard drive or user account if you are working on a network.
The files take the following form:
- Exercises – work through these completing the tasks one by one and filling in the gaps in the document
- Programs – an initial program for each exercise, which you use as the basis for your prediction and can run to see output. You can also then extend the program in the exercise.
- Data files – mostly databases with sample data to run SQL queries against.
Some exercises will involve the adding or deleting of records from the database so keep a copy as you may want to have access to the original files.
Now start the first exercise, connect. You will need to make use of the schoolDatabase.db file and the connect.py source file.
If you get stuck, ask for help from others if you are able to, or make use of the resources below. If you came across this resource in school or college, your teacher has been given access to solutions for many of the tasks and some sample programs.