A natural language to SQL closed-loop generation method and system for a power business system

Abstract

This invention discloses a closed-loop generation method and system for natural language to SQL in power business systems. The method involves inputting natural language query data and context data into a large SQL model to generate candidate SQL queries. The candidate SQL queries undergo security governance processing to obtain controllable SQL. The controllable SQL is then subjected to case backfilling and structural alignment to obtain aligned SQL. The aligned SQL is then executed on the database to obtain execution data. If the execution data is the execution result, it is encapsulated with the aligned SQL to obtain a structured success response and end the current query process. If the SQL execution fails, a repair process is initiated, using a large SQL repair model to attempt repairs multiple times based on the error type, forming an automatic closed loop and improving the execution success rate. This invention offers systematic engineering advantages in executability, self-healing capabilities, security compliance, and domain adaptability.

Description

Technical Field

[0001] This invention relates to a closed-loop generation method and system for natural language to SQL for power business systems, belonging to the fields of power information technology, database design automation and artificial intelligence application technology. Background Technology

[0002] With the deepening development of informatization and digitalization, data has become a crucial production factor for enterprises and government organizations. Modern enterprise information systems typically rely on relational databases (RDBMS) to store and manage business data. Databases organize data in a structured manner, offering high consistency, high reliability, and scalability, and are therefore widely used in fields such as financial management, supply chain management, operational analysis, and intelligent manufacturing.

[0003] In these application scenarios, data querying and analysis are among the most fundamental requirements. Traditional data access methods rely on Structured Query Language (SQL), which has complex syntax, rigorous logical expression, and involves concepts such as table joins, aggregation operations, and window functions. This makes it difficult for ordinary business personnel to directly manipulate the database, often requiring data analysts or engineers to write query statements, resulting in high communication costs, long response times, and fragmented analysis processes. Especially today, with data-driven decision-making becoming increasingly prevalent, non-technical personnel hope to ask questions directly using natural language and obtain results instantly. This demand has driven the research and application of Natural Language Interface to Database (NLIDB).

[0004] On the other hand, the scale and complexity of enterprise databases continue to increase. A medium to large-scale information system often contains hundreds to thousands of tables with inconsistent field naming styles, including issues such as case sensitivity, mixed abbreviations, and diverse business logic. Simultaneously, data security and compliance requirements are becoming increasingly stringent, with databases typically prohibiting write operations or restricting cross-table access. Business environments also demand auditability, controllable execution, and defense against accidental operations. Traditional methods of manually writing SQL are not only inefficient but also prone to syntax errors, performance bottlenecks, and even security risks.

[0005] In the field of artificial intelligence, the development of Natural Language Processing (NLP) and Large Language Models (LLM) has provided a new technological foundation for automated database access. By combining natural language with structured data queries, the system can understand user intent and automatically generate SQL statements, achieving intelligent interaction of "querying instead of querying." However, in practical engineering implementation, the general generation capabilities of large models also bring new challenges: the model may output statements that do not conform to database constraints; it lacks precise awareness of table structure and field names; it is prone to execution failure in case-sensitive environments; it lacks a self-feedback mechanism after execution; and some outputs may contain non-read-only operations, posing potential security risks.

[0006] Furthermore, as enterprises improve their data governance, data query systems must not only generate correct SQL, but also ensure that the generated statements are executable, secure, controllable, and consistent with business logic, and possess self-repair and traceability capabilities when errors occur. This requires NL2SQL systems to upgrade from simple "language generation models" to engineered systems with closed-loop self-healing, rule constraints, and observability capabilities.

[0007] To lower the barrier to entry for SQL, the industry has proposed Natural Language to SQL (NL2SQL) technology, which uses Natural Language Processing (NLP) and machine learning methods to automatically convert user-inputted natural language questions into SQL queries. NL2SQL technology mainly includes three paths:

[0008] (1) Template and rule-based approach: Matching natural language questions to predefined SQL templates. Its advantages are strong interpretability and stable generated results. However, it also has disadvantages such as poor flexibility and difficulty in covering complex queries.

[0009] (2) End-to-end models based on deep learning: Typical examples of this method include Seq2SQL, SQLNet, and RAT-SQL. Its advantage is that it can automatically learn the semantic mapping to SQL and has strong adaptability. However, it requires a large amount of labeled training data and has insufficient generalization ability to different database schemas.

[0010] (3) Natural language query method based on large language model (LLM) to directly generate SQL: Using models such as ChatGPT, ERNIE, and Qwen, SQL is directly generated based on prompt words. This method does not require additional training and has strong versatility.

[0011] The closest technical solution to this invention is a natural language query method that directly generates SQL based on a Large Language Model (LLM). This type of solution uses ChatGPT, ERNIE, Qwen, or similar models as its core, and uses prompts to allow the model to convert the user's natural language question into a structured query language (SQL) statement, thereby achieving database question answering.

[0012] However, such methods typically lack strict security constraints before execution and also lack SQL cleansing steps. The model may output explanatory text (such as "The following is the query statement:") or contain non-read-only statements (such as UPDATE and DELETE), potentially causing the database to refuse execution or posing a security risk. Furthermore, due to frequent issues such as case sensitivity, inconsistent table fields, and JOIN logic errors, this method has a low success rate in real-world enterprise database environments.

[0013] While this solution performs well in generating general semantics, its process is one-way, lacking error feedback and automatic repair capabilities, and it does not perform read-only constraints or case normalization on the SQL. Consequently, the SQL's executability, security, and business adaptability are poor, failing to meet the stability, reliability, and self-healing requirements of enterprise-level environments.

[0014] This is the closest technical approach to the present invention, but it still has problems such as syntax errors and table field errors. Therefore, there is an urgent need for a new solution that can realize the closed-loop intelligent agent conversion from natural language to SQL, and ensure the correctness, executability and business adaptability of SQL output. Summary of the Invention

