def delete_data():
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# delete median value records across all tables
cursor.execute(
DELETE_MEDIAN_VALUE_PER_SQFT_TEMPLATE.format(
MEDIAN_VALUE_PER_SQFT_ALL_HOMES_TABLE))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
def delete_data():
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# delete price cut records across all tables
cursor.execute(DELETE_MEDIAN_PRICE_CUT_TEMPLATE.format(MEDIAN_PRICE_CUT_ALL_HOMES_TABLE))
cursor.execute(DELETE_MEDIAN_PRICE_CUT_TEMPLATE.format(MEDIAN_PRICE_CUT_CONDO_TABLE))
cursor.execute(DELETE_MEDIAN_PRICE_CUT_TEMPLATE.format(MEDIAN_PRICE_CUT_SFR_TABLE))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
def delete_data():
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# delete listings price reduction records across all tables
cursor.execute(DELETE_PERCENT_LISTINGS_PRICE_REDUCTION_TEMPLATE.format(PERCENT_LISTINGS_PRICE_REDUCTION_ALL_HOMES_TABLE))
cursor.execute(DELETE_PERCENT_LISTINGS_PRICE_REDUCTION_TEMPLATE.format(PERCENT_LISTINGS_PRICE_REDUCTION_CONDO_TABLE))
cursor.execute(DELETE_PERCENT_LISTINGS_PRICE_REDUCTION_TEMPLATE.format(PERCENT_LISTINGS_PRICE_REDUCTION_SFR_TABLE))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
def update(self):
# clear county dictionary
self.county_data.clear()
# retrieve county records from database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
cursor.execute(SELECT_COUNTIES_TEMPLATE)
# create County objects from records
for (id, state_id, county_name) in cursor:
self.county_data[int(id)] = County(int(id), int(state_id),
county_name)
# close connection and cursor
cursor.close()
db_conn.close()
def insert_data(self):
# open file
data_file = open("data/demographic/median_income.csv", "r")
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# parse data lines
data_lines = data_file.readlines()
for data_line in data_lines[2:]:
# split data line
data_line = data_line.replace('\"', '').split(',')
# get zip field from data line
zip_data = data_line[ZIP_INDEX]
zip_data = zip_data.split(' ')[1]
# filter based on Los Angeles zips
if int(zip_data) not in zip_codes:
continue
# get median income data if zip is in LA list
median_income_data = data_line[MEDIAN_INCOME_INDEX]
# get parent record objects
state = self.state_dao.insert_data("CA")
county = self.county_dao.insert_data(state.state_id, "Los Angeles")
metro = self.metro_dao.insert_data(state.state_id, "Los Angeles-Long Beach-Anaheim")
city = self.city_dao.insert_data(county.county_id, metro.metro_id, state.state_id, "Los Angeles")
zip_code = self.zip_dao.insert_data(city.city_id,
county.county_id,
metro.metro_id,
state.state_id,
zip_data)
# insert median income records
cursor.execute(INSERT_MEDIAN_INCOME_TEMPLATE.format(zip_code.zip_id, int(median_income_data)))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
def update(self):
# clear metro dictionary
self.metro_data.clear()
# retrieve metro records from database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
cursor.execute(SELECT_METROS_TEMPLATE)
# create Metro objects from records
for (id, state_id, metro_name) in cursor:
self.metro_data[int(id)] = Metro(int(id), int(state_id),
metro_name)
# close connection and cursor
cursor.close()
db_conn.close()
def update(self):
# clear zip dictionary
self.zip_data.clear()
# retrieve zip records from database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
cursor.execute(SELECT_ZIPS_TEMPLATE)
# create Zip objects from records
for (id, city_id, county_id, metro_id, state_id, zip_code) in cursor:
self.zip_data[int(id)] = Zip(int(id), int(city_id), int(county_id),
int(metro_id), int(state_id),
zip_code)
# close connection and cursor
cursor.close()
db_conn.close()
def delete_data():
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# delete percent homes values records across all tables
cursor.execute(
DELETE_PERCENT_HOMES_VALUES_TEMPLATE.format(
PERCENT_HOMES_VALUES_INCREASING_TABLE))
cursor.execute(
DELETE_PERCENT_HOMES_VALUES_TEMPLATE.format(
PERCENT_HOMES_VALUES_DECREASING_TABLE))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
def insert_data(self):
data_file = open("data/zillow/Zip_MedianValuePerSqft_AllHomes.csv",
"r")
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# parse data lines
data_lines = data_file.readlines()
date_fields = data_lines[0].replace(
'\"', '').split(',')[MEDIAN_VALUE_PER_SQFT_DATA_MIN_INDEX:]
for data_line in data_lines[1:]:
# split data line
data_line = data_line.replace('\"', '').split(',')
# get record fields from data line
zip_data = data_line[ZIP_INDEX]
state_data = data_line[STATE_INDEX]
county_data = data_line[COUNTY_NAME_INDEX]
metro_data = data_line[METRO_INDEX]
city_data = data_line[CITY_INDEX]
median_price_cut_data = \
data_line[MEDIAN_VALUE_PER_SQFT_DATA_MIN_INDEX:]
# get parent record objects
state = self.state_dao.insert_data(state_data)
county = self.county_dao.insert_data(state.state_id, county_data)
metro = self.metro_dao.insert_data(state.state_id, metro_data)
city = self.city_dao.insert_data(county.county_id, metro.metro_id,
state.state_id, city_data)
zip_code = self.zip_dao.insert_data(city.city_id, county.county_id,
metro.metro_id, state.state_id,
zip_data)
# insert median value records
for date_time, median_price_cut in zip(date_fields,
median_price_cut_data):
# format date
date_time = date_time.split('-')
date_time = date(int(date_time[0]), int(date_time[1]),
1) # MM/1/YYYY
# format median value
if median_price_cut == '':
median_price_cut = "NULL"
else:
median_price_cut = float(median_price_cut)
cursor.execute(
INSERT_MEDIAN_VALUE_PER_SQFT_TEMPLATE.format(
MEDIAN_VALUE_PER_SQFT_ALL_HOMES_TABLE, zip_code.zip_id,
date_time.isoformat(), median_price_cut))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
def insert_percent_homes_values_data(self, table, data_file):
# get connection to database
db_conn = DatabaseManager.get_connection()
cursor = db_conn.cursor()
# parse data lines
data_lines = data_file.readlines()
date_fields = data_lines[0].replace(
'\"', '').split(',')[PERCENT_HOMES_VALUES_DATA_MIN_INDEX:]
for data_line in data_lines[1:]:
# split data line
data_line = data_line.replace('\"', '').split(',')
# get record fields from data line
zip_data = data_line[ZIP_INDEX]
state_data = data_line[STATE_INDEX]
county_data = data_line[COUNTY_NAME_INDEX]
metro_data = data_line[METRO_INDEX]
city_data = data_line[CITY_INDEX]
percent_home_value_data = \
data_line[PERCENT_HOMES_VALUES_DATA_MIN_INDEX:]
# get parent record objects
state = self.state_dao.insert_data(state_data)
county = self.county_dao.insert_data(state.state_id, county_data)
metro = self.metro_dao.insert_data(state.state_id, metro_data)
city = self.city_dao.insert_data(county.county_id, metro.metro_id,
state.state_id, city_data)
zip_code = self.zip_dao.insert_data(city.city_id, county.county_id,
metro.metro_id, state.state_id,
zip_data)
# insert percent homes values records
for date_time, percent_home_value in zip(date_fields,
percent_home_value_data):
# format date
date_time = date_time.split('-')
date_time = date(int(date_time[0]), int(date_time[1]),
1) # MM/1/YYYY
# format percent homes values
if percent_home_value == '':
percent_home_value = "NULL"
else:
try:
percent_home_value = float(percent_home_value)
except:
continue
cursor.execute(
INSERT_PERCENT_HOMES_VALUES_TEMPLATE.format(
table, zip_code.zip_id, date_time.isoformat(),
percent_home_value))
# commit transaction
db_conn.commit()
# close cursor and connection
cursor.close()
db_conn.close()
