· 7 years ago · Oct 06, 2018, 09:38 AM
1import csv
2import sqlite3
3
4from util import data_for_dept
5
6
7DATABASE = "force.sqlite3"
8TABLES = ["Location", "Incident", "Officer", "Subject"]
9
10# Core tables
11CREATE_OFFICER_TABLE = """
12 CREATE TABLE Officer (
13 id INTEGER NOT NULL PRIMARY KEY UNIQUE,
14 sex TEXT NOT NULL,
15 race TEXT NOT NULL,
16 hired TEXT NOT NULL,
17 exp INTEGER NOT NULL
18 );
19"""
20
21CREATE_SUBJECT_TABLE = """
22 CREATE TABLE Subject (
23 id INTEGER NOT NULL PRIMARY KEY UNIQUE,
24 sex TEXT NOT NULL,
25 race TEXT NOT NULL
26 );
27"""
28
29CREATE_LOCATION_TABLE = """
30 CREATE TABLE Location (
31 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
32 state TEXT NOT NULL,
33 city TEXT NOT NULL,
34 address TEXT NOT NULL,
35 area INTEGER,
36 beat INTEGER,
37 section INTEGER,
38 division TEXT,
39 district TEXT,
40 latitude REAL,
41 longitude REAL
42 );
43"""
44
45CREATE_INCIDENT_TABLE = """
46 CREATE TABLE Incident (
47 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
48 subject_id INTEGER,
49 officer_id INTEGER,
50 location_id INTEGER,
51
52 date TEXT NOT NULL,
53 time TEXT NOT NULL,
54
55 off_injure_desc TEXT,
56 off_hospit INTEGER,
57
58 subj_injure_desc TEXT,
59
60 cit_infl_a TEXT,
61 charge TEXT,
62 arrested INTEGER,
63
64 FOREIGN KEY(subject_id) REFERENCES Subject(id),
65 FOREIGN KEY(officer_id) REFERENCES Officer(id),
66 FOREIGN KEY(location_id) REFERENCES Location(id)
67 );
68"""
69
70TABLE_2_QUERY = {
71 "Officer": CREATE_OFFICER_TABLE,
72 "Subject": CREATE_SUBJECT_TABLE,
73 "Location": CREATE_LOCATION_TABLE,
74 "Incident": CREATE_INCIDENT_TABLE
75}
76
77# Create the database and its tables.
78connection = sqlite3.connect(DATABASE)
79cursor = connection.cursor()
80for table, query in TABLE_2_QUERY.items():
81 cursor.execute("DROP TABLE IF EXISTS '{0}'".format(table))
82 cursor.execute(query)
83
84
85def insert_officer(cursor, row):
86 """Add a column to the Officer table.
87 """
88 id_ = row[3].strip()
89 if int(id_) <= 0:
90 print("[WARNING]: Officer with ID = {0}".format(id_))
91 sex = row[4].strip()
92 race = row[5].strip()
93 hired = row[6].strip()
94 exp = row[7].strip()
95
96 query = 'SELECT * FROM Officer WHERE id="{0}"'
97 officer_id = cursor.execute(query.format(id_)).fetchone()
98 if not officer_id:
99 # We've never seen this officer before.
100 cursor.execute(
101 "INSERT INTO Officer(id, sex, race, hired, exp) VALUES (?,?,?,?,?)",
102 [id_, sex, race, hired, exp]
103 )
104
105 return id_
106
107
108def insert_subject(cursor, row):
109 """Add a column to the Subject table.
110 """
111 id_ = row[11].strip()
112 race = row[12].strip()
113 sex = row[13].strip()
114
115 query = 'SELECT * FROM Subject WHERE id="{0}"'
116 subject_id = cursor.execute(query.format(id_)).fetchone()
117 if not subject_id:
118 cursor.execute(
119 "INSERT INTO Subject(id, race, sex) VALUES (?,?,?)",
120 [id_, race, sex]
121 )
122
123 return id_
124
125
126def insert_location(cursor, row):
127 """Add a column to the Location table.
128 """
129 area = row[19].strip()
130 beat = row[20].strip()
131 sect = row[21].strip()
132 div = row[22].strip()
133 dist = row[23].strip()
134 addr = row[28].strip()
135 city = row[29].strip()
136 state = row[30].strip()
137 lat = row[31].strip()
138 long_ = row[32].strip()
139
140 query = 'SELECT {0} FROM Location WHERE address="{1}"'
141 loc_id = cursor.execute(query.format("id", addr)).fetchone()
142 if not loc_id:
143 cursor.execute("""
144 INSERT INTO
145 Location(
146 state, city, address, area, beat, section, division, district,
147 latitude, longitude
148 )
149 VALUES (?,?,?,?,?,?,?,?,?,?)""",
150 [state, city, addr, area, beat, sect, div, dist, lat, long_])
151 loc_id = cursor.execute(query.format("id", addr)).fetchone()
152
153 return loc_id[0]
154
155
156CREATE_INCIDENT_TABLE = """
157 CREATE TABLE Incident (
158 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
159
160 date TEXT NOT NULL,
161 time TEXT NOT NULL,
162
163 off_injure_desc TEXT,
164 off_hospit INTEGER,
165
166 subj_injure_desc TEXT,
167 subj_arrested INTEGER,
168
169 cit_infl_a TEXT,
170 charge TEXT,
171
172 FOREIGN KEY(subject_id) REFERENCES Subject(id)
173 FOREIGN KEY(officer_id) REFERENCES Officer(id)
174 FOREIGN KEY(location_id) REFERENCES Location(id)
175 );
176"""
177
178def insert_incident(cursor, row, off, sub, loc):
179 """Add a column to the Incident table.
180 """
181 date = row[0].strip()
182 time = row[1].strip()
183
184 off_injure_desc = row[9].strip()
185 off_hospit = row[10].strip()
186
187 subj_injure_desc = row[15].strip()
188 arrested = row[16].strip()
189
190 cit_infl_a = row[17].strip()
191 charge = row[18].strip()
192
193 cursor.execute("""
194 INSERT INTO Incident(
195 date, time, off_injure_desc, off_hospit, subj_injure_desc,
196 arrested, cit_infl_a, charge, subject_id, officer_id,
197 location_id
198 ) VALUES (?,?,?,?,?,?,?,?,?,?,?)
199 """, [
200 date, time, off_injure_desc, off_hospit, subj_injure_desc,
201 arrested, cit_infl_a, charge, sub, off, loc])
202
203if __name__ == "__main__":
204 print("Generating SQLlite database ...")
205 force = data_for_dept("Dept_37-00049", "**/*_.prepped.csv")[0]
206 with open(force, "r") as csv_file:
207 reader = csv.reader(csv_file)
208
209 headers1 = next(reader)
210 headers2 = next(reader)
211
212 for i, row in enumerate(reader):
213 officer_id = insert_officer(cursor, row)
214 subject_id = insert_subject(cursor, row)
215
216 location_id = insert_location(cursor, row)
217 insert_incident(cursor, row, officer_id, subject_id, location_id)
218
219 connection.commit()
220 connection.close()