skyowen

import pymysql, sys
 
try:
	conn = pymysql.connect(host='igor.gold.ac.uk', port = 3306, user = 'co304so', passwd = '**********', db = 'co304so_LondonCrime')
	cur = conn.cursor()
	cur.execute("CREATE TABLE AprilNov2012(Date DATE, Westminster VARCHAR(11), HACKNEY VARCHAR(7), Tower_Hamlets VARCHAR(13))")
	data = cur.fetchall()
	print data
	
except Exception as e:

skyowen

Last login: Sat Jan 18 16:13:31 on ttys000
docm7155:~ co304so$ ssh co304so@igor.gold.ac.uk
co304so@igor.gold.ac.uk's password: 
Last login: Sat Jan 18 16:19:17 2014 from 158.223.57.155
---------------GOLDSMITHS COLLEGE DEPARTMENT OF COMPUTING---------------
                          (_) ___   ___  _ __
                          | |/ _ \ / _ \| '__|
                          | | (_) | (_) | |
                          |_|\__  |\___/|_|
                             |___/

skyowen

import csv
import MySQLdb
# open the connection to the MySQL server.
# using MySQLdb
mydb = MySQLdb.connect(host='igor.gold.ac.uk', user='co304so', passwd='*********', db='co304so_LondonCrime')
cursor = mydb.cursor()
csv_data = csv.reader(file('1.csv'))
# execute and insert the csv into the database.
for row in csv_data:
	cursor.execute('INSERT INTO AprilNov2013(Date,Westminster,HACKNEY,Tower_Hamlets)''VALUES(%s, %s, %s, %s)',row)

skyowen

Highest number(monthly) of thefts from the person between 2011-2012:

mysql> SELECT MAX(Westminster) FROM April11_12;
+------------------+
| MAX(Westminster) |
+------------------+
| 887              |
+------------------+

mysql> SELECT MAX(Hackney) FROM April11_12;

skyowen

import csv
import MySQLdb

mydb = MySQLdb.connect(host='igor.gold.ac.uk',
    user='co304so',
    passwd='**********',
    db='co304so_LondonCrime')
cursor = mydb.cursor()

## query