Query 17 - How many bikes were stolen per month in Tower Hamlets from May to October 2013? This query shows us the evolution of bike theft in Tower Hamlets in this period.

Query 17 - How many bikes were stolen per month in Tower Hamlets from May to October 2013?

Query in MySql:

mysql> select borough, date, count(*) from bike where borough = 'Tower Hamlets' group by date;
+---------------+------------+----------+
| borough       | date       | count(*) |
+---------------+------------+----------+
| 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 |
+---------------+------------+----------+
6 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, date, count(*) from bike where borough = 'Tower Hamlets' group by date")
    data = cur.fetchall()
    c = csv.writer(open("th_month.csv","wb"))
    for row in data:
      print row
      c.writerow(row)

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