File size: 3,393 Bytes
27de439 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | import sqlite3
import os
# Define database path
DB_PATH = "crime_records.db"
# Connect to SQLite database
try:
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# Create Crimes table
cursor.execute("""
CREATE TABLE IF NOT EXISTS Crimes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
crime_type TEXT NOT NULL,
description TEXT,
location TEXT,
date TEXT,
officer_in_charge TEXT,
status TEXT
)
""")
# Create FIRs table
cursor.execute("""
CREATE TABLE IF NOT EXISTS FIRs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
crime_id INTEGER,
complainant_name TEXT,
complainant_contact TEXT,
filing_date TEXT,
FOREIGN KEY (crime_id) REFERENCES Crimes(id)
)
""")
# Create Users table
cursor.execute("""
CREATE TABLE IF NOT EXISTS Users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
role TEXT
)
""")
# Clear existing data and insert diverse sample data for ML
cursor.execute("DELETE FROM Crimes")
cursor.execute("""
INSERT INTO Crimes (crime_type, description, location, date, officer_in_charge, status)
VALUES
('cyber crimes', 'phishing attack', 'new york', '2025-07-01', 'john doe', 'open'),
('cyber crimes', 'data breach', 'los angeles', '2025-07-02', 'jane smith', 'closed'),
('theft', 'shoplifting', 'chicago', '2025-07-03', 'bob johnson', 'open'),
('assault', 'physical altercation', 'miami', '2025-07-04', 'alice brown', 'open'),
('cyber crimes', 'ransomware attack', 'new york', '2025-07-05', 'john doe', 'closed'),
('theft', 'vehicle theft', 'los angeles', '2025-07-06', 'jane smith', 'open'),
('fraud', 'credit card scam', 'chicago', '2025-07-07', 'bob johnson', 'closed'),
('assault', 'domestic violence', 'miami', '2025-07-08', 'alice brown', 'open'),
('cyber crimes', 'identity theft', 'boston', '2025-07-09', 'john doe', 'open'),
('theft', 'burglary', 'los angeles', '2025-07-10', 'jane smith', 'closed'),
('fraud', 'online scam', 'new york', '2025-07-11', 'bob johnson', 'open'),
('assault', 'street fight', 'chicago', '2025-07-12', 'alice brown', 'closed')
""")
cursor.execute("DELETE FROM FIRs")
cursor.execute("""
INSERT INTO FIRs (crime_id, complainant_name, complainant_contact, filing_date)
VALUES
(1, 'alice brown', 'alice@example.com', '2025-07-01'),
(2, 'bob green', 'bob@example.com', '2025-07-02'),
(3, 'charlie davis', 'charlie@example.com', '2025-07-03'),
(4, 'diana evans', 'diana@example.com', '2025-07-04'),
(5, 'edward fox', 'edward@example.com', '2025-07-05')
""")
cursor.execute("DELETE FROM Users")
cursor.execute("""
INSERT INTO Users (username, password, role)
VALUES
('admin', 'admin123', 'admin'),
('police1', 'pass123', 'police'),
('police2', 'pass456', 'police')
""")
conn.commit()
print(f"SQLite database '{DB_PATH}' initialized successfully at {os.path.abspath(DB_PATH)}")
except Exception as e:
print(f"Error initializing database: {e}")
finally:
cursor.close()
conn.close() |