# 数据库表API使用指南 本文档介绍数据库表查询相关的API接口,包括获取表列表和表结构分析功能。 ## API概览 | API端点 | 功能 | 必需参数 | 可选参数 | |---------|------|----------|----------| | `POST /api/v0/database/tables` | 获取数据库表列表 | 无 | `db_connection`, `schema` | | `POST /api/v0/database/table/ddl` | 获取表DDL和结构分析 | `table` | `db_connection`, `business_context`, `type` | ## 1. 获取数据库表列表 ### 接口信息 - **URL**: `POST /api/v0/database/tables` - **功能**: 获取指定数据库中的表列表 - **特点**: 纯数据库查询,不涉及AI功能 ### 请求参数 | 参数名 | 类型 | 必需 | 说明 | 示例 | |--------|------|------|------|------| | `db_connection` | string | 否 | PostgreSQL连接字符串
不传则使用默认配置 | `"postgresql://user:pass@host:port/db"` | | `schema` | string | 否 | Schema名称,支持逗号分隔多个
默认为"public" | `"public,ods,dw"` | ### 请求示例 #### 使用默认数据库配置 ```json { "schema": "public,ods" } ``` #### 使用指定数据库 ```json { "db_connection": "postgresql://postgres:postgres@192.168.67.1:5432/bank_db", "schema": "public" } ``` #### 最简调用(使用所有默认值) 此时的数据库连接配置来自于app_config.py ```json {} ``` ### 响应示例 ```json { "code": 200, "data": { "db_connection_info": { "database": "highway_db" }, "response": "获取表列表成功", "schemas": [ "public" ], "tables": [ "public.bss_branch_copy", "public.bss_business_day_data", "public.bss_car_day_count", "public.bss_company", "public.bss_section_route", "public.bss_section_route_area_link", "public.bss_service_area", "public.bss_service_area_mapper" ], "total": 8 }, "message": "操作成功", "success": true } ``` ## 2. 获取表DDL和结构分析 ### 接口信息 - **URL**: `POST /api/v0/database/table/ddl` - **功能**: 获取表的DDL语句或Markdown文档,支持AI智能注释生成 - **特点**: 结合数据库查询和AI分析能力 ### 请求参数 | 参数名 | 类型 | 必需 | 说明 | 示例 | |--------|------|------|------|------| | `table` | string | **是** | 表名,支持schema.table格式 | `"public.bank_churners"` | | `db_connection` | string | 否 | PostgreSQL连接字符串
不传则使用默认配置 | `"postgresql://user:pass@host:port/db"` | | `business_context` | string | 否 | 业务上下文描述
传入则启用AI注释生成 | `"银行信用卡持卡人信息"` | | `type` | string | 否 | 输出类型:`ddl`/`md`/`both`
默认为"ddl" | `"md"` | ### 请求示例 #### 基础DDL获取(使用默认配置) ```json { "table": "public.bank_churners" } ``` ```json { "db_connection": "postgresql://postgres:postgres@192.168.67.1:5432/bank_db", "table": "public.bank_churners", "business_context": "银行信用卡用户统计表" } ``` 注意:只有提供 "business_context" 参数且不为空时,才会在返回结果中生成字段注释。 ```json { "code": 200, "data": { "ddl": "-- 中文名: 银行信用卡客户流失分析表\n-- 描述: 银行信用卡客户流失分析表,记录用户人口统计特征及流失状态,用于预测客户流失风险并制定客户保留策略。\ncreate table public.bank_churners (\n client_num bigint not null -- 客户编号,主键,\n attrition_flag varchar(32) -- 客户流失标识,\n customer_age smallint -- 客户年龄,\n gender varchar(8) -- 性别,\n dependent_count smallint -- 家属数量,\n education_level varchar(32) -- 学历等级,\n marital_status varchar(16) -- 婚姻状况,\n income_category varchar(32) -- 收入等级,\n card_category varchar(16) -- 信用卡类别,\n months_on_book smallint -- 开户月份数,\n credit_limit numeric(12,2) -- 信用额度,\n total_revolving_bal numeric(12,2) -- 总循环余额,\n avg_open_to_buy numeric(12,2) -- 平均可用额度,\n total_amt_chng_q4_q1 double precision -- 季度交易金额变化率,\n total_trans_amt numeric(12,2) -- 总交易金额,\n total_trans_ct smallint -- 总交易次数,\n total_ct_chng_q4_q1 double precision -- 季度交易次数变化率,\n avg_utilization_ratio double precision -- 平均利用率,\n nb_classifier_attrition_flag_1 double precision -- 流失预测模型1得分,\n nb_classifier_attrition_flag_2 double precision -- 流失预测模型2得分,\n primary key (client_num)\n);", "fields": [ { "comment": "客户编号", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": true, "name": "client_num", "nullable": false, "type": "bigint" }, { "comment": "客户流失标识", "default_value": null, "enum_values": [ "Existing Customer", "Attrited Customer" ], "is_enum": true, "is_foreign_key": false, "is_primary_key": false, "name": "attrition_flag", "nullable": true, "type": "character varying" }, { "comment": "客户年龄", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "customer_age", "nullable": true, "type": "smallint" }, { "comment": "性别", "default_value": null, "enum_values": [ "F", "M" ], "is_enum": true, "is_foreign_key": false, "is_primary_key": false, "name": "gender", "nullable": true, "type": "character varying" }, { "comment": "家属数量", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "dependent_count", "nullable": true, "type": "smallint" }, { "comment": "学历等级", "default_value": null, "enum_values": [ "Graduate", "High School", "Unknown", "Uneducated", "College", "Post-Graduate", "Doctorate" ], "is_enum": true, "is_foreign_key": false, "is_primary_key": false, "name": "education_level", "nullable": true, "type": "character varying" }, { "comment": "婚姻状况", "default_value": null, "enum_values": [ "Married", "Single", "Unknown", "Divorced" ], "is_enum": true, "is_foreign_key": false, "is_primary_key": false, "name": "marital_status", "nullable": true, "type": "character varying" }, { "comment": "收入等级", "default_value": null, "enum_values": [ "Less than $40K", "$40K - $60K", "$80K - $120K", "$60K - $80K", "Unknown", "$120K +" ], "is_enum": true, "is_foreign_key": false, "is_primary_key": false, "name": "income_category", "nullable": true, "type": "character varying" }, { "comment": "信用卡类别", "default_value": null, "enum_values": [ "Blue", "Silver", "Gold", "Platinum" ], "is_enum": true, "is_foreign_key": false, "is_primary_key": false, "name": "card_category", "nullable": true, "type": "character varying" }, { "comment": "开户月份数", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "months_on_book", "nullable": true, "type": "smallint" }, { "comment": "信用额度", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "credit_limit", "nullable": true, "type": "numeric" }, { "comment": "总循环余额", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "total_revolving_bal", "nullable": true, "type": "numeric" }, { "comment": "平均可用额度", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "avg_open_to_buy", "nullable": true, "type": "numeric" }, { "comment": "季度交易金额变化率", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "total_amt_chng_q4_q1", "nullable": true, "type": "double precision" }, { "comment": "总交易金额", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "total_trans_amt", "nullable": true, "type": "numeric" }, { "comment": "总交易次数", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "total_trans_ct", "nullable": true, "type": "smallint" }, { "comment": "季度交易次数变化率", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "total_ct_chng_q4_q1", "nullable": true, "type": "double precision" }, { "comment": "平均利用率", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "avg_utilization_ratio", "nullable": true, "type": "double precision" }, { "comment": "流失预测模型1得分", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "nb_classifier_attrition_flag_1", "nullable": true, "type": "double precision" }, { "comment": "流失预测模型2得分", "default_value": null, "enum_values": null, "is_enum": false, "is_foreign_key": false, "is_primary_key": false, "name": "nb_classifier_attrition_flag_2", "nullable": true, "type": "double precision" } ], "generation_info": { "business_context": "银行信用卡用户统计表", "database": "bank_db", "has_llm_comments": true, "output_type": "ddl" }, "response": "获取表DDL成功", "table_info": { "comment": "银行信用卡客户流失分析表,记录用户人口统计特征及流失状态,用于预测客户流失风险并制定客户保留策略。", "field_count": 20, "full_name": "public.bank_churners", "row_count": 10127, "schema_name": "public", "table_name": "bank_churners", "table_size": "2008 kB" } }, "message": "操作成功", "success": true } ``` #### 生成智能注释的Markdown文档 ```json { "table": "public.bank_churners", "business_context": "银行信用卡持卡人信息表,用于分析客户流失情况", "type": "md" } ``` #### 指定数据库获取DDL和MD ```json { "db_connection": "postgresql://postgres:postgres@192.168.67.1:5432/bank_db", "table": "public.bank_churners", "business_context": "银行信用卡持卡人信息", "type": "both" } ``` ### 响应示例 #### DDL模式响应 ```json { "success": true, "code": 200, "message": "获取表DDL成功", "data": { "ddl": "-- 中文名: 银行信用卡持卡人信息表\ncreate table public.bank_churners (\n client_num bigint not null,\n attrition_flag varchar(32),\n ...\n);", "table_info": { "table_name": "bank_churners", "schema_name": "public", "full_name": "public.bank_churners", "comment": "银行信用卡持卡人信息表,记录客户流失状态、人口统计特征及账户活跃度数据", "field_count": 20, "row_count": 10127, "table_size": "2008 kB" }, "fields": [ { "name": "client_num", "type": "bigint", "nullable": false, "comment": "客户编号", "is_primary_key": true, "is_foreign_key": false, "default_value": null, "is_enum": false, "enum_values": null }, { "name": "attrition_flag", "type": "character varying", "nullable": true, "comment": "客户流失标志", "is_primary_key": false, "is_foreign_key": false, "default_value": null, "is_enum": true, "enum_values": ["Existing Customer", "Attrited Customer"] } ], "generation_info": { "business_context": "银行信用卡持卡人信息", "output_type": "ddl", "has_llm_comments": true, "database": "bank_db" } } } ``` ## 功能特性说明 ### AI智能注释功能 当传入 `business_context` 参数时,系统会: 1. **生成中文注释**: 基于业务上下文为表和字段生成准确的中文注释 2. **识别枚举字段**: 自动检测可能的枚举类型字段(如状态、类型、级别等) 3. **验证枚举值**: 查询数据库获取字段的实际枚举值 4. **优化字段描述**: 结合字段名、数据类型和样例数据生成更准确的描述 ### 输出类型说明 | 类型 | 说明 | 适用场景 | |------|------|----------| | `ddl` | 返回CREATE TABLE语句 | 数据库迁移、表结构复制 | | `md` | 返回Markdown格式文档 | 文档生成、团队共享 | | `both` | 同时返回DDL和MD | 完整的表分析需求 | ### 默认数据库配置 当不传入 `db_connection` 参数时,系统会自动使用 `app_config.py` 中的 `APP_DB_CONFIG` 配置: - 便于内部系统调用 - 减少重复的连接参数传递 - 保持与其他服务的数据库一致性 ## 错误处理 ### 常见错误码 | 错误码 | 说明 | 解决方案 | |--------|------|----------| | 400 | 缺少必需参数 | 检查请求参数,确保传入必需的参数 | | 500 | 数据库连接失败 | 检查数据库连接字符串和网络连接 | | 500 | 表不存在 | 确认表名正确,检查schema和表是否存在 | ### 错误响应示例 ```json { "success": false, "code": 400, "message": "请求参数错误", "data": { "response": "缺少必需参数:table", "missing_params": ["table"], "timestamp": "2025-07-02T10:30:00" } } ``` ## 使用注意事项 ### 性能考虑 1. **表列表查询**: 速度较快,适合频繁调用 2. **DDL分析**: 涉及AI处理,响应时间较长(5-30秒) 3. **大表处理**: 系统会自动进行智能采样,避免性能问题 ### 安全考虑 1. **数据库连接**: 使用独立连接,不影响其他服务 2. **权限要求**: 需要数据库的SELECT权限 3. **并发安全**: 支持多用户同时调用,无资源冲突 ### 最佳实践 1. **内部调用**: 推荐不传 `db_connection`,使用默认配置 2. **外部调用**: 明确传入 `db_connection` 参数 3. **文档生成**: 传入详细的 `business_context` 以获得更好的AI注释 4. **批量处理**: 先调用表列表API获取所有表,再逐个分析 ## 示例代码 ### Python调用示例 ```python import requests # 获取表列表 def get_tables(schema="public"): url = "http://localhost:8084/api/v0/database/tables" data = {"schema": schema} response = requests.post(url, json=data) return response.json() # 获取表DDL def get_table_ddl(table, business_context=None, output_type="ddl"): url = "http://localhost:8084/api/v0/database/table/ddl" data = { "table": table, "type": output_type } if business_context: data["business_context"] = business_context response = requests.post(url, json=data) return response.json() # 使用示例 tables = get_tables("public") ddl = get_table_ddl("public.bank_churners", "银行客户信息", "md") ``` ### JavaScript调用示例 ```javascript // 获取表列表 async function getTables(schema = 'public') { const response = await fetch('/api/v0/database/tables', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ schema }) }); return await response.json(); } // 获取表DDL async function getTableDDL(table, businessContext = null, type = 'ddl') { const data = { table, type }; if (businessContext) { data.business_context = businessContext; } const response = await fetch('/api/v0/database/table/ddl', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(data) }); return await response.json(); } ``` ## 更新日志 - **v1.0** (2025-07-02): 初始版本,支持基础的表查询和DDL生成 - **v1.1** (2025-07-02): 新增AI智能注释功能,支持枚举字段识别 - **v1.2** (2025-07-02): `db_connection` 参数改为可选,支持使用默认配置 --- 如有问题或建议,请联系开发团队。