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()