def upsert_mayors(mayor): mayor['former_names'] = mayor.get('former_names', []) variants = common.make_variants_set(mayor['name']) mayor['identifiers'] = list((variants | set(mayor['former_names']) | {mayor['name'], re.sub(u'[\w‧]', '', mayor['name']), re.sub(u'\W', '', mayor['name']).lower(), }) - {''}) complement = {"birth": None} complement.update(mayor) c.execute(''' INSERT INTO mayors_mayors(uid, name, birth, identifiers) VALUES (%(uid)s, %(name)s, %(birth)s, %(identifiers)s) ON CONFLICT (uid) DO UPDATE SET name = %(name)s, birth = %(birth)s, identifiers = %(identifiers)s ''', complement)
def Councilors(councilor): councilor['former_names'] = councilor.get('former_names', []) variants = common.make_variants_set(councilor['name']) councilor['identifiers'] = list( (variants | set(councilor['former_names']) | { councilor['name'], re.sub(u'[\w‧]', '', councilor['name']), re.sub(u'\W', '', councilor['name']).lower(), }) - {''}) councilor['former_names'] = '\n'.join(councilor['former_names']) complement = {"birth": None} complement.update(councilor) c.execute( ''' INSERT INTO councilors_councilors(uid, name, birth, former_names, identifiers) VALUES (%(uid)s, %(name)s, %(birth)s, %(former_names)s, %(identifiers)s) ON CONFLICT (uid) DO UPDATE SET name = %(name)s, birth = %(birth)s, former_names = %(former_names)s, identifiers = %(identifiers)s ''', complement)
def upsertCandidates(candidate): candidate['former_names'] = candidate.get('former_names', []) variants = common.make_variants_set(candidate['name']) candidate['identifiers'] = list( (variants | set(candidate['former_names']) | { candidate['name'], re.sub(u'[\w‧]', '', candidate['name']), re.sub(u'\W', '', candidate['name']).lower(), }) - {''}) complement = { 'birth': None, 'gender': '', 'party': '', 'number': None, 'contact_details': None, 'district': '', 'education': None, 'experience': None, 'remark': None, 'image': '', 'links': None, 'platform': '', 'data': None } complement.update(candidate) c.execute( ''' INSERT INTO candidates_candidates(uid, name, birth, identifiers) VALUES (%(candidate_uid)s, %(name)s, %(birth)s, %(identifiers)s) ON CONFLICT (uid) DO UPDATE SET name = %(name)s, birth = %(birth)s, identifiers = %(identifiers)s ''', complement) c.execute( ''' INSERT INTO candidates_terms(uid, candidate_id, type, election_year, number, name, gender, party, constituency, county, district, contact_details, education, experience, remark, image, links, platform, votes, votes_percentage, votes_detail, elected, occupy) VALUES (%(candidate_term_uid)s, %(candidate_uid)s, %(type)s, %(election_year)s, %(number)s, %(name)s, %(gender)s, %(party)s, %(constituency)s, %(county)s, %(district)s, %(contact_details)s, %(education)s, %(experience)s, %(remark)s, %(image)s, %(links)s, %(platform)s, %(votes)s, %(votes_percentage)s, %(votes_detail)s, %(elected)s, %(occupy)s) ON CONFLICT (election_year, candidate_id) DO UPDATE SET type = %(type)s, number = %(number)s, name = %(name)s, gender = %(gender)s, party = %(party)s, constituency = %(constituency)s, county = %(county)s, district = %(district)s, contact_details = %(contact_details)s, education = %(education)s, experience = %(experience)s, remark = %(remark)s, image = %(image)s, links = %(links)s, votes = %(votes)s, votes_percentage = %(votes_percentage)s, votes_detail = %(votes_detail)s, elected = %(elected)s, occupy = %(occupy)s ''', complement)
def upsertCandidates(candidate): candidate['former_names'] = candidate.get('former_names', []) variants = common.make_variants_set(candidate['name']) candidate['identifiers'] = list((variants | set(candidate['former_names']) | {candidate['name'], re.sub(u'[\w‧]', '', candidate['name']), re.sub(u'\W', '', candidate['name']).lower(), }) - {''}) c.execute(''' SELECT district FROM councilors_councilorsdetail WHERE county = %(previous_county)s AND constituency = %(constituency)s ORDER BY election_year DESC ''', candidate) r = c.fetchone() if r: candidate['district'] = r[0] for district_change in district_versions[election_year].get(candidate['county'], []): if candidate['constituency'] == district_change['constituency']: candidate['district'] = district_change['district'] candidate['constituency_change'] = district_change break complement = {'birth': None, 'gender': '', 'party': '', 'number': None, 'contact_details': None, 'district': '', 'education': None, 'experience': None, 'remark': None, 'image': '', 'links': None, 'platform': '', 'data': None} complement.update(candidate) c.execute(''' INSERT INTO candidates_candidates(uid, name, birth, identifiers) VALUES (%(candidate_uid)s, %(name)s, %(birth)s, %(identifiers)s) ON CONFLICT (uid) DO UPDATE SET name = %(name)s, birth = %(birth)s, identifiers = %(identifiers)s ''', complement) c.execute(''' INSERT INTO candidates_terms(uid, candidate_id, elected_councilor_id, councilor_terms, election_year, number, name, gender, party, constituency, county, district, contact_details, education, experience, remark, image, links, platform, type) VALUES (%(candidate_term_uid)s, %(candidate_uid)s, %(councilor_term_id)s, %(councilor_terms)s, %(election_year)s, %(number)s, %(name)s, %(gender)s, %(party)s, %(constituency)s, %(county)s, %(district)s, %(contact_details)s, %(education)s, %(experience)s, %(remark)s, %(image)s, %(links)s, %(platform)s, %(type)s) ON CONFLICT (election_year, candidate_id) DO UPDATE SET elected_councilor_id = %(councilor_term_id)s, councilor_terms = %(councilor_terms)s, number = %(number)s, name = %(name)s, gender = %(gender)s, party = %(party)s, constituency = %(constituency)s, county = %(county)s, district = %(district)s, contact_details = %(contact_details)s, education = %(education)s, experience = %(experience)s, remark = %(remark)s, image = %(image)s, links = %(links)s ''', complement) if complement.get('constituency_change'): c.execute(''' UPDATE candidates_terms SET data = (COALESCE(data, '{}'::jsonb) || %s::jsonb) WHERE election_year = %s AND candidate_id = %s ''', (json.dumps({'constituency_change': complement['constituency_change']}), complement['election_year'], complement['candidate_uid']))
def upsertCandidates(candidate): candidate['former_names'] = candidate.get('former_names', []) variants = common.make_variants_set(candidate['name']) candidate['identifiers'] = list( (variants | set(candidate['former_names']) | { candidate['name'], re.sub(u'[\w‧]', '', candidate['name']), re.sub(u'\W', '', candidate['name']).lower(), }) - {''}) complement = { 'birth': None, 'gender': '', 'party': '', 'number': None, 'contact_details': None, 'district': '', 'education': None, 'experience': None, 'remark': None, 'image': '', 'links': None, 'platform': '', 'data': None, 'occupy': None } complement.update(candidate) c.execute( ''' INSERT INTO candidates_candidates(uid, name, birth, identifiers) VALUES (%(candidate_uid)s, %(name)s, %(birth)s, %(identifiers)s) ON CONFLICT (uid) DO UPDATE SET name = %(name)s, birth = %(birth)s, identifiers = %(identifiers)s ''', complement) c.execute( ''' INSERT INTO candidates_terms(uid, candidate_id, elected_councilor_id, councilor_terms, election_year, number, name, gender, party, constituency, county, district, contact_details, education, experience, remark, image, links, platform, type, occupy) VALUES (%(candidate_term_uid)s, %(candidate_uid)s, %(councilor_term_id)s, %(councilor_terms)s, %(election_year)s, %(number)s, %(name)s, %(gender)s, %(party)s, %(constituency)s, %(county)s, %(district)s, %(contact_details)s, %(education)s, %(experience)s, %(remark)s, %(image)s, %(links)s, %(platform)s, %(type)s, %(occupy)s) ON CONFLICT (election_year, candidate_id) DO UPDATE SET elected_councilor_id = %(councilor_term_id)s, councilor_terms = %(councilor_terms)s, number = %(number)s, name = %(name)s, gender = %(gender)s, party = %(party)s, constituency = %(constituency)s, county = %(county)s, district = %(district)s, contact_details = %(contact_details)s, education = %(education)s, experience = %(experience)s, remark = %(remark)s, image = %(image)s, links = %(links)s, occupy = %(occupy)s ''', complement) terms = [] for t in ['mayor', 'legislator', 'councilor']: if candidate.get('%s_terms' % t): for term in candidate['%s_terms' % t]: term['type'] = t terms.append(term) c.execute( ''' UPDATE candidates_terms SET data = (COALESCE(data, '{}'::jsonb) || %s::jsonb) WHERE election_year = %s and candidate_id = %s ''', [ json.dumps({'terms': terms}), complement['election_year'], complement['candidate_uid'], ]) if candidate.get('mayor_terms'): c.execute( ''' UPDATE candidates_terms SET data = (COALESCE(data, '{}'::jsonb) || %s::jsonb) WHERE election_year = %s and candidate_id = %s ''', [ json.dumps({'mayor_terms': complement['mayor_terms']}), complement['election_year'], complement['candidate_uid'], ]) if candidate.get('legislator_terms'): c.execute( ''' UPDATE candidates_terms SET data = (COALESCE(data, '{}'::jsonb) || %s::jsonb) WHERE election_year = %s and candidate_id = %s ''', [ json.dumps( {'legislator_terms': complement['legislator_terms']}), complement['election_year'], complement['candidate_uid'], ]) if candidate.get('legislator_data'): c.execute( ''' UPDATE candidates_terms SET data = (COALESCE(data, '{}'::jsonb) || %s::jsonb) WHERE election_year = %s and candidate_id = %s ''', [ json.dumps({'legislator_data': complement['legislator_data']}), complement['election_year'], complement['candidate_uid'], ]) if candidate.get('legislator_candidate_info'): c.execute( ''' UPDATE candidates_terms SET politicalcontributions = COALESCE(politicalcontributions, '[]'::jsonb) || %s::jsonb WHERE election_year >= %s and candidate_id = %s ''', [ candidate['legislator_candidate_info'] ['politicalcontributions'], complement['election_year'], complement['candidate_uid'], ]) c.execute( ''' UPDATE candidates_terms SET politicalcontributions = (SELECT jsonb_agg(x) FROM ( SELECT x from ( SELECT DISTINCT(value) as x FROM jsonb_array_elements(politicalcontributions) ) t ORDER BY x->'election_year' DESC ) tt) WHERE candidate_id = %s AND election_year >= %s ''', [ complement['candidate_uid'], complement['election_year'], ])