[0015] Objective: To overcome the shortcomings of existing technologies, this invention provides a closed-loop generation method and system for natural language to SQL for power business systems. Through steps such as automatic cleaning, case correction, execution feedback and minimization repair, it significantly improves the SQL execution success rate and system self-healing capability, reduces manual intervention and debugging costs, and meets the comprehensive requirements of enterprise-level database environments for security, stability, observability and business adaptability.

[0016] Technical solution: To solve the above technical problems, the technical solution adopted by the present invention is as follows:

[0017] Firstly, a closed-loop generation method for natural language to SQL for power business systems, specifically including:

[0018] Step S1: Obtain the natural language query request submitted by the user, and perform access and normalization processing on the natural language query request to obtain natural language query data.

[0019] Step S2: Perform semantic parsing and knowledge retrieval on the natural language query data to obtain contextual data.

[0020] Step S3: Generate a large model based on natural language query data, context data, and SQL, and generate candidate SQL.

[0021] Step S4: Perform security governance processing on the candidate SQL to obtain controllable SQL.

[0022] Step S5: Perform case backfilling and structure alignment on the controllable SQL to obtain the aligned SQL.

[0023] Step S6: Execute the aligned SQL in the database to obtain the execution data.

[0024] Step S7: If the execution data is the execution result data, encapsulate the execution result data with the aligned SQL to obtain a structured success response and end the current query process.

[0025] Optionally, it also includes: Step S8: If the executed data is database error data, repair the aligned SQL corresponding to the database error data to obtain the repaired SQL.

[0026] Step S9: Perform closed-loop retry and iterative termination control on the repair SQL to obtain a new SQL.

[0027] Step S10: If the new SQL fails to execute, output a fallback solution for the failure.

[0028] In a second aspect, a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements a closed-loop generation method for natural language to SQL for power business systems as described in any of the first aspects.

[0029] Thirdly, a computer device comprising:

[0030] Memory is used to store instructions.

[0031] A processor is configured to execute the instructions, causing the computer device to perform operations of a closed-loop generation method for natural language to SQL for power business systems as described in any of the first aspects.

[0032] Beneficial Effects: This invention provides a closed-loop generation method and system for natural language to SQL in power business systems. The invention employs a closed-loop mechanism of "generation—cleaning—execution—error classification—minimum repair—retry," transforming problems such as syntax instability, table column misalignment, and manual intervention that are prone to occur in general large models into "constrained generation + iterative correction based on feedback from real databases." By narrowing the search space at the front end with read-only validation, explicit column names, and default rate limiting, and by using schema_dict (architectural dictionary) to backfill table and field names with standard capitalization in the middle stage to ensure executability, and by using database error-driven "minimum repair" at the back end to modify only the erroneous fragments and immediately perform a second cleanup and retry, the invention logically improves the first-round execution rate and overall closed-loop success rate, reduces manual modification and debugging rounds, shortens response time, and lowers computational and operational costs. Meanwhile, this invention introduces power business logic constraints during the generation and repair phases, ensuring that the resulting SQL not only runs successfully but also better aligns with power business semantics. The mandatory read-only and rate-limiting strategies mitigate data and performance risks associated with write operations and large scans from the outset. Maximum retries and fallback outputs ensure controllable behavior and interpretable results in unrepairable scenarios. Therefore, addressing the technical problems of existing technologies—high error rate, high execution failure rate, slow response speed, high security risks, and weak adaptability to power business—this invention offers systematic engineering advantages in executability, self-healing capability, security compliance, and domain adaptability. Compared to existing technologies, the advantages of this invention are as follows:

[0033] 1. Closed-loop self-healing mechanism: A closed-loop NL2SQL method of "knowledge generation - cleaning - execution - error classification - minimal repair - retry" is proposed. Unlike the existing one-time generation, it can automatically trigger repair and retry in a loop when SQL execution fails until it succeeds or reaches the maximum number of times, thereby significantly improving the execution success rate.

[0034] 2. Use Retrieval Knowledge Enhancement (RAG) technology combined with the knowledge graph of power business table structure and the generation constraints of power business logic description: Introduce power business logic rule knowledge (such as time caliber, common query paths, primary and foreign key constraints) into the SQL generation and repair process, so that the generated SQL is not only syntactically correct, but also conforms to the semantics of power business, enhancing the domain adaptability and practicality of the solution.

[0035] 3. Error classification-driven minimal repair strategy: Classify the error information returned by the database into categories such as "table does not exist, column does not exist, case error, syntax error, timeout / data too large", and generate targeted repair prompts. The large language model is required to make minimal modifications only to the error fragments and avoid unnecessary rewriting of the correct parts.

[0036] 4. Case-sensitive backfilling method based on schema truth values: A schema_dict is created to force the table names and field names output by the model to be aligned to the actual case of the database. During the backfilling process, table names must be forcibly matched, column names are automatically corrected when their ownership can be uniquely determined, and aliases and quotation mark styles remain unchanged. This method solves a common execution failure problem in case-sensitive databases.

[0037] 5. SQL Cleaning and Security Governance Pipeline: Add a cleaner before SQL execution to remove interpreted text and redundant symbols, perform read-only verification, and automatically append LIMIT or pagination to prevent write operations or large-scale data scans, thereby ensuring database security and execution controllability. Attached Figure Description

[0038] Figure 1 This is a flowchart of a closed-loop conversion system for natural language to SQL based on a large model. Detailed Implementation

[0039] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the protection scope of the present invention.

[0040] The present invention will be further described below with reference to specific embodiments.

[0041] Example 1:

