def check_permanent_status_of_new_sales(self):
"""
Examine first-time sales and assign True or False for permanent_flag.
"""
log.debug('Check permanent status of new sales')
# TODO: Is this function called for sales that have already been given
# a False flag? Need to change that if so, because this only looks
# for sales with no flag. Could change to check for None or False.
# Get dates to inspect
earliest_datetime = self.earliest_date_no_flag()
latest_datetime = self.latest_date_no_flag()
# For all folders (dates)
while earliest_datetime != (latest_datetime + timedelta(days=1)):
current_iteration_date = earliest_datetime.strftime('%Y-%m-%d')
early_permanent_datetime = self.find_early_perm_date_when_scraped(
current_iteration_date)
late_permanent_datetime = self.find_late_perm_date_when_scraped(
current_iteration_date)
# For this date that is currently considered temporary (whether by
# default or because it was previously confirmed to be temporary),
# check on the permanent date range at the time of the scrape.
self.update_this_dates_permanent_flag(earliest_datetime,
early_permanent_datetime,
late_permanent_datetime)
earliest_datetime += timedelta(days=1)
def create_db(self):
"""Create database."""
log.debug('create_db')
call(['createdb', DATABASE_NAME]) # Create database
self._database_connection() # Connect to database
self._add_db_extensions() # Add Postgres extensions
def __init__(self, initial_date=None, until_date=None):
"""Initialize self variables and establish connection to database."""
self.initial_date = initial_date
self.until_date = until_date
log.debug('self.initial_date: %s', self.initial_date)
log.debug('self.until_date: %s', self.until_date)
def __init__(self, initial_date=None, until_date=None):
"""Create class variables for date range and connect to database."""
self.initial_date = initial_date
self.until_date = until_date
log.debug('self.initial_date: {}'.format(self.initial_date))
log.debug('self.until_date: {}'.format(self.until_date))
def __init__(self, initial_date=None, until_date=None):
"""Create class variables for date range and connect to database."""
self.initial_date = initial_date
self.until_date = until_date
log.debug('self.initial_date: {}'.format(self.initial_date))
log.debug('self.until_date: {}'.format(self.until_date))
def list_parse(self, parser_name, table):
"""
Parse data structured as a list of dicts.
This is how `locations`, `vendees` and `vendors` returns.
"""
initial_datetime = datetime.strptime(
self.initial_date, '%Y-%m-%d').date()
until_datetime = datetime.strptime(self.until_date, '%Y-%m-%d').date()
while initial_datetime != (until_datetime + timedelta(days=1)):
current_date = initial_datetime.strftime('%Y-%m-%d')
log.debug('Current date: {}'.format(current_date))
print(current_date)
glob_string = '{0}/data/raw/{1}/form-html/*.html'.format(
PROJECT_DIR, current_date)
for filepath in sorted(glob.glob(glob_string)):
list_output = getattr(parse, parser_name)(filepath).form_list()
# Because output might have multiple rows:
for output in list_output:
self.commit_to_database(table, output)
initial_datetime += timedelta(days=1)
def initialize(initial_date=None, until_date=None):
"""Build, geocode, clean and publish records to the cleaned table."""
if initial_date is None or until_date is None:
date_range = GetDates().get_date_range()
initial_date = date_range['initial_date']
until_date = date_range['until_date']
log.debug('self.initial_date: {}'.format(initial_date))
log.debug('self.until_date: {}'.format(until_date))
# try: # TODO
Build(initial_date=initial_date, until_date=until_date).build_all()
# except Exception as error:
# log.exception(error, exc_info=True)
# Geocoding takes over an hour
Geocode(initial_date=initial_date, until_date=until_date).geocode()
Geocode().update_locations_with_neighborhoods()
# try: # TODO
Publish(initial_date=initial_date, until_date=until_date).main()
# except Exception as error:
# log.exception(error, exc_info=True)
# try: # TODO
Clean(initial_date=initial_date, until_date=until_date).main()
# except Exception as error:
# log.exception(error, exc_info=True)
Clean(initial_date=initial_date,
until_date=until_date).update_cleaned_geom()
def check_permanent_status_of_temp_sales(self):
"""
Compare latest permanent date range to the range of sales in our
database that are labeled "temporary." If any of those temporary sales
now fall within the permanent range, re-scrape and re-initialize.
"""
log.debug('Check permanent status of temporary sales')
# Don't need to know temporary end date or permanent start date.
# Only need to know temporary start date and permanent end date
# to determine the dates that were temporary but are now permanent.
# See find_date_range_to_rescrape_and_initialize() for logic.
earliest_temp_datetime = self.earliest_date_temp_flag()
if earliest_temp_datetime is None: # No temporary sales
return
global_permanent_range_last_datetime = self.latest_permanent_datetime()
dates_to_redo = self.find_newly_permanent_date_range(
earliest_temp_datetime,
global_permanent_range_last_datetime)
if dates_to_redo is not None:
self.scrape_days(dates_to_redo[0], dates_to_redo[1])
def get_existing_until_date(self):
"""TODO: Docstring."""
query_until_date = SESSION.query(Detail.document_recorded).order_by(
Detail.document_recorded.desc()).limit(1).all()
# Check if any records at all
if len(query_until_date) == 0:
# Make it so initialized date range will start from beginning.
until_date = OPENING_DATE - timedelta(days=1)
# log.debug(until_date)
# log.debug(type(until_date))
else:
log.debug(len(query_until_date))
for row in query_until_date:
# TODO: will this fail w/o .one()?
until_date = row.document_recorded
# log.debug(until_date)
# log.debug(type(until_date))
# log.debug(until_date)
SESSION.close()
return until_date
def list_parse(self, parser_name, table):
"""
Parse data structured as a list of dicts.
This is how `locations`, `vendees` and `vendors` returns.
"""
initial_datetime = datetime.strptime(self.initial_date,
'%Y-%m-%d').date()
until_datetime = datetime.strptime(self.until_date, '%Y-%m-%d').date()
while initial_datetime != (until_datetime + timedelta(days=1)):
current_date = initial_datetime.strftime('%Y-%m-%d')
log.debug('Current date: {}'.format(current_date))
print(current_date)
glob_string = '{0}/data/raw/{1}/form-html/*.html'.format(
PROJECT_DIR, current_date)
for filepath in sorted(glob.glob(glob_string)):
list_output = getattr(parse, parser_name)(filepath).form_list()
# Because output might have multiple rows:
for output in list_output:
self.commit_to_database(table, output)
initial_datetime += timedelta(days=1)
def initialize(initial_date=None, until_date=None):
"""Build, geocode, clean and publish records to the cleaned table."""
if initial_date is None or until_date is None:
date_range = GetDates().get_date_range()
initial_date = date_range['initial_date']
until_date = date_range['until_date']
log.debug('self.initial_date: {}'.format(initial_date))
log.debug('self.until_date: {}'.format(until_date))
# try: # TODO
Build(initial_date=initial_date, until_date=until_date).build_all()
# except Exception as error:
# log.exception(error, exc_info=True)
# Geocoding takes over an hour
Geocode(initial_date=initial_date, until_date=until_date).geocode()
Geocode().update_locations_with_neighborhoods()
# try: # TODO
Publish(initial_date=initial_date, until_date=until_date).main()
# except Exception as error:
# log.exception(error, exc_info=True)
# try: # TODO
Clean(initial_date=initial_date, until_date=until_date).main()
# except Exception as error:
# log.exception(error, exc_info=True)
Clean(
initial_date=initial_date,
until_date=until_date
).update_cleaned_geom()
def check_permanent_status_of_temp_sales(self):
"""
Compare latest permanent date range to the range of sales in our
database that are labeled "temporary." If any of those temporary sales
now fall within the permanent range, re-scrape and re-initialize.
"""
log.debug('Check permanent status of temporary sales')
# Don't need to know temporary end date or permanent start date.
# Only need to know temporary start date and permanent end date
# to determine the dates that were temporary but are now permanent.
# See find_date_range_to_rescrape_and_initialize() for logic.
earliest_temp_datetime = self.earliest_date_temp_flag()
if earliest_temp_datetime is None: # No temporary sales
return
global_permanent_range_last_datetime = self.latest_permanent_datetime()
dates_to_redo = self.find_newly_permanent_date_range(
earliest_temp_datetime, global_permanent_range_last_datetime)
if dates_to_redo is not None:
self.scrape_days(dates_to_redo[0], dates_to_redo[1])
def get_rows_with_null_rating(self):
"""
Return query result for locations with rating IS NULL.
:returns: SQLAlchemy query result.
"""
query = SESSION.query(
Location.rating,
Location.document_id,
Location.street_number,
Location.address
).join(
Detail
).filter(
Location.rating.is_(None)
).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)
).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).all()
log.debug('Rows with rating is NULL: {}'.format(len(query)))
SESSION.close()
return query
def build_features_json(query):
"""TODO."""
log.debug(len(query))
features = []
features_dict = {}
for row in query:
# log.debug(row.buyers)
if not row.location_publish:
row.document_date = row.document_date + "*"
if not row.permanent_flag:
row.document_date = row.document_date + u"\u2020"
features_dict = {
"type": "Feature",
"properties": {
"document_date": row.document_date,
"address": row.address,
"location_info": row.location_info,
"amount": row.amount,
"buyers": row.buyers,
"sellers": row.sellers,
"instrument_no": row.instrument_no,
"location_publish": row.location_publish,
"permanent_flag": row.permanent_flag
},
"geometry": {
"type": "Point",
"coordinates": [row.longitude, row.latitude]
}
}
features.append(features_dict)
return features
def check_permanent_status_of_new_sales(self):
"""
Examine first-time sales and assign True or False for permanent_flag.
"""
log.debug('Check permanent status of new sales')
# TODO: Is this function called for sales that have already been given
# a False flag? Need to change that if so, because this only looks
# for sales with no flag. Could change to check for None or False.
# Get dates to inspect
earliest_datetime = self.earliest_date_no_flag()
latest_datetime = self.latest_date_no_flag()
# For all folders (dates)
while earliest_datetime != (latest_datetime + timedelta(days=1)):
current_iteration_date = earliest_datetime.strftime('%Y-%m-%d')
early_permanent_datetime = self.find_early_perm_date_when_scraped(
current_iteration_date)
late_permanent_datetime = self.find_late_perm_date_when_scraped(
current_iteration_date)
# For this date that is currently considered temporary (whether by
# default or because it was previously confirmed to be temporary),
# check on the permanent date range at the time of the scrape.
self.update_this_dates_permanent_flag(earliest_datetime,
early_permanent_datetime,
late_permanent_datetime)
earliest_datetime += timedelta(days=1)
def get_locations(self):
"""Return SQL query of locations table for given date range."""
log.debug('get_locations')
subquery = SESSION.query(
Location.document_id,
func.bool_and(Location.location_publish).label('location_publish'),
func.string_agg(
cast(Location.street_number, Text) + ' ' +
cast(Location.address, Text), '; ').label('address'),
func.string_agg(
'Unit: ' + cast(Location.unit, Text) + ', ' + 'Condo: ' +
cast(Location.condo, Text) + ', ' + 'Weeks: ' +
cast(Location.weeks, Text) + ', ' + 'Subdivision: ' +
cast(Location.subdivision, Text) + ', ' + 'District: ' +
cast(Location.district, Text) + ', ' + 'Square: ' +
cast(Location.square, Text) + ', ' + 'Lot: ' +
cast(Location.lot, Text), '; ').label('location_info')
# todo: Once SQLAlchemy supports this, add these fields this way.
# 'mode() WITHIN GROUP (ORDER BY locations.zip_code) AS zip_code',
# 'mode() WITHIN GROUP (ORDER BY locations.latitude) AS latitude',
# 'mode() WITHIN GROUP (ORDER BY locations.longitude) ' +
# ' AS longitude',
# 'mode() WITHIN GROUP (ORDER BY locations.neighborhood) ' +
# 'AS neighborhood'
).group_by(Location.document_id).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def get_existing_until_date(self):
"""TODO: Docstring."""
query_until_date = SESSION.query(
Detail.document_recorded
).order_by(
Detail.document_recorded.desc()
).limit(1).all()
# Check if any records at all
if len(query_until_date) == 0:
# Make it so initialized date range will start from beginning.
until_date = OPENING_DATE - timedelta(days=1)
# log.debug(until_date)
# log.debug(type(until_date))
else:
log.debug(len(query_until_date))
for row in query_until_date:
# TODO: will this fail w/o .one()?
until_date = row.document_recorded
# log.debug(until_date)
# log.debug(type(until_date))
# log.debug(until_date)
SESSION.close()
return until_date
def drop_tables(self):
'''DROP all tables except those for PostGIS.'''
# gather all data first before dropping anything.
# some DBs lock after things have been dropped in
# a transaction.
log.debug('drop_tables')
metadata = MetaData()
tables = []
all_foreign_keys = []
for table_name in self.inspector.get_table_names():
foreign_keys = []
for foreign_key in self.inspector.get_foreign_keys(table_name):
if not foreign_key['name']:
continue
foreign_keys.append(
ForeignKeyConstraint((), (), name=foreign_key['name'])
)
table = Table(table_name, metadata, *foreign_keys)
tables.append(table)
all_foreign_keys.extend(foreign_keys)
for foreign_key in all_foreign_keys:
self.conn.execute(DropConstraint(foreign_key))
for table in tables:
# This table is part of PostGIS extension.
if table.name == 'spatial_ref_sys':
continue
self.conn.execute(DropTable(table))
self.trans.commit()
def _import_neighorhoods(self):
"""Import neighborhood shapefiles."""
# TODO: This causes errors on second run.
log.debug('import_neighorhoods')
p1 = Popen(
[
'shp2pgsql',
'-I',
'-a', # Append data to existing table. Don't create.
('{}/neighborhoods/shapefile/Neighborhood_Statistical_Areas'
).format(GEO_DIR),
'neighborhoods'
],
stdout=PIPE)
p2 = Popen(['psql', '-d', DATABASE_NAME], stdin=p1.stdout, stdout=PIPE)
p1.stdout.close() # Allow p1 to receive a SIGPIPE if p2 exits.
p2.communicate()[0]
# If need to alter geometry's SRID
self.conn.execute("""
SELECT updategeometrysrid('neighborhoods', 'geom', 3452);""")
self.conn.execute("""
ALTER TABLE neighborhoods
ALTER COLUMN geom TYPE geometry(MultiPolygon, 4326)
USING ST_Transform(geom, 4326);""")
def create_db(self):
"""Create database."""
log.debug('create_db')
call(['createdb', DATABASE_NAME]) # Create database
self._database_connection() # Connect to database
self._add_db_extensions() # Add Postgres extensions
def update_cleaned_geom(self):
"""Update the PostGIS geom field in the cleaned table."""
log.debug('Update Cleaned geometry')
sql = """UPDATE cleaned
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);"""
self.engine.execute(sql)
def update_cleaned_geom(self):
"""Update the PostGIS geom field in the cleaned table."""
log.debug('Update Cleaned geometry')
sql = """UPDATE cleaned
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);"""
self.engine.execute(sql)
def __init__(self, initial_date=None, until_date=None):
"""Initialize self variables and establish connection to database."""
self.engine = create_engine(ENGINE_STRING)
self.initial_date = initial_date
self.until_date = until_date
log.debug('self.initial_date: {}'.format(self.initial_date))
log.debug('self.until_date: {}'.format(self.until_date))
def __init__(self, initial_date=None, until_date=None):
"""Initialize self variables and establish connection to database."""
self.engine = create_engine(ENGINE_STRING)
self.initial_date = initial_date
self.until_date = until_date
log.debug('self.initial_date: {}'.format(self.initial_date))
log.debug('self.until_date: {}'.format(self.until_date))
def _spatial_index_on_cleaned_geom(self):
"""Create spatial index on cleaned table."""
log.debug('spatial_index_on_cleaned_geom')
sql = """
CREATE INDEX index_cleaned_geom
ON cleaned
USING GIST(geom);"""
self.conn.execute(sql)
def _spatial_index_on_cleaned_geom(self):
"""Create spatial index on cleaned table."""
log.debug('spatial_index_on_cleaned_geom')
sql = """
CREATE INDEX index_cleaned_geom
ON cleaned
USING GIST(geom);"""
self.conn.execute(sql)
def get_details(self):
"""Return SQL query of details table for given date range."""
subquery = SESSION.query(Detail).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).subquery()
log.debug(subquery)
SESSION.close()
return subquery
def send_email(self, msg):
"""Initialize and send the email."""
log.debug('Mail')
s = smtplib.SMTP('smtp.gmail.com', 587)
s.ehlo()
s.starttls()
s.ehlo()
s.login(os.environ.get('REAL_ESTATE_GMAIL_USERNAME'),
os.environ.get('REAL_ESTATE_GMAIL_PASSWORD'))
s.sendmail(self.frm, self.to, msg.as_string())
s.quit()
def form_dict(self):
"""
Return dict of this sale's detail table using class self variables.
:returns: A dict containing all of the details table values.
"""
log.debug('form_dict')
dict_output = self.__dict__
del dict_output['rows']
return dict_output
def form_dict(self):
"""
Return dict of this sale's detail table using class self variables.
:returns: A dict containing all of the details table values.
"""
log.debug('form_dict')
dict_output = self.__dict__
del dict_output['rows']
return dict_output
def drop_db():
'''
Deletes the database.
'''
log.debug('drop DB')
try:
call([
'dropdb',
'%s' % DATABASE_NAME
])
except Exception as error:
log.debug(error, exc_info=True)
def commit_to_database(self, table, output):
"""Commit to database using nested transactions and exceptions."""
try:
# TODO: Is this the correct method for this?
with SESSION.begin_nested():
i = insert(getattr(db, table))
vals = i.values(output)
SESSION.execute(vals) # TODO: What is this?
SESSION.flush()
except Exception as error:
log.debug(error, exc_info=True)
SESSION.rollback()
SESSION.commit() # TODO: Should this be here?
def commit_to_database(self, table, output):
"""Commit to database using nested transactions and exceptions."""
try:
# TODO: Is this the correct method for this?
with SESSION.begin_nested():
i = insert(getattr(db, table))
vals = i.values(output)
SESSION.execute(vals) # TODO: What is this?
SESSION.flush()
except Exception as error:
log.debug(error, exc_info=True)
SESSION.rollback()
SESSION.commit() # TODO: Should this be here?
def __init__(self, initial_date=None, until_date=None):
"""Initialize self variables and establish connection to database."""
engine_string = (
'host=localhost dbname={0} user={1} password={2}').format(
DATABASE_NAME, os.environ.get('REAL_ESTATE_DATABASE_USERNAME'),
os.environ.get('REAL_ESTATE_DATABASE_PASSWORD'))
self.conn = psycopg2.connect(engine_string)
self.cursor = self.conn.cursor()
self.initial_date = initial_date
self.until_date = until_date
log.debug('self.initial_date: {}'.format(self.initial_date))
log.debug('self.until_date: {}'.format(self.until_date))
def no_neighborhood_found(self):
"""If no neighborhood is found, update with "None" in nbhd field."""
log.debug('no_neighborhood_found')
SESSION.query(
Location
).filter(
Location.neighborhood.is_(None)
).update(
{Location.neighborhood: "None"},
synchronize_session='fetch'
)
SESSION.commit()
def get_details(self):
"""Return SQL query of details table for given date range."""
subquery = SESSION.query(
Detail
).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)
).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).subquery()
log.debug(subquery)
SESSION.close()
return subquery
def scrape_days(early_date, late_date):
"""docstring"""
early_datetime = datetime.strptime(early_date, '%Y-%m-%d')
log.debug(early_datetime)
late_datetime = datetime.strptime(late_date, '%Y-%m-%d')
log.debug(early_datetime)
# Scrape those days over again
log.info('scrape')
try:
Scrape(initial_date=early_datetime,
until_date=late_datetime).main()
except Exception as error:
log.error(error, exc_info=True)
def vacuum_database():
'''VACUUM the database.'''
log.debug('vacuum_database')
# Make sure to get rid of deleted rows
try:
call([
'psql',
'%s' % DATABASE_NAME,
'-c',
'VACUUM;'
])
except Exception as error:
log.debug(error, exc_info=True)
def post_search(self, data):
"""Process incoming POST data."""
log.debug('post_search')
data = self.decode_data(data)
data = self.convert_entries_to_db_friendly(data)
log.debug(data)
# If a geo query (search near me). Not yet a feature.
# if 'latitude' in data and 'longitude' in data:
# response = self.geoquery_db(data)
# else:
response = self.mapquery_db(data)
return response
def searchbar_input():
"""
Receive a POST call from the autocomplete dropdown.
Return a dict of suggestions.
:param query: The search bar input.
:type query: string
:returns: A dict of matching suggestions.
"""
term = request.args.get('q')
log.debug('term: %s', term)
data = Models().searchbar_input(term)
return data
def get_vendees(self):
"""Return SQL query of vendees table for given date range."""
log.debug('get_vendees')
subquery = SESSION.query(
Vendee.document_id,
func.string_agg(
cast(Vendee.vendee_firstname, Text) + " " +
cast(Vendee.vendee_lastname, Text),
', ').label('buyers')).group_by(Vendee.document_id).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def do_not_filter_by_map(self, data):
"""TODO."""
query = self.find_all_publishable_rows_fitting_criteria(data)
# data['page_length'] = self.PAGE_LENGTH
data['number_of_records'] = len(query) # number of records
# total number of pages:
data['number_of_pages'] = int(
math.ceil(
float(data['number_of_records']) / float(data['page_length'])))
data = self.update_pager(data)
query = self.find_page_of_publishable_rows_fitting_criteria(data)
log.debug(query)
return query
def get_rows_from_query(self):
"""Convert query result to row of dicts."""
log.debug('get_rows_from_query')
query = self.join_subqueries()
rows = []
for row in query:
# dict_val = row.__dict__ # Old
dict_val = dict(zip(row.keys(), row)) # New. TODO: Check it works.
rows.append(dict_val)
log.debug('len(rows): {}'.format(len(rows)))
return rows
def scrape_days(early_date, late_date):
"""docstring"""
early_datetime = datetime.strptime(early_date, '%Y-%m-%d')
log.debug(early_datetime)
late_datetime = datetime.strptime(late_date, '%Y-%m-%d')
log.debug(early_datetime)
# Scrape those days over again
log.info('scrape')
try:
Scrape(
initial_date=early_datetime,
until_date=late_datetime
).main()
except Exception as error:
log.error(error, exc_info=True)
def get_rows_from_query(self):
"""Convert query result to row of dicts."""
log.debug('get_rows_from_query')
query = self.join_subqueries()
rows = []
for row in query:
# dict_val = row.__dict__ # Old
dict_val = dict(zip(row.keys(), row)) # New. TODO: Check it works.
rows.append(dict_val)
log.debug('len(rows): {}'.format(len(rows)))
return rows
def main(self):
"""Run through each check method."""
log.debug('Publish')
print('Publishing...')
self.make_all_locations_publishable()
self.check_geocoder_bad_rating()
self.check_geocoder_good_rating()
self.check_west_of_new_orleans()
self.check_east_of_new_orleans()
self.check_north_of_new_orleans()
self.check_south_of_new_orleans()
self.make_all_details_publishable()
self.check_if_no_date()
self.check_relative_date()
self.check_low_amount()
self.check_high_amount()
def get_search(self, request):
"""
GET call for /realestate/search.
:param request: The request object(?).
:returns: A data dict, SQL query result and JS data.
"""
data = self.parse_query_string(request)
data = self.decode_data(data)
data = self.convert_entries_to_db_friendly(data)
data['update_date'] = self.get_last_updated_date()
data['neighborhoods'] = self.get_neighborhoods()
data = self.determine_pages(data)
query = self.find_page_of_publishable_rows_fitting_criteria(data)
for row in query:
row.amount = get_num_with_curr_sign(row.amount)
row.document_date = ymd_to_full_date(
(row.document_date).strftime('%Y-%m-%d'), no_day=True)
features = self.build_features_json(query)
jsdata = {"type": "FeatureCollection", "features": features}
data['results_css_display'] = 'none'
if data['number_of_records'] == 0:
data['current_page'] = 0
data['results_css_display'] = 'block'
data = self.revert_entries(data)
data['map_button_state'] = False
data['results_language'] = ResultsLanguage(data).main()
log.debug('data')
return data, query, jsdata
def get_vendors(self):
"""Return SQL query of vendors table for given date range."""
log.debug('get_vendors')
subquery = SESSION.query(
Vendor.document_id,
func.string_agg(
cast(Vendor.vendor_firstname, Text) + " " +
cast(Vendor.vendor_lastname, Text),
', '
).label('sellers')
).group_by(
Vendor.document_id
).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def build_all(self):
"""Run through all of the building methods."""
log.debug('Build all')
print('Building...')
log.debug('Detail')
print('\nAdding to details table for:')
self.dict_parse('DetailParser', 'Detail')
log.debug('Vendor')
print('\nAdding to vendors table for:')
self.list_parse('VendorParser', 'Vendor')
log.debug('Vendee')
print('\nAdding to vendees table for:')
self.list_parse('VendeeParser', 'Vendee')
log.debug('Location')
print('\nAdding to locations table for:')
self.list_parse('LocationParser', 'Location')
def get_locations(self):
"""Return SQL query of locations table for given date range."""
log.debug('get_locations')
subquery = SESSION.query(
Location.document_id,
func.bool_and(Location.location_publish).label('location_publish'),
func.string_agg(
cast(Location.street_number, Text) + ' ' +
cast(Location.address, Text),
'; '
).label('address'),
func.string_agg(
'Unit: ' + cast(Location.unit, Text) + ', ' +
'Condo: ' + cast(Location.condo, Text) + ', ' +
'Weeks: ' + cast(Location.weeks, Text) + ', ' +
'Subdivision: ' + cast(Location.subdivision, Text) + ', ' +
'District: ' + cast(Location.district, Text) + ', ' +
'Square: ' + cast(Location.square, Text) + ', ' +
'Lot: ' + cast(Location.lot, Text),
'; '
).label('location_info')
# todo: Once SQLAlchemy supports this, add these fields this way.
# 'mode() WITHIN GROUP (ORDER BY locations.zip_code) AS zip_code',
# 'mode() WITHIN GROUP (ORDER BY locations.latitude) AS latitude',
# 'mode() WITHIN GROUP (ORDER BY locations.longitude) ' +
# ' AS longitude',
# 'mode() WITHIN GROUP (ORDER BY locations.neighborhood) ' +
# 'AS neighborhood'
).group_by(
Location.document_id
).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def dict_parse(self, parser_name, table):
"""Parse data structured in a dict, which is how `details` returns."""
initial_datetime = datetime.strptime(
self.initial_date, '%Y-%m-%d').date()
until_datetime = datetime.strptime(self.until_date, '%Y-%m-%d').date()
while initial_datetime != (until_datetime + timedelta(days=1)):
current_date = initial_datetime.strftime('%Y-%m-%d')
log.debug('Current date: {}'.format(current_date))
print(current_date)
glob_string = '{0}/data/raw/{1}/form-html/*.html'.format(
PROJECT_DIR, current_date)
# Allows for variable calls to a class.
# Ex module.Class().method -> parse.parser_name(f).list_output
for filepath in sorted(glob.glob(glob_string)):
# log.debug('filepath: {}'.format(filepath))
dict_output = getattr(parse, parser_name)(filepath).form_dict()
self.commit_to_database(table, dict_output)
initial_datetime += timedelta(days=1)
def earliest_date_temp_flag(self):
"""Find earliest date with permanent_flag = False."""
query = SESSION.query(
func.min(Detail.document_recorded).label('early_date')
).filter(
Detail.permanent_flag.is_(False) # To satisfy PEP8
).all()
for row in query:
earliest_temp_date = row.early_date
if earliest_temp_date is not None:
earliest_temp_datetime = datetime.combine(
earliest_temp_date, datetime.min.time())
log.debug(earliest_temp_datetime)
SESSION.close()
return earliest_temp_datetime
else:
SESSION.close()
return None
def neighborhood_found(self):
"""Use PostGIS to find which neighborhood a long/lat pair is in."""
log.debug('neighborhood_found')
SESSION.query(
Location
).filter(
func.ST_Contains(
Neighborhood.geom,
func.ST_SetSRID(
func.ST_Point(
cast(Location.longitude, Float),
cast(Location.latitude, Float)
),
4326
)
)
).update(
{Location.neighborhood: Neighborhood.gnocdc_lab},
synchronize_session='fetch'
)
SESSION.commit()
def _import_neighorhoods(self):
"""Import neighborhood shapefiles."""
# TODO: This causes errors on second run.
log.debug('import_neighorhoods')
p1 = Popen(
[
'shp2pgsql',
'-I',
'-a', # Append data to existing table. Don't create.
(
'{}/neighborhoods/shapefile/Neighborhood_Statistical_Areas'
).format(GEO_DIR),
'neighborhoods'
],
stdout=PIPE)
p2 = Popen(
[
'psql',
'-d',
DATABASE_NAME
],
stdin=p1.stdout,
stdout=PIPE)
p1.stdout.close() # Allow p1 to receive a SIGPIPE if p2 exits.
p2.communicate()[0]
# If need to alter geometry's SRID
self.conn.execute("""
SELECT updategeometrysrid('neighborhoods', 'geom', 3452);""")
self.conn.execute("""
ALTER TABLE neighborhoods
ALTER COLUMN geom TYPE geometry(MultiPolygon, 4326)
USING ST_Transform(geom, 4326);""")
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 join_subqueries(self):
"""Run a JOIN on subqueries."""
log.debug('join_subqueries')
subq_vendees = self.get_vendees()
subq_vendors = self.get_vendors()
subq_location = self.get_locations()
log.debug('query...')
query = SESSION.query(
Detail.document_id,
Detail.amount,
Detail.document_date,
Detail.document_recorded,
Detail.instrument_no,
Detail.detail_publish,
Detail.permanent_flag,
subq_vendees.c.buyers,
subq_vendors.c.sellers,
subq_location.c.location_publish,
subq_location.c.address,
subq_location.c.location_info
# TODO: Once SQLAlchemy supports WITHIN GROUP, uncomment these.
# subq_location.c.zip_code,
# subq_location.c.latitude,
# subq_location.c.longitude,
# subq_location.c.neighborhood
).join(
subq_vendees
).join(
subq_vendors
).join(
subq_location
).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)
).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).all()
log.debug('len(query): %d', len(query))
SESSION.close()
return query
