Query 4 - How many bikes were stolen in this period? (by month and borough) This query enables the readers to know what was the evolution of bikes stolen from May to October 2013.

Query 4 - How many bikes were stolen in this period? (by month and borough)

Query on MySql:

mysql> SELECT Borough, Date, COUNT(*) FROM bike GROUP BY Borough, Date;
+---------------+------------+----------+
| Borough       | Date       | COUNT(*) |
+---------------+------------+----------+
| Croydon       | 2013-05-01 |        5 |
| Croydon       | 2013-06-01 |       20 |
| Croydon       | 2013-07-01 |       10 |
| Croydon       | 2013-08-01 |        4 |
| Croydon       | 2013-09-01 |       11 |
| Croydon       | 2013-10-01 |       14 |
| Greenwich     | 2013-05-01 |        7 |
| Greenwich     | 2013-06-01 |        5 |
| Greenwich     | 2013-07-01 |        4 |
| Greenwich     | 2013-08-01 |        3 |
| Greenwich     | 2013-09-01 |        3 |
| Greenwich     | 2013-10-01 |        2 |
| Hackney       | 2013-05-01 |       12 |
| Hackney       | 2013-06-01 |        9 |
| Hackney       | 2013-07-01 |       13 |
| Hackney       | 2013-08-01 |        4 |
| Hackney       | 2013-09-01 |        6 |
| Hackney       | 2013-10-01 |        7 |
| Lewisham      | 2013-05-01 |        4 |
| Lewisham      | 2013-06-01 |        1 |
| Lewisham      | 2013-07-01 |        2 |
| Lewisham      | 2013-08-01 |        6 |
| Lewisham      | 2013-09-01 |        6 |
| Lewisham      | 2013-10-01 |        5 |
| Tower Hamlets | 2013-05-01 |       17 |
| Tower Hamlets | 2013-06-01 |       33 |
| Tower Hamlets | 2013-07-01 |       24 |
| Tower Hamlets | 2013-08-01 |       23 |
| Tower Hamlets | 2013-09-01 |       28 |
| Tower Hamlets | 2013-10-01 |       33 |
+---------------+------------+----------+
30 rows in set (0.00 sec)


Output results in Python:

import pymysql, sys
import csv

try:
    conn = pymysql.connect(host='localhost', 
                           port=3306, 
                           user='co302mc', 
                           passwd='mafol597', 
                           db='co302mc_bike_theft')
    cur = conn.cursor()
    cur.execute("SELECT Borough, Date, COUNT(*) FROM bike GROUP BY Borough, Date")
    data = cur.fetchall()
    c = csv.writer(open("evolution.csv","wb"))
    for row in data:
      print row
      c.writerow(row)

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