def find_page_of_publishable_rows_fitting_criteria(self, data): """TODO.""" # log.debug(data) query = SESSION.query(Cleaned).filter( Cleaned.detail_publish.is_(True) ).filter( (Cleaned.sellers.ilike('%%%s%%' % data['name_address'])) | (Cleaned.buyers.ilike('%%%s%%' % data['name_address'])) | (Cleaned.address.ilike('%%%s%%' % data['name_address'])) | (Cleaned.instrument_no.ilike('%%%s%%' % data['name_address'])) ).filter(Cleaned.neighborhood.ilike( '%%%s%%' % data['neighborhood'])).filter( Cleaned.zip_code.ilike('%%%s%%' % data['zip_code'])).filter( Cleaned.document_date >= '%s' % data['begin_date']).filter( Cleaned.document_date <= '%s' % data['end_date']).filter( Cleaned.amount >= '%s' % data['amount_low']).filter( Cleaned.amount <= '%s' % data['amount_high']).order_by( desc(Cleaned.document_date)).offset( '%d' % int(data['page_offset'] ) # convert unicode to int ).limit('%d' % int(data['page_length'])).all() # log.debug(query) SESSION.close() return query
def find_all_publishable_rows_fitting_criteria(self, data): """TODO.""" # log.debug(data) query = SESSION.query(Cleaned).filter( Cleaned.detail_publish.is_(True)).filter( (Cleaned.sellers.ilike('%%%s%%' % data['name_address'])) | (Cleaned.buyers.ilike('%%%s%%' % data['name_address'])) | (Cleaned.address.ilike('%%%s%%' % data['name_address'])) | (Cleaned.instrument_no.ilike('%%%s%%' % data['name_address']) )).filter( Cleaned.neighborhood.ilike( '%%%s%%' % data['neighborhood'])).filter( Cleaned.zip_code.ilike( '%%%s%%' % data['zip_code'])).filter( Cleaned.document_date >= '%s' % data['begin_date']).filter( Cleaned.document_date <= '%s' % data['end_date']).filter( Cleaned.amount >= '%s' % data['amount_low']).filter( Cleaned.amount <= '%s' % data['amount_high']).all() # log.debug(query) SESSION.close() return query
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 get_sale(self, instrument_no): """TODO.""" data = {} data['update_date'] = self.get_last_updated_date() query = SESSION.query(Cleaned).filter( Cleaned.instrument_no == '%s' % (instrument_no)).filter( Cleaned.detail_publish.is_(True) # Only publish trusted data ).all() 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) # address = row.address # location_info = row.location_info data['assessor_publish'] = row.assessor_publish # newrows = query features = self.build_features_json(query) jsdata = {"type": "FeatureCollection", "features": features} SESSION.close() if len(query) == 0: return None, None, None else: return data, jsdata, query
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
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 query_with_map_boundaries(self, data): """TODO.""" query = SESSION.query(Cleaned).filter( Cleaned.detail_publish.is_(True) ).filter( (Cleaned.sellers.ilike('%%%s%%' % data['name_address'])) | (Cleaned.buyers.ilike('%%%s%%' % data['name_address'])) | (Cleaned.address.ilike('%%%s%%' % data['name_address'])) | (Cleaned.instrument_no.ilike('%%%s%%' % data['name_address'])) ).filter(Cleaned.neighborhood.ilike( '%%%s%%' % data['neighborhood'])).filter( Cleaned.zip_code.ilike('%%%s%%' % data['zip_code'])).filter( Cleaned.document_date >= '%s' % data['begin_date']).filter( Cleaned.document_date <= '%s' % data['end_date']).filter( Cleaned.amount >= '%s' % data['amount_low']).filter( Cleaned.amount <= '%s' % data['amount_high'] ).filter((Cleaned.latitude <= data['bounds'][0]) & (Cleaned.latitude >= data['bounds'][2]) & (Cleaned.longitude <= data['bounds'][1]) & (Cleaned.longitude >= data['bounds'][3]) ).order_by(desc( Cleaned.document_date)).offset( '%d' % data['page_offset']).limit( '%d' % data['page_length']).all() SESSION.close() return query
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 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 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 latest_date_no_flag(self): """Finds the latest date_recorded without permanent_flag set.""" query = SESSION.query( func.max(Detail.document_recorded).label('late_date')).filter( Detail.permanent_flag.is_(None)).all() for row in query: latest_none_date = row.late_date latest_none_datetime = datetime.combine(latest_none_date, datetime.min.time()) SESSION.close() return latest_none_datetime
def get_neighborhoods(self): """TODO.""" query = SESSION.query(Neighborhood.gnocdc_lab).all() neighborhoods = [] for neighborhood in query: neighborhoods.append( (neighborhood.gnocdc_lab).title().replace('Mcd', 'McD')) neighborhoods.sort() SESSION.close() return neighborhoods
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 earliest_date_no_flag(self): """Find the earliest date_recorded without permanent_flag set.""" query = SESSION.query( func.min(Detail.document_recorded).label('early_date')).filter( Detail.permanent_flag.is_(None) # To satisfy PEP8 ).all() for row in query: earliest_none_date = row.early_date earliest_none_datetime = datetime.combine(earliest_none_date, datetime.min.time()) SESSION.close() return earliest_none_datetime
def query_search_term_limit_3(self, table, term): """ Get the top three results for autocomplete dropdown. :param table: string. The database to query. :type table: string :param term: string. The autocomplete term entered in search box. :type term: string :returns: A SQLAlchemy query result for three matches, at most. """ query = SESSION.query(getattr(Cleaned, table)).filter( getattr(Cleaned, table).ilike( '%%{}%%'.format(term))).distinct().limit(3).all() SESSION.close() return query
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 latest_date_no_flag(self): """Finds the latest date_recorded without permanent_flag set.""" query = SESSION.query( func.max(Detail.document_recorded).label('late_date') ).filter( Detail.permanent_flag.is_(None) ).all() for row in query: latest_none_date = row.late_date latest_none_datetime = datetime.combine( latest_none_date, datetime.min.time()) SESSION.close() return latest_none_datetime
def earliest_date_no_flag(self): """Find the earliest date_recorded without permanent_flag set.""" query = SESSION.query( func.min(Detail.document_recorded).label('early_date') ).filter( Detail.permanent_flag.is_(None) # To satisfy PEP8 ).all() for row in query: earliest_none_date = row.early_date earliest_none_datetime = datetime.combine( earliest_none_date, datetime.min.time()) SESSION.close() return earliest_none_datetime
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
def get_last_updated_date(self): """TODO.""" query = SESSION.query(Cleaned).filter( Cleaned.detail_publish.is_(True)).order_by( desc(Cleaned.document_recorded)).limit(1).all() log.info(query) updated_date = '' for row in query: updated_date = ymd_to_full_date( (row.document_recorded).strftime('%Y-%m-%d'), no_day=True) log.info(updated_date) SESSION.close() return updated_date
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 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 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 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