Difference between revisions of "2015 UK General Election using mysql"
(Created page with "This tutorial assumes you have access to a Linux account with mysql. Napier students can use the machine inf08104.napier.ac.uk ==Get to the linux prompt and download the csv==...")
Revision as of 17:29, 6 October 2016
This tutorial assumes you have access to a Linux account with mysql. Napier students can use the machine inf08104.napier.ac.uk
Get to the linux prompt and download the csv
Now move this file to somewhere where the MySQL process can see it (mysql doesn't have read access to your file space)
mv hocl-ge2015-results-full.csv /tmp
Go into mysql and create a table for the results
mysql -u 40000036 -ptiger 40000036
The create table statement could be:
CREATE TABLE ge( ons_id VARCHAR(10), ons_region_id VARCHAR(10), constituency_name VARCHAR(50), county_name VARCHAR(50), region_name VARCHAR(50), country_name VARCHAR(50), constituency_type VARCHAR(10), party_name VARCHAR(50), party_abbreviation VARCHAR(50), firstname VARCHAR(50), surname VARCHAR(50), gender VARCHAR(6), sitting_mp VARCHAR(3), former_mp VARCHAR(3), votes INT, `share` FLOAT, `change` VARCHAR(20), PRIMARY KEY(ons_id,firstname,surname) );
You can import the data using this line
LOAD DATA INFILE '/tmp/hocl-ge2015-results-full.csv' INTO TABLE ge COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Now let's look at some data. How many female candidates were there?
select count(1) from ge where gender='female';
Who stood in Edinburgh South?
select surname,votes from ge where constituency_name='Edinburgh South';