import os
import openai
from dotenv import load_dotenv
load_dotenv
from pymilvus import MilvusClient
from milvus_model.dense import OpenAIEmbeddingFunction
from sqlalchemy import create_engine, text
# ====================== 基础配置 ======================
openai.api_key = os.getenv
embedding_fn = OpenAIEmbeddingFunction(
    model_name='text-embedding-3-large',
    api_key=openai.api_key
)
# 连接 Milvus 服务
client = MilvusClient
# 连接 MySql 数据库
DB_URL = "mysql+pymysql://root:root@localhost:3306/finance_enterprise_db"
engine = create_engine
# ====================== 创建集合 ======================
def create_collections:
    for name, dim in :
        if client.has_collection:
            client.drop_collection
        schema = client.create_schema
        schema.add_field
        schema.add_field
        schema.add_field
        index_params = client.prepare_index_params
        index_params.add_index
        client.create_collection
# ====================== 获取表结构和字段注释 ======================
def get_all_tables:
    with engine.connect as conn:
        tables = conn.execute).fetchall
        return  for t in tables]
def get_ddl:
    # 这里简化处理，实际应查询 INFORMATION_SCHEMA 获取完整的建表DDL
    return f"CREATE TABLE {table} "
def get_columns:
    # 这里简化为固定示例，实际应查询 INFORMATION_SCHEMA.COLUMNS 获取真实字段和注释
    return 
def get_examples:
    # 示例 SQL，用于给 LLM 参考的学习样本
    return 
# ====================== 数据入库 ======================
def build_all:
    create_collections
    tables = get_all_tables
    # 将 DDL 信息入库
    ddls = )} for t in tables]
    client.insert
    # 将字段描述入库
    descs = 
    for t in tables:
        cols = get_columns
        for col in cols:
            text = f"{t}.{col}：{col}"
            vector = embedding_fn
            descs.append
    client.insert
    # 将示例Q2SQL入库
    examples = 
    for q, sql in get_examples:
        text = f"问题：{q}
SQL：{sql}"
        vector = embedding_fn
        examples.append
    client.insert
# ====================== Text2SQL核心流程 ======================
def text2sql_finance:
    # 问题向量化
    q_emb = embedding_fn
    # 向量检索三大知识库
    ddl_hits = client.search(
        collection_name="ddl_knowledge",
        data=,
        limit=3,
        output_fields=
    )
   example_hits = client.search(
       collection_name="q2sql_knowledge",
       data=,
       limit=3,
       output_fields=
   )
   desc_hits = client.search(
       collection_name="dbdesc_knowledge",
       data=,
       limit=5,
       output_fields=
   )
   ddl_texts=for hit in ddl_hits]
   example_texts=for hit in example_hits]
   desc_texts=for hit in desc_hits]
   prompt=f"""你是财务专家，请根据下面提供的、、，
           生成可直接运行的MySql查询语句。
           ### 表结构信息：
           {chr.join}
           ### 字段含义说明：
           {chr.join}
           ### 参考示例：
           {chr.join}
           ### 用户问题：
           {question}
           ### 要求：
           . 只返回纯SQL，不要任何解释和
标记。
           . 必须使用提供的真实表和字段。
           . 日期、金额等严格按业务逻辑处理。
           . 不要编造字段。"""
response=openai.chat.completions.create(
model=“gpt-4o-mini”,
messages=,
temperature=0.7)
sql=response.choices.message.content.strip
print
try:
with engine.connectas conn:
result=conn.execute).fetchall
print
for row in result:
print
except Exception as e:
print)
if __name__==“__main__”:
build_all
question=“查2026年主营业务收入TOP前5的公司”
text2sql_finance