Query 7 - How many bikes were stolen per month? This query allows the reader to see which months were the best and the worst in what concerns to bike theft.

Query 7 - How many bikes were stolen per month?

Query in MySql:

mysql> select date, count(*) from bike group by date;
+------------+----------+
| date       | count(*) |
+------------+----------+
| 2013-05-01 |       45 |
| 2013-06-01 |       68 |
| 2013-07-01 |       53 |
| 2013-08-01 |       40 |
| 2013-09-01 |       54 |
| 2013-10-01 |       61 |
+------------+----------+
6 rows in set (0.01 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 date, count(*) from bike group by date")
    data = cur.fetchall()
    c = csv.writer(open("bikes_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()