Which Database Software?

  • Paul Gardner

    Re Validation:
    I teach the pupils how to write/edit a script that creates the tables with validation rules defined in the SQL create table commands. These can validate presence check, field length, range check, list of values and PK/FK constraints.


    shows how this can be done in SQLite, with some code to test that it works.


    Richard Scott

    Regarding Validation then that is implemented as follows when using sqlite3:

    The four required types of validation are added as follows:
    1. presence check
    use NOT NULL in the table definition
    petName TEXT(20) NOT NULL, a petName is required

    2. restricted choice
    add a check constraint in the table definition:
    CHECK( house IN(‘Assisi’,’Galilee’,’Jericho’) ) house must be Assisi, Galilee or Jericho

    3. field length
    add a check constraint in the table definition:
    CHECK( LENGTH(dateBorn) = 10 ) checks that a dateBorn has exactly 10 characters
    CHECK( LENGTH(password) >= 8 ) checks that password is at least 8 characters

    4. range check
    add a check constraint in the table definition:
    CHECK( weight BETWEEN 0.1 AND 200 ) min weight 0.1 and max weight 200 are allowed

    Richard Scott

    Consider a portion of the database code I posted previously.
    This could implement the following validation rules:

      Every record must have an artist name
      genre can only be Rock or Metal
      country must be 2 characters or more
    CREATE TABLE artist(
        artistname     TEXT NOT NULL,
        country        TEXT,
        genre          TEXT,
        PRIMARY KEY    (artistname),
            CHECK( genre IN ('Rock','Metal') ),
            CHECK( LENGTH(country) >= 2 )

    BTW – I’m not actually a fan of CAPITALISING SQL commands but when creating sheets to be printed in black ink and also when typed into a Python editor they are formatted as text (usually green) I think capitalisation makes the keywords stand out in a more readable way.

    Richard Scott

    This is a repeat of the previous code but hopefully formatted better. Just copy and paste this into any Python editor and run it…

    import sqlite3
    db = sqlite3.connect(':memory:')
    db.execute( '''
        CREATE TABLE artist(
            artistname TEXT PRIMARY KEY,
            country    TEXT,
            genre    TEXT
    db.execute( '''
        INSERT INTO artist(artistname, country, genre)
            ( 'ACDC' ,'Australia' ,'Rock'),
            ( 'Beatles','UK' ,'Pop'),
            ( 'Cramps' ,'USA' ,'Psychobilly'),
            ( 'Danzig' ,'USA' ,'Rock')
    results = db.execute('''
        SELECT artistname, country
        FROM artist
        WHERE artist.genre = 'Rock'
    print ('Rock bands…')
    for record in results:
        print( f'The band {record[0]} is from {record[1]}')
Viewing 4 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic.