Query 15 - Where did people that cycled everyday to work in 2001 and 2011 live? This query allows the reader to understand which are the boroughs that were cycling the most and the least in 2001 and 2011.

Query 15 - Where did people that cycled everyday to work in 2001 and 2011 live?

Query in MySql:

2001:

mysql> select borough, bike_2001 from cycling group by borough;
+---------------+-----------+
| borough       | bike_2001 |
+---------------+-----------+
| Croydon       |      1636 |
| Greenwich     |      1351 |
| Hackney       |      4929 |
| Lewisham      |      2112 |
| Tower Hamlets |      2214 |
+---------------+-----------+
5 rows in set (0.00 sec)

2011:

mysql> select borough, bike_2011 from cycling group by borough;
+---------------+-----------+
| borough       | bike_2011 |
+---------------+-----------+
| Croydon       |      2172 |
| Greenwich     |      2738 |
| Hackney       |     17312 |
| Lewisham      |      5375 |
| Tower Hamlets |      8112 |
+---------------+-----------+
5 rows in set (0.00 sec)

Outup 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, bike_2001 from cycling group by borough")
    data = cur.fetchall()
    c = csv.writer(open("cycle_borough_2001.csv","wb"))
    for row in data:
      print row
      c.writerow(row)

    cur.execute("SELECT borough, bike_2011 from cycling group by borough")
    data = cur.fetchall()
    c = csv.writer(open("cycle_borough_2011.csv","wb"))
    for row in data:
      print row
      c.writerow(row)

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