def post(self):
ward_code = self.request.get('ward_code')
district_code = self.request.get('district_code')
county_code = self.request.get('county_code')
counties = County.all().filter('code =', county_code).fetch(limit=1)
if ward_code:
districts = District.all().filter('code =', district_code).filter('county =', counties[0].key()).fetch(limit=1)
ward = Ward(
name=self.request.get('name'),
code=ward_code,
full_code=county_code + district_code + ward_code,
district=districts[0],
)
ward.put()
elif district_code:
district = District(
name=self.request.get('name'),
code=district_code,
county=counties[0],
)
district.put()
else:
county = County(
name=self.request.get('name'),
code=county_code,
)
county.put()
def addData(session, data_input):
try:
state = session.query(State).filter(State.state_name == data_input["State"]).one()
except:
state = State()
state.state_name = data_input["State"]
session.add(state)
try:
county = County()
county.county_name = data_input["County"]
county.majority_white = data_input["Ethnicities.White Alone, not Hispanic or Latino"]
county.state = state
session.add(county)
session.commit()
except exc.IntegrityError:
session.rollback()
def enter_county_data():
"""Inserts data for the City table"""
api_data = read_json()
db_cities = create_city_ids()
status_data = {
'state': None,
'city': None,
'city_id': None,
}
city_seen = {}
for dict_ in api_data:
city = dict_.get('City')
status_data['city'] = city
state = dict_['Province']
status_data['state'] = state
if status_data['city'] not in city_seen:
if status_data['city'] != "Unassigned":
county_inst = County(county_id=db_cities[status_data['city']],
county_name=status_data['city'],
state_name=status_data['state'])
db.session.add(county_inst)
city_seen[status_data['city']] = db_cities[status_data['city']]
db.session.commit()
def handle_voting_place(electoral_zone_code, county_code, county_name,
place_code, place_name):
place_db_id = place_cache.get(
f"{electoral_zone_code}#{county_code}#{place_code}")
if not place_db_id:
county_db_id = county_cache.get(
f"{electoral_zone_code}#{county_code}")
if not county_db_id:
electoral_zone_db_id = electoral_zone_cache.get(
electoral_zone_code)
if not electoral_zone_db_id:
electoral_zone = ElectoralZone.create(
code=electoral_zone_code)
electoral_zone_cache.update(
{electoral_zone_code: electoral_zone.id})
electoral_zone_db_id = electoral_zone.id
county = County.create(electoral_zone_id=electoral_zone_db_id,
code=county_code,
name=county_name)
county_cache.update(
{f"{electoral_zone_code}#{county_code}": county.id})
county_db_id = county.id
place = VotingPlace.create(county_id=county_db_id, name=place_name)
place_cache.update({
f"{electoral_zone_code}#{county_code}#{place_code}":
place.id
})
def add_county():
session = dbconnect()
request_dict = request.get_json()
try:
state_instance = session.query(State).filter(
State.id == request_dict["state_id"]).one()
except:
return "State does not exist, please add it", 400
try:
county = County()
county.county_name = data_input["County"]
county.majority_white = data_input[
"Ethnicities.White Alone, not Hispanic or Latino"]
county.state = state_instance
session.add(county)
session.commit()
return jsonify(county.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def load_counties():
"""Load counties from into database."""
print "Counties"
# Delete all rows in table, so if we need to run this a second time,
# we won't be trying to add duplicates
County.query.delete()
# Read identifiers.csv file and insert data
df = pd.read_csv('data/identifiers.csv')
print(df.columns)
df2 = pd.read_csv('data/DataIncomeCounty.csv').fillna(0)
income_counties = df2[(df2.State != 'State')]
for index, row in df.iterrows():
name = row[2]
x = income_counties[income_counties['County-equivalent'].str.contains(name)]
id_state = row[4]
id_county = row[5]
longitude = row[6]
latitude = row[7]
if (not x.empty):
percapita_income = x.iloc[0]['Per capita']
median_household_income = x.iloc[0]['Median household']
population = x.iloc[0]['Population']
number_households = x.iloc[0]['number households']
else:
percapita_income = 0
median_household_income = 0
population = 0
number_households = 0
county = County(county_id=id_county,
name=name,
state_id=id_state,
longitude=longitude,
latitude=latitude,
percapita_income = percapita_income,
median_household_income = median_household_income,
population = population,
number_households = number_households)
print id_state, "-", name, "-", id_county, longitude, latitude, percapita_income, median_household_income
# We need to add to the session or it won't ever be stored
db.session.add(county)
# Once we're done, we should commit our work
db.session.commit()
def get_counties():
"""Load counties from dataset into database."""
for i, row in enumerate(open('data/counties_data.csv')):
data = row.rstrip().split(",")
county_name, latitude, longitude, county_name_lower = data
county = County(county_name=county_name, latitude=latitude, longitude=longitude, county_name_lower=county_name_lower)
db.session.add(county)
if i % 100 == 0:
print(i)
db.session.commit()
def insert_county_data(db_cities):
"""Parsing JSON directly from API response and seed into database."""
confirmed_response = requests.get(URL)
dataset_confirmed = json.loads(confirmed_response.text)
status_data = {
'state': None,
'city': None,
'confirmed': None,
'date': None,
'city_id': None,
'lat': None,
'lon': None,
}
city_seen = {}
# Creating county_ids & inserting data into County Table
for dict_ in dataset_confirmed:
if dict_['City']:
city = dict_['City']
if dict_['Province']:
state = dict_['Province']
status_data['state'] = state
status_data['city'] = city + "," + " " + state
if dict_['Lat']:
lat = dict_['Lat']
status_data['lat'] = lat
if dict_['Lon']:
lon = dict_['Lon']
status_data['lon'] = lon
if status_data['city'] not in city_seen:
db_county = status_data['city'].split(",")
db_county_name = db_county[0]
county = County(county_name=db_county_name,
state_name=status_data['state'],
lat=status_data['lat'],
lon=status_data['lon'])
city_seen[status_data['city']] = status_data['city']
db.session.add(county)
db.session.commit()
print(f"Successfully created {county}")
def insert_county_data(dataset_confirmed, db_cities):
"""Inserting data into County Table"""
status_data = {
'state': None,
'city': None,
'confirmed': None,
'date': None,
'city_id': None,
'lat': None,
'lon': None,
}
city_seen = {}
for dict_ in dataset_confirmed:
if dict_['City']:
city = dict_['City']
if dict_['Province']:
state = dict_['Province']
status_data['state'] = state
status_data['city'] = city + "," + " " + state
if dict_['Lat']:
lat = dict_['Lat']
status_data['lat'] = lat
if dict_['Lon']:
lon = dict_['Lon']
status_data['lon'] = lon
if status_data['city'] not in city_seen:
db_county = status_data['city'].split(",")
db_county_name = db_county[0]
county = County(county_name=db_county_name,
state_name=status_data['state'],
lat=status_data['lat'],
lon=status_data['lon'])
city_seen[status_data['city']] = status_data['city']
db.session.add(county)
db.session.commit()
print(f"Successfully created {county}")
def seed_data_directly_from_api():
"""Parsing JSON directly from API response and seed into database."""
confirmed_response = requests.get(URL)
fatality_response = requests.get(URL2)
dataset_confirmed = json.loads(confirmed_response.text)
dataset_fatality = json.loads(fatality_response.text)
status_data = {
'state': None,
'city': None,
'confirmed': None,
'date': None,
'city_id': None,
'lat': None,
'lon': None,
}
city_seen = {}
db_cities = {}
i = 1
# Creating county_ids & inserting data into County Table
for dict_ in dataset_confirmed:
if dict_['City']:
city = dict_['City']
if dict_['Province']:
state = dict_['Province']
status_data['state'] = state
status_data['city'] = city + "," + " " + state
if dict_['Lat']:
lat = dict_['Lat']
status_data['lat'] = lat
if dict_['Lon']:
lon = dict_['Lon']
status_data['lon'] = lon
if status_data['city'] not in city_seen:
db_county = status_data['city'].split(",")
db_county_name = db_county[0]
county = County(county_name=db_county_name,
state_name=status_data['state'],
lat=status_data['lat'],
lon=status_data['lon'])
city_seen[status_data['city']] = status_data['city']
db_cities[status_data['city']] = i
i += 1
db.session.add(county)
db.session.commit()
print(f"Successfully created {county}")
print(db_cities)
json_file = json.dumps(db_cities)
f = open("db_cities.json", "w")
f.write(json_file)
f.close()
# Insert data into Confirmed Table
for dict_ in dataset_confirmed:
if dict_['City']:
city = dict_['City']
if dict_['Province']:
state = dict_['Province']
status_data['state'] = state
status_data['city'] = city + "," + " " + state
if (city + "," + " " + state) in db_cities:
status_data['city_id'] = db_cities[city + "," + " " + state]
case = dict_['Cases']
status_data['case'] = case
date = dict_['Date']
date = datetime.strptime(date[0:10], '%Y-%m-%d')
status_data['date'] = date
confirmed = Confirmed(confirmed=int(status_data['case']),
date=status_data['date'],
county_id=int(status_data['city_id']),
state_name=status_data['state'])
db.session.add(confirmed)
db.session.commit()
print(f"Successfully created {confirmed}")
# Insert data into Fatality Table
for dict_ in dataset_fatality:
if dict_['City']:
city = dict_['City']
if dict_['Province']:
state = dict_['Province']
status_data['state'] = state
status_data['city'] = city + "," + " " + state
if (city + "," + " " + state) in db_cities:
status_data['city_id'] = db_cities[city + "," + " " + state]
if dict_['Cases']:
case = dict_['Cases']
status_data['case'] = case
if dict_['Date']:
date = dict_['Date']
date = datetime.strptime(date[0:10], '%Y-%m-%d')
status_data['date'] = date
fatality = Fatality(fatalities=int(status_data['case']),
date=status_data['date'],
county_id=int(status_data['city_id']),
state_name=status_data['state'])
db.session.add(fatality)