[0042] This embodiment provides a closed-loop generation method for natural language to SQL in power business systems. It converts user-input natural language query requests into read-only SQL that can be executed on a target database, and automatically iterates and repairs based on database error information until success or a preset limit is reached when execution fails. The method includes the following steps.

[0043] Step S1: Obtain the natural language query request submitted by the user, and perform access and normalization processing on the natural language query request to obtain natural language query data.

[0044] Furthermore, step 1 specifically includes:

[0045] The system obtains the user-submitted natural language query request as raw input data (raw_query), which includes the query text; it performs authentication, rate limiting, and basic security checks on the raw input data, and performs normalization processing on the query text (including removing irrelevant symbols, uniform encoding, whitespace standardization, etc.) to obtain normalized natural language query data (query); and outputs the query for subsequent steps.

[0046] Step S2: Perform semantic parsing and knowledge retrieval on the natural language query data to obtain contextual data.

[0047] Furthermore, step 2 specifically includes:

[0048] Using the natural language query data as input, the query is semantically parsed to obtain retrieval features (such as keywords, entities, time ranges, indicator definitions, etc.); based on the retrieval features, a retrieval is performed in a pre-built knowledge base to obtain retrieval results; the retrieval results are aggregated and pruned to form context data that can be input into a large model. The context data includes: knowledge fragments of power business table structure (knowledge_t) and knowledge fragments of power business logic rules (knowledge_p).

[0049] Among them, knowledge_t is used to represent the table name, field name, primary and foreign key relationship, field value examples, and case-sensitive truth value structure information of the target database.

[0050] The `knowledge_p` attribute is used to characterize power business definitions, time window rules, typical query paths, and constraint rules.

[0051] Step S3: Generate a large model based on natural language query data, context data, and SQL, and generate candidate SQL.

[0052] Furthermore, step 3 specifically includes:

[0053] The system constructs SQL prompt information based on the user's natural language request query, table structure knowledge fragment knowledge_t, and power business logic rule knowledge fragment knowledge_p, according to a preset prompt word template. The generated prompt information includes the system prompt word role_prompt_g and the user prompt word user_prompt_g.

[0054] The system prompt term `role_prompt_g` is used to specify the output boundaries and generation rules of the large model, including: only allowing the generation of query statements, only allowing the use of table names and field names that exist in the knowledge base, outputting table names and field names according to the standard capitalization of the knowledge base, prohibiting the output of explanatory text, and prohibiting the output of multiple statements.

[0055] The user prompt term `user_prompt_g` carries the specific input content for this task, including the user request query, the table structure knowledge fragment `knowledge_t`, and the power business logic rule knowledge fragment `knowledge_p`.

[0056] The system inputs the system prompt word `role_prompt_g` and the user prompt word `user_prompt_g` into SQL to generate a large model, resulting in the candidate SQL statement `sql0`. By incorporating constraint rules into the system prompt word and task data into the user prompt word, the large model generates candidate SQL statements that match the target database table structure and business logic under constrained conditions.

[0057] Step S4: Perform security governance processing on the candidate SQL to obtain controllable SQL.

[0058] Furthermore, step 4 specifically includes:

[0059] The candidate SQL is cleaned and security managed to obtain controllable SQL (sql_clean).

[0060] The cleaning and security management process includes: extracting the SQL body from candidate SQL, removing code block markers or explanatory text; performing read-only verification to intercept write / management keywords or multi-statement concatenation; supplementing or verifying result restrictions according to preset strategies to avoid excessively large returned data; and performing length restrictions, symbol balancing, and format standardization when necessary.

[0061] Step S5: Perform case backfilling and structure alignment on the controllable SQL to obtain the aligned SQL.

[0062] Furthermore, step 5 specifically includes:

[0063] Using sql_clean and knowledge_t as input, a structure dictionary (schema_dict) is obtained by parsing knowledge_t. The schema_dict is used to record the standard uppercase and lowercase forms of table names and field names in the target database. Based on the schema_dict, case backfilling and alignment processing are performed on the table names and field names involved in sql_clean to obtain the aligned SQL (sql1).

[0064] Among them, the case-sensitivity of table names is filled back, and the real table names are identified based on the FROM / JOIN clause.

[0065] Field name case backfilling: Identifying the real field name based on the mapping relationship between table aliases and the real table, and the field affiliation relationship.

[0066] Step S6: Execute the aligned SQL in the database to obtain the execution data.

[0067] Furthermore, step 6 specifically includes:

[0068] Using the SQL1 as input, execute the SQL1 through the database adapter under a read-only connection; if the execution is successful, obtain the execution result data (db_result); if the execution fails, obtain the database error data (db_error).

[0069] The db_error includes: error code and error message text.

[0070] Step S7: If the execution data is the execution result data, encapsulate the execution result data with the aligned SQL to obtain a structured success response and end the current query process.

[0071] Furthermore, step 7 specifically includes:

[0072] When step S6 outputs db_result, the sql1 and db_result are encapsulated into a structured success response (response_success).

[0073] The response_success includes: the final executed SQL and result data (including but not limited to field list, data row, and execution information).

[0074] Step S8: If the executed data is database error data, repair the aligned SQL corresponding to the database error data to obtain the repaired SQL.

[0075] Furthermore, step 8 specifically includes:

[0076] When the SQL statement sql1 fails to execute, the system parses the error message db_error returned by the database and identifies the error category. The error category includes one or more of the following: table does not exist, field does not exist, case mismatch, syntax error, result set is too large, or execution timeout.

[0077] The system uses the user's original request query, table structure knowledge fragment knowledge_t, power business logic rule knowledge fragment knowledge_p, the last failed SQL statement last_sql, and error information db_error to select an appropriate repair strategy error_fix_p based on db_error. It then constructs SQL repair prompt information according to a preset repair prompt word template, wherein the repair prompt information includes the system prompt word role_prompt_f and the user prompt word user_prompt_f.

