def find_matching_landmarks(cls, data): """ Looks in the database for landmarks matching datastring :param data: string we want to check against landmarks :type data: str :return: list of landmarks :rtype: list """ database = LocationDatabase.get_database() cursor = database.cursor() try: rows = cursor.execute( 'SELECT * FROM landmark WHERE resource like ?', (data + '%',) ).fetchall() entities = LocationDatabase.hydrate(rows, LandmarkEntity) entities = \ LocationDatabase.substitute_country_data(entities, cursor) entities = [vars(x) for x in entities] database.close() except sqlite3.Error: database.close() return return entities
def get_street_suffix(cls, data_set): """ Looks at our data for a street suffix and returns the entity :param data_set: the list of parts of address data :type data_set: list :return: the street suffix if one was found :rtype: list """ sql_items = [] for _ in data_set: sql_items.append('suffix_name = ?') database = LocationDatabase.get_database() cursor = database.cursor() try: row = cursor.execute( 'SELECT * FROM street_suffix WHERE ' + " or ".join(sql_items), tuple(data_set) ).fetchone() database.close() except sqlite3.Error: database.close() return None if row is not None: return LocationDatabase.hydrate(row, StreetSuffixEntity)
def get_street_suffix(cls, data_set): """ Looks at our data for a street suffix and returns the entity :param data_set: the list of parts of address data :type data_set: list :return: the street suffix if one was found :rtype: list """ sql_items = [] for _ in data_set: sql_items.append('suffix_name = ?') database = LocationDatabase.get_database() cursor = database.cursor() try: row = cursor.execute( 'SELECT * FROM street_suffix WHERE ' + " or ".join(sql_items), tuple(data_set)).fetchone() database.close() except sqlite3.Error: database.close() return None if row is not None: return LocationDatabase.hydrate(row, StreetSuffixEntity)
def test_hydrate_with_non_lists(self): data = ("US",) result = LocationDatabase.hydrate(data, StreetSuffixEntity) self.assertIsInstance(result, StreetSuffixEntity) with self.assertRaises(TypeError): LocationDatabase.hydrate("foo", StreetSuffixEntity)
def test_hydrate_with_non_lists(self): data = ('US', ) result = LocationDatabase.hydrate(data, StreetSuffixEntity) self.assertIsInstance(result, StreetSuffixEntity) with self.assertRaises(TypeError): LocationDatabase.hydrate('foo', StreetSuffixEntity)
def test_select_returns_expected_data(self): SQLite3Mock.fetchall_returns = [ sqlite3.Error("Error"), [], [("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l")], [("us", "united states")], ] ldb = LocationDatabase() # Country Result result = ldb.single_select("sql1", ("tuple1",), CountryEntity) self.assertIsInstance(result, list) self.assertEqual(1, len(result)) self.assertIsInstance(result[0], CountryEntity) self.assertEqual(result[0].abbreviation, "us") self.assertEqual(result[0].name, "united states") # City Result result = ldb.single_select("sql2", ("tuple2",), CityEntity) self.assertIsInstance(result, list) self.assertEqual(1, len(result)) self.assertIsInstance(result[0], CityEntity) self.assertEqual(result[0].country, "a") self.assertEqual(result[0].postal_code, "b") self.assertEqual(result[0].city, "c") self.assertEqual(result[0].state1, "d") self.assertEqual(result[0].state2, "e") self.assertEqual(result[0].province1, "f") self.assertEqual(result[0].province2, "g") self.assertEqual(result[0].community1, "h") self.assertEqual(result[0].community2, "i") self.assertEqual(result[0].latitude, "j") self.assertEqual(result[0].longitude, "k") self.assertEqual(result[0].coord_accuracy, "l") # No results result = ldb.single_select("sql3", ("tuple3",), CityEntity) self.assertIsNone(result) # Error while fetching result = ldb.single_select("sql4", ("tuple4",), CountryEntity) self.assertIsNone(result) self.assertEqual( SQLite3Mock.execute_calls, [ ("PRAGMA temp_store = 2", None), ("sql1", ("tuple1",)), ("PRAGMA temp_store = 2", None), ("sql2", ("tuple2",)), ("PRAGMA temp_store = 2", None), ("sql3", ("tuple3",)), ("PRAGMA temp_store = 2", None), ("sql4", ("tuple4",)), ], )
def get_postal_code_data(cls, data): """ If this looks like a postal code, we try to get its info :param data: a possibly detected zip code :type data: str :return: possible locations for this zip code :rtype: list """ plus_postal_code = '-' in data if plus_postal_code: prefix = data.split('-')[0] database = LocationDatabase.get_database() cursor = database.cursor() try: rows = cursor.execute( 'SELECT * FROM city WHERE postal_code = ?', (data,) ).fetchall() entities = LocationDatabase.hydrate(rows, CityEntity) except sqlite3.Error: database.close() return None if plus_postal_code: try: rows = cursor.execute( 'SELECT * FROM city WHERE postal_code = ?', (prefix,) ).fetchall() prefix_entities = LocationDatabase.hydrate(rows, CityEntity) entities.extend(prefix_entities) except sqlite3.Error: database.close() return None if not entities: database.close() return None entities = LocationDatabase.substitute_country_data(entities, cursor) entities = [vars(x) for x in entities] database.close() return entities
def test_substitute_country_data(self): SQLite3Mock.fetchall_returns = [sqlite3.Error("Error")] entity = CityEntity(("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l")) result = LocationDatabase.substitute_country_data([entity], SQLite3Mock) self.assertEqual(SQLite3Mock.execute_calls, [("SELECT * FROM country WHERE abbreviation = ?", ("a",))]) self.assertIsInstance(result[0], CityEntity)
def find_matching_landmarks(cls, data): """Looks in the database for landmarks matching datastring""" database = LocationDatabase.get_database() cursor = database.cursor() try: rows = cursor.execute( 'SELECT * FROM landmark WHERE resource like ?', (data + '%', )).fetchall() entities = LocationDatabase.hydrate(rows, LandmarkEntity) entities = \ LocationDatabase.substitute_country_data(entities, cursor) entities = [vars(x) for x in entities] database.close() except sqlite3.Error: database.close() return return entities
def test_substitute_country_data(self): SQLite3Mock.fetchall_returns = [sqlite3.Error('Error')] entity = CityEntity( ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l')) result = \ LocationDatabase.substitute_country_data([entity], SQLite3Mock) self.assertEqual(SQLite3Mock.execute_calls, [('SELECT * FROM country WHERE abbreviation = ?', ('a', ))]) self.assertIsInstance(result[0], CityEntity)
def find_address_tokens(self, data, results): """ Looking for known city or country, zip, etc. :param data: list of address related tokens we want to check :type data: list :param results: the address result dict :type results: dict :return: the address result dict :rtype: dict """ tokens = 0 working_data = list(data) data_combinations = list(itertools.combinations(working_data, 2)) data_combinations = [" ".join(x) for x in data_combinations] working_data.extend(data_combinations) database = LocationDatabase.get_database() cursor = database.cursor() for test in [x for x in working_data if not self.is_invalid_int(x)]: params = (test, test, test, test, test) sql = 'SELECT count(*) FROM city WHERE ' +\ 'country = ? OR ' +\ 'postal_code = ? OR ' +\ 'city = ? OR ' +\ 'state1 = ? OR ' +\ 'state2 = ?' try: row = cursor.execute(sql, params).fetchone() row = row[0] except sqlite3.Error: database.close() return 0, results if row: entity = {} entity['token'] = test entity['matches'] = row results['address_tokens'].append(entity) tokens += 1 self.confidence += 20 database.close() return (tokens, results)
def get_postal_code_data(cls, data): """If this looks like a postal code, we try to get its info""" plus_postal_code = '-' in data if plus_postal_code: prefix = data.split('-')[0] database = LocationDatabase.get_database() cursor = database.cursor() try: rows = cursor.execute('SELECT * FROM city WHERE postal_code = ?', (data, )).fetchall() entities = LocationDatabase.hydrate(rows, CityEntity) except sqlite3.Error: database.close() return None if plus_postal_code: try: rows = cursor.execute( 'SELECT * FROM city WHERE postal_code = ?', (prefix, )).fetchall() prefix_entities = LocationDatabase.hydrate(rows, CityEntity) entities.extend(prefix_entities) except sqlite3.Error: database.close() return None if not entities: database.close() return None entities = LocationDatabase.substitute_country_data(entities, cursor) entities = [vars(x) for x in entities] database.close() return entities
def test_select_returns_expected_data(self): SQLite3Mock.fetchall_returns = [ sqlite3.Error('Error'), [], [('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l')], [('us', 'united states')] ] ldb = LocationDatabase() # Country Result result = ldb.single_select('sql1', ('tuple1', ), CountryEntity) self.assertIsInstance(result, list) self.assertEqual(1, len(result)) self.assertIsInstance(result[0], CountryEntity) self.assertEqual(result[0].abbreviation, 'us') self.assertEqual(result[0].name, 'united states') # City Result result = ldb.single_select('sql2', ('tuple2', ), CityEntity) self.assertIsInstance(result, list) self.assertEqual(1, len(result)) self.assertIsInstance(result[0], CityEntity) self.assertEqual(result[0].country, 'a') self.assertEqual(result[0].postal_code, 'b') self.assertEqual(result[0].city, 'c') self.assertEqual(result[0].state1, 'd') self.assertEqual(result[0].state2, 'e') self.assertEqual(result[0].province1, 'f') self.assertEqual(result[0].province2, 'g') self.assertEqual(result[0].community1, 'h') self.assertEqual(result[0].community2, 'i') self.assertEqual(result[0].latitude, 'j') self.assertEqual(result[0].longitude, 'k') self.assertEqual(result[0].coord_accuracy, 'l') # No results result = ldb.single_select('sql3', ('tuple3', ), CityEntity) self.assertIsNone(result) # Error while fetching result = ldb.single_select('sql4', ('tuple4', ), CountryEntity) self.assertIsNone(result) self.assertEqual(SQLite3Mock.execute_calls, [ ('PRAGMA temp_store = 2', None), ('sql1', ('tuple1', )), ('PRAGMA temp_store = 2', None), ('sql2', ('tuple2', )), ('PRAGMA temp_store = 2', None), ('sql3', ('tuple3', )), ('PRAGMA temp_store = 2', None), ('sql4', ('tuple4', )), ])
def test_select(self): ldb = LocationDatabase() with self.assertRaises(TypeError): ldb.single_select("sql", "burp", CityEntity)
def test_select(self): ldb = LocationDatabase() with self.assertRaises(TypeError): ldb.single_select('sql', 'burp', CityEntity)