def store_walk_score():
"""Store walk score data into database.
Args:
walk_score_list: A list contains area zipcode and the walk score.
"""
try:
with open(r"..\data\walk score.csv","r") as csvfile:
file=csvfile.readlines()
except:
print("error")
else:
info_list=[]
for row in file:
row=row.strip()
row=row.split(",")
info_list.append(row)
sql.create_walk_score_table()
for r in range(1,len(info_list)):
walk_score=info_list[r][1]
walk_score=float(walk_score)
zip_code=info_list[r][0]
zip_code=str(zip_code)
zipcode_id=sql.get_zipcode_id(zip_code)
sql.insert_walk_score(walk_score,zipcode_id)
def store_income():
"""Store income data into database.
"""
sql.create_income_table()
try:
f = open(r"..\data\income.csv", "r")
except IOError as err:
print(f"File error: {err}.")
else:
file = csv.reader(f)
keys = next(file)
data = {}
for key in keys:
data[key] = []
for row in file:
for i, entry in enumerate(row):
data[keys[i]].append(entry)
for r in range(0, len(data["income"])):
income = data["income"][r]
income = int(income)
zip_code = data["zip code"][r]
zip_code = str(zip_code)
zipcode_id = sql.get_zipcode_id(zip_code)
sql.insert_income(income, zipcode_id)
f.close()
def store_community():
"""Store the community data into database.
"""
try:
with open(r"..\data\community.csv", "r") as csvfile:
file = csvfile.readlines()
except:
print("error")
else:
info_list = []
for row in file:
row = row.strip()
row = row.split(",")
info_list.append(row)
sql.create_community_table()
for r in range(1, len(info_list)):
name = info_list[r][1]
name = str(name)
zip_code = info_list[r][0]
zip_code = str(zip_code)
zipcode_id = sql.get_zipcode_id(zip_code)
sql.insert_community(zipcode_id, name)
def store_crime_rate():
"""Read the downloaded crime rate data file. Store the data in dictionary.
Raises:
IOError: An error occurred accessing the crime rate.csv file.
"""
try:
f = open(r"..\data\crime rate.csv", "r")
except IOError as err:
print(f"File error: {err}.")
else:
file = csv.reader(f)
keys = next(file)
data = {}
for key in keys:
data[key] = []
for row in file:
for i, entry in enumerate(row):
data[keys[i]].append(entry)
f.close()
sql.create_crime_rate_table()
for r in range(0, len(data["crime rate"])):
crime_rate = data["crime rate"][r]
crime_rate = float(crime_rate)
zip_code = data["zip code"][r]
zip_code = str(zip_code)
zipcode_id = sql.get_zipcode_id(zip_code)
sql.insert_crime_rate(crime_rate, zipcode_id)
def store_house_value():
"""Store hosing value data into database.
Args:
value_list: Return value from get_value() function.A list with area zipcode, hosing value and coordinates.
"""
try:
with open(r"..\data\housing value.csv","r") as csvfile:
file=csvfile.readlines()
except:
print("error")
else:
info_list=[]
for row in file:
row=row.strip()
row=row.split(",")
info_list.append(row)
sql.create_housing_value_talble()
for r in range(1,len(info_list)):
value=info_list[r][0]
if value!="-1":
value=int(value)
else:
value=-1
latitude=info_list[r][2]
latitude=float(latitude)
longitude=info_list[r][3]
longitude=float(longitude)
zip_code=info_list[r][1]
zip_code=str(zip_code)
zipcode_id=sql.get_zipcode_id(zip_code)
sql.insert_housing_value(value,latitude,longitude,zipcode_id)
def store_urban_data():
"""Read urbanicity data from local csv file.Store the data into database.
Raises:
IOError: An error occurred accessing the crime rate.csv file.
"""
try:
f = open(r"..\data\zip_urban_rural_ca.csv", "r")
except IOError as err:
print(f"File error: {err}.")
else:
file = csv.reader(f)
keys = next(file)
urban_dict = {}
for key in keys:
urban_dict[key] = []
for row in file:
for i, entry in enumerate(row):
urban_dict[keys[i]].append(entry)
f.close()
length = len(urban_dict['zip code'])
sql.create_urban_table()
for r in range(0, length):
urban_units = urban_dict["housing units urban"][r]
urban_units = str(urban_units)
rural_units = urban_dict["housing units rural"][r]
rural_units = str(rural_units)
density = float(urban_dict["population density"][r])
zip_code = urban_dict["zip code"][r]
zip_code = str(zip_code)
zipcode_id = sql.get_zipcode_id(zip_code)
sql.insert_urbanicity(urban_units, rural_units, density,
zipcode_id)