[0078] The system prompt word `role_prompt_f` limits the repair behavior to minimal modifications to the error-related fragments, and maintains read-only queries, explicit column name output, and consistent capitalization of table field names with the knowledge base standard. The user prompt word `user_prompt_f` carries the input content for this repair task, including the user request `query`, the table structure knowledge fragment `knowledge_t`, the power business logic rule knowledge fragment `knowledge_p`, the failed SQL `last_sql`, the error message `db_error`, and the repair strategy `error_fix_p`.

[0079] The system inputs the system prompt word role_prompt_f and the user prompt word user_prompt_f into the SQL repair model to obtain the repaired SQL statement sql_fix. The SQL repair model follows the principle of minimal modification, only correcting table names, field names, function expressions, keyword order, or syntax fragments related to the error category, while keeping the rest of the query logic unchanged.

[0080] Step S9: Perform closed-loop retry and iterative termination control on the repair SQL to obtain a new SQL.

[0081] Furthermore, step 9 specifically includes:

[0082] Using the sql_fix as input, repeat steps S4 and S5 to obtain the repaired executable SQL (sql2), and execute step S6 on sql2; if the execution is successful, proceed to step S7 and output a success response; if the execution fails, update last_sql and db_error, and determine whether the current iteration count has reached the preset maximum iteration count (max_iteration); if it has not reached, return to step S8 to continue generating a new sql_fix and enter the next closed loop; if it has reached, proceed to step S10.

[0083] Step S10: If the new SQL fails to execute, output a fallback solution for repair failure.

[0084] Furthermore, step 10 specifically includes:

[0085] When the number of iterations reaches max_iteration and no successful execution result is obtained, a fallback response (response_o) is output. The response_o includes the SQL of the last attempt, the corresponding error message summary, and prompts to guide the user to supplement information or narrow down the query scope; the query process ends.

[0086] The above steps form a closed-loop processing flow from "natural language input - SQL generation - cleaning and alignment - execution feedback - error repair - re-execution", which improves the executability and robustness of natural language to SQL conversion while ensuring read-only security and controllable results.

[0087] Example 2:

[0088] This embodiment describes a computer-readable storage medium storing a computer program that, when executed by a processor, implements a closed-loop generation method for natural language to SQL for power business systems, as described in any of Embodiment 1.

[0089] Example 3:

[0090] This embodiment describes a computer device, including:

[0091] Memory is used to store instructions.

[0092] A processor is configured to execute the instructions, causing the computer device to perform the operation of a closed-loop generation method for natural language to SQL for power business systems as described in any of Embodiment 1.

[0093] Example 4:

[0094] This embodiment uses a relational database related to the power industry as an example to illustrate how the method of the present invention, when generating SQL from natural language in a power business scenario and encountering an execution failure due to a "field not found" error, triggers a closed-loop repair based on the database error information and ultimately achieves successful execution. The closed-loop process conforms to the processing logic of "execution failure → error classification → constructing repair prompts → minimizing repair → cleaning and backfilling again → retrying execution".

[0095] Step 1: Enter the query request:

[0096] The system receives and standardizes the user request raw_query = "I need to query the prepayment ratio (prepayment percentage) of the contract for the Xishanghe 580kV substation upgrade project", and obtains the natural language query data query:

[0097] query = "Query the prepayment ratio (prepayment percentage) of the contract for the Xishanghe 580kV substation upgrade project".

[0098] Step 2: Construct the knowledge context (knowledge_t, knowledge_p):

[0099] 1) Table structure knowledge fragments (knowledge_t), including:

[0100] The system retrieved tables, fields, and relationships related to this query from the "Table Structure Knowledge Graph Database," including: the Purchase Request Line Subcontracting Information table DWD_MAT_PUR_REQ_ITEM_BIDPKG, which contains the project name field SINGLE_ENG_NAME and fields such as HQ_REQ_UNIQUE_CODE used for cross-table relationships; and the Contract Details table DWD_MAT_CON_ITEM, which contains the contract name CON_NAME, purchase order number PUR_ODR_CODE, and purchase order line item number PUR_ODR_ITEM_CODE, and has a corresponding relationship with EBELN / EBELP of UN01_03_ERP_CD_EKPO.

[0101] 2) Knowledge fragments of power business logic rules (knowledge_p), including:

[0102] The system retrieves rules related to the "Project → Contract" locating path from the "Power Business Logic Rules Knowledge Base" (the "Project Material Procurement Contract Query" logic given in this document is: first, locate the procurement application by project name in the subcontracting information table of the procurement application line; then, obtain the procurement order in the procurement voucher item table; finally, search for the contract name by procurement order number in the contract details table). This logic is used to guide "how to locate the contract from the project name".

[0103] Step 3: Initial candidate SQL generation (sql0):

[0104] The system assembles query, knowledge_t, and knowledge_p into a prompt word and calls the SQL to generate a large model, resulting in candidate SQL (sql0).

[0105] In this example, the SQL generator mistakenly wrote the prepayment percentage field as DP_PCT. The generated executable SQL command is as follows: "SELECT

[0106] c.CON_NAME,

[0107] e.DP_PCT AS PREPAY_PCT

[0108] FROM DWD_MAT_PUR_REQ_ITEM_BIDPKG b

[0109] JOIN DWD_MAT_CON_ITEM c

[0110] ON b.HQ_REQ_UNIQUE_CODE = c.HQ_REQ_UNIQUE_CODE

[0111] JOIN UN01_03_ERP_CD_EKPO e

[0112] ON c.PUR_ODR_CODE = e.EBELN

