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'],
])
