Query 20 - In which areas of Tower Hamlets were the bikes stolen? This query shows in which areas of Tower Hamlets the bikes were stolen and how many per area.

Query 20 - In which areas of Tower Hamlets were the bikes stolen?

Query in MySql:

mysql> SELECT area, count(*) as count from bike where borough = 'Tower Hamlets' group by area;
+------------------------------------------------+-------+
| area                                           | count |
+------------------------------------------------+-------+
| On or near Acton Mews                          |     2 |
| On or near Audrey Street                       |     1 |
| On or near Austin Street                       |     2 |
| On or near Bethnal Green Road                  |     1 |
| On or near Blackall Street                     |     1 |
| On or near Boundary Pass                       |     2 |
| On or near Boundary Street                     |     9 |
| On or near Calvert Avenue                      |     7 |
| On or near Charlotte Road                      |     4 |
| On or near Christopher Street                  |     1 |
| On or near Clere Street                        |     2 |
| On or near Clifton Street                      |     1 |
| On or near Conference/exhibition Centre        |     2 |
| On or near Coronet Street                      |     1 |
| On or near Cotton's Gardens                    |     5 |
| On or near Cremer Street                       |     6 |
| On or near Curtain Place                       |     1 |
| On or near Curtain Road                        |     2 |
| On or near Denne Terrace                       |     3 |
| On or near Dove Row                            |     1 |
| On or near Drysdale Place                      |     1 |
| On or near Dunston Street                      |     3 |
| On or near Ebor Street                         |     6 |
| On or near Further/higher Educational Building |     4 |
| On or near Geffrye Street                      |     1 |
| On or near Gloucester Square                   |     1 |
| On or near Goldsmith's Row                     |     1 |
| On or near Govan Street                        |     3 |
| On or near Great Eastern Street                |     3 |
| On or near Hoxton Market                       |     5 |
| On or near Hoxton Square                       |     2 |
| On or near Hoxton Street                       |     1 |
| On or near Jeger Avenue                        |     1 |
| On or near Kiffen Street                       |     1 |
| On or near Mills Court                         |     4 |
| On or near Nazrul Street                       |     2 |
| On or near New Inn Broadway                    |     2 |
| On or near New Inn Yard                        |     2 |
| On or near Nightclub                           |     9 |
| On or near Old Street                          |     1 |
| On or near Parking Area                        |     3 |
| On or near Paul Street                         |     1 |
| On or near Petrol Station                      |     1 |
| On or near Phipp Street                        |     2 |
| On or near Printing House Yard                 |     2 |
| On or near Pritchard's Road                    |     2 |
| On or near Queensbridge Road                   |     3 |
| On or near Ravey Street                        |     1 |
| On or near Redvers Street                      |     2 |
| On or near Retford Street                      |     5 |
| On or near Rivington Place                     |     8 |
| On or near Rivington Street                    |     4 |
| On or near Rufus Street                        |     2 |
| On or near Scawfell Street                     |     1 |
| On or near Shenfield Street                    |     1 |
| On or near Supermarket                         |     2 |
| On or near Tabernacle Street                   |     1 |
| On or near Theatre/concert Hall                |     5 |
| On or near Tyssen Street                       |     1 |
| On or near Union Walk                          |     3 |
| On or near Whiston Road                        |     1 |
+------------------------------------------------+-------+
61 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 area, count(*) as count from bike where borough = 'Tower Hamlets' group by area")
    data = cur.fetchall()
    c = csv.writer(open("th_area.csv","wb"))
    for row in data:
      print row
      c.writerow(row)

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