[0113] AND c.PUR_ODR_ITEM_CODE = e.EBELP

[0114] WHERE b.SINGLE_ENG_NAME LIKE '%Upgrading of 580kv Substation in Xishanghe%'”

[0115] Among them, the basis for table joining is the knowledge graph: DWD_MAT_CON_ITEM contains PUR_ODR_CODE / PUR_ODR_ITEM_CODE, and there is a corresponding relationship with EBELN / EBELP of UN01_03_ERP_CD_EKPO.

[0116] Step 4: SQL Cleaning and Case Filling (sql_clean → sql1):

[0117] The system performs cleaning and read-only verification on sql0 (only retaining SELECT, prohibiting write operations, etc.), and appends LIMIT protection according to the strategy; subsequently, based on the schema_dict parsed from knowledge_t, standard case filling is performed on table names / field names to obtain the executable SQL sql1 (the example here only shows the form after appending LIMIT):

[0118] SELECT c.CON_NAME, e.DP_PCT AS PREPAY_PCTFROM DWD_MAT_PUR_REQ_ITEM_BIDPKG bJOIN DWD_MAT_CON_ITEM c ON b.HQ_REQ_UNIQUE_CODE = c.HQ_REQ_UNIQUE_CODEJOIN UN01_03_ERP_CD_EKPO e ON c.PUR_ODR_CODE = e.EBELN AND c.PUR_ODR_ITEM_CODE = e.EBELPWHERE b.SINGLE_ENG_NAME LIKE '%Xishanghe 580kv power substation upgrade%'LIMIT20

[0119] Among them, DWD_MAT_PUR_REQ_ITEM_BIDPKG contains the project name field SINGLE_ENG_NAME, which is used for filtering by project dimension.

[0120] Step 5: Initial database execution failed (db_error):

[0121] The system submitted SQL1 to the database for execution. Because the field DP_PCT does not exist, the database returned the error message db_error.

[0122] { "code": 1054, "msg": "db error: Unknown column 'e.DP_PCT' in 'fieldlist'", "rows": []} Step 6: Error Classification and Minimum Fix (sql_fix):

[0123] The system parses db_error.msg and categorizes it as an error type of "column not found / field not found". Then, it constructs a repair prompt user_prompt_f and inputs it along with role_prompt_f into the SQL to repair the large model. The requirement is to make minimal changes to the original SQL: only replace the non-existent field, without changing the JOIN and filter conditions.

[0124] The SQL repair model retrieved DPPCT (Percentage of Advance Payment) from schema_dict[UN01_03_ERP_CD_EKPO], finding it to be the semantically closest and actually existing field. Based on this, the SQL repair function sql_fix (simply replacing DP_PCT with DPPCT) was output:

[0125] SELECT c.CON_NAME, e.DPPCT AS PREPAY_PCT FROM DWD_MAT_PUR_REQ_ITEM_BIDPKG b JOIN DWD_MAT_CON_ITEM c ON b.HQ_REQ_UNIQUE_CODE = c.HQ_REQ_UNIQUE_CODE JOIN UN01_03_ERP_CD_EKPO e ON c.PUR_ODR_CODE = e.EBELN AND c.PUR_ODR_ITEM_CODE = e.EBELP WHERE b.SINGLE_ENG_NAME LIKE '%580kv Substation Upgrade of Xishang River%' LIMIT 20 Meanwhile, the association relationship between the contract detail table and the procurement application line subcontracting information table through HQ_REQ_UNIQUE_CODE remains unchanged.

[0126] Step 7: Secondary cleaning, backfilling, and retry execution successful (sql2 → db_result):

[0127] The system re-enters the sql_fix into the "cleaning - case-sensitive backfilling - execution" link, obtains sql2 (identical to sql_fix or only with formatting differences), and submits it to the database for execution, returning the successful result db_result:

[0128] { "code": 0, "msg": "ok", "rows": [ { "CON_NAME": "Contract for 580kv Substation Upgrade Project of Xishang River", "PREPAY_PCT": "80"} ]} As can be seen from the above embodiment process: when the system encounters an execution failure due to "column does not exist" during the first-round SQL generation, it can utilize the database error information and the set of true value fields of the table structure to minimize the repair of the error fields and keep the original JOIN link and filtering conditions unchanged, thus completing the closed-loop self-healing of "generation - cleaning - execution - repair - retry - success".

[0129] This system architecture consists of six core modules, forming a closed loop in series according to the data flow:

[0130] (1) Input parsing module: Receives the user's natural language question, extracts keywords and time entities, and interfaces with the knowledge base to select relevant table domains.

[0131] (2) Knowledge Enhancement Module (RAG): Loads the "Power Business Table Structure Knowledge Graph Library (Table Name / Field / Primary / Foreign Key / Maximum / Minimum Values)" and the "Power Business Logic Rule Knowledge Base (e.g., Annual Scope, Time Window Definition, Typical Query Path)", recalls relevant fragments by Top-k, and assembles them into a usable context for the model. The Knowledge Enhancement Module is used to recall table structure fragments and business rule fragments related to user questions from the Power Business Knowledge Base. The table structure fragments include tables, fields, and association keys corresponding to business objects such as engineering, projects, procurement applications, procurement orders, contracts, bidding batches, supply performance, supervision, or commissioning. The business rule fragments include engineering traceability rules, procurement rules, contract payment rules, performance supply rules, and status and validity rules.

[0132] (3) SQL generation module: Based on the large model of constrained prompt words (system / user and repair prompts) (DeepSeek-R1 is recommended), generate a single read-only SQL or WITH…SELECT.

[0133] (4) SQL Cleaning and Standardization Module:

[0134] a. Remove Markdown / comments / explanations.

[0135] b. Read-only verification (intercepting DDL / DML / multiple statements).

