Case study: Criminals

crim1_dbsetup.py

import sqlite3
import os
if os.path.exists("criminals.db"):
  os.remove("criminals.db")
db = sqlite3.connect("criminals.db")
db.cursor().executescript("""
  
  CREATE TABLE IF NOT EXISTS criminals(
    nickname TEXT NOT NULL PRIMARY KEY UNIQUE,
    age INTEGER,
    offences TEXT
  );

  INSERT INTO criminals(nickname, age, offences) VALUES
    ("Bonnie", 22, "Armed roberry.");

  INSERT INTO criminals(nickname, age, offences) VALUES
    ("Clyde", 23, "Resisting arrest.\nPossession of firearms.");
""")

crim2_phoneoperator.py

crim_name = input("Enter name: ")
crim_age = int(input("Enter age: "))
crim_desc = input("Enter crime: ")

import sqlite3
insertionSQLstring = """
INSERT INTO criminals (nickname, age, offences)
  VALUES (?, ?, ?)
"""

feedback=""

try:
  db = sqlite3.connect("criminals.db")
  cursor = db.cursor()
  cursor.execute(insertionSQLstring, (crim_name, str(crim_age), crim_desc))
  db.commit()
  feedback = "Record inserted."
except Exception as error_msg:
  db.rollback()
  feedback = str(error_msg)
finally:
  db.close()
  print(feedback)

crim3_policevehicle.py

import sqlite3
import os
import time

query = """
  SELECT *
  FROM criminals
"""

while True:
    db = sqlite3.connect("criminals.db")
    records = db.cursor().execute(query).fetchall()

    for each_tuple in records:
        line = ""
        line = each_tuple[0] + "|" + str(each_tuple[1]) + "|" + each_tuple[2]
        print(line)
        
    time.sleep(2)
    db.close()