SQLite Advanced

SQLite is covered in more detail here

parentchild_and_foreignkeys.py

import sqlite3
db = sqlite3.connect('parentchild.db')

### STEP 0: TURN ON FOREIGN KEY SUPPORT:
db.execute("PRAGMA foreign_keys = 1")

### STEP 1: DEFINE PARENT TABLE:
db.executescript("""CREATE TABLE IF NOT EXISTS parent (
                            a INT,
                            b TEXT,
                            PRIMARY KEY (a)
                          );""")

### STEP 2: DEFINE CHILD TABLE WITH FOREIGN KEY CONSTRAINTS:
db.executescript("""CREATE TABLE IF NOT EXISTS child (
                            c INT,
                            d REAL,
                            PRIMARY KEY (c, d)
                              FOREIGN KEY (c)
                              REFERENCES parent(a)
                              ON DELETE CASCADE
                              ON UPDATE CASCADE
                          );""")

### STEP 3: PARENT TABLE IS FIRST ENTRY POINT FOR DATA:
db.execute("INSERT OR IGNORE INTO parent(a, b) VALUES (1, 'guff');")

### STEP 4: ENTRY INTO CHILD TABLE, ENSURING FK CONSTRAINTS ARE MET:
db.executescript("""
    INSERT OR IGNORE INTO child(c, d) VALUES (1, 0.99);
    INSERT OR IGNORE INTO child(c, d) VALUES (1, -0.27);""")

### STEP 5: TEST WITH QUERY TO IDENTIFY WORKING DATASET:
print(db.execute("""
  SELECT parent.b, child.d
  FROM parent JOIN child ON parent.a == child.c
""").fetchall())


### STEP 6: DELETE A RECORD FROM PARENT TABLE:
db.execute("DELETE FROM parent WHERE b == 'guff'")
db.commit()

### STEP 7: RECORDS EQUAL TO THE FK HAVE BEEN REMOVED FROM CHILD TABLE:
print(db.execute("SELECT * FROM child").fetchall())


### OTHER OPTIONS FOR ON DELETE ON UPDATE:
############
#NO ACTION
#RESTRICT
#SET NULL
#SET DEFAULT
#CASCADE
#RESTRICT
############

sqlite_adv_a.py

from flask import Flask
import sqlite3
app = Flask(__name__)

# In this example, we will create a database
# with a foreign key constraint. The foreign
# key constraint is applied to the CHILD table,
# and this constraint references the PARENT table.

# In addition to this, you will note there is
# a COMPOSITE PRIMARY KEY below. This means that
# a COMBINATION of fields is used to UNIQUELY
# IDENTIFY any record (or tuple) in the table.

# FOREIGN KEY CONSTRAINTS help us maintain
# REFERENTIAL INTEGRITY, as when a change is made
# in a PARENT TABLE, that change can be CASCADED
# through to CHILD TABLES, so that a dataset is
# kept 'up to date' with accurate and existing data.

import os
if os.path.exists("people.db"):
  os.remove("people.db")
db = sqlite3.connect('people.db')
db.cursor().executescript("""
  
  CREATE TABLE IF NOT EXISTS customers (
    f_name TEXT NOT NULL PRIMARY KEY UNIQUE
  );

  CREATE TABLE IF NOT EXISTS contacts (
    f_name TEXT NOT NULL,
    details TEXT NOT NULL,
    PRIMARY KEY (f_name, details)
    FOREIGN KEY (f_name)
      REFERENCES customers(f_name)
      ON DELETE CASCADE
      ON UPDATE CASCADE
  );


  INSERT INTO customers (f_name) values ("mary");
  INSERT INTO customers (f_name) values ("sam");
  INSERT INTO contacts (f_name, details) values ("mary","0412 345 678");
  INSERT INTO contacts (f_name, details) values ("sam","0412 345 678");
  INSERT INTO contacts (f_name, details) values ("mary","mary@abc.com");
  
"""
)

try:
  db.commit() 
except Exception:
  db.rollback() 
finally:
  db.close()


###----------------------- home page:
@app.route("/")
def main():
  all_records_sql = "SELECT * FROM customers JOIN contacts ON customers.f_name == contacts.f_name"
  html = ""
  
  try:
    db = sqlite3.connect('people.db')

    #view all records:
    html += "<h1>Prior to delete:</h1><br>"
    html += str(db.cursor().execute(all_records_sql).fetchall()) + "<br>"

    #delete a customer CASCADES changes so that customer's contact methods also deleted:
    db.cursor().execute("DELETE FROM customers WHERE f_name == 'mary'")

    #view all records (again) to see change:
    html += "<h1>After deletion:</h1><br>"
    html += str(db.cursor().execute(all_records_sql).fetchall()) + "<br>"

    
  except Exception as error_msg:
    html = str(error_msg)
    
  finally:
    db.close()
    return html
  
app.run(debug=True)