[0136] c. Automatic LIMIT / OFFSET protection.

[0137] d. Case-sensitive backfilling based on schema_dict (table name mandatory, column name uniqueness can be determined backfilling, aliases are not changed).

[0138] e. Length and syntax protection (truncating excessive lengths, balancing parentheses and quotation marks).

[0139] (5) Database execution module: uniformly adapts to MySQL / GaiaDB / PostgreSQL databases, executes and returns structured results or errors.

[0140] (6) Error collection and minimization repair module:

[0141] a. Input the previously generated SQL statement, the error message returned by the database, the user's original question, and contextual knowledge fragments into the repair prompt words.

[0142] b. Only fix the error-related segments (fields / tables / functions / syntax / case).

[0143] c. Output the revised SQL and re-enter it into the "cleaning-execution" process to form a closed loop.

[0144] The construction of the knowledge graph library for the power business table structure specifically includes:

[0145] The table structure of the power business database is restructured into a knowledge database of "business objects + key field semantics + business link relationships," enabling the model to select tables, fields, and connection paths as if it were someone knowledgeable about the power business. This table structure knowledge database is not simply stored as a flat array of table and field names, but rather constructed as a structured schema knowledge base oriented towards the power business system.

[0146] The structured schema knowledge base includes: a power business object layer, a table role layer, a field semantic layer, a business association layer, a synonym mapping layer, and a status and time semantic layer.

[0147] (1) The power business object layer is used to classify the database tables according to the power business objects such as engineering projects, procurement applications, purchase orders, contract payments, bidding batches, supply performance, supervision and commissioning;

[0148] (2) The table role layer is used to describe the business role of each table in the power business link;

[0149] (3) The field semantic layer is used to label key business fields such as project name, voltage level, batch plan number, procurement method, contract number, payment ratio, prepayment percentage, delivery notification status, and commissioning date;

[0150] (4) The business association layer is used to record primary and foreign key relationships, composite key relationships, and mapping relationships across business systems;

[0151] (5) The synonym mapping layer is used to establish the semantic correspondence between natural language business terms and real fields;

[0152] (6) The status and time semantic layer is used to mark the business meaning of fields such as deletion, freezing, release, signature, validity period, and commissioning conditions.

[0153] After parsing the user's question, the system retrieves the table structure knowledge fragment knowledge_t corresponding to the current query from the table structure knowledge database to constrain the large model to select the correct table, field and connection path during SQL generation and SQL repair.

[0154] The construction of the power business logic rule knowledge base specifically includes:

[0155] 1. Definition and Boundaries of the Logic Rules for Power Business:

[0156] The "Business Logic Description" is not the table structure itself, but rather a summary of the business problem type, query path, filtering rules, and output result definition, addressing two key issues:

[0157] (1) Path problem: Natural language only describes business intent, but databases often need to cross multiple tables and multiple intermediate keys to execute queries.

[0158] (2) Definition issue: The same concept is represented in the database by a specific encoding field, enumeration value, or prefix rule. It must be filtered according to business constraints; otherwise, even if the SQL can run, the query results will be incorrect.

[0159] 2. The constituent objects of the business logic rule description:

[0160] (1) Electricity business problem types: used to label / cluster business intent, classify related natural language problems into a clear category, and follow the same set of query logic.

[0161] (2) Query logic: Provide an executable step-by-step description of the intent of the business problem, including: step-by-step links (the tables that need to be passed through to reach the query result); clear field constraints and enumeration filtering; specific prefix / pattern rules.

[0162] 3. The aforementioned power business constraint rules are further subdivided into:

[0163] (1) Path rules, used to limit which tables should be joined and the order of joining;

[0164] (2) Definition rules, used to limit the statistical scope, time range and business definition;

[0165] (3) Status rules, used to limit the query to data that is not deleted, not frozen, not unfrozen, in effect, or in other valid states;

[0166] (4) Selection rules are used to determine the most recent date, latest year, highest priority record or return multiple candidate results from multiple candidate records.

[0167] In this system, business logic descriptions are retrieved and used as input to the knowledge_p large model, ensuring that when generating and repairing SQL, not only are the table fields correctly matched, but they are also aligned with the business context.

[0168] The SQL generation model is constrained by a prompt word system, which specifically includes:

[0169] SQL generation large model system prompt word role_prompt_g:

[0170] You are an NL2SQL assistant. Please generate valid and executable SQL commands based on user requirements, the structured knowledge of the relevant table structure knowledge graph, and business logic rules, and strictly adhere to the following rules when generating SQL:

[0171] 1. Only SELECT queries are allowed; UPDATE / DELETE / INSERT / DDL queries are prohibited.

[0172] 2. Use only the tables and fields provided in the knowledge base; do not use fields that have not appeared before. Note that the table and field names in SQL commands must strictly adhere to the capitalization provided in the knowledge base. Please strictly follow the capitalization of the tables and fields provided in the knowledge base and do not arbitrarily change the capitalization.

[0173] 3. When performing a JOIN operation, follow the primary and foreign key relationships or business logic specifications in the knowledge base.

[0174] 4. Fields must be explicitly listed in the query statement; SELECT * is prohibited.

[0175] 5. Only output the SQL; do not output any explanations or add semicolons.

[0176] 6. If the user's question involves "latest / year / time range", please handle it according to the rules in the business logic description.

[0177] 7. Please pay attention to the subtle differences in the interpretation of similar column field meanings, and please choose the accurate column field name.

[0178] SQL generates large-scale user prompt words user_prompt_g:

[0179] User requirement: {{query}};

[0180] The structured knowledge content of the table structure knowledge graph includes available table structures and fields: {{knowledge_t}};

[0181] Business logic description: {{knowledge_p}};

