Spaces:
Sleeping
Sleeping
File size: 2,301 Bytes
bc7640d |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
import os
import json
import pandas as pd
import streamlit as st
import snowflake.connector
from cryptography.hazmat.primitives import serialization
from comscore_site_list import render as render_comscore_site_list
from media_plan_templater import render as render_media_plan_templater
st.set_page_config(layout="wide", page_title="Sales Toolkit", page_icon="📊")
# ─── Snowflake Helpers ───────────────────────────────────────
def get_conn():
raw_key = os.getenv("snowflake_private_key")
priv_key = serialization.load_pem_private_key(raw_key.encode(), password=None)
return snowflake.connector.connect(
user=os.getenv("snowflake_user"),
account=os.getenv("snowflake_account_identifier"),
private_key=priv_key,
role=os.getenv("snowflake_role"),
warehouse=os.getenv("snowflake_warehouse"),
database=os.getenv("snowflake_database"),
)
@st.cache_data
def get_lookup_table():
conn = get_conn()
cs = conn.cursor()
database = os.getenv("snowflake_database") # e.g. ANALYTICS
schema = "SIGMA_SCRATCH"
view = "VIEW_NEW_DATASET_FROM_SQL_3B3605DCE9F84DBC8F8508F33C4364FF"
cs.execute(
f"""
SELECT
"ENTITY" AS entity,
"SITE ID" AS site_id,
"SITE NAME" AS site_name,
"STATUS" AS status,
"AD OPTIONS" AS ad_options
FROM {database}.{schema}.{view}
"""
)
cols = [d[0].lower() for d in cs.description]
rows = cs.fetchall()
cs.close()
conn.close()
return pd.DataFrame(rows, columns=cols)
def extract_pmp(ad_opts):
"""Parse the JSON in `ad_options` and return the top-level 'pmp' boolean."""
try:
obj = json.loads(ad_opts)
return obj.get("pmp")
except Exception:
return None
# ─── App Layout ───────────────────────────────────────────────
st.title("Sales Toolkit")
tab1, tab2 = st.tabs(["📊 Comscore Site List", "Media Plan Templater"])
with tab1:
render_comscore_site_list(get_conn, get_lookup_table, extract_pmp)
with tab2:
render_media_plan_templater(get_conn)
|