GLM 5.2 数据分析指南：结构化输出、SQL 生成与 Python 工作流

from openai import OpenAI

client = OpenAI(
    api_key="YOUR_ZAI_API_KEY",
    base_url="https://api.z.ai/v1",
)

# Quick sanity check
response = client.chat.completions.create(
    model="glm-5.2",
    messages=[{"role": "user", "content": "Reply with the word READY."}],
    max_tokens=10,
)
print(response.choices[0].message.content)

schema = open("full_schema.sql").read()  # entire DB schema, no truncation needed

def generate_sql(natural_language_query: str, schema: str) -> str:
    prompt = f"""You are a SQL expert. Use the schema below to write a precise SQL query.
Return only the SQL — no explanation, no markdown fences.

SCHEMA:
{schema}

QUERY REQUEST:
{natural_language_query}"""

    response = client.chat.completions.create(
        model="glm-5.2",
        messages=[{"role": "user", "content": prompt}],
        temperature=0,  # deterministic output for SQL
    )
    return response.choices[0].message.content.strip()

# Example
sql = generate_sql(
    "Show the top 10 customers by revenue in Q2 2026, including their churn risk score.",
    schema,
)
print(sql)

import pandas as pd

def analyze_csv(filepath: str, question: str) -> str:
    df = pd.read_csv(filepath)
    csv_text = df.to_csv(index=False)

    prompt = f"""Analyze the following CSV data and answer the question.
Be specific: include exact numbers, percentages, and column names.

DATA:
{csv_text}

QUESTION:
{question}"""

    response = client.chat.completions.create(
        model="glm-5.2",
        messages=[{"role": "user", "content": prompt}],
    )
    return response.choices[0].message.content

result = analyze_csv(
    "sales_q2_2026.csv",
    "Which product category had the highest month-over-month growth in June? "
    "What drove it, based on the data?",
)
print(result)

import json

def extract_structured(text: str, schema_description: str) -> dict:
    response = client.chat.completions.create(
        model="glm-5.2",
        response_format={"type": "json_object"},
        messages=[
            {
                "role": "system",
                "content": f"Extract data from the input and return a JSON object. Schema: {schema_description}",
            },
            {"role": "user", "content": text},
        ],
        temperature=0,
    )
    return json.loads(response.choices[0].message.content)

# Example: extract KPIs from a financial report paragraph
report_excerpt = """
Revenue for Q2 2026 came in at $4.2M, up 18% year over year.
Gross margin improved to 67% from 61% in Q2 2025.
Operating expenses were $2.8M, driven by a 40% increase in R&D headcount.
Net loss narrowed to $380K from $910K in the prior year quarter.
"""

kpis = extract_structured(
    report_excerpt,
    '{"revenue_usd": number, "revenue_yoy_pct": number, "gross_margin_pct": number, '
    '"opex_usd": number, "net_loss_usd": number}',
)
print(json.dumps(kpis, indent=2))
# Output:
# {
#   "revenue_usd": 4200000,
#   "revenue_yoy_pct": 18,
#   "gross_margin_pct": 67,
#   "opex_usd": 2800000,
#   "net_loss_usd": 380000
# }

def generate_pandas_code(df_description: str, transformation: str) -> str:
    prompt = f"""Write a Python function using pandas that performs the transformation described.
Use type hints. Handle edge cases (NaN, empty strings, type mismatches).
Return only the function — no imports, no explanation.

DATAFRAME DESCRIPTION:
{df_description}

TRANSFORMATION:
{transformation}"""

    response = client.chat.completions.create(
        model="glm-5.2",
        messages=[{"role": "user", "content": prompt}],
        temperature=0.1,
    )
    return response.choices[0].message.content

code = generate_pandas_code(
    df_description="""
    Columns: customer_id (str), signup_date (str, various formats like '2024-01-15', 'Jan 15 2024', '01/15/24'),
    plan (str, values: 'free', 'pro', 'enterprise', sometimes null), mrr (float, sometimes negative due to credits),
    churn_date (str, null if active)
    """,
    transformation="""
    1. Parse signup_date and churn_date to datetime, coerce errors to NaT
    2. Normalize plan to lowercase, fill nulls with 'free'
    3. Clip mrr to 0 minimum (no negative values)
    4. Add a boolean 'is_churned' column (True if churn_date is not NaT)
    5. Add 'tenure_days' as days from signup_date to churn_date (or today if active)
    """,
)
print(code)

import asyncio
from openai import AsyncOpenAI

async_client = AsyncOpenAI(
    api_key="YOUR_ZAI_API_KEY",
    base_url="https://api.z.ai/v1",
)

SYSTEM_PROMPT = """You are a data analyst. Classify the customer feedback and extract:
- sentiment: positive | neutral | negative
- topics: list of up to 3 main topics mentioned
- urgency: low | medium | high
Return JSON only."""

async def classify_feedback(text: str) -> dict:
    response = await async_client.chat.completions.create(
        model="glm-5.2",
        response_format={"type": "json_object"},
        messages=[
            {"role": "system", "content": SYSTEM_PROMPT},
            {"role": "user", "content": text},
        ],
        temperature=0,
    )
    return json.loads(response.choices[0].message.content)

async def batch_classify(feedbacks: list[str]) -> list[dict]:
    tasks = [classify_feedback(f) for f in feedbacks]
    return await asyncio.gather(*tasks)

# Run
feedbacks = [
    "The dashboard is slow and keeps timing out during peak hours.",
    "Loving the new export feature, saved me hours this week.",
    "Billing charged me twice last month, need a refund immediately.",
]

results = asyncio.run(batch_classify(feedbacks))
for feedback, result in zip(feedbacks, results):
    print(f"{feedback[:50]}... → {result}")