[0182] Based on the provided table structure knowledge graph and business logic description, please generate SQL statements that meet the user's requirements.

[0183] Among them, the SQL repair feature for the constrained prompt word system of the large model specifically includes:

[0184] SQL repair large model system prompt word role_prompt_f:

[0185] You are a professional SQL command repair assistant. Based on user needs, the error messages returned by the database, and the structured knowledge of the relevant table structure knowledge graph, make minimal modifications to the previously executed SQL command to fix the error; unless necessary, do not change other logic or filtering conditions. Only output a single SQL statement.

[0186] Please strictly adhere to the following rules when generating SQL:

[0187] 1. Only SELECT queries are allowed; UPDATE / DELETE / INSERT / DDL queries are prohibited.

[0188] 2. Only use the tables and fields provided in the knowledge base; do not use fields that have not appeared before. Note that all table and field names must strictly adhere to the capitalization of the names in the knowledge base. Carefully check whether capitalization issues are causing SQL command errors.

[0189] 3. When performing a JOIN operation, follow the primary and foreign key relationships or business logic specifications in the knowledge base.

[0190] 4. Fields must be listed explicitly; SELECT * is not allowed.

[0191] 5. Only output the SQL; do not output any explanations or add semicolons.

[0192] 6. If the user's question involves "latest / year / time range", please handle it according to the rules in the business logic description.

[0193] 7. Please pay attention to the subtle differences in the interpretation of similar column field meanings, and please choose the accurate column field name.

[0194] SQL Repair Large Model User Prompt Message user_prompt_f:

[0195] User requirements: {{query}}

[0196] The structured knowledge content of the table structure knowledge graph includes available table structures and fields: {{knowledge_t}}

[0197] Business logic description: {{knowledge_p}}

[0198] The last SQL command that failed to execute: {{last_sql}}

[0199] The database returned the following error message when executing the last SQL command: {{db_error}}.

[0200] Based on the above user requirements, knowledge content, business logic description, original SQL command, and the error information returned by the database when executing the original SQL command, please correct the erroneous original SQL command and output the new SQL. The repair strategy is: {{error_fix_p}}. Please strictly follow the output rules: only output the SQL, do not output explanations, and do not add semicolons.

[0201] The SQL cleaning and standardization process specifically includes:

[0202] (1) Extraction: Extract the Markdown code block and natural language from the model output and locate the first SELECT / CTE fragment;

[0203] (2) Read-only verification: Regular expression / simple parser intercepts non-query commands;

[0204] (3) Rate limiting: If the query is not statistical and no LIMIT / OFFSET / UNION is set, add LIMIT N according to the policy;

[0205] (4) Case backfilling (key point of the algorithm):

[0206] 1. Parse the FROM / JOIN sequence, identify "table name + optional alias", and force backfill the table name (the alias remains unchanged);

[0207] 2. For alias.col / table.col formats, use the "alias → real table" mapping to query schema_dict and only fill in the standard case of column names.

[0208] 3. Backfill "naked columns" only when there is a unique table to which they belong to, to avoid accidental replacement;

[0209] 4. The table in the three-part db.table.col is also backfilled;

[0210] 5. The quotation mark style should not be changed during the backfilling process, and the positions of blanks and punctuation marks should remain unchanged to ensure idempotency.

[0211] (5) Length and syntax defense: Select "safe breakpoint" for excessively long truncation; remove trailing commas / unclosed parentheses; avoid false positives in comments that carry keywords.

[0212] Error classification and minimization repair specifically include:

[0213] The classifier extracts "error code + key phrase" from the db_error text and maps it to the corresponding repair strategy;

[0214] Large model suggests the following repair strategy: error_fix_p

[0215] 1. Table does not exist / case-sensitive: Only replace the table name with a true value;

[0216] 2. Column does not exist / case-sensitive: Select the most similar column or synonym from the table structure knowledge fragment;

[0217] 3. Syntax error: Keep the table / column unchanged, only adjust the order of functions, parentheses, and keywords;

[0218] 4. Result too large / timeout: Automatically add time window / pagination or necessary aggregation;

[0219] The retry strategy specifically includes:

[0220] The system should be repaired a maximum of max_iteration times (2-3 times recommended) to avoid entering an infinite loop. After each repair, a cleaning process should be performed to ensure consistency between read-only and backfill.

[0221] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0222] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0223] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0224] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0225] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A closed-loop generation method for natural language to SQL for power business systems, characterized in that: Specifically, it includes: Step S1: Obtain the natural language query request submitted by the user, and perform access and normalization processing on the natural language query request to obtain natural language query data; Step S2: Perform semantic parsing and knowledge retrieval on the natural language query data to obtain contextual data; Step S3: Generate a large model based on natural language query data, context data, and SQL, and generate candidate SQL; Step S4: Perform security governance processing on the candidate SQL to obtain controllable SQL; Step S5: Perform case backfilling and structure alignment on the controllable SQL to obtain the aligned SQL; Step S6: Execute the aligned SQL in the database to obtain the execution data; Step S7: If the execution data is the execution result data, encapsulate the execution result data with the aligned SQL to obtain a structured success response and end the current query process.

2. The closed-loop generation method for natural language to SQL for power business systems according to claim 1, characterized in that: Also includes: Step S8: If the executed data is database error data, repair the aligned SQL corresponding to the database error data to obtain the repaired SQL; Step S9: Perform closed-loop retry and iterative termination control on the repair SQL to obtain a new SQL; Step S10: If the new SQL fails to execute, output a fallback solution for the failure.

