def by_city_and_state(self, city, state, standard_only=True): """Search zipcode information by City and State name. You can use either short state name and long state name. My engine use fuzzy match and guess what is you want. :param city: city name. :param state: 2 letter short name or long name. :param standard_only: boolean, default True, only returns standard type zipcode """ # check if it is a abbreviate name if state.upper() in self.all_state_short: state = state.upper() # if not, find out what is the state that user looking for else: choice, confidence = extractOne(state.lower(), self.all_state_long) if confidence < 70: raise Exception("'%s' is not a valid statename, use 2 letter " "short name or correct full name please." % state) state = STATE_ABBR_LONG_TO_SHORT[choice] # find out what is the city that user looking for select_sql = "SELECT City FROM zipcode WHERE State == '%s'" % state all_city = [record[0] for record in self.cursor.execute(select_sql)] choice, confidence = extractOne(city.lower(), all_city) if confidence < 70: raise Exception("Cannot found '%s' in '%s'." % (city, state)) else: city = choice # execute query select_sql = \ """ SELECT * FROM zipcode WHERE City = '%s' AND State = '%s' """ % (city, state) if standard_only: select_sql = select_sql + self._standard_only_param res = list() for row in self.cursor.execute(select_sql): res.append(Zipcode(self.all_column, list(row))) return res
def _find_state(self, state, best_match=True): """Fuzzy search correct state. :param best_match: bool, when True, only one state will return. otherwise, will return all matching states. """ result = list() # check if it is a abbreviate name if state.upper() in self.all_state_short: result.append(state.upper()) # if not, find out what is the state that user looking for else: if best_match: choice, confidence = extractOne( state.lower(), self.all_state_long) if confidence >= 70: result.append(STATE_ABBR_LONG_TO_SHORT[choice]) else: for choice, confidence in extract(state.lower(), self.all_state_long): if confidence >= 70: result.append(STATE_ABBR_LONG_TO_SHORT[choice]) if len(result) == 0: message = ("'%s' is not a valid state name, use 2 letter " "short name or correct full name please.") raise ValueError(message % state) return result
def by_city(self, city, standard_only=True): """Search zipcode information by City and State name. My engine use fuzzy match and guess what is you want. :param city: city name. :param standard_only: boolean, default True, only returns standard type zipcode """ # find out what is the city that user looking for select_sql = "SELECT City FROM zipcode WHERE City == '%s'" % city all_city = [record[0] for record in self.cursor.execute(select_sql)] choice, confidence = extractOne(city.lower(), all_city) if confidence < 70: raise Exception("Cannot found '%s' in '%s'." % (city, state)) else: city = choice # execute query select_sql = \ """ SELECT * FROM zipcode WHERE City = '%s' """ % (city,) if standard_only: select_sql = select_sql + self._standard_only_param res = list() for row in self.cursor.execute(select_sql): res.append(Zipcode(self.all_column, list(row))) return res
def _find_city(self, city, state=None, best_match=True): """Fuzzy search correct city. :param city: city name. :param state: search city in specified state. :param best_match: bool, when True, only one city will return. otherwise, will return all matching cities. **中文文档** 如果给定了state, 则只在state里的城市中寻找, 否则, 在全国所有的城市中 寻找。 """ # find out what is the city that user looking for if state: state = self._find_state(state, best_match=True)[0] select_sql = "SELECT DISTINCT City FROM zipcode WHERE State == '%s'" % state else: select_sql = "SELECT DISTINCT City FROM zipcode" all_city = [row[0] for row in self.cursor.execute(select_sql)] if len(all_city) == 0: raise ValueError("No city is available in state('%s')" % state) result = list() if best_match: choice, confidence = extractOne(city.lower(), all_city) if confidence >= 70: result.append(choice) else: for choice, confidence in extract(city.lower(), all_city): if confidence >= 70: result.append(choice) if len(result) == 0: raise ValueError("'%s' is not a valid city name" % city) return result