帮我编写一份python程序可以用来调用AI的API接口读取excel表格并分析题目的风险程度并进行评分,导出为excel表格。
- User
帮我编写一份python程序可以用来调用AI的API接口读取excel表格并分析题目的风险程度并进行评分,导出为excel表格。
Assistant
我来帮你编写一个Python程序,用于读取Excel表格中的题目,调用AI API进行风险分析,并将结果导出到新的Excel表格。
import requests import pandas as pd import json import time from datetime import datetime import os # --- 配置区 --- # AI API配置 AI_API_URL = "YOUR_AI_API_ENDPOINT" # 例如: "https://api.openai.com/v1/chat/completions" AI_API_KEY = "YOUR_API_KEY" # 你的API密钥 AI_MODEL = "gpt-3.5-turbo" # 或其他模型名称 # 输入输出文件配置 INPUT_EXCEL_FILE = "questions.xlsx" # 输入的Excel文件名 INPUT_SHEET_NAME = "Sheet1" # Excel中的工作表名称 QUESTION_COLUMN = "题目" # 包含题目的列名 OUTPUT_EXCEL_FILE = f"risk_analysis_results_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" # API请求配置 REQUEST_TIMEOUT = 30 # 请求超时时间(秒) RETRY_TIMES = 3 # 重试次数 DELAY_BETWEEN_REQUESTS = 1 # 请求之间的延迟(秒),避免触发速率限制 # 风险评估提示词模板 RISK_ANALYSIS_PROMPT = """ 请分析以下题目的风险程度,并从以下几个维度进行评估: 1. 内容敏感度(0-10分):是否涉及敏感话题、争议性内容 2. 法律合规性(0-10分):是否可能涉及法律风险 3. 道德伦理(0-10分):是否涉及道德伦理问题 4. 信息安全(0-10分):是否可能泄露敏感信息 5. 社会影响(0-10分):可能产生的社会影响 题目:{question} 请以JSON格式返回分析结果,包含以下字段: {{ "content_sensitivity": 分数, "legal_compliance": 分数, "ethical_concerns": 分数, "information_security": 分数, "social_impact": 分数, "total_risk_score": 总风险分数(0-100), "risk_level": "低风险/中风险/高风险", "risk_factors": ["具体风险因素1", "具体风险因素2"], "recommendations": "建议措施" }} """ # --- 功能函数 --- def read_excel_questions(file_path, sheet_name, column_name): """从Excel文件读取题目列表""" try: print(f"正在读取Excel文件: {file_path}") df = pd.read_excel(file_path, sheet_name=sheet_name) if column_name not in df.columns: print(f"错误:找不到列 '{column_name}'") print(f"可用的列: {', '.join(df.columns)}") return None questions = df[column_name].dropna().tolist() print(f"成功读取 {len(questions)} 个题目") return questions, df except FileNotFoundError: print(f"错误:找不到文件 '{file_path}'") return None except Exception as e: print(f"错误:读取Excel文件时出错 - {e}") return None def call_ai_api(question, retry_count=0): """调用AI API进行风险分析""" headers = { "Authorization": f"Bearer {AI_API_KEY}", "Content-Type": "application/json" } # 构建请求数据(以OpenAI格式为例) data = { "model": AI_MODEL, "messages": [ { "role": "system", "content": "你是一个专业的风险评估专家,擅长分析各类题目和内容的潜在风险。" }, { "role": "user", "content": RISK_ANALYSIS_PROMPT.format(question=question) } ], "temperature": 0.3, # 降低温度以获得更一致的结果 "max_tokens": 500 } try: print(f"正在分析题目: {question[:50]}...") response = requests.post( AI_API_URL, headers=headers, json=data, timeout=REQUEST_TIMEOUT ) if response.status_code == 200: result = response.json() # 提取AI的回复内容(根据不同API调整) ai_response = result['choices'][0]['message']['content'] # 尝试解析JSON响应 try: # 清理可能的markdown代码块标记 ai_response = ai_response.strip() if ai_response.startswith("```json"): ai_response = ai_response[7:] if ai_response.endswith("```"): ai_response = ai_response[:-3] analysis_result = json.loads(ai_response.strip()) return analysis_result except json.JSONDecodeError: print(f"警告:AI响应不是有效的JSON格式,尝试提取信息") # 如果不是JSON,返回默认结构 return { "content_sensitivity": 0, "legal_compliance": 0, "ethical_concerns": 0, "information_security": 0, "social_impact": 0, "total_risk_score": 0, "risk_level": "解析失败", "risk_factors": ["无法解析AI响应"], "recommendations": ai_response[:200] } elif response.status_code == 429: # 速率限制 if retry_count < RETRY_TIMES: wait_time = (retry_count + 1) * 5 print(f"遇到速率限制,等待 {wait_time} 秒后重试...") time.sleep(wait_time) return call_ai_api(question, retry_count + 1) else: print(f"错误:超过最大重试次数") return None else: print(f"错误:API请求失败,状态码: {response.status_code}") print(f"响应内容: {response.text}") return None except requests.exceptions.Timeout: print(f"错误:请求超时") if retry_count < RETRY_TIMES: return call_ai_api(question, retry_count + 1) return None except Exception as e: print(f"错误:调用API时出错 - {e}") return None def analyze_questions(questions): """批量分析题目""" results = [] total = len(questions) for i, question in enumerate(questions, 1): print(f"\n进度: {i}/{total}") # 调用AI API analysis = call_ai_api(question) if analysis: # 整理结果 result = { "题目": question, "内容敏感度": analysis.get("content_sensitivity", 0), "法律合规性": analysis.get("legal_compliance", 0), "道德伦理": analysis.get("ethical_concerns", 0), "信息安全": analysis.get("information_security", 0), "社会影响": analysis.get("social_impact", 0), "总风险分数": analysis.get("total_risk_score", 0), "风险等级": analysis.get("risk_level", "未知"), "风险因素": ", ".join(analysis.get("risk_factors", [])), "建议措施": analysis.get("recommendations", ""), "分析时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S") } else: # API调用失败时的默认值 result = { "题目": question, "内容敏感度": -1, "法律合规性": -1, "道德伦理": -1, "信息安全": -1, "社会影响": -1, "总风险分数": -1, "风险等级": "分析失败", "风险因素": "API调用失败", "建议措施": "", "分析时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S") } results.append(result) # 延迟以避免触发API速率限制 if i < total: time.sleep(DELAY_BETWEEN_REQUESTS) return results def export_results(results, original_df=None): """将结果导出到Excel文件""" try: # 创建结果DataFrame results_df = pd.DataFrame(results) # 如果有原始数据,可以合并 if original_df is not None and "题目" in original_df.columns: # 合并原始数据和分析结果 merged_df = pd.merge( original_df, results_df, left_on=QUESTION_COLUMN, right_on="题目", how="left" ) final_df = merged_df else: final_df = results_df # 创建Excel写入器,支持多个工作表 with pd.ExcelWriter(OUTPUT_EXCEL_FILE, engine='openpyxl') as writer: # 写入详细结果 final_df.to_excel(writer, sheet_name='详细分析结果', index=False) # 创建统计摘要 summary_data = { "统计项": ["总题目数", "高风险题目数", "中风险题目数", "低风险题目数", "平均风险分数"], "数值": [ len(results), len([r for r in results if r["风险等级"] == "高风险"]), len([r for r in results if r["风险等级"] == "中风险"]), len([r for r in results if r["风险等级"] == "低风险"]), sum([r["总风险分数"] for r in results if r["总风险分数"] >= 0]) / max(1, len([r for r in results if r["总风险分数"] >= 0])) ] } summary_df = pd.DataFrame(summary_data) summary_df.to_excel(writer, sheet_name='统计摘要', index=False) # 高风险题目单独列出 high_risk_df = final_df[final_df["风险等级"] == "高风险"] if not high_risk_df.empty: high_risk_df.to_excel(writer, sheet_name='高风险题目', index=False) print(f"\n结果已成功导出到: {OUTPUT_EXCEL_FILE}") # 打印统计信息 print("\n=== 分析统计 ===") for item, value in zip(summary_data["统计项"], summary_data["数值"]): print(f"{item}: {value:.2f}" if isinstance(value, float) else f"{item}: {value}") except Exception as e: print(f"错误:导出结果时出错 - {e}") def validate_config(): """验证配置是否正确""" if AI_API_URL == "YOUR_AI_API_ENDPOINT": print("错误:请设置 AI_API_URL 为实际的API端点") return False if AI_API_KEY == "YOUR_API_KEY": print("错误:请设置 AI_API_KEY 为你的API密钥") return False if not os.path.exists(INPUT_EXCEL_FILE): print(f"错误:找不到输入文件 '{INPUT_EXCEL_FILE}'") print("请确保文件存在并且路径正确") return False return True # --- 主程序 --- def main(): """主执行函数""" print("=== AI风险分析程序 ===") print(f"开始时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") # 验证配置 if not validate_config(): return # 读取Excel文件中的题目 result = read_excel_questions(INPUT_EXCEL_FILE, INPUT_SHEET_NAME, QUESTION_COLUMN) if result is None: return questions, original_df = result if not questions: print("错误:没有找到任何题目") return print(f"\n准备分析 {len(questions)} 个题目...") # 确认是否继续 user_input = input("\n是否继续?(y/n): ") if user_input.lower() != 'y': print("已取消操作") return # 分析题目 results = analyze_questions(questions) # 导出结果 export_results(results, original_df) print(f"\n完成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") print("程序执行完毕!") if __name__ == "__main__": main()使用说明:
- 安装必要的库:
pip install requests pandas openpyxl-
准备输入文件:
- 创建一个名为
questions.xlsx的Excel文件 - 在第一个工作表中创建一列,列名为"题目"
- 在该列中填入需要分析的题目
- 创建一个名为
-
配置API信息:
- 将
AI_API_URL替换为你使用的AI服务的API端点 - 将
AI_API_KEY替换为你的API密钥 - 根据你使用的AI服务调整请求格式
- 将
-
常见AI服务配置示例:
OpenAI GPT:
AI_API_URL = "https://api.openai.com/v1/chat/completions" AI_API_KEY = "sk-your-api-key" AI_MODEL = "gpt-3.5-turbo"Claude API:
AI_API_URL = "https://api.anthropic.com/v1/messages" AI_API_KEY
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.