def get_team_data(): conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn() conn.autocommit = True cur = conn.cursor() params = {"token": Variable.get("workboard_token")} base_url = "https://www.myworkboard.com/wb/apis" r = requests.get(base_url + "/team?include=org_teams", params=params) if r.status_code != 200: raise ValueError("Request not successful. Status code {}".format( r.status_code)) else: teams = r.json()["data"]["team"] logging.info("There are {} teams".format(len(teams))) for t in teams: try: cur.execute( """INSERT INTO teams_raw (team_id, team_data) VALUES (%s, %s) ON CONFLICT (team_id) DO UPDATE SET team_data = EXCLUDED.team_data """, [t["team_id"], json.dumps(t)], ) logging.info("Team id #{} inserted into the table".format( t["team_id"])) except BaseException: logging.warning("Record skipped") pass cur.close() conn.close()
def get_ads_html(): conn = PostgresHook(postgres_conn_id="postgres_apple").get_conn() conn.autocommit = True cur = conn.cursor() urls = apple.get_urls() for url in urls: html = apple.get_html(url) cur.execute( """INSERT INTO raw (url, html) VALUES (%s, %s)""", [url, html], ) cur.close() conn.close()
def get_goal_data(goal_status=1): conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn() conn.autocommit = True cur = conn.cursor() params = { "token": Variable.get("workboard_token"), "goal_status": goal_status } base_url = "https://www.myworkboard.com/wb/apis" r = requests.get(base_url + "/goal", params=params) if r.status_code != 200: raise ValueError("Request not successful. Status code {}".format( r.status_code)) else: goals = r.json()["data"]["goal"] logging.info("There are {} person(s) with goals".format(len(goals))) for person in goals: logging.info("{} has {} goals".format(person["user_email"], len(person["people_goals"]))) for g in person["people_goals"]: try: cur.execute( """INSERT INTO goals_raw (goal_id, goal_data) VALUES (%s, %s) ON CONFLICT (goal_id) DO UPDATE SET goal_data = EXCLUDED.goal_data """, [int(g["goal_id"]), json.dumps(g)], ) logging.info("Goal id #{} inserted into the table".format( g["goal_id"])) except BaseException: logging.warning("Record skipped") pass cur.close() conn.close()
def get_team_member_data(): conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn() conn.autocommit = True cur = conn.cursor() cur.execute("SELECT team_id FROM teams_raw") team_ids = [line[0] for line in cur] params = {"token": Variable.get("workboard_token")} base_url = "https://www.myworkboard.com/wb/apis" for t in team_ids: r = requests.get(base_url + "/team/{}".format(t), params=params) if r.status_code != 200: logging.warning("Request not successful. Status code {}".format( r.status_code)) pass else: team = r.json()["data"]["team"] try: cur.execute( """INSERT INTO team_members_raw (team_id, team_member_data) VALUES (%s, %s) ON CONFLICT (team_id) DO UPDATE SET team_member_data = EXCLUDED.team_member_data """, [team["team_id"], json.dumps(team)], ) logging.info("Team id #{} inserted into the table".format( team["team_id"])) except BaseException: logging.warning("Record skipped") pass cur.close() conn.close()
default_args = { 'owner': 'airflow', 'depends_on_past': False, 'start_date': datetime(2020, 3, 13), 'email': ['*****@*****.**'], 'email_on_failure': False, 'email_on_retry': False, 'retries': 1, 'retry_delay': timedelta(minutes=5), } dag = DAG('refresh_sensors', default_args=default_args, schedule_interval='0 * * * *') conn = PostgresHook(postgres_conn_id='agg2_db') conn.autocommit = True def query_func(ds, **kwargs): print(ds) print(query) result = '' result = conn.run(query) return result sshHook = SSHHook(ssh_conn_id='agg2') query = \ """ REFRESH MATERIALIZED VIEW charger.estp_sensors; """