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()