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