Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Build ETL pipelines and analytics dashboards for Harvard Art Museums API data with Python, SQL, and Streamlit
triggers
["how do I extract data from Harvard Art Museums API","create an ETL pipeline for museum artifact data","build a Streamlit dashboard for art collection analytics","query Harvard artifacts database with SQL","visualize museum collection data with Plotly","set up data engineering pipeline for art museum data","transform Harvard API JSON into relational database","analyze artifact metadata by culture and century"]
This skill enables AI coding agents to help developers build end-to-end ETL pipelines and analytics applications using the Harvard Art Museums API. The project demonstrates real-world data engineering patterns including API integration, data transformation, SQL database design, and interactive visualization with Streamlit.
What This Project Does
The Harvard Artifacts Collection application:
Extracts artifact data from Harvard Art Museums API with pagination and rate limiting
Transforms nested JSON into normalized relational tables
Loads data into MySQL/TiDB Cloud databases
Provides 20+ predefined analytical SQL queries
Visualizes results with interactive Plotly charts in Streamlit
import mysql.connector
from mysql.connector import Error
defget_db_connection():
"""
Create MySQL database connection from environment variables
"""return mysql.connector.connect(
host=os.getenv('DB_HOST'),
port=int(os.getenv('DB_PORT', 3306)),
user=os.getenv('DB_USER'),
password=os.getenv('DB_PASSWORD'),
database=os.getenv('DB_NAME')
)
defload_metadata(df_metadata):
"""
Batch insert artifact metadata into database
"""
conn = get_db_connection()
cursor = conn.cursor()
insert_query = """
INSERT INTO artifactmetadata
(id, title, culture, century, classification, department, dated,
division, medium, technique, period, accessionyear,
totalpageviews, totaluniquepageviews)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
title=VALUES(title), culture=VALUES(culture)
"""
records = df_metadata.to_records(index=False).tolist()
cursor.executemany(insert_query, records)
conn.commit()
print(f"Inserted {cursor.rowcount} metadata records")
cursor.close()
conn.close()
defload_all_data(df_metadata, df_media, df_colors):
"""
Load all transformed data into respective tables
"""
load_metadata(df_metadata)
# Similar functions for media and colorsprint("ETL pipeline completed successfully")
Streamlit Analytics Dashboard
import streamlit as st
import plotly.express as px
st.set_page_config(page_title="Harvard Artifacts Analytics", layout="wide")
st.title("🎨 Harvard Art Museums Collection Analytics")
# Sidebar for query selection
query_options = {
"Top 10 Cultures by Artifact Count": """
SELECT culture, COUNT(*) as count
FROM artifactmetadata
WHERE culture IS NOT NULL
GROUP BY culture
ORDER BY count DESC
LIMIT 10
""",
"Artifacts by Century": """
SELECT century, COUNT(*) as count
FROM artifactmetadata
WHERE century IS NOT NULL
GROUP BY century
ORDER BY count DESC
""",
"Most Common Colors": """
SELECT color, COUNT(*) as usage_count, AVG(percent) as avg_percent
FROM artifactcolors
GROUP BY color
ORDER BY usage_count DESC
LIMIT 10
""",
"Department Distribution": """
SELECT department, COUNT(*) as artifact_count
FROM artifactmetadata
WHERE department IS NOT NULL
GROUP BY department
ORDER BY artifact_count DESC
"""
}
selected_query = st.sidebar.selectbox("Select Analysis", list(query_options.keys()))
if st.button("Run Analysis"):
conn = get_db_connection()
df_result = pd.read_sql(query_options[selected_query], conn)
conn.close()
st.subheader(f"Results: {selected_query}")
st.dataframe(df_result)
# Auto-generate visualizationiflen(df_result.columns) >= 2:
fig = px.bar(df_result,
x=df_result.columns[0],
y=df_result.columns[1],
title=selected_query)
st.plotly_chart(fig, use_container_width=True)
-- Top viewed artifactsSELECT title, culture, totalpageviews
FROM artifactmetadata
ORDERBY totalpageviews DESC
LIMIT 20;
-- Artifacts with color dataSELECT a.title, a.culture, c.color, c.percent
FROM artifactmetadata a
JOIN artifactcolors c ON a.id = c.artifact_id
WHERE c.percent >50ORDERBY c.percent DESC;
-- Media availability by departmentSELECT
a.department,
COUNT(*) as total_artifacts,
COUNT(m.id) as with_media,
ROUND(COUNT(m.id) *100.0/COUNT(*), 2) as media_percentage
FROM artifactmetadata a
LEFTJOIN artifactmedia m ON a.id = m.artifact_id
GROUPBY a.department
ORDERBY total_artifacts DESC;
This skill provides everything needed to build production-ready ETL pipelines and analytics dashboards for museum collection data using modern Python data engineering tools.
