def geocode(self):
"""Update latitude, longitude, rating and ZIP in Locations table."""
print('\nGeocoding...')
null_rating_rows = self.get_rows_with_null_rating()
for row in null_rating_rows:
full_address = "{0} {1}, New Orleans, LA".format(
row.street_number, row.address)
result = self.gmaps.geocode(full_address)
if len(result) == 0:
log.info('No geocoding results for: {}'.format(full_address))
# TODO: Need to also note failure so future geocoding scripts
# don't keep trying and failing on the same addresses.
# Possibly update Location's `rating` and/or Cleaned's
# `location_publish` fields.
continue
details = self.process_google_results(result)
try:
with SESSION.begin_nested():
u = update(Location)
u = u.values(details)
u = u.where(Location.document_id == row.document_id)
SESSION.execute(u)
SESSION.flush()
except Exception as error: # TODO: Handle specific errors.
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def login(self):
"""Load homepage, find login, enter credentials."""
self.load_homepage()
# time.sleep(1.0)
self.find_login_link()
log.info('Sleep 1.0 second')
time.sleep(1.0)
self.enter_username()
log.info('Sleep 1.0 second')
time.sleep(1.0)
self.enter_password()
log.info('Sleep 5.0 seconds')
time.sleep(5.0)
try:
self.driver.find_element_by_id("Header1_lnkLogout")
log.info("Login successful")
except Exception as error:
log.info("Login failed")
log.exception(error)
raise
def parse_sale(self, j, rows, year, month, day):
"""Parse single sale page and save HTML."""
document_id = rows[j].string
url = ('http://onlinerecords.orleanscivilclerk.com/RealEstate/' +
'SearchResults.aspx?global_id={}&type=dtl').format(document_id)
try:
log.info('Load sale URL {}'.format(url))
self.driver.get(url)
except Exception: # TODO
log.exception('Error loading sale URL {}'.format(url))
html = self.driver.page_source
html_out = "{0}/data/raw/{1}-{2}-{3}/form-html/{4}.html".format(
PROJECT_DIR, year, month, day, document_id)
log.info('Save {}'.format(html_out))
with open(html_out, "wb") as f_out:
f_out.write(html.encode('utf-8'))
try:
assert not self.is_error_page(html_out) # TODO: Read from memory
except Exception: # TODO
log.exception('Received error page')
log.info('Deleting error page {}'.format(html_out))
os.remove(html_out)
def other_stuff_location_info(rows):
"""Run checks for location_info."""
for row in rows:
# To remove district ordinal
row['location_info'] = row['location_info'].replace('1st', '1')
row['location_info'] = row['location_info'].replace('2nd', '2')
row['location_info'] = row['location_info'].replace('3rd', '3')
row['location_info'] = row['location_info'].replace('4th', '4')
row['location_info'] = row['location_info'].replace('5th', '5')
row['location_info'] = row['location_info'].replace('6th', '6')
row['location_info'] = row['location_info'].replace('7th', '7')
all_locations_text = ''
list1 = row['location_info'].split(';')
for i in list1:
list2 = i.split(',')
individiual_location_text = ''
for j in list2:
try:
if j.strip()[-1] != ':':
# If first addition:
if individiual_location_text == '':
individiual_location_text = j.strip()
else: # If second addition or later
individiual_location_text = (
individiual_location_text +
', ' +
j.strip())
except Exception as error:
log.exception(error, exc_info=True)
continue
if all_locations_text == '':
if individiual_location_text != '':
all_locations_text = individiual_location_text.strip()
else:
if individiual_location_text != '':
all_locations_text = (
all_locations_text +
'; ' +
individiual_location_text.strip())
row['location_info'] = all_locations_text
return rows
def other_stuff_location_info(rows):
"""Run checks for location_info."""
for row in rows:
# To remove district ordinal
row['location_info'] = row['location_info'].replace('1st', '1')
row['location_info'] = row['location_info'].replace('2nd', '2')
row['location_info'] = row['location_info'].replace('3rd', '3')
row['location_info'] = row['location_info'].replace('4th', '4')
row['location_info'] = row['location_info'].replace('5th', '5')
row['location_info'] = row['location_info'].replace('6th', '6')
row['location_info'] = row['location_info'].replace('7th', '7')
all_locations_text = ''
list1 = row['location_info'].split(';')
for i in list1:
list2 = i.split(',')
individiual_location_text = ''
for j in list2:
try:
if j.strip()[-1] != ':':
# If first addition:
if individiual_location_text == '':
individiual_location_text = j.strip()
else: # If second addition or later
individiual_location_text = (
individiual_location_text + ', ' +
j.strip())
except Exception as error:
log.exception(error, exc_info=True)
continue
if all_locations_text == '':
if individiual_location_text != '':
all_locations_text = individiual_location_text.strip()
else:
if individiual_location_text != '':
all_locations_text = (
all_locations_text + '; ' +
individiual_location_text.strip())
row['location_info'] = all_locations_text
return rows
def check_geocoder_good_rating(self):
"""Check if PostGIS Geocoder rating scored 3 or lower: good."""
SESSION.query(
Location.rating,
Location.location_publish
).filter(
(Location.rating == 'RANGE_INTERPOLATED') |
(Location.rating == 'ROOFTOP')
).update({"location_publish": True})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def check_geocoder_bad_rating(self):
"""Check if PostGIS Geocoder rating scored higher than 3: bad."""
SESSION.query(
Location.rating,
Location.location_publish
).filter(
(Location.rating == 'GEOMETRIC_CENTER') |
(Location.rating == 'APPROXIMATE') |
(Location.rating.is_(None))
).update({"location_publish": False})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def __init__(self):
"""Create database."""
try:
self._database_connection()
except OperationalError as error:
print(error)
log.exception(error, exc_info=True)
db_error = ('(psycopg2.OperationalError) FATAL: database "{}" ' +
'does not exist').format(DATABASE_NAME)
if str(error).strip() == db_error:
self.create_db()
self._create_tables()
self._import_neighorhoods()
self._spatial_index_on_cleaned_geom()
self.conn.close()
def other_stuff_addresses(rows):
"""Run checks for addresses."""
# log.debug(rows)
for row in rows:
all_addresses_text = ''
address_list1 = row['address'].split(';')
for i in address_list1:
address_list2 = i.split(',')
individual_address_text = ''
for j in address_list2:
try:
# If first addition:
if individual_address_text == '':
individual_address_text = j.strip()
else: # If second addition or later
individual_address_text = (
individual_address_text +
', ' +
j.strip())
except Exception as error:
log.exception(error, exc_info=True)
continue
if all_addresses_text == '' and individual_address_text != '':
all_addresses_text = individual_address_text.strip()
elif individual_address_text != '':
all_addresses_text = (
all_addresses_text +
'; ' +
individual_address_text.strip())
# location_info = location_info.replace(';', ',')
# So can split on commas for both semi-colons and commas
row['address'] = all_addresses_text
return rows
def check_north_of_new_orleans(self):
"""Check if geocoded coords are within north border of New Orleans."""
# Lat less than 29.864543 is north of New Orleans:
SESSION.query(
Location.latitude,
Location.location_publish
).filter(
Location.latitude > 30.181719
).update({
"location_publish": False
})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def check_west_of_new_orleans(self):
"""Check if geocoded coords are within west border of New Orleans."""
# Long less than -90.140388 is west of New Orleans:
SESSION.query(
Location.longitude,
Location.location_publish
).filter(
Location.longitude < -90.140388
).update({
"location_publish": False
})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def check_high_amount(self):
"""Check if sale amount is unreasonably high (>= $20,000,000)."""
# Anything over $20,000,000 wouldn't be impossible, but is rare
SESSION.query(
Detail.amount,
Detail.detail_publish
).filter(
Detail.amount >= 20000000
).update({
"detail_publish": False
})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def check_low_amount(self):
"""Check if sale amount is unreasonably low (<= $0)."""
# Not sure about these, so check them all for now to be safe
SESSION.query(
Detail.amount,
Detail.detail_publish
).filter(
Detail.amount <= 0
).update({
"detail_publish": False
})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def commit_rows(self, rows):
"""Commit JOIN-ed rows to the cleaned table."""
log.debug('Committing %d rows', len(rows))
for count, row in enumerate(rows):
log.debug("Row %d", count)
try:
with SESSION.begin_nested():
i = insert(Cleaned)
i = i.values(row)
SESSION.execute(i)
SESSION.flush()
except Exception as error:
log.debug('count: %s', count)
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
log.debug('%d rows committed', len(rows))
def main(self):
"""The main scrape method."""
try:
self.login()
except Exception: # TODO
log.exception("Problem during login")
slack.chat.post_message("#realestate", "Expired password @channel")
self.driver.close()
self.driver.quit()
raise
try:
self.cycle_through_dates()
except Exception: # TODO
log.exception("")
finally:
self.logout()
self.driver.close()
self.driver.quit()
def __init__(self):
"""Create database."""
try:
self._database_connection()
except OperationalError as error:
print(error)
log.exception(error, exc_info=True)
db_error = (
'(psycopg2.OperationalError) FATAL: database "{}" ' +
'does not exist').format(DATABASE_NAME)
if str(error).strip() == db_error:
self.create_db()
self._create_tables()
self._import_neighorhoods()
self._spatial_index_on_cleaned_geom()
self.conn.close()
def commit_rows(self, rows):
"""Commit JOIN-ed rows to the cleaned table."""
log.debug('Committing %d rows', len(rows))
for count, row in enumerate(rows):
log.debug("Row %d", count)
try:
with SESSION.begin_nested():
i = insert(Cleaned)
i = i.values(row)
SESSION.execute(i)
SESSION.flush()
except Exception as error:
log.debug('count: %s', count)
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
log.debug('%d rows committed', len(rows))
def check_if_no_date(self):
"""Check if sale has a date."""
SESSION.query(
Detail.document_date,
Detail.document_recorded,
Detail.detail_publish
).filter(
(Detail.document_date is None) |
(Detail.document_recorded is None)
).update(
{"detail_publish": False}
)
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def make_all_locations_publishable(self):
"""
Assume all sales are publishable.
Set location_publish = 1. Then set to 0 if questionable data is found.
"""
# Assume publishable, then check for reasons not to publish.
SESSION.query(
Location.location_publish
).update({
"location_publish": True
})
try:
with SESSION.begin_nested():
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
def other_stuff_addresses(rows):
"""Run checks for addresses."""
# log.debug(rows)
for row in rows:
all_addresses_text = ''
address_list1 = row['address'].split(';')
for i in address_list1:
address_list2 = i.split(',')
individual_address_text = ''
for j in address_list2:
try:
# If first addition:
if individual_address_text == '':
individual_address_text = j.strip()
else: # If second addition or later
individual_address_text = (
individual_address_text + ', ' + j.strip())
except Exception as error:
log.exception(error, exc_info=True)
continue
if all_addresses_text == '' and individual_address_text != '':
all_addresses_text = individual_address_text.strip()
elif individual_address_text != '':
all_addresses_text = (all_addresses_text + '; ' +
individual_address_text.strip())
# location_info = location_info.replace(';', ',')
# So can split on commas for both semi-colons and commas
row['address'] = all_addresses_text
return rows
def check_relative_date(self):
"""Check if sale date is >6 months prior to the recorded date."""
# Convert date strings to date format
new_initial_date = datetime.strptime(
self.initial_date, '%Y-%m-%d').date()
new_until_date = datetime.strptime(
self.until_date, '%Y-%m-%d').date()
current_date = new_initial_date
# Evaluate "30 days ago" based on that particular day
while current_date != new_until_date:
# Update date range
old_date = current_date - timedelta(days=180)
previous_date = current_date - timedelta(days=1)
# Copy datetime objects to date strings
old_date_string = old_date.strftime('%Y-%m-%d')
previous_date_string = previous_date.strftime('%Y-%m-%d')
current_date_string = current_date.strftime('%Y-%m-%d')
# For sales recorded on a given day, check if the document
# date is unbelievable (too old or in the future)
try:
with SESSION.begin_nested():
SESSION.query(
Detail.document_recorded,
Detail.document_date,
Detail.detail_publish
).filter(
Detail.document_recorded == current_date_string
).filter(
Detail.document_date < old_date_string
).update({"detail_publish": False})
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
try:
with SESSION.begin_nested():
SESSION.query(
Detail.document_recorded,
Detail.document_date,
Detail.detail_publish
).filter(
Detail.document_recorded == current_date_string
).filter(
Detail.document_date > previous_date_string
).update({
"detail_publish": False
})
SESSION.flush()
except Exception as error:
log.exception(error, exc_info=True)
SESSION.rollback()
SESSION.commit()
current_date = current_date + timedelta(days=1)