3. The closed-loop generation method for natural language to SQL for power business systems according to claim 1, characterized in that: Step 2 specifically includes: Semantic parsing is performed on the natural language query data to obtain retrieval features; based on the retrieval features, a retrieval is performed in a pre-built knowledge graph library of power business table structure and a knowledge library of power business logic rules to obtain retrieval results; the retrieval results are aggregated and pruned to form context data that can be input into a large model, the context data including: knowledge fragments of power business table structure and knowledge fragments of power business logic rules. The knowledge fragment of the power business table structure includes tables, fields and association keys corresponding to business objects such as engineering, projects, procurement applications, procurement orders, contracts, bidding batches, supply performance, and supervision or commissioning. The knowledge fragments of the power business logic rules include engineering traceability rules, procurement rules, contract payment rules, performance and supply rules, and status and validity rules.

4. The closed-loop generation method for natural language to SQL for power business systems according to claim 3, characterized in that: The method for constructing the knowledge graph base of the power business table structure specifically includes: The table structure of the power business table structure knowledge graph base is reconstructed into a knowledge database of "business objects + key field semantics + business link relationships", and a structured schema knowledge base oriented towards the power business system is adopted. The structured schema knowledge base includes: power business object layer, table role layer, field semantic layer, business association layer, synonym mapping layer, and state and time semantic layer; The method for constructing the power business logic rule knowledge base specifically includes: Definitions and boundaries of action of power business logic rules: Natural language only describes the intent of power business, but databases often require multiple tables and multiple intermediate keys to execute queries. The same concept is represented in the database using specific coded fields, enumeration values, and prefix rules, and filtered according to power business constraints; otherwise, even if the SQL can run, it will lead to incorrect query results. The constituent objects of business logic rule description: (1) Electricity business question types: used to label / cluster business intent, classify related natural language questions into a clear category, and follow the same set of query logic; (2) Query logic: Provide an executable step-by-step description of the intent of the business problem, including: step-by-step chain; clear field constraints and enumeration filtering; specific prefix / pattern rules; The power business constraint rules are further subdivided as follows: (1) Path rules, used to limit which tables should be joined and the order of joining; (2) Definition rules, used to limit the statistical scope, time range and business definition; (3) Status rules, used to limit the query to data that is not deleted, not frozen, not unfrozen, in effect, or in other valid states; (4) Selection rules are used to determine the most recent date, latest year, highest priority record or return multiple candidate results from multiple candidate records.

5. The closed-loop generation method for natural language to SQL for power business systems according to claim 1, characterized in that: Step 3 specifically includes: Based on natural language query data, knowledge fragments of power business table structure, and knowledge fragments of power business logic rules, SQL is constructed to generate prompt information according to a preset prompt word template. The generated prompt information includes the system prompt word role_prompt_g and the user prompt word user_prompt_g. The system prompt word role_prompt_g is used to specify the output boundaries and generation rules of the large model, including: only allowing the generation of query statements, only allowing the use of table names and field names that exist in the knowledge base, outputting table names and field names according to the standard capitalization of the knowledge base, prohibiting the output of explanatory text, and prohibiting the output of multiple statements; The user prompt term user_prompt_g is used to carry the specific input content for this task, including natural language query data, knowledge fragments of power business table structure, and knowledge fragments of power business logic rules. The system prompt word role_prompt_g and the user prompt word user_prompt_g are input into SQL to generate a large model and obtain candidate SQL.

6. The closed-loop generation method for natural language to SQL for power business systems according to claim 2, characterized in that: Step 8 specifically includes: When the aligned SQL fails to execute, parse the error message db_error returned by the database to identify the error category; Based on natural language query data, knowledge fragments of power business table structure and power business logic rules, the last failed SQL statement last_sql and error message db_error, and selecting an appropriate repair strategy error_fix_p based on db_error, SQL repair prompt information is constructed according to a preset repair prompt word template, wherein the repair prompt information includes the system prompt word role_prompt_f and the user prompt word user_prompt_f; Among them, the system prompt word role_prompt_f is used to limit the repair behavior to only make minimal modifications to the error-related fragments, and to continue to maintain read-only queries, explicit column name output, and table field names consistent with the knowledge base standard capitalization; The user prompt word user_prompt_f is used to carry the input content of this repair task, including natural language query data, knowledge fragments of power business table structure and power business logic rules, the SQL statement that failed to execute last time last_sql, as well as the error message db_error and the repair strategy error_fix_p; Input the system prompt word role_prompt_f and the user prompt word user_prompt_f into the SQL repair model to obtain the repaired SQL statement sql_fix.

7. The closed-loop generation method for natural language to SQL for power business systems according to claim 2, characterized in that: Step 9 specifically includes: Repeat steps S4 and S5 on the repaired SQL statement sql_fix to obtain the repaired executable SQL (sql2), and execute step S6 on sql2; if the execution is successful, proceed to step S7 and output a success response; if the execution fails, update last_sql and db_error, and determine whether the current iteration count has reached the preset maximum iteration count; if it has not reached the maximum iteration count, return to step S8 to continue generating a new sql_fix and enter the next closed loop; if it has reached the maximum iteration count, proceed to step S10.

8. The closed-loop generation method for natural language to SQL for power business systems according to claim 2, characterized in that: Step 10 specifically includes: When the number of iterations reaches the preset maximum number of iterations and still no successful execution result is obtained, a fallback response is output; the fallback response includes the SQL of the last attempt, the corresponding error message summary, and prompts to guide the user to supplement information or narrow down the query scope.

9. A computer-readable storage medium, characterized in that: It stores a computer program that, when executed by a processor, implements a closed-loop generation method for natural language to SQL for power business systems as described in any one of claims 1 to 8.

10. A computer device, characterized in that: include: Memory, used to store instructions; A processor is configured to execute the instructions, causing the computer device to perform the operation of a closed-loop generation method for natural language to SQL for power business systems as described in any one of claims 1 to 8.

Images