Query 19 - How is the police acting concerning to the bike theft cases in Tower Hamlets? This query shows what was the police resolution for the bike theft cases reported in Tower Hamlets.

Query 19 - How is the police acting concerning to the bike theft cases in Tower Hamlets?

Query in MySql:

mysql> SELECT borough, investigation, count(*) from bike where borough = 'Tower Hamlets' group by investigation;
+---------------+--------------------------------+----------+
| borough       | investigation                  | count(*) |
+---------------+--------------------------------+----------+
| Tower Hamlets | No further action at this time |       41 |
| Tower Hamlets | Offender sent to prison        |        1 |
| Tower Hamlets | Suspect charged                |        3 |
| Tower Hamlets | Under investigation            |      113 |
+---------------+--------------------------------+----------+
4 rows in set (0.00 sec)

Output results in Python:

import pymysql, sys
import csv

try:
    # Connect to the database
    conn = pymysql.connect(host='localhost', 
                           port=3306, 
                           user='co302mc', 
                           passwd='mafol597', 
                           db='co302mc_bike_theft')
    # Create a cursos to query the DB
    cur = conn.cursor()
    cur.execute("SELECT borough, investigation, count(*) from bike where borough = 'Tower Hamlets' group by investigation")
    data = cur.fetchall()
    c = csv.writer(open("th_police.csv","wb"))
    for row in data:
      print row
      c.writerow(row)

except Exception as e:
    print "couldn't connect to database",e
    sys.exit()