sql_demo/dm/111.py

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import dmPython
from typing import List, Dict

app = FastAPI()

class TableRequest(BaseModel):
    table: str

def get_column_comments(cursor, schema: str, table: str) -> Dict[str, str]:
    """
    获取指定表中所有字段的备注信息。
    """
    query = f"""
    SELECT aa.COLUMN_NAME, ss.comments AS 描述
    FROM user_tab_columns aa
    INNER JOIN user_col_comments ss 
        ON ss.COLUMN_NAME = aa.COLUMN_NAME 
        AND ss.Table_Name = aa.Table_Name
    WHERE aa.Table_Name = '{table}'
    ORDER BY aa.column_id
    """
    cursor.execute(query)
    return {row[0]: row[1] if row[1] else row[0] for row in cursor.fetchall()}

@app.post("/fetch_table_data")
async def fetch_table_data(request: TableRequest):
    result_list = []
    try:
        conn = dmPython.connect(user="SYSDBA", password="SYSDBA123", server="192.168.100.102", port=5236, schema='lqbz')
        cursor = conn.cursor()
        try:
            # 获取字段备注信息
            column_comments = get_column_comments(cursor, schema='lqbz', table=request.table)

            # 执行查询
            cursor.execute(f"select * from {request.table}")

            # 获取列名
            column_names = [description[0] for description in cursor.description]

            # 获取所有数据
            rows = cursor.fetchall()

            # 将每一行数据与列名组合成字典，并使用备注信息作为键
            result_dicts = [
                {column_comments.get(column_name, column_name): value for column_name, value in zip(column_names, row)} for
                row in rows]

            return {"data": result_dicts}

        except (dmPython.Error, Exception) as err:
            raise HTTPException(status_code=500, detail=f"发生错误: {err}")
        finally:
            cursor.close()
    except (dmPython.Error, Exception) as err:
        raise HTTPException(status_code=500, detail=f"发生错误: {err}")
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=7077)