saxXfAFZ

1#!/usr/bin/python
2import pg
3import sys
4import re
5
6db_host = 'twix'
7db_user = 'yuchen'
8db_pass = 'yuchen'
9db_name = 'pvsadmin'
10local_file_name = 'rating4878/4878.txt'
11table_name = 'scratch.import_4878'
12rating_id = '4878'
13state_id = 'NH'
14
15def main():
16	conn = pg.connect(db_name, db_host, -1, None, None, db_user, db_pass)
17	#rating_create_table(conn)
18	#rating_import(conn)
19	#rating_match(conn)
20	rating_update(conn)
21	#trunkate_rating_candidate(conn)
22	conn.close()
23	
24def trunkate_rating_candidate(conn):
25	sqlquery = '''UPDATE scratch.rating_candidate SET sig_rating = btrim(sig_rating), our_rating = btrim(our_rating)'''
26	do_query(conn, sqlquery, 1)
27	
28def rating_update(conn):
29	# pvs_convert_ratingstring(rating) 
30	sqlquery = '''INSERT INTO scratch.rating_candidate (rating_id, candidate_id, sig_rating, our_rating) SELECT 
31	rating_id, candidate_id, sig_rating, pvs_convert_ratingstring(sig_rating) 
32FROM ''' + table_name + ' WHERE candidate_id IS NOT NULL;'
33	do_query(conn, sqlquery, 1)
34	
35def rating_match(conn):
36	# "'" + state_id + "'" + 
37	sqlquery = 'update ' + table_name + """ p set candidate_id = c.candidate_id 
38FROM candidate c JOIN office_candidate oc USING (candidate_id) JOIN office o USING (office_id) 
39WHERE  
40oc.state_id = '""" + state_id + """' 
41AND btrim(p.last_name) = c.lastname
42AND p.first_name = c.firstname
43AND btrim(p.office) = o.name
44and p.candidate_id IS NULL     
45AND NOT EXISTS (SELECT 1 FROM candidate c2 JOIN office_candidate oc2 USING (candidate_id) 
46WHERE oc2.office_id = oc.office_id 
47AND btrim(p.last_name) = c2.lastname
48AND p.first_name = c2.firstname
49AND oc2.candidate_id != oc.candidate_id AND oc2.state_id = oc.state_id)"""
50	do_query(conn, sqlquery, 1)
51
52
53def rating_create_table(conn):
54	# create the table
55	sqlquery = 'CREATE TABLE ' + table_name + ''' ("candidate_id" integer, 
56							 "office" text, 
57							 "district" text, 
58							 "first_name" text,
59							 "last_name" text,
60							 "party" text,
61							 "our_rating" text,
62							 "sig_rating" text, 
63							 "rating_id" integer,
64							 "state_id" text);'''
65	#sqlquery = "SELECT * FROM " + table_name
66	do_query(conn, sqlquery, 1)
67	
68	
69def rating_import(conn):
70	# try opening the local file
71	fin = open(local_file_name)
72
73	# import from the local file
74	pa = re.compile('\t')
75	for line in fin.readlines():
76		tokens = pa.split(line[:-1])
77		if (len(tokens) != 6):
78			print 'Error with line ' + line
79		sqlquery = 'INSERT INTO ' + table_name + ' (office, district, first_name, last_name, party, sig_rating, rating_id)' + " VALUES ('" + tokens[0] + "','" + tokens[1] + "','" + tokens[2] + "','" + tokens[3] + "','" + tokens[4] + "','" + tokens[5] + "'," + rating_id + ");"
80		if (not do_query(conn, sqlquery, 0)):
81			print 'Halt.'
82			break;
83
84	fin.close()
85
86
87def do_query(conn, sqlquery, prompt):
88	print conn.query('BEGIN;')
89	print sqlquery
90	if (prompt):
91		while (1):
92			ch = str(raw_input('Proceed? Y/N:'))
93			if (ch == 'Y' or ch == 'y'):
94				try:
95					print conn.query(sqlquery)
96					print conn.query('COMMIT;')
97					return 1
98				except:
99					print conn.query('ROLLBACK;')
100					print 'Error. Rolled back.'
101					return 0
102					
103			elif (ch == 'N' or ch == 'n'):
104				print conn.query('ROLLBACK;');
105				return 0
106			else:
107				print 'Invalid input.'
108	else:
109		try:
110			print conn.query(sqlquery)
111			print conn.query('COMMIT;')
112			return 1
113		except:
114			print conn.query('ROLLBACK;')
115			print 'Error. Rolled back.'
116			return 0
117	
118
119if __name__ == "__main__":
120	sys.exit(main())