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