In this article we will learn how to use the SQLite database management system with Python for GCSE and A Level Computer Science.

You will need to know about how SQL works for GCSE Computer Science with OCR and it will certainly help with Computer Science GCSE with Edexcel and Cambridge, although in those syllabuses you technically only need to know about database structure rather than SQL specifically. For A Level, knowledge of SQL is required by almost all the boards.

The video above explains the following points:

• Importing SQLite into Python
• Making a connection to a SQLite database
• Using a cursor to read and write data
• Inserting data in to a SQLite database using INSERT.
• Reading data from a SQLite database using SELECT
• Committing changes
• Closing the connection to the database.

The context of this exercise is creating a table to store information about the topics in the OCR GCSE Computer Science syllabus. The full listing is provided below for your convenience.

Please feel free to comment and ask any questions you may have in the comments section below.

Python Listing for SQLite Database Example

import sqlite3

conn = sqlite3.connect('demo.db')
c = conn.cursor()

c.execute('DROP TABLE IF EXISTS "topics";')

c.execute('''
    CREATE TABLE "topics" (
        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
        "topic_name" TEXT NOT NULL,
        "syllabus_reference" TEXT NOT NULL
    );
''')

def insert_topic(topic, ref):
    c.execute("INSERT INTO topics (topic_name, syllabus_reference) VALUES ('{}', '{}')"\
                  .format(topic, ref)) # Single quotes for strings


def get_topic_by_ref(ref):
    c.execute("SELECT topic_name FROM topics WHERE syllabus_reference = '{}'"\
              .format(ref))
    return c.fetchone()[0]


insert_topic('Systems Architecture', '1.1')
insert_topic('Memory', '1.2')
insert_topic('Storage ', '1.3')

print(get_topic_by_ref('1.1'))

# Commit changes and close the connection to the database file
conn.commit()
conn.close()

Sharing is caring!