def dispatch_request(self): orientation = request.args.get('orientation', 'tabular') pagination_size = flask_app.config['app']['pagination_size'] next_id = request.args.get('next-id') pagination_clause = '' pagination_values = [] if next_id is not None: pagination_clause = 'where id >= %s' pagination_values = [next_id] sql_query = f''' select id, {self.get_select_clause()} from {self.get_from_clause()} {pagination_clause} order by id limit {pagination_size} + 1 ''' df = flask_app.dbi.execute_query(sql_query, data=pagination_values, df=True) if len(df) == pagination_size + 1: next_ = '{}{}?'.format(request.host_url[:-1], request.path) next_ += 'orientation={}'.format(orientation) next_ += '&next-id={}'.format(df['id'].values[-1]) df = df[:-1] else: next_ = None df = df if self.include_id_column else df.iloc[:, 1:] web_dict = to_web_dict(df, orientation, self.camel_case_columns) web_dict['next'] = next_ return flask_app.make_response(web_dict)
def dispatch_request(self): orientation = request.args.get('orientation', 'tabular') postcode = request.args.get('postcode') if not postcode: raise BadRequest('No postcode specified') postcode = postcode.replace(' ', '').lower() sql_query = f''' select {','.join( [field for field, _ in DITReferencePostcodesPipeline._l1_data_column_types] )} from {DITReferencePostcodesL1.get_fq_table_name()} where lower(replace(postcode, ' ', '')) = %s limit 1 ''' df = flask_app.dbi.execute_query(sql_query, data=[postcode], df=True) web_dict = to_web_dict(df, orientation) return flask_app.make_response(web_dict)
def test_invalid_input(self): df = unittest.mock.Mock() with self.assertRaises(Exception): utils.to_web_dict(df, orient='invalid')
def test_if_tabular_orientation(self, to_tabular_web_dict): df = unittest.mock.Mock() utils.to_web_dict(df, orient='tabular') to_tabular_web_dict.assert_called_once()
def test_if_record_orientation(self, to_records_web_dict): df = unittest.mock.Mock() utils.to_web_dict(df, orient='records') to_records_web_dict.assert_called_once()
def get_data_visualisation_data(field): date_trunc = 'quarter' data_source = request.args['data-source'] interests_table = internal_models.CountriesAndSectorsInterest.__tablename__ from app.config import constants valid_data_sources = [ constants.Source.EXPORT_WINS.value, constants.Source.DATAHUB_INTERACTIONS.value, constants.Source.DATAHUB_OMIS.value, ] valid_fields = ['country', 'sector'] if field not in valid_fields: raise BadRequest( f'invalid field: {field}, valid values: {valid_fields}') if data_source not in valid_data_sources: raise BadRequest(f'invalid data-source: {data_source}, ' f'valid values: {valid_data_sources}') if field == 'sector' and data_source == constants.Source.DATAHUB_INTERACTIONS.value: raise BadRequest( f'invalid args: data-source: {constants.Source.DATAHUB_INTERACTIONS.value}' ' not supported by sector') sql = ''' with n_interests as ( select date_trunc('{date_trunc}', timestamp) as date, {field}, count(1) from {interests_table} where source = '{data_source}' and {field} != '' and {field} is not null and {field} != 'United Kingdom' group by 1, 2 ), dates as ( select distinct date from n_interests ), fields as ( select distinct {field} from n_interests ), zero_inflated as ( select date, {field}, coalesce(count, 0) as n_interests from dates left join fields on 1=1 left join n_interests using (date, {field}) ), cumulative as ( select date, {field}, sum(n_interests::int) over (partition by {field} order by date) as n_interests_cumulative from zero_inflated ), total_interest as ( select date, sum(n_interests) as total_interest from zero_inflated group by 1 ), total_interest_cumulative as ( select date, sum(total_interest::int) over (order by date) as total_interest_cumulative from total_interest ), results as ( select date, {field}, n_interests, n_interests::float / total_interest as share_of_interest, n_interests_cumulative, n_interests_cumulative::float / total_interest_cumulative as share_of_interest_cumulative from zero_inflated join total_interest using (date) join cumulative using (date, {field}) join total_interest_cumulative using (date) ) select * from results order by (date, {field}) '''.format( date_trunc=date_trunc, data_source=data_source, field=field, interests_table=interests_table, ) df = flask_app.dbi.execute_query(sql, df=True) df_top = df.groupby(field)[['n_interests_cumulative']].max() df_top = df_top.reset_index() df_top = df_top.sort_values('n_interests_cumulative', ascending=False) output = { 'nInterests': to_web_dict(df)['results'], 'top': df_top[field].tolist(), } return output
def get_company_countries_and_sectors_of_interest(orientation): pagination_size = flask_app.config['app']['pagination_size'] next_id = request.args.get('next-id') service_company_ids = request.args.getlist('service-company-id') company_match_ids = request.args.getlist('company-match-id') countries = request.args.getlist('country') sectors = request.args.getlist('sector') types = request.args.getlist('type') services = request.args.getlist('service') sources = request.args.getlist('source') where = '' values = (countries + sectors + service_company_ids + company_match_ids + types + sources + services) if len(countries) == 1: where = 'where country=%s' elif len(countries) > 1: where = 'where country in (' + ','.join( '%s' for i in range(len(countries))) + ')' if len(sectors) == 1: where = where + ' and' if where != '' else 'where' where = where + ' sector=%s' elif len(sectors) > 1: where = where + ' and' if where != '' else 'where' where = where + ' sector in (' + ','.join( ['%s' for i in range(len(sectors))]) + ')' if len(service_company_ids) == 1: where = where + ' and' if where != '' else 'where' where = where + ' service_company_id=%s' elif len(service_company_ids) > 1: where = where + ' and' if where != '' else 'where' where = (where + ' service_company_id in (' + ','.join(['%s' for i in range(len(service_company_ids))]) + ')') if len(company_match_ids) == 1: where = where + ' and' if where != '' else 'where' where = where + ' company_match_id=%s' elif len(company_match_ids) > 1: where = where + ' and' if where != '' else 'where' where = (where + ' company_match_id in (' + ','.join(['%s' for i in range(len(company_match_ids))]) + ')') if len(types) == 1: where = where + ' and' if where != '' else 'where' where = where + ' type=%s' elif len(types) > 1: where = where + ' and' if where != '' else 'where' where = where + ' type in (' + ','.join( ['%s' for i in range(len(types))]) + ')' if len(sources) == 1: where = where + ' and' if where != '' else 'where' where = where + ' source=%s' elif len(sources) > 1: where = where + ' and' if where != '' else 'where' where = where + ' source in (' + ','.join( ['%s' for i in range(len(sources))]) + ')' if len(services) == 1: where = where + ' and' if where != '' else 'where' where = where + ' service=%s' elif len(services) > 1: where = where + ' and' if where != '' else 'where' where = where + ' service in (' + ','.join( ['%s' for i in range(len(services))]) + ')' if next_id is not None: where = where + ' and' if where != '' else 'where' where = where + ' id >= %s' values = values + [next_id] sql_query = f''' select id, service_company_id, company_match_id, country, sector, type, service, source, source_id, timestamp from {CountriesAndSectorsInterest.get_fq_table_name()} {where} order by id limit {pagination_size} + 1 ''' df = flask_app.dbi.execute_query(sql_query, data=values, df=True) if len(df) == pagination_size + 1: next_ = '{}{}?'.format(request.host_url[:-1], request.path) next_ += '&'.join([ 'service-company-id={}'.format(company_id) for company_id in service_company_ids ]) next_ += '&'.join([ 'company-match-id={}'.format(company_match_id) for company_match_id in company_match_ids ]) next_ += '&'.join( ['country={}'.format(country) for country in countries]) next_ += '&'.join(['sector={}'.format(sector) for sector in sectors]) next_ += '&'.join(['type={}'.format(type) for type in types]) next_ += '&'.join( ['service={}'.format(service) for service in services]) next_ += '&'.join(['source={}'.format(source) for source in sources]) next_ += '&' if next_[-1] != '?' else '' next_ += 'orientation={}'.format(orientation) next_ += '&next-id={}'.format(df['id'].values[-1]) df = df[:-1] else: next_ = None web_dict = to_web_dict(df.iloc[:, 1:], orientation) web_dict['next'] = next_ return flask_app.make_response(web_dict)