def GET(self, series_id, group_id, subgroup_name_id, date, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT mv.id, country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name, mv.value, to_char(mv.date, mv.date_precision) AS date FROM gestalt_frontend_country_data AS mv INNER JOIN gestalt_country_with_name AS country ON mv.country_id = country.id INNER JOIN gestalt_series AS series ON mv.series_id = series.id INNER JOIN gestalt_subgroup AS subgroup ON mv.country_id = subgroup.country_id WHERE mv.series_id = """ + series_id + """ AND subgroup.group_id = """ + group_id + """ AND subgroup.name_id = """ + subgroup_name_id + """ AND to_char(mv.date, mv.date_precision) = '""" + date + """' ORDER BY country.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, series_id, low_val, high_val, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT mv.id, country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name, mv.value, to_char(mv.date, mv.date_precision) AS date FROM gestalt_frontend_country_data AS mv INNER JOIN gestalt_country_with_name AS country ON mv.country_id = country.id INNER JOIN gestalt_series AS series ON mv.series_id = series.id WHERE mv.series_id = """ + series_id + """ AND mv.date = (SELECT max(date) FROM gestalt_frontend_country_data WHERE series_id = """ + series_id + """) AND (CASE WHEN """ + low_val + """ IS NULL THEN 1 ELSE """ + low_val + """ END) < mv.value AND mv.value <= """ + high_val + """ ORDER BY country.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, series_id, low_val, high_val, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT mv.id, country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name, mv.value, to_char(mv.date, mv.date_precision) AS date FROM gestalt_frontend_country_data AS mv INNER JOIN gestalt_country_with_name AS country ON mv.country_id = country.id INNER JOIN gestalt_series AS series ON mv.series_id = series.id WHERE mv.series_id = """ + series_id + """ AND mv.date = (SELECT max(date) FROM gestalt_frontend_country_data WHERE series_id = """ + series_id + """) AND (CASE WHEN """ + low_val + """ IS NULL THEN 1 ELSE """ + low_val + """ END) < mv.value AND mv.value <= """ + high_val + """ ORDER BY country.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, workspace_url_name, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (w.id) w.id, wn.name, p.name AS persona_name, w.url_name FROM """ + helper.table_prefix + """workspace AS w LEFT JOIN """ + helper.table_prefix + """workspace_name AS wn ON w.workspace_name_id = wn.id LEFT JOIN """ + helper.table_prefix + """persona AS p ON w.persona_id = p.id WHERE w.id IS NOT NULL AND w.url_name = '""" + workspace_url_name + """' ORDER BY w.id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, workspace_url_name, persona_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select distinct on (p.name, vd.name) pps.panel_id, p.url_name, w.url_name as workspace_url_name, p.name, pps.persona_id, vd.name as default_vis from """ + helper.table_prefix + """persona_panel_story pps left join """ + helper.table_prefix + """workspace_panel wp on wp.panel_id = pps.panel_id left join """ + helper.table_prefix + """story s on s.id = pps.story_id left join """ + helper.table_prefix + """vis v on v.id = s.vis_id left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """panel p on p.id = wp.panel_id left join """ + helper.table_prefix + """workspace w on w.id = wp.workspace_id where w.url_name = '""" + workspace_url_name + """' and pps.persona_id = """ + persona_id + """ and vd.name is not null order by p.name asc, vd.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, series_id, group_id, subgroup_name_id, date, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT mv.id, country.id AS country_id, country.iso2code, country.iso3code, country.name AS country_name, mv.value, to_char(mv.date, mv.date_precision) AS date FROM gestalt_frontend_country_data AS mv INNER JOIN gestalt_country_with_name AS country ON mv.country_id = country.id INNER JOIN gestalt_series AS series ON mv.series_id = series.id INNER JOIN gestalt_subgroup AS subgroup ON mv.country_id = subgroup.country_id WHERE mv.series_id = """ + series_id + """ AND subgroup.group_id = """ + group_id + """ AND subgroup.name_id = """ + subgroup_name_id + """ AND to_char(mv.date, mv.date_precision) = '""" + date + """' ORDER BY country.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, source_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select gn.name as source_name, cy.iso2code as source, gnt.name as target_name, cyt.iso2code as target, cy.id as source_id, count(cdis.target_id) as value from """ + helper.table_prefix + """cdis cdis left join """ + helper.table_prefix + """geography_name gn on gn.id = cdis.source_id left join """ + helper.table_prefix + """geography_name gnt on gnt.id = cdis.target_id left join """ + helper.table_prefix + """country cy on cy.id = cdis.source_id left join """ + helper.table_prefix + """country cyt on cyt.id = cdis.target_id where source_id = """ + source_id + """ and cyt.iso2code is not null group by gn.name, cy.iso2code, cy.id, gnt.name, cyt.iso2code """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select distinct on (w.persona_id) w.id as workspace_id, w.url_name as workspace_url_name, p.id, p.name, p.description, pt.name as persona_type, pl.url_name as default_panel, vd.name as default_vis from """ + helper.table_prefix + """workspace w left join """ + helper.table_prefix + """persona p on p.id = w.persona_id left join """ + helper.table_prefix + """persona_type pt on pt.id = p.persona_type left join """ + helper.table_prefix + """workspace_panel wp on wp.workspace_id = w.id left join """ + helper.table_prefix + """panel pl on pl.id = wp.panel_id left join """ + helper.table_prefix + """persona_panel_story pps on pps.panel_id = wp.panel_id left join """ + helper.table_prefix + """story s on s.id = pps.story_id left join """ + helper.table_prefix + """vis v on v.id = s.vis_id left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id where w.is_default = true order by w.persona_id; """) # obtain the data data = self.cursor.fetchall() # close cursor and connection connection.close() self.cursor.close() # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select distinct on (w.persona_id) w.id as workspace_id, w.url_name as workspace_url_name, p.id, p.name, p.description, pt.name as persona_type, pl.url_name as default_panel, vd.name as default_vis from gestalt_workspace w left join gestalt_persona p on p.id = w.persona_id left join gestalt_persona_type pt on pt.id = p.persona_type left join gestalt_workspace_panel wp on wp.workspace_id = w.id left join gestalt_panel pl on pl.id = wp.panel_id left join gestalt_persona_panel_story pps on pps.panel_id = wp.panel_id left join gestalt_story s on s.id = pps.story_id left join gestalt_vis v on v.id = s.vis_id left join gestalt_vis_directive vd on vd.id = v.vis_directive_id where w.is_default = true order by w.persona_id; """) # obtain the data data = self.cursor.fetchall() # close cursor and connection connection.close() self.cursor.close() # convert data to a string return json.dumps(data)
def GET(self, persona_id, panel_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select pps.*, s.name, s.url_name, s.intro, array_agg(row_to_json(si)) as ideas from """ + helper.table_prefix + """persona_panel_story pps left join """ + helper.table_prefix + """story s on s.id = pps.story_id left join ( select sti.*, vt.url_name as vis_type_name, array_agg(row_to_json(c)) as controls from """ + helper.table_prefix + """story_idea sti left join """ + helper.table_prefix + """vis_type vt on vt.id = sti.vis_type_id left join ( select sac.*, case when sac.story_action_id = 1 then g.name when sac.story_action_id = 2 then g.name when sac.story_action_id = 3 then g.name when sac.story_action_id = 4 then h.name when sac.story_action_id = 5 then g.name when sac.story_action_id = 6 then g.name when sac.story_action_id = 7 then g.name end as name from """ + helper.table_prefix + """story_action_control sac left join """ + helper.table_prefix + """group g on g.id = sac.name_id left join """ + helper.table_prefix + """heuristic h on h.id = sac.name_id ) c on c.story_action_id = sti.action_id group by sti.id, vt.url_name ) si on si.story_id = pps.story_id where pps.persona_id = """ + persona_id + """ and pps.panel_id = """ + panel_id + """ group by pps.id, s.name, s.url_name, s.intro, s.story_order order by s.story_order; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, story_idea_id, control_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute( """ select sac.id, sim.label, sim.description, si.id as story_idea_id, case when sac.story_action_id = 1 then g.name when sac.story_action_id = 2 then g.name when sac.story_action_id = 5 then g.name when sac.story_action_id = 6 then g.name when sac.story_action_id = 7 then g.name else f.name end as control_name, sa.name as action_name, sim.name as metric_name, array_agg(row_to_json(m)) as metrics from """ + helper.table_prefix + """story_action_control sac left join """ + helper.table_prefix + """story_action sa on sa.id = sac.story_action_id left join """ + helper.table_prefix + """story_idea si on si.action_id = sac.story_action_id left join """ + helper.table_prefix + """group g on g.id = sac.name_id left join """ + helper.table_prefix + """vertex v on v.id = sac.name_id left join """ + helper.table_prefix + """flow f on f.id = sac.name_id left join """ + helper.table_prefix + """story_idea_metric sim on sim.control_id = sac.id left join ( select * from """ + helper.table_prefix + """story_idea_metric_value ) m on m.control_id = sac.id where si.id = """ + story_idea_id + """ and sac.id = """ + control_id + """ group by sac.id, sim.label, sim.description, si.id, g.name, v.name, f.name, sa.name, sim.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select g.*, array_agg(row_to_json(s)) as subgroups from """ + helper.table_prefix + """group g left join ( select distinct on (sg.name_id, sg.group_id) sg.name_id, sg.group_id, case when gt.id = 1 then gn.name else sgn.name end as name, case when gt.id = 1 then gt.id || '_' || gn.id else gt.id || '_' || sgn.id end as id, geo.hexagon_center_x as center_x, geo.hexagon_center_y as center_y, array_agg(row_to_json(n)) as nodes from """ + helper.table_prefix + """subgroup sg left join """ + helper.table_prefix + """geography_name gn on gn.id = sg.name_id left join """ + helper.table_prefix + """subgroup_name sgn on sgn.id = sg.name_id left join """ + helper.table_prefix + """group g on g.id = sg.group_id left join """ + helper.table_prefix + """group_type gt on gt.id = g.type_id left join """ + helper.table_prefix + """geography geo on geo.name_id = sg.name_id and gt.id = 1 left join ( select gn.name, gcy.id, gcy.iso2code as iso from """ + helper.table_prefix + """country gcy left join """ + helper.table_prefix + """geography_name gn on gn.id = gcy.name_id ) n on n.id = sg.country_id group by sg.name_id, sg.group_id, gn.name, sgn.name, geo.hexagon_center_x, geo.hexagon_center_y, gt.id, g.id, gn.id, sgn.id ) s on s.group_id = g.id group by g.id,g.name, g.type_id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT * FROM gestalt_panel; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, persona_id, panel_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select pps.*, s.name, s.url_name, s.intro, array_agg(row_to_json(si)) as ideas from """ + helper.table_prefix + """persona_panel_story pps left join """ + helper.table_prefix + """story s on s.id = pps.story_id left join ( select sti.*, vt.url_name as vis_type_name, array_agg(row_to_json(c)) as controls from """ + helper.table_prefix + """story_idea sti left join """ + helper.table_prefix + """vis_type vt on vt.id = sti.vis_type_id left join ( select sac.*, case when sac.story_action_id = 1 then g.name when sac.story_action_id = 2 then g.name when sac.story_action_id = 3 then g.name when sac.story_action_id = 4 then h.name when sac.story_action_id = 5 then g.name when sac.story_action_id = 6 then g.name when sac.story_action_id = 7 then g.name end as name from """ + helper.table_prefix + """story_action_control sac left join """ + helper.table_prefix + """group g on g.id = sac.name_id left join """ + helper.table_prefix + """heuristic h on h.id = sac.name_id ) c on c.story_action_id = sti.action_id group by sti.id, vt.url_name ) si on si.story_id = pps.story_id where pps.persona_id = """ + persona_id + """ and pps.panel_id = """ + panel_id + """ group by pps.id, s.name, s.url_name, s.intro, s.story_order order by s.story_order; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, vis_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select v.* from """ + helper.table_prefix + """vis v where v.id = """ + vis_id + """; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, persona_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute( """ select distinct on (p.url_name) w.id, w.persona_id, w.url_name, w.is_default, wn.name, p.url_name as default_panel, vd.name as default_vis from """ + helper.table_prefix + """workspace w left join """ + helper.table_prefix + """workspace_name wn on wn.id = w.workspace_name_id left join """ + helper.table_prefix + """workspace_panel wp on wp.workspace_id = w.id left join """ + helper.table_prefix + """panel p on p.id = wp.panel_id left join """ + helper.table_prefix + """persona_panel_story pps on pps.panel_id = wp.panel_id and pps.persona_id = """ + persona_id + """ left join """ + helper.table_prefix + """story s on s.id = pps.story_id left join """ + helper.table_prefix + """vis v on v.id = s.vis_id left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id where w.persona_id = """ + persona_id + """ and wp.is_default = true group by w.id, w.persona_id, w.url_name, w.url_name, wn.name, p.url_name, vd.name order by default_panel, wn.name asc; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, story_idea_id, control_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select sac.id, sim.label, sim.description, si.id as story_idea_id, case when sac.story_action_id = 1 then g.name when sac.story_action_id = 2 then g.name when sac.story_action_id = 5 then g.name when sac.story_action_id = 6 then g.name when sac.story_action_id = 7 then g.name else f.name end as control_name, sa.name as action_name, sim.name as metric_name, array_agg(row_to_json(m)) as metrics from """ + helper.table_prefix + """story_action_control sac left join """ + helper.table_prefix + """story_action sa on sa.id = sac.story_action_id left join """ + helper.table_prefix + """story_idea si on si.action_id = sac.story_action_id left join """ + helper.table_prefix + """group g on g.id = sac.name_id left join """ + helper.table_prefix + """vertex v on v.id = sac.name_id left join """ + helper.table_prefix + """flow f on f.id = sac.name_id left join """ + helper.table_prefix + """story_idea_metric sim on sim.control_id = sac.id left join ( select * from """ + helper.table_prefix + """story_idea_metric_value ) m on m.control_id = sac.id where si.id = """ + story_idea_id + """ and sac.id = """ + control_id + """ group by sac.id, sim.label, sim.description, si.id, g.name, v.name, f.name, sa.name, sim.name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, region_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries cursor = connection.cursor() # execute query cursor.execute(""" TRUNCATE gestalt_frontend_country_data; """) # make changes to the database persistent connection.commit() # close cursor.close() connection.close() return
def GET(self, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT * FROM gestalt_panel; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'gestalt_source%' ORDER BY table_name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, table_name, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (src.series_id) src.series_id, series.code, series.description FROM """ + table_name + """ AS src INNER JOIN gestalt_series AS series ON src.series_id = series.id """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, group_id, subgroup_name_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT country.id, country.name FROM gestalt_subgroup AS subgroup INNER JOIN gestalt_country_with_name AS country ON subgroup.country_id = country.id WHERE subgroup.group_id = """ + group_id + """ AND subgroup.name_id = """ + subgroup_name_id + """ """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, table_name, series_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries cursor = connection.cursor() # execute query cursor.execute(""" INSERT INTO gestalt_frontend_country_data (source_name, source_id, series_id, country_id, date, date_precision, value) SELECT '""" + table_name + """' AS source_name, src.id, src.series_id, src.country_id, src.date, src.date_precision, src.value FROM """ + table_name + """ AS src WHERE src.series_id = """ + series_id + """; """) # make changes to the database persistent connection.commit() # close cursor.close() connection.close() return
def GET(self, region_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries cursor = connection.cursor() # execute query cursor.execute(""" TRUNCATE gestalt_frontend_country_data; """) # make changes to the database persistent connection.commit() # close cursor.close() connection.close() return
def GET(self, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'gestalt_source%' ORDER BY table_name; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, persona_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (pl.id) pl.id, pl.name, pl.url_name FROM gestalt_panel AS pl RIGHT JOIN gestalt_persona_panel_story AS pps ON pl.id = pps.panel_id AND pps.persona_id = """ + persona_id + """ WHERE pl.id IS NOT NULL ORDER BY pl.id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, vis_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select v.* from """ + helper.table_prefix + """vis v where v.id = """ + vis_id + """; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, story_id, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (tag.id) tag.id, tag.name FROM gestalt_tag AS tag RIGHT JOIN gestalt_story_tag AS story_tag ON tag.id = story_tag.tag_id AND story_tag.story_id = """ + story_id + """ WHERE tag.id IS NOT NULL ORDER BY tag.id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, table_name, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (src.series_id) src.series_id, series.code, series.description FROM """ + table_name + """ AS src INNER JOIN gestalt_series AS series ON src.series_id = series.id """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, workspace_url_name, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (w.id) w.id, wn.name, p.name AS persona_name, w.url_name FROM """ + helper.table_prefix + """workspace AS w LEFT JOIN """ + helper.table_prefix + """workspace_name AS wn ON w.workspace_name_id = wn.id LEFT JOIN """ + helper.table_prefix + """persona AS p ON w.persona_id = p.id WHERE w.id IS NOT NULL AND w.url_name = '""" + workspace_url_name + """' ORDER BY w.id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, group_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (subgroup.name_id) subgroup.id, subgroup.name_id, subgroup_name.name FROM gestalt_subgroup AS subgroup INNER JOIN gestalt_subgroup_name AS subgroup_name ON subgroup.name_id = subgroup_name.id WHERE subgroup.group_id = """ + group_id + """ ORDER BY subgroup.name_id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, persona_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" SELECT DISTINCT ON (pl.id) pl.id, pl.name, pl.url_name FROM gestalt_panel AS pl RIGHT JOIN gestalt_persona_panel_story AS pps ON pl.id = pps.panel_id AND pps.persona_id = """ + persona_id + """ WHERE pl.id IS NOT NULL ORDER BY pl.id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data)
def GET(self, table, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" select distinct on (gn.name) gn.name, cy.iso2code as iso, cy.id, count(distinct cdis.target_id) as count from """ + helper.table_prefix + """country cy left join """ + helper.table_prefix + """geography_name gn on gn.id = cy.name_id left join """ + helper.table_prefix + """geography geo on geo.name_id = cy.name_id left join """ + helper.table_prefix + """cdis cdis on source_id = cy.id group by gn.name, cy.iso2code, cy.id """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, table_name, series_id, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries cursor = connection.cursor() # execute query cursor.execute( """ INSERT INTO gestalt_frontend_country_data (source_name, source_id, series_id, country_id, date, date_precision, value) SELECT '""" + table_name + """' AS source_name, src.id, src.series_id, src.country_id, src.date, src.date_precision, src.value FROM """ + table_name + """ AS src WHERE src.series_id = """ + series_id + """; """) # make changes to the database persistent connection.commit() # close cursor.close() connection.close() return
def GET(self, data_type, polygon_type, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # check param if (data_type == "xy"): # execute query to return svg simple x/y coordinate system self.cursor.execute(""" select geo.id, gn.name, cty.iso2code as iso, geo.hexagon_center_x as center_x, geo.hexagon_center_y as center_y from """ + helper.table_prefix + """geography geo left join """ + helper.table_prefix + """geography_name gn on gn.id = geo.name_id left join """ + helper.table_prefix + """country cty on cty.id = geo.name_id where geo.hexagon_center_x is not null and geo.hexagon_center_y is not null; """) else: # execute query to return mercator projection in geojson coordinate system self.cursor.execute(""" select 'FeatureCollection' as type, array_agg(row_to_json(r)) as features from ( with t as ( select 'Feature'::text ) select t.text as type, row_to_json(f) as properties, row_to_json(c) as geometry from t, """ + helper.table_prefix + """geography geo left join ( select geo.id, gn.name, cy.iso2code as iso from """ + helper.table_prefix + """geography geo left join """ + helper.table_prefix + """geography_name gn on gn.id = geo.name_id left join """ + helper.table_prefix + """country cy on cy.id = geo.name_id ) f on f.id = geo.id left join ( with t as ( select 'Polygon'::text ) select t.text as type, geo.""" + polygon_type + """_polygon as coordinates from t, """ + helper.table_prefix + """geography geo ) c on c.coordinates = geo.""" + polygon_type + """_polygon where geo.""" + polygon_type + """_polygon is not null ) r group by type; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" with data_array as ( select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name, dd.value) r))) as data from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id group by dd.vis_id ), -- attributes attrs_array as ( select vca.vis_id, array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs from """ + helper.table_prefix + """vis_code_attr vca left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id group by vca.vis_id ), -- do guidance dos_array as ( select doa.vis_id, array_agg(row_to_json(( select r from (select doa.*) r ))) as dos from """ + helper.table_prefix + """vis_do_attr doa group by doa.vis_id ), -- dont guidance donts_array as ( select donta.vis_id, array_agg(row_to_json(( select r from (select donta.*) r ))) as donts from """ + helper.table_prefix + """vis_dont_attr donta group by donta.vis_id ), -- alternatives alts_array as ( select val.vis_id, array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts from """ + helper.table_prefix + """vis_alt_attr val left join """ + helper.table_prefix + """vis_directive vd on vd.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis v on v.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id group by val.vis_id ) select v.*, vt.name as vis_type_name, vt.url_name as vis_type_urlname, vd.name as directive, dd.data, vca.attrs, doa.dos, donta.donts, val.alts from """ + helper.table_prefix + """vis v left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id left join data_array dd on dd.vis_id = v.id left join attrs_array vca on vca.vis_id = v.id left join dos_array doa on doa.vis_id = v.id left join donts_array donta on donta.vis_id = v.id left join alts_array val on val.vis_id = v.id where vt.url_name = 'hierarchy'; """) # obtain the data data = self.cursor.fetchall() # temp open from file for now with open("data/hierarchy.json") as json_data: # dict fc = json.load(json_data) # loop through meta info for obj in data: # add dummy data until db is updated obj["data"] = fc # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" -- dummy data with data_array as ( -- nodes with node_array as ( select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name) r))) as nodes from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id group by dd.vis_id ), -- links link_array as ( select ddv.data_id, array_agg(row_to_json((select r from (select ddl.name) r))) as links from """ + helper.table_prefix + """vis_dummy_data_values ddv left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = ddv.value group by ddv.data_id ) select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name, na.nodes, la.links) r))) as data from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id left join node_array na on na.vis_id = dd.id left join link_array la on la.data_id = dd.id group by dd.vis_id ) select v.*, vt.name as vis_type_name, vt.url_name as vis_type_urlname, vd.name as directive--, --dd.data from """ + helper.table_prefix + """vis v left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id --left join data_array dd on dd.vis_id = v.id where vt.url_name = 'relatedness'; """) # obtain the data data = self.cursor.fetchall() # temp open from file for now with open("data/related.json") as json_data: # dict fc = json.load(json_data) # loop through meta info for obj in data: # add dummy data until db is updated obj["data"] = fc # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute( """ -- dummy data with data_array as ( -- values with value_array as ( select ddv.data_id, array_agg(row_to_json((select r from (select ddv.value, ddv.date as timestamp) r))) as values from """ + helper.table_prefix + """vis_dummy_data_values ddv group by ddv.data_id ) select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name, va.values ) r))) as data from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id left join value_array va on va.data_id = dd.id group by dd.vis_id ), -- attributes attrs_array as ( select vca.vis_id, array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs from """ + helper.table_prefix + """vis_code_attr vca left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id group by vca.vis_id ), -- do guidance dos_array as ( select doa.vis_id, array_agg(row_to_json(( select r from (select doa.*) r ))) as dos from """ + helper.table_prefix + """vis_do_attr doa group by doa.vis_id ), -- dont guidance donts_array as ( select donta.vis_id, array_agg(row_to_json(( select r from (select donta.*) r ))) as donts from """ + helper.table_prefix + """vis_dont_attr donta group by donta.vis_id ), -- alternatives alts_array as ( select val.vis_id, array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts from """ + helper.table_prefix + """vis_alt_attr val left join """ + helper.table_prefix + """vis_directive vd on vd.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis v on v.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id group by val.vis_id ) select v.*, vt.name as vis_type_name, vt.url_name as vis_type_urlname, vd.name as directive, dd.data, vca.attrs, doa.dos, donta.donts, val.alts from """ + helper.table_prefix + """vis v left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id left join data_array dd on dd.vis_id = v.id left join attrs_array vca on vca.vis_id = v.id left join dos_array doa on doa.vis_id = v.id left join donts_array donta on donta.vis_id = v.id left join alts_array val on val.vis_id = v.id where vt.url_name = 'time-series'; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute( """ select g.*, array_agg(row_to_json(s)) as subgroups from """ + helper.table_prefix + """group g left join ( select distinct on (sg.name_id, sg.group_id) sg.name_id, sg.group_id, case when gt.id = 1 then gn.name else sgn.name end as name, case when gt.id = 1 then gt.id || '_' || gn.id else gt.id || '_' || sgn.id end as id, geo.hexagon_center_x as center_x, geo.hexagon_center_y as center_y, array_agg(row_to_json(n)) as nodes from """ + helper.table_prefix + """subgroup sg left join """ + helper.table_prefix + """geography_name gn on gn.id = sg.name_id left join """ + helper.table_prefix + """subgroup_name sgn on sgn.id = sg.name_id left join """ + helper.table_prefix + """group g on g.id = sg.group_id left join """ + helper.table_prefix + """group_type gt on gt.id = g.type_id left join """ + helper.table_prefix + """geography geo on geo.name_id = sg.name_id and gt.id = 1 left join ( select gn.name, gcy.id, gcy.iso2code as iso from """ + helper.table_prefix + """country gcy left join """ + helper.table_prefix + """geography_name gn on gn.id = gcy.name_id ) n on n.id = sg.country_id group by sg.name_id, sg.group_id, gn.name, sgn.name, geo.hexagon_center_x, geo.hexagon_center_y, gt.id, g.id, gn.id, sgn.id ) s on s.group_id = g.id group by g.id,g.name, g.type_id; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" -- dummy data with data_array as ( -- nodes with node_array as ( select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name) r))) as nodes from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id group by dd.vis_id ), -- links link_array as ( select ddv.data_id, array_agg(row_to_json((select r from (select ddl.name) r))) as links from """ + helper.table_prefix + """vis_dummy_data_values ddv left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = ddv.value group by ddv.data_id ) select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name, na.nodes, la.links) r))) as data from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id left join node_array na on na.vis_id = dd.id left join link_array la on la.data_id = dd.id group by dd.vis_id ) select v.*, vt.name as vis_type_name, vt.url_name as vis_type_urlname, vd.name as directive--, --dd.data from """ + helper.table_prefix + """vis v left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id --left join data_array dd on dd.vis_id = v.id where vt.url_name = 'relatedness'; """) # obtain the data data = self.cursor.fetchall() # temp open from file for now with open("data/related.json") as json_data: # dict fc = json.load(json_data) # loop through meta info for obj in data: # add dummy data until db is updated obj["data"] = fc # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute( """ with data_array as ( select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name, dd.value) r))) as data from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id group by dd.vis_id ), -- attributes attrs_array as ( select vca.vis_id, array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs from """ + helper.table_prefix + """vis_code_attr vca left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id group by vca.vis_id ), -- do guidance dos_array as ( select doa.vis_id, array_agg(row_to_json(( select r from (select doa.*) r ))) as dos from """ + helper.table_prefix + """vis_do_attr doa group by doa.vis_id ), -- dont guidance donts_array as ( select donta.vis_id, array_agg(row_to_json(( select r from (select donta.*) r ))) as donts from """ + helper.table_prefix + """vis_dont_attr donta group by donta.vis_id ), -- alternatives alts_array as ( select val.vis_id, array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts from """ + helper.table_prefix + """vis_alt_attr val left join """ + helper.table_prefix + """vis_directive vd on vd.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis v on v.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id group by val.vis_id ) select v.*, vt.name as vis_type_name, vt.url_name as vis_type_urlname, vd.name as directive, dd.data, vca.attrs, doa.dos, donta.donts, val.alts from """ + helper.table_prefix + """vis v left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id left join data_array dd on dd.vis_id = v.id left join attrs_array vca on vca.vis_id = v.id left join dos_array doa on doa.vis_id = v.id left join donts_array donta on donta.vis_id = v.id left join alts_array val on val.vis_id = v.id where vt.url_name = 'hierarchy'; """) # obtain the data data = self.cursor.fetchall() # temp open from file for now with open("data/hierarchy.json") as json_data: # dict fc = json.load(json_data) # loop through meta info for obj in data: # add dummy data until db is updated obj["data"] = fc # convert data to a string return json.dumps(data)
def GET(self, connection_string=helper.get_connection_string(os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # execute query self.cursor.execute(""" -- dummy data with data_array as ( -- values with value_array as ( select ddv.data_id, array_agg(row_to_json((select r from (select ddv.value, ddv.date as timestamp) r))) as values from """ + helper.table_prefix + """vis_dummy_data_values ddv group by ddv.data_id ) select dd.vis_id, array_agg(row_to_json((select r from (select ddl.name, va.values ) r))) as data from """ + helper.table_prefix + """vis_dummy_data dd left join """ + helper.table_prefix + """vis_dummy_data_label ddl on ddl.id = dd.name_id left join value_array va on va.data_id = dd.id group by dd.vis_id ), -- attributes attrs_array as ( select vca.vis_id, array_agg(row_to_json((select r from (select vca.*, va.name as attr_name, va.value as default_value) r ))) as attrs from """ + helper.table_prefix + """vis_code_attr vca left join """ + helper.table_prefix + """vis_attr va on va.id = vca.attr_id group by vca.vis_id ), -- do guidance dos_array as ( select doa.vis_id, array_agg(row_to_json(( select r from (select doa.*) r ))) as dos from """ + helper.table_prefix + """vis_do_attr doa group by doa.vis_id ), -- dont guidance donts_array as ( select donta.vis_id, array_agg(row_to_json(( select r from (select donta.*) r ))) as donts from """ + helper.table_prefix + """vis_dont_attr donta group by donta.vis_id ), -- alternatives alts_array as ( select val.vis_id, array_agg(row_to_json((select r from (select val.*, vd.name as alt_name, vt.url_name as alt_type) r ))) as alts from """ + helper.table_prefix + """vis_alt_attr val left join """ + helper.table_prefix + """vis_directive vd on vd.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis v on v.id = val.alt_vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id group by val.vis_id ) select v.*, vt.name as vis_type_name, vt.url_name as vis_type_urlname, vd.name as directive, dd.data, vca.attrs, doa.dos, donta.donts, val.alts from """ + helper.table_prefix + """vis v left join """ + helper.table_prefix + """vis_directive vd on vd.id = v.vis_directive_id left join """ + helper.table_prefix + """vis_type vt on vt.id = v.vis_type_id left join data_array dd on dd.vis_id = v.id left join attrs_array vca on vca.vis_id = v.id left join dos_array doa on doa.vis_id = v.id left join donts_array donta on donta.vis_id = v.id left join alts_array val on val.vis_id = v.id where vt.url_name = 'time-series'; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)
def GET(self, data_type, polygon_type, connection_string=helper.get_connection_string( os.environ['DATABASE_URL'])): # connect to postgresql based on configuration in connection_string connection = psycopg2.connect(connection_string) # get a cursor to perform queries self.cursor = connection.cursor( cursor_factory=psycopg2.extras.RealDictCursor) # check param if (data_type == "xy"): # execute query to return svg simple x/y coordinate system self.cursor.execute(""" select geo.id, gn.name, cty.iso2code as iso, geo.hexagon_center_x as center_x, geo.hexagon_center_y as center_y from """ + helper.table_prefix + """geography geo left join """ + helper.table_prefix + """geography_name gn on gn.id = geo.name_id left join """ + helper.table_prefix + """country cty on cty.id = geo.name_id where geo.hexagon_center_x is not null and geo.hexagon_center_y is not null; """) else: # execute query to return mercator projection in geojson coordinate system self.cursor.execute(""" select 'FeatureCollection' as type, array_agg(row_to_json(r)) as features from ( with t as ( select 'Feature'::text ) select t.text as type, row_to_json(f) as properties, row_to_json(c) as geometry from t, """ + helper.table_prefix + """geography geo left join ( select geo.id, gn.name, cy.iso2code as iso from """ + helper.table_prefix + """geography geo left join """ + helper.table_prefix + """geography_name gn on gn.id = geo.name_id left join """ + helper.table_prefix + """country cy on cy.id = geo.name_id ) f on f.id = geo.id left join ( with t as ( select 'Polygon'::text ) select t.text as type, geo.""" + polygon_type + """_polygon as coordinates from t, """ + helper.table_prefix + """geography geo ) c on c.coordinates = geo.""" + polygon_type + """_polygon where geo.""" + polygon_type + """_polygon is not null ) r group by type; """) # obtain the data data = self.cursor.fetchall() # convert data to a string return json.dumps(data, default=helper.decimal_encoder)