mportocarrero

mysql> create database co302mc_bike_theft;
Query OK, 1 row affected (0.00 sec)

mysql> exit;

mportocarrero

import pymysql, sys

try:
    conn = pymysql.connect(host='localhost', 
                           port=3306, 
                           user='co302mc', 
                           passwd='mafol597', 
                           db='co302mc_bike_theft')
    cur = conn.cursor()
    cur.execute("CREATE TABLE bike (Id INT PRIMARY KEY AUTO_INCREMENT, Borough VARCHAR(20), Date DATE, Latitude DECIMAL(10, 8) NOT NULL, Longitude DECIMAL(10, 8) NOT NULL, Area VARCHAR(100), Investigaton VARCHAR(100))")

mportocarrero

mysql> LOAD DATA LOCAL INFILE "Bikee.csv" INTO TABLE bike FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES;
Query OK, 358 rows affected (0.00 sec)
Records: 358  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from bike
    -> ;
+---------------+------------+-------------+-------------+------------------------------------------------+-----------------------------------+
| Borough       | Date       | Latitude    | Longitude   | Area                                           | Investigaton                      |
+---------------+------------+-------------+-------------+------------------------------------------------+-----------------------------------+
| Lewisham      | 2013-10-01 | 51.44585000 | -0.02131800 | On or near Supermarket                         | No further action at this time    |

mportocarrero

Query on MySql:

mysql> select count(*) from bike as total;
+----------+
| count(*) |
+----------+
|      321 |
+----------+
1 row in set (0.00 sec)

mportocarrero

Query on MySql:

mysql> SELECT borough, COUNT(*) as count FROM bike GROUP BY Borough;
+---------------+-------+
| borough       | count |
+---------------+-------+
| Croydon       |    64 |
| Greenwich     |    24 |
| Hackney       |    51 |
| Lewisham      |    24 |

mportocarrero

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 |

mportocarrero

Query in MySql:

mysql> select avg(count), max(count), min(count) from (select borough, count(*) as count from bike group by borough) as c;
+------------+------------+------------+
| avg(count) | max(count) | min(count) |
+------------+------------+------------+
|    64.2000 |        158 |         24 |
+------------+------------+------------+
1 row in set (0.00 sec)

mportocarrero

Query in MySql:

mysql> SELECT investigation, COUNT(*) FROM bike GROUP BY investigation;
+-----------------------------------+----------+
| investigation                     | COUNT(*) |
+-----------------------------------+----------+
| No further action at this time    |      105 |
| Offender given a caution          |        1 |
| Offender given community sentence |        1 |
| Offender sent to prison           |        2 |

mportocarrero

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 |

mportocarrero

Creating table in Python

import pymysql, sys

try:
    conn = pymysql.connect(host='localhost', 
                           port=3306, 
                           user='co302mc', 
                           passwd='mafol597', 
                           db='co302mc_bike_theft')