7/24/17

On 7/21/17 I spent a lot of time trying to fix my macbook so that I could more easily work on this project at home. I was also able to export my database as a .csv file. I used the COPY command.

COPY precincts TO '/home/dsmall/VA_votes/CSV_converter/Untitled Folder' DELIMITER ',' CSV HEADER;

This just copies the table into a .txt file or a .csv file. It can then be easily put into an excel spreadsheet. The last thing I did was make a different spreadsheet with all of Elizabeth Guzman's donors with the value of their donations. I also sent these files to Louis who is working on her campaign. The next thing that he wanted me to do was to make a scraper that gets the phone number or home address of her donors. While looking into this I found that the White pages and the Yellow pages aren't just databases with everyone's phone number and address.
http://www.whitepages.com/  https://www.yellowpages.com/
To actually get the information you have to pay. I brought this problem up with Marco and he said that the White pages have an API (Application Program Interface) which means that he could make a program that works directly with a program that was made by the white pages to help gather data from their website. I am not sure how much information he will be able to get with it. I am now going to start working on some custom Queries for my database with the last 3 HoD elections in District 31.

SELECT precincts.precinctnames, ROUND(AVG(precincts.totalvotescast)) AS average, ROUND(AVG(recent.totalvotescast)) AS last, (ROUND(AVG(precincts.totalvotescast))) - (ROUND(AVG(recent.totalvotescast))) AS diff FROM precincts FULL OUTER JOIN recent ON precincts.precinctnames = recent.precinctnames GROUP BY precincts.precinctnames ORDER BY average;

This Query looks a bit ridiculous, but I will explain it. It starts by saying SELECT the precinct names, the average number of votes, the most recent vote counts, and then then difference FROM the precincts table. Then it does a FULL OUTER JOIN of the 'recent' table on the names of the precincts. What this does is it merges the two tables together and matches the data of the precinct names from both tables.
This is one of the most important and common commands used in SQL. It is very important, because it lets you tell the computer how things are related. For example if I have a table of online orders with the customer id and the item, then another table with the customer address, name, contact information and their id. Using a JOIN command you can tell the computer that the item needs to go to a customer id, which is the same in both tables, so having the id is the same as having their address and other information.
Then it GROUPs the data by the name of the precinct. This is done so that the AVG is of the correct data. The data is then ORDERed by the AVG.
    This is what it looks like:

precinctnames	average	last	diff
Minnieville	70	58	12
Penn	154	54	100
Casanova	463	455	8
Kettle_Run	466	457	9
Godwin	467
Forest_Park	679	595	84
Ashland	714	660	54
Enterprise	756	736	20
Independent_Hill	766	698	68
New_Baltimore	855	880	-25
Pattie	928	774	154
Saunders	931	677	254
King	934	956	-22
Catlett	987	948	39
Spriggs	1002	923	79
Henderson	1010	896	114
Beville	1011	1083	-72
Montclair	1097	611	486
Washington-reid	1171	1073	98
Vint_Hill	1200	1452	-252
Hylton	1200	694	506

    This is what it looks like if I just do

SELECT * FROM precincts;

precinctnames	precinctid	parentcounty	republican	democrat	othervotes	totalvotescast	filename
Kettle_Run	101	Fauquier_County	289	80	0	369	2011_House_of_Delegates_General_Election_District_31
Catlett	102	Fauquier_County	685	129	1	815	2011_House_of_Delegates_General_Election_District_31
Casanova	103	Fauquier_County	241	121	0	362	2011_House_of_Delegates_General_Election_District_31
New_Baltimore	502	Fauquier_County	451	106	1	558	2011_House_of_Delegates_General_Election_District_31
Vint_Hill	504	Fauquier_County	529	151	0	680	2011_House_of_Delegates_General_Election_District_31
Saunders	201	Prince_William_County	372	440	0	812	2011_House_of_Delegates_General_Election_District_31
Spriggs	207	Prince_William_County	436	409	3	848	2011_House_of_Delegates_General_Election_District_31
Hylton	208	Prince_William_County	701	414	3	1118	2011_House_of_Delegates_General_Election_District_31
Independent_Hill	209	Prince_William_County	475	171	0	646	2011_House_of_Delegates_General_Election_District_31
Penn	210	Prince_William_County	46	106	1	153	2011_House_of_Delegates_General_Election_District_31
Pattie	305	Prince_William_County	482	326	3	811	2011_House_of_Delegates_General_Election_District_31
Washington-reid	306	Prince_William_County	594	449	2	1045	2011_House_of_Delegates_General_Election_District_31
Henderson	307	Prince_William_County	544	376	2	922	2011_House_of_Delegates_General_Election_District_31
Montclair	308	Prince_William_County	575	458	1	1034	2011_House_of_Delegates_General_Election_District_31
Ashland	309	Prince_William_County	335	250	0	585	2011_House_of_Delegates_General_Election_District_31
Forest_Park	310	Prince_William_County	262	277	3	542	2011_House_of_Delegates_General_Election_District_31
Beville	602	Prince_William_County	302	436	0	738	2011_House_of_Delegates_General_Election_District_31
Godwin	603	Prince_William_County	179	190	2	371	2011_House_of_Delegates_General_Election_District_31
Minnieville	605	Prince_William_County	21	30	1	52	2011_House_of_Delegates_General_Election_District_31
Enterprise	608	Prince_William_County	286	327	0	613	2011_House_of_Delegates_General_Election_District_31
King	609	Prince_William_County	286	423	0	709	2011_House_of_Delegates_General_Election_District_31
NA	NA	TOTALS	8435	5930	25	14390	2011_House_of_Delegates_General_Election_District_31
Kettle_Run	101	Fauquier_County	405	167	1	573	2013_House_of_Delegates_General_Election_District_31
Catlett	102	Fauquier_County	910	288	0	1198	2013_House_of_Delegates_General_Election_District_31
Casanova	103	Fauquier_County	364	208	0	572	2013_House_of_Delegates_General_Election_District_31
New_Baltimore	502	Fauquier_County	805	320	2	1127	2013_House_of_Delegates_General_Election_District_31
Vint_Hill	504	Fauquier_County	997	468	3	1468	2013_House_of_Delegates_General_Election_District_31
Saunders	201	Prince_William_County	484	818	1	1303	2013_House_of_Delegates_General_Election_District_31
Spriggs	207	Prince_William_County	525	709	1	1235	2013_House_of_Delegates_General_Election_District_31
Hylton	208	Prince_William_County	905	882	2	1789	2013_House_of_Delegates_General_Election_District_31
Independent_Hill	209	Prince_William_County	660	293	1	954	2013_House_of_Delegates_General_Election_District_31
Penn	210	Prince_William_County	76	178	0	254	2013_House_of_Delegates_General_Election_District_31
Pattie	305	Prince_William_County	605	591	4	1200	2013_House_of_Delegates_General_Election_District_31
Washington-reid	306	Prince_William_County	662	730	3	1395	2013_House_of_Delegates_General_Election_District_31
Henderson	307	Prince_William_County	637	571	4	1212	2013_House_of_Delegates_General_Election_District_31
Montclair	308	Prince_William_County	832	810	3	1645	2013_House_of_Delegates_General_Election_District_31
Ashland	309	Prince_William_County	437	459	2	898	2013_House_of_Delegates_General_Election_District_31
Forest_Park	310	Prince_William_County	340	559	1	900	2013_House_of_Delegates_General_Election_District_31
Beville	602	Prince_William_County	402	808	3	1213	2013_House_of_Delegates_General_Election_District_31
Godwin	603	Prince_William_County	189	369	5	563	2013_House_of_Delegates_General_Election_District_31
Minnieville	605	Prince_William_County	27	73	0	100	2013_House_of_Delegates_General_Election_District_31
Enterprise	608	Prince_William_County	300	617	2	919	2013_House_of_Delegates_General_Election_District_31
King	609	Prince_William_County	365	767	5	1137	2013_House_of_Delegates_General_Election_District_31
NA	NA	TOTALS	11508	11280	45	22833	2013_House_of_Delegates_General_Election_District_31
Kettle_Run	101	Fauquier_County	353	104	0	457	2015_House_of_Delegates_General_Election_District_31
Catlett	102	Fauquier_County	732	216	0	948	2015_House_of_Delegates_General_Election_District_31
Casanova	103	Fauquier_County	273	182	0	455	2015_House_of_Delegates_General_Election_District_31
New_Baltimore	502	Fauquier_County	642	238	0	880	2015_House_of_Delegates_General_Election_District_31
Vint_Hill	504	Fauquier_County	1029	422	1	1452	2015_House_of_Delegates_General_Election_District_31
Saunders	201	Prince_William_County	238	439	0	677	2015_House_of_Delegates_General_Election_District_31
Spriggs	207	Prince_William_County	430	493	0	923	2015_House_of_Delegates_General_Election_District_31
Hylton	208	Prince_William_County	474	219	1	694	2015_House_of_Delegates_General_Election_District_31
Independent_Hill	209	Prince_William_County	515	181	2	698	2015_House_of_Delegates_General_Election_District_31
Penn	210	Prince_William_County	23	31	0	54	2015_House_of_Delegates_General_Election_District_31
Pattie	305	Prince_William_County	427	345	2	774	2015_House_of_Delegates_General_Election_District_31
Washington-reid	306	Prince_William_County	568	504	1	1073	2015_House_of_Delegates_General_Election_District_31
Henderson	307	Prince_William_County	515	380	1	896	2015_House_of_Delegates_General_Election_District_31
Montclair	308	Prince_William_County	359	251	1	611	2015_House_of_Delegates_General_Election_District_31
Ashland	309	Prince_William_County	330	330	0	660	2015_House_of_Delegates_General_Election_District_31
Forest_Park	310	Prince_William_County	263	329	3	595	2015_House_of_Delegates_General_Election_District_31
Beville	602	Prince_William_County	359	722	2	1083	2015_House_of_Delegates_General_Election_District_31
Minnieville	605	Prince_William_County	22	36	0	58	2015_House_of_Delegates_General_Election_District_31
Enterprise	608	Prince_William_County	269	467	0	736	2015_House_of_Delegates_General_Election_District_31
King	609	Prince_William_County	334	621	1	956	2015_House_of_Delegates_General_Election_District_31
NA	NA	TOTALS	9506	8287	16	17809	2015_House_of_Delegates_General_Election_District_31