SQL statement generation method and system based on execution feedback iterative correction

Abstract

The invention provides an SQL statement generation method and system based on execution of feedback iterative correction, and relates to the technical field of large language models.The method comprises the steps that a query request of a user is received, and metadata information of a target database is obtained; generating a current SQL statement based on the query request and the metadata information; pre-executing the current SQL statement in a database sandbox environment; obtaining a pre-execution result, directly outputting the pre-execution result if the pre-execution result succeeds, and performing iterative loop if the pre-execution result fails; and analyzing error information in the pre-execution result, and determining an error type. According to the method, the illusion problem of a large language model is fundamentally solved, and the semantic correctness of SQL generation is ensured. And meanwhile, the iterative correction process replaces low-efficiency manual cue word adjustment, so that the use experience and the problem solving efficiency can be remarkably improved. In addition, the whole pre-execution process is completed in a sandbox environment, so that the influence of potential misoperation on production data is effectively isolated, and the safety of the system is enhanced.

Description

Technical Field

[0001] This invention relates to the field of large language model technology, and in particular to a method and system for generating SQL statements based on execution feedback iterative correction. Background Technology

[0002] In the field of database technology, Text-to-SQL (Text to Structured Query Language) technology aims to automatically convert the data query requirements of non-technical users into executable SQL (Structured Query Language) statements through natural language understanding, thereby lowering the technical threshold for database operations. With the rapid development of Large Language Models (LLMs), Text-to-SQL solutions based on prompt word engineering have become mainstream. These solutions typically combine the user's natural language description with database schema information, constructing prompt words that are directly input into the LLM, which then generates the corresponding SQL code in one step. This technical approach has shown great potential in simple scenarios and standard datasets and is considered a key enabling technology for achieving natural language data interaction.

[0003] However, existing Text-to-SQL technologies based on large language models have revealed several technical shortcomings that urgently need to be addressed in actual enterprise-level application deployments: First, the model illusion problem causes generated SQL statements to be syntactically correct but semantically incorrect, potentially referencing non-existent table or field names, such as mistakenly writing "phone_number" as "phone," leading to SQL execution failure; Second, existing solutions are mostly open-loop generation models, meaning that after the model generates SQL, it is directly handed over to the user or system for execution, lacking a reliable verification and correction mechanism for the generated results. When users encounter execution errors, they often find themselves in the predicament of repeatedly adjusting prompts, resulting in a poor user experience and low efficiency. Summary of the Invention

[0004] In view of this, this invention proposes a method and system for generating SQL statements based on execution feedback and iterative correction. After generating the current SQL statement, it is first pre-executed in a database sandbox environment to obtain definite execution feedback. When pre-execution fails, the system automatically analyzes the error information to determine the error type and selects the corresponding correction strategy. Based on this, a corrected SQL statement is generated and returned for a new round of verification. This closed-loop mechanism utilizes the database engine itself as an objective validator, fundamentally overcoming the illusion problem of large language models and ensuring the semantic correctness of the generated SQL. Simultaneously, this automated iterative correction process replaces inefficient manual word suggestion adjustments, significantly improving user experience and problem-solving efficiency. Furthermore, the entire pre-execution process is completed in a sandbox environment, effectively isolating the impact of potential erroneous operations on production data, thereby enhancing system security.

[0005] The technical solution of this invention is implemented as follows: On the one hand, the present invention provides a method for generating SQL statements based on execution feedback iterative correction, comprising the following steps: S1 receives the user's query request and obtains the metadata information of the target database; S2 generates the current SQL statement based on the query request and metadata information; S3 pre-executes the current SQL statement in the database sandbox environment; S4 retrieves the pre-execution result. If the execution is successful, proceed to S7; otherwise, proceed to S5. S5 analyzes the error information in the pre-execution results, determines the error type, and selects the corresponding correction strategy based on the error type; S6 generates a corrected SQL statement based on the error information, correction strategy, and metadata information, uses the corrected SQL statement as the current SQL statement, and returns to S3; S7 outputs the current SQL statement that has passed verification as the target SQL statement.

[0006] Based on the above technical solutions, preferably, step S3 includes the following sub-steps: S31 performs syntax analysis on the current SQL statement to determine whether it is a Data Manipulation Language (DML) statement; When S32 determines that it is a DML statement, it sends a transaction start command to the target database and establishes an isolated sandbox session; S33 executes the current SQL statement in the sandbox session and obtains the original execution response returned by the database; Regardless of the content of the original execution response, S34 sends a transaction rollback instruction to the target database through the sandbox session to ensure that all data changes are undone; S35 encapsulates the original execution response into a pre-execution result object, which contains an execution status identifier and response data, as input to step S4.

[0007] Based on the above technical solutions, the preferred method involves analyzing error information in the pre-execution results to determine the error type, including the following sub-steps: S51 extracts the error code and error message text returned by the database from the error information; S52 matches the error code with a predefined error type mapping table to determine the first-level error type, wherein the error type includes at least one of the following: column not found, table not found, syntax error, and unique constraint conflict. S53 When the first level error type is column not found or table not found, extract the name identifier that is reported as invalid by the database from the error message text; S54 performs similarity matching between invalid name identifiers and the set of legal identifiers in the metadata information to generate a list of correction suggestions containing Top-K candidate identifiers.

[0008] More preferably, in step S54, the similarity matching is calculated using the following formula:

[0009] Where A represents the invalid identifier extracted from the error message, B represents the legal identifier in the metadata information, S(A,B) represents the comprehensive similarity score between the invalid identifier and the legal identifier, E(A,B) represents the edit distance similarity between the invalid identifier and the legal identifier, G(A,B) represents the N-Gram similarity between the invalid identifier and the legal identifier, K(A,B) represents the semantic keyword weight between the invalid identifier and the legal identifier, and ω1, ω2, ω3 are adjustable weight coefficients, and ω1+ ω2+ ω3= 1.

[0010] Based on the above technical solutions, preferably, after receiving the user's query request and obtaining the metadata information of the target database, step S1 further includes: S11 parses the field annotations in the metadata information to identify predefined business logic patterns; S12 analyzes the natural language intent of the query request and determines the type of data operation triggered by the intent; S13 generates at least one SQL generation constraint rule based on the matching result of the business logic mode and the data operation type; S14 uses the SQL generation constraint rules, the query request, and the metadata information together as input constraints for generating the current SQL statement in S2.

[0011] Based on the above technical solutions, preferably, before step S3, the following is also included: S21 parses the current SQL statement to identify its operation type and structural features; S22 performs risk assessments on identified operational types and structural characteristics based on a predefined risk rule base. S23 generates execution control decisions based on the risk assessment results.

[0012] Based on the above technical solutions, preferably, step S6 includes the following sub-steps: S61 constructs SQL correction generation instructions based on the error type and correction strategy, combined with metadata information; S62 executes the SQL correction generation instruction to generate corrected SQL statement candidates; S63 verifies the syntax compliance and policy compliance of the modified SQL statement candidate, and sets the verified modified SQL statement candidate as the new current SQL statement, and returns to S3 for the next round of iteration verification.

[0013] Based on the above technical solutions, the preferred option also includes an iterative control step: S81 sets the maximum number of iterations threshold and convergence criteria; After each execution of S6 to generate the revised SQL statement, S82 records the current iteration information; S83 evaluates whether to terminate the iteration early based on the convergence criteria; When the maximum number of iterations is reached or the convergence condition is met, S84 outputs the final result and an iteration analysis report.

[0014] On the other hand, the present invention provides an SQL statement generation system based on execution feedback iterative correction, for implementing the above-mentioned SQL statement generation method based on execution feedback iterative correction, including: The request processing module is used to receive user query requests and obtain metadata information from the target database; The SQL generation module, connected to the request processing module, is used to generate the current SQL statement based on the query request and metadata information; The sandbox execution module, connected to the SQL generation module, is used to pre-execute the current SQL statement in the database sandbox environment and obtain the pre-execution result; An error analysis module, connected to the sandbox execution module, is used to analyze the error information in the pre-execution result when pre-execution fails, determine the error type, and select the corresponding correction strategy according to the error type; The correction generation module, connected to the error analysis module, is used to generate a corrected SQL statement based on the error information, correction strategy, and metadata information, and to feed the corrected SQL statement back to the SQL generation module as the new current SQL statement. An iteration control module is connected to the sandbox execution module, the error analysis module, and the correction generation module, respectively, and is used to control the iterative loop of sandbox execution, error analysis, and correction generation. The result output module, connected to the sandbox execution module, is used to output the verified current SQL statement as the target SQL statement when the pre-execution is successful.

[0015] On the other hand, the present invention provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the above-described SQL statement generation method based on execution feedback iterative correction.

[0016] The SQL statement generation method and system based on execution feedback iteration correction of the present invention have the following advantages over the prior art: 1. By pre-executing the current SQL statement in a database sandbox environment after generation to obtain definite execution feedback, and automatically analyzing the error information to determine the error type and select the corresponding correction strategy when the pre-execution fails, the system generates a corrected SQL statement based on this and returns it for a new round of verification. This closed-loop mechanism uses the database engine itself as an objective validator, fundamentally overcoming the illusion problem of large language models and ensuring the semantic correctness of the generated SQL. At the same time, this automated iterative correction process replaces the inefficient manual adjustment of prompts, significantly improving the user experience and problem-solving efficiency. In addition, the entire pre-execution process is completed in a sandbox environment, effectively isolating the impact of potential erroneous operations on production data to enhance system security. 2. By introducing a transaction-based pre-execution and forced rollback mechanism in a sandbox environment, the system fundamentally eliminates any potential risk of contaminating production data during the verification process, achieving absolute execution security and making it possible to automate the testing of SQL statements that contain data modification operations. 3. By employing a multi-feature intelligent similarity matching algorithm that integrates edit distance, N-Gram, and semantic keyword weights, it can accurately locate and recommend the most likely correct identifier when faced with field or table name errors caused by model illusion. This transforms the correction process from blind guessing to data-driven decision-making, significantly improving the accuracy of error diagnosis and the efficiency of correction. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] Figure 1 This is a flowchart illustrating the SQL statement generation method based on execution feedback iteration correction according to the present invention. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0020] like Figure 1 As shown, the SQL statement generation method based on execution feedback and iterative correction of the present invention is fundamentally based on constructing a closed-loop system that includes generation, verification, analysis, and correction steps. Specifically, the system first receives the user's natural language query request and obtains the metadata of the target database; then, it generates an initial SQL statement based on this; subsequently, instead of directly executing the statement in the production database, it performs pre-execution in an isolated and secure database sandbox environment; based on the feedback results of the pre-execution, the system enters different branches: if the execution is successful, the verified SQL statement is output as the final result; if the execution fails, the system automatically analyzes the error information, diagnoses the error type, and selects an appropriate correction strategy accordingly; finally, it integrates the error information, correction strategy, and metadata to generate a corrected SQL statement, and returns it as a new input to the pre-execution step to start the next round of iterative verification until a correct SQL statement that can be successfully executed is generated. This method, by introducing database execution feedback as the basis for iterative correction, transforms the traditional open-loop Text-to-SQL generation process into a closed loop capable of self-verification and self-correction, thereby significantly improving the accuracy, reliability, and security of SQL generation. Specifically, it can be divided into steps S1-S7.

[0021] Step S1: Receive the user's query request and obtain the metadata information of the target database.

[0022] The system receives natural language query requests from users via a graphical user interface, command-line interface, or application programming interface. This request is a textual description of the intent to manipulate the target data, such as "query the names and email addresses of all employees in the R&D department" or "delete all invalid order records from last month." The system also needs to connect to one or more target databases. In actual deployment, connections can be established by configuring database connection strings, and connection pool management should be ensured to optimize performance.

[0023] Obtaining metadata is the core technology of this step. The system queries the system directory table of the target database to dynamically obtain and cache the necessary metadata. This metadata includes at least table structure information, field details, constraint information, and other metadata such as database engine type, character set, and dialect version, which are used to adapt to different SQL syntaxes.

[0024] In some embodiments, after obtaining the metadata information of the target database, it is necessary to prevent the generation of dangerous or non-compliant SQL code and realize the pre-emptive automatic adaptation of business rules, which can be achieved through steps S11-S14.

[0025] Step S11: Parse the field annotations in the metadata information to identify predefined business logic patterns.

[0026] In this step, the system performs a deep scan of the field comments obtained from the database. Using pre-configured keyword matching rules, such as keywords like "deletion flag", "logical deletion", "del_flag", and "is_deleted", the system can automatically identify fields marked as "logical deletion". Similarly, by matching keywords such as "version number", "version", "tenant_id", and "tenant", the system can identify version control fields and tenant isolation fields. This process transforms unstructured comment text into structured business logic tags.

[0027] Step S12: Analyze the natural language intent of the query request and determine the type of data operation triggered by the intent.

[0028] In this step, the system uses a lightweight intent classification model or rules to parse the user's natural language query. For example, it identifies requests containing verbs such as "delete," "remove," and "clear" as "delete operation"; requests containing verbs such as "update," "modify," and "change" as "update operation"; and for query-type requests, it analyzes whether they implicitly contain data filtering requirements.

[0029] Step S13: Generate at least one SQL generation constraint rule based on the matching result of the business logic pattern and the data operation type.

[0030] This step involves decision-making and rule generation. For example, when the system identifies a logical deletion marker field in the database (i.e., a business logic pattern) and the user's intent is a deletion operation (i.e., a data operation type), these two match successfully, and the system will generate a strong constraint rule: "Prohibit the generation of physical DELETE statements; the operation must be rewritten as an UPDATE statement targeting the logically deleted field." This rule is not a simple text prompt, but a structured instruction object that can be parsed by the subsequent generation engine.

[0031] Step S14: Use the SQL generation constraint rules, the query request, and the metadata information together as input constraints for generating the current SQL statement in S2.

[0032] Finally, in this step, the generated SQL generation constraint rules, along with the original user query request and database metadata information, are encapsulated into an enhanced input context and passed to the subsequent step S2. When constructing prompt words or applying rules, the SQL generation engine can clearly know that although the user requests deletion, a statement to update the del_flag field must be generated. This hard constraint prevents the generation of dangerous or non-compliant SQL code from the source and achieves pre-emptive automated adaptation of business rules.

[0033] Step S2: Generate the current SQL statement based on the query request and metadata information.

[0034] This step serves as the initial conversion hub from natural language to structured query language. Its core task is to integrate multi-source input information and drive the large language model to produce executable SQL code. This step is based on the structured data output from previous steps, which includes the original natural language query, parsed and organized target database metadata, and any explicit SQL generation constraints that may be generated by the business rule adaptation module.

[0035] The system first intelligently assembles these input elements based on a predefined prompt template framework, constructing a well-structured and clearly defined composite prompt. This prompt may include the following key parts: First, the system role and task definition, clearly instructing the large language model to act as an SQL expert; second, clearly displaying relevant database schema information, providing the model with an accurate data map; third, clearly listing all the business rules and security constraints that must be followed, guiding compliance from the source of intent; and finally, accurately embedding the user's original query request and strictly limiting the output format to require the model to return only clean SQL code.

[0036] After constructing the prompt words, the system calls the selected large language model service through the configured application programming interface, submitting the prompt words and requesting generation. During this process, appropriate generation parameters can be configured, such as using a moderate temperature parameter to balance the creativity and controllability of the generated results. The large language model understands and infers based on the received rich context, ultimately outputting a candidate SQL statement. The system post-processes the model's raw response, including stripping away any non-code wrapper characters and formatting tags to extract the standard SQL command text.

[0037] The SQL command string generated by this process is formally defined as the current SQL statement. As the starting point of the entire closed-loop iterative verification process, it is immediately sent to the subsequent step S3, ready to be tested and fed back by the real execution environment in the database sandbox environment.

[0038] In some specific implementations, after the current SQL statement is generated and before the sandbox pre-execution in step S3, an automated defense and optimization checkpoint needs to be inserted. Its core purpose is to identify and intercept SQL statements with high security risks or potential performance issues in advance through static analysis and prediction, thereby avoiding unnecessary sandbox execution overhead and triggering a correction process in advance to improve overall system efficiency and security. This is specifically divided into steps S21-S23.

[0039] Step S21: Parse the current SQL statement to identify its operation type and structural features.

[0040] In this step, the system first invokes an SQL syntax parser, such as a parser generated by tools like ANTLR, or integrates the parsing functionality of the database driver itself, to convert the text-formatted SQL statement into a structured abstract syntax tree. By traversing this syntax tree, the system can accurately identify the operation type of the statement and extract key structural features. For example, for UPDATE / DELETE statements, it checks whether they contain a WHERE clause and the complexity of that clause; for SELECT statements, it analyzes the number of table joins involved, the depth of subquery nesting, etc.

[0041] Step S22: Based on a predefined risk rule base, conduct a risk assessment on the identified operation types and structural characteristics.

[0042] A risk rule base is set up in the system, containing several judgment logics. For example, a security rule might be defined as: "If the operation type is UPDATE or DELETE, and no WHERE clause is identified, or the WHERE clause is a tautological condition, then the risk level is set to 'High Risk'." Another performance rule might be defined as: "If the operation type is SELECT, and lightweight heuristic analysis reveals that it may involve a Cartesian product join of multiple large tables, then the risk level is set to 'Warning'." The system matches the characteristics of the current SQL against these rules one by one to calculate its comprehensive risk rating.

[0043] Step S23: Generate execution control decisions based on the risk assessment results.

[0044] In this step, if the risk assessment determines it to be high-risk, the decision is to directly intercept it. The system will construct a simulated failure pre-execution result, where the error type is marked as a security rule violation, and skip the actual sandbox pre-execution, directly jumping to step S5 to enter the correction process. If it is determined to be a performance warning, the decision may be to allow execution but with additional monitoring or to directly suggest optimization, which can also trigger correction in advance. If no risk rules are triggered, the decision is to allow normal execution, and the SQL statement will successfully enter the sandbox environment in step S3. Through this pre-check mechanism, the system can effectively filter dangerous and inefficient operations before execution, providing a better starting point for subsequent iterative correction cycles.

[0045] Step S3: Pre-execute the current SQL statement in the database sandbox environment.

[0046] This step is to create a test sandbox that is logically isolated from the production environment and where all data changes can be undone. At the same time, SQL statements are executed to trigger a deterministic response from the database engine. This can be divided into steps S31-S33.

[0047] Step S31: Perform syntax analysis on the current SQL statement to determine whether it is a Data Manipulation Language (DML) statement.

[0048] In this step, the SQL syntax parser is invoked to quickly analyze the current SQL statement, identify its statement type, and in particular determine whether it is a DML statement that will modify data. This determination will determine the level of subsequent security measures.

[0049] Step S32: When it is determined to be a DML statement, a transaction start command is sent to the target database to establish an isolated sandbox session.

[0050] The system uses credentials specifically configured for testing to establish a physically or logically isolated session connection with the target database. A key security enhancement is that, only when a statement is determined to be a DML statement, the system explicitly sends a START TRANSACTION command within this isolated session, initiating a database transaction. This transaction serves as an atomic container for all subsequent data operations, providing the technical basis for data rollback.

[0051] Step S33: Execute the current SQL statement in the sandbox session and obtain the original execution response returned by the database.

[0052] The system sends the SQL statement to be verified to the database server for execution through an established isolated session. The database engine then fully parses, optimizes, and runs the statement. Upon completion, the system synchronously captures the raw execution response returned by the database. This response has two possibilities: either execution succeeds, returning a result set or the number of affected rows; or execution fails, returning an error message containing a specific error code and detailed text.

[0053] Step S34: Regardless of the content of the original execution response, a transaction rollback instruction is sent to the target database through the sandbox session to ensure that all data changes are undone.

[0054] For DML statements executed within a previously initiated transaction, the system unconditionally sends a ROLLBACK command to the same session. This command ensures that all temporary data insertion, update, or deletion operations within the transaction are completely rolled back, and the database state is restored to its state before execution, thus achieving the goal of zero pollution of production data. Even for query statements, the system may perform an empty rollback or close the session to release resources.

[0055] Step S35: Encapsulate the original execution response into a pre-execution result object, which contains an execution status identifier and response data, as input to step S4.

[0056] Finally, the system encapsulates the captured raw response, execution status identifier, and metadata such as session ID and timestamp into a structured data object, namely the pre-execution result object. This object, as the deterministic output of step S3, fully records all the information of this sandbox verification, provides a basis for judgment in the process control step S4, and provides detailed diagnostic materials for possible error analysis step S5.

[0057] Through the above sub-steps, step S3 transforms a potentially risky SQL execution attempt into a safe, controllable, and information-rich authenticity verification experiment.

[0058] Step S4: Obtain the pre-execution result. If the execution is successful, proceed to S7; if the execution fails, proceed to S5.

[0059] This step is the logical decision-making center in the closed-loop system. No complex calculations or transformations are performed in this step. Its core function is to perform state analysis and process routing on the pre-execution result object produced by step S3, and to determine whether the system moves towards the final successful output or enters the error analysis and correction loop.

[0060] Step S5: Analyze the error information in the pre-execution results, determine the error type, and select the corresponding correction strategy according to the error type.

[0061] After step S4 determines that the pre-execution has failed, this step is responsible for accurately diagnosing the cause of the failure, providing key decision-making basis for generating an effective correction plan. Specifically, the system receives the pre-execution result object from step S4, which contains the original error information returned by the database. In this step, the error information in the pre-execution result is analyzed to determine the error type, which can be divided into steps S51-S54.

[0062] Step S51: Extract the error code and error message text returned by the database from the error information.

[0063] In this step, the system parses the database's native error codes and complete error message text from the pre-execution result object.

[0064] Step S52: Match the error code with a predefined error type mapping table to determine the first-level error type. The error type includes at least one of the following: column not found, table not found, syntax error, and unique constraint conflict.

[0065] In this step, the system maintains a predefined error code-error type mapping dictionary. For example, error code 1054 is mapped to column not found, 1146 to table not found, and 1062 to unique constraint conflict. The system quickly matches the extracted error codes with this dictionary to determine a general error type classification, which serves as the basis for subsequent different correction strategies.

[0066] Step S53: When the first level error type is column not found or table not found, extract the name identifier reported as invalid by the database from the error message text.

[0067] For certain error types, further details need to be extracted. Only when the error type is determined to be a column or table not existing will the system use text processing techniques such as regular expressions to precisely extract the specific name explicitly reported as invalid by the database from the lengthy error message text. For example, from the error message "Unknown column 'phone' in 'field list'", the invalid column name "phone" will be extracted; from "Table 'test.user_info' doesn't exist", the invalid table name "user_info" will be extracted.

[0068] Step S54: Perform similarity matching between invalid name identifiers and the set of legal identifiers in the metadata information to generate a list of correction suggestions containing Top-K candidate identifiers.

[0069] Finally, for the invalid identifiers extracted in the previous step, the system compares them with the set of all legal identifiers obtained from the metadata. The core difference lies in not performing a simple exact string match, but rather employing a multi-feature similarity matching algorithm. This algorithm calculates a comprehensive similarity score between the invalid identifier A and each legal identifier B. The score comprehensively considers edit distance, N-gram overlap, and whether they contain the same semantic keywords from the database domain. Based on the score, the algorithm sorts all legal identifiers in descending order, generating a list of correction suggestions containing the Top-K most likely correct candidates. For example, for the invalid column name "phone," this list might recommend "phone_number" as the first choice and "telephone" as the second choice.

[0070] Specifically, in step S54, the similarity matching is calculated using the following formula:

[0071] Where A represents the invalid identifier extracted from the error message, B represents the legal identifier in the metadata information, S(A,B) represents the comprehensive similarity score between the invalid identifier and the legal identifier, E(A,B) represents the edit distance similarity between the invalid identifier and the legal identifier, G(A,B) represents the N-Gram similarity between the invalid identifier and the legal identifier, K(A,B) represents the semantic keyword weight between the invalid identifier and the legal identifier, and ω1, ω2, ω3 are adjustable weight coefficients, and ω1+ ω2+ ω3= 1.

[0072] The character edit similarity E(A,B) is calculated using the Levenstein edit distance, which measures the minimum number of single-character edits required to convert string A to B, and is then normalized. E(A,B) = 1 - Lev(A,B) / max(len(A), len(B)) This component can effectively capture spelling errors and character additions, deletions, and modifications. Here, Lev(A,B) represents the Levenstein edit distance between A and B, which is the minimum number of steps required to convert A to B through insertion, deletion, and replacement operations. |A| represents the character length of string A, and max(|A|, |B|) represents taking the maximum value of the two string lengths for normalization.

[0073] The structural sequence similarity G(A,B) adopts the N-Gram model. The algorithm splits strings A and B into sets of consecutive character substrings of length 2, and then calculates the Jaccard similarity coefficient between the two sets: G(A,B) = |N2(A) ∩ N2(B)| / |N2(A) ∪ N2(B)| This component can effectively evaluate the commonalities of local character sequences of identifiers, and is particularly effective for identifying variants such as user_name and username that are highly similar in character composition. Here, N2(S) represents the 2-gram set of string S, which is the set of substrings obtained by splitting S into two consecutive characters. ∩ represents the intersection operation of sets, ∪ represents the union operation of sets, and |·| represents the number of elements in the set.

[0074] The semantic keyword weight K(A,B) incorporates domain knowledge. The system predefines a set containing nine common database semantic keywords: {id, name, code, type, time, date, num, flag, status}. The algorithm checks whether A and B contain the same keyword. If they do, a fixed reward weight is assigned; otherwise, it is 0, represented as follows: K(A,B) = (1 / 9) * Σ [I(A,k) * I(B,k)] It ensures that semantically relevant identifiers can still get high scores when there are large differences in characters. Here, k represents the keyword index, with a value range of 1 to 9. I(S,k) is an indicator function, which is 1 when the string S contains the k-th preset keyword, and 0 otherwise. Σ is the summation symbol, which represents the summation of k from 1 to 9.

[0075] In some specific embodiments, the weighting coefficients can be set to ω1=0.65, ω2=0.25, ω3=0.10, with character editing similarity given the highest weight because spelling errors are the most common cause; structural similarity is second; and semantic weight serves as a supplement and enhancement. Furthermore, these weights are designed to be dynamically adjusted within a certain range, such as ω1 ∈ [0.60, 0.70], to adapt to the naming conventions of different databases.

[0076] Step S6: Based on the error information, correction strategy and metadata information, generate the corrected SQL statement, use the corrected SQL statement as the current SQL statement, and return to S3.

[0077] After step S5 completes error diagnosis and strategy selection, this step is responsible for transforming the abstract correction intent into new executable SQL code to drive the next round of verification iterations. Specifically, this can be divided into steps S61-S63.

[0078] Step S61: Based on the error type and correction strategy, and combined with metadata information, construct the SQL correction generation instruction.

[0079] In this step, the system does not simply transmit the raw error information, but performs in-depth analysis and structuring, extracting key error features from the pre-execution results, such as the precise error location, specific invalid text fragments, and relevant contextual information. This process transforms unstructured error messages into feature vectors that can be precisely processed by the machine.

[0080] Step S62: Execute the SQL correction generation instruction to generate corrected SQL statement candidates.

[0081] The system has a pre-configured correction strategy template library. Based on the error features extracted in step S61 and the error types determined in step S5, the system performs intelligent matching to select the most suitable template. For example, for an error where a column does not exist, an identifier replacement strategy template is matched. This template is not fixed code, but a blueprint containing placeholders and logical instructions, such as: "Replace the identifier [invalid identifier] at [error location] with the optimal item from the [candidate identifier list] while keeping the SQL structure unchanged."

[0082] Step S63: Verify the syntax compliance and policy compliance of the modified SQL statement candidate, and set the verified modified SQL statement candidate as the new current SQL statement, so as to return to S3 for the next round of iteration verification.

[0083] In this step, the system integrates the matched correction strategy template, the error features extracted in S61, and the complete table structure information from the metadata. If a correction suggestion list from step S5 exists, the system injects it as a key parameter. The specific generation can be achieved through two main technical paths: one is deterministic rewriting based on a rule engine, which directly manipulates the abstract syntax tree of the original SQL according to template instructions, performing identifier replacement or structural adjustments; the other is intelligent generation based on a large language model, which constructs a reinforced prompt word containing the original erroneous SQL, the error reason, the correction requirement, and the correct metadata, and submits it to the model to request the generation of a corrected version. Regardless of the path used, a syntactically complete SQL statement will ultimately be produced.

[0084] Step S7: Output the current SQL statement that has passed the verification as the target SQL statement.

[0085] This step marks the end of the entire closed-loop iterative correction process and the delivery of results. When step S4 determines that the current SQL statement has been executed successfully based on the pre-execution result, the process branch points to this step, signifying that the system has generated a final SQL code that is secure and executable, having passed rigorous testing in the database sandbox environment in terms of syntax, semantics, and business logic.

[0086] In some specific embodiments, an iterative control step is also included, which can be divided into steps S81-S84.

[0087] Step S81: Set the maximum number of iterations threshold and convergence criteria.

[0088] In this step, the system presets two key control parameters: first, the maximum number of iterations threshold N, which can be set to an integer between 5 and 10, serving as a safety boundary to ensure that the system will not fall into an infinite loop even in extreme cases; and second, the convergence judgment condition, which is a set of configurable logical rules used to determine whether the iteration has reached a stable state.

[0089] Step S82: After each execution of S6 to generate the corrected SQL statement, record the current iteration information.

[0090] Iteration information is recorded in an iteration log, which may include: the current iteration number, the generated SQL statement text, the types of errors encountered, the correction strategies used, and the pre-execution time.

[0091] Step S83: Evaluate whether to terminate the iteration early based on the convergence criteria.

[0092] After recording each iteration, the system immediately analyzes the current iteration log according to the convergence criteria set in S81. By calculating the similarity of SQL statements in the latest iterations or checking the change patterns of error types, if the evaluation finds that any convergence criterion is met, such as two consecutive SQL statements being almost identical, the system determines that convergence has been achieved. It considers that continuing the iteration is unlikely to produce more meaningful improvements, and thus generates a decision to terminate early.

[0093] Step S84: When the maximum number of iterations is reached or the convergence condition is met, output the final result and iterative analysis report.

[0094] The system will terminate the main iteration loop and execute this step in two situations: first, when the number of iterations reaches the preset maximum threshold N; and second, when the S83 evaluation determines that the convergence condition is met. Regardless of the termination method, the system will generate a structured iteration analysis report. This report includes not only the final successful target SQL statement but also a complete iteration trajectory analysis, such as: the total number of iterations, the error type and correction strategy for each iteration, the performance time curve, and an explanation of the termination reason.

[0095] The SQL statement generation system based on execution feedback iteration correction of the present invention is used to implement the above-mentioned SQL statement generation method based on execution feedback iteration correction, and includes a request processing module, an SQL generation module, a sandbox execution module, an error analysis module, a correction generation module, an iteration control module, and a result output module.

[0096] The request processing module receives user query requests and retrieves metadata information from the target database. As the system entry point, it provides various interfaces such as RESTful API, WebSocket, or message queues to receive JSON-formatted natural language query requests from clients or front-end applications. Simultaneously, this module maintains a connection pool with one or more target databases, is responsible for querying and caching system directories such as INFORMATION_SCHEMA on demand, and dynamically constructs a metadata knowledge base containing table structures, fields, constraints, and annotations.

[0097] The SQL generation module, connected to the request processing module, generates the current SQL statement based on the query request and metadata information. It integrates a large language model invocation and prompt word engineering. This module receives the request context, dynamically constructs multi-level prompt words based on predefined templates, and invokes large language models such as GPT-4, Claude, or locally deployed CodeLlama through a configured API key. The model's response is denoised and extracted to generate the initial current SQL statement.

[0098] The sandbox execution module connects to the SQL generation module and is used to pre-execute the current SQL statement in the database sandbox environment and obtain the pre-execution result. This module creates an independent database connection session for each verification. For DML statements, START TRANSACTION is executed automatically; after SQL execution, regardless of success or failure, ROLLBACK is executed immediately to ensure the atomicity and reversibility of pre-execution. It captures all database output and encapsulates it into a pre-execution result object containing status codes and detailed data.

[0099] The error analysis module, connected to the sandbox execution module, analyzes the error information in the pre-execution result when pre-execution fails, determines the error type, and selects an appropriate correction strategy based on the error type. It can parse error objects, identify error categories by searching a pre-defined error code-type mapping, and for object name errors, run a multi-feature similarity algorithm to calculate the matching degree between invalid names and all legal names, generating a sorted list of correction suggestions.

[0100] The correction generation module, connected to the error analysis module, generates a corrected SQL statement based on the error information, correction strategy, and metadata information. This corrected SQL statement is then fed back to the SQL generation module as the new current SQL statement. It selects the corresponding correction template from the strategy library based on the diagnosed error type, and then, combining the correction suggestion list and metadata, directly rewrites the SQL abstract syntax tree through the rule engine, or reconstructs refined prompt words and calls a large language model to generate the corrected SQL statement.

[0101] The iteration control module is connected to the sandbox execution module, error analysis module, and correction generation module, respectively, and is used to control the iterative loop of sandbox execution, error analysis, and correction generation. It is used for convergence judgment and, when the maximum number of iterations is reached or the convergence condition is met, commands the system to exit the loop. Simultaneously, it is also responsible for passing the state between iterations and recording end-to-end logs for generating analysis reports.

[0102] The result output module is connected to the sandbox execution module and is used to output the verified current SQL statement as the target SQL statement when the pre-execution is successful.

[0103] The present invention provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the above-described SQL statement generation method based on execution feedback iterative correction.

[0104] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0105] In the above embodiments, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0106] In the several embodiments provided in this application, it should be understood that the disclosed apparatus can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the mutual coupling or direct coupling or communication connection shown or discussed may be through some service interface; the indirect coupling or communication connection between apparatuses or units may be electrical or other forms.

[0107] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0108] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0109] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage device (CMD). Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a memory and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of the various embodiments of this application. The aforementioned memory includes various media capable of storing program code, such as USB flash drives, portable hard drives, magnetic disks, or optical disks.

[0110] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for generating SQL statements based on execution feedback iterative correction, characterized in that, Includes the following steps: S1 receives the user's query request and obtains the metadata information of the target database; S2 generates the current SQL statement based on the query request and metadata information; S3 pre-executes the current SQL statement in the database sandbox environment; S4 retrieves the pre-execution result. If the execution is successful, proceed to S7; otherwise, proceed to S5. S5 analyzes the error information in the pre-execution results, determines the error type, and selects the corresponding correction strategy based on the error type; S6 generates a corrected SQL statement based on the error information, correction strategy, and metadata information, uses the corrected SQL statement as the current SQL statement, and returns to S3; S7 outputs the current SQL statement that has passed verification as the target SQL statement.

2. The SQL statement generation method based on execution feedback iterative correction as described in claim 1, characterized in that, Step S3 includes the following sub-steps: S31 performs syntax analysis on the current SQL statement to determine whether it is a Data Manipulation Language (DML) statement; When S32 determines that it is a DML statement, it sends a transaction start command to the target database and establishes an isolated sandbox session; S33 executes the current SQL statement in the sandbox session and obtains the original execution response returned by the database; Regardless of the content of the original execution response, S34 sends a transaction rollback instruction to the target database through the sandbox session to ensure that all data changes are undone; S35 encapsulates the original execution response into a pre-execution result object, which contains an execution status identifier and response data, as input to step S4.

3. The SQL statement generation method based on execution feedback iterative correction as described in claim 1, characterized in that, Analyze the error information in the pre-execution results to determine the error type, including the following sub-steps: S51 extracts the error code and error message text returned by the database from the error information; S52 matches the error code with a predefined error type mapping table to determine the first-level error type. The error type includes at least one of the following: column not found, table not found, syntax error, and unique constraint conflict. S53 When the first level error type is column not found or table not found, extract the name identifier that is reported as invalid by the database from the error message text; S54 performs similarity matching between invalid name identifiers and the set of legal identifiers in the metadata information to generate a list of correction suggestions containing Top-K candidate identifiers.

4. The SQL statement generation method based on execution feedback iterative correction as described in claim 3, characterized in that, In step S54, similarity matching is performed using the following formula: ; Where A represents the invalid identifier extracted from the error message, B represents the valid identifier in the metadata information, S(A,B) represents the comprehensive similarity score between the invalid and valid identifiers, E(A,B) represents the edit distance similarity between the invalid and valid identifiers, G(A,B) represents the N-Gram similarity between the invalid and valid identifiers, K(A,B) represents the semantic keyword weight between the invalid and valid identifiers, and ω 1, ω2 and ω3 are adjustable weight coefficients, and ω1 + ω2 + ω3 = 1.

5. The SQL statement generation method based on execution feedback iterative correction as described in claim 1, characterized in that, Step S1, after receiving the user's query request and obtaining the target database's metadata information, also includes: S11 parses the field annotations in the metadata information to identify predefined business logic patterns; S12 analyzes the natural language intent of the query request and determines the type of data operation triggered by the intent; S13 generates at least one SQL generation constraint rule based on the matching result of the business logic mode and the data operation type; S14 uses the SQL generation constraint rules, the query request, and the metadata information together as input constraints for generating the current SQL statement in S2.

6. The SQL statement generation method based on execution feedback iterative correction as described in claim 1, characterized in that, Before step S3, the following are also included: S21 parses the current SQL statement to identify its operation type and structural features; S22 performs risk assessments on identified operational types and structural characteristics based on a predefined risk rule base. S23 generates execution control decisions based on the risk assessment results.

7. The SQL statement generation method based on execution feedback iterative correction as described in claim 1, characterized in that, Step S6 includes the following sub-steps: S61 constructs SQL correction generation instructions based on the error type and correction strategy, combined with metadata information; S62 executes the SQL correction generation instruction to generate corrected SQL statement candidates; S63 verifies the syntax compliance and policy compliance of the modified SQL statement candidate, and sets the verified modified SQL statement candidate as the new current SQL statement, and returns to S3 for the next round of iteration verification.

8. The SQL statement generation method based on execution feedback iterative correction as described in claim 1, characterized in that, It also includes iterative control steps: S81 sets the maximum number of iterations threshold and convergence criteria; After each execution of S6 to generate the revised SQL statement, S82 records the current iteration information; S83 evaluates whether to terminate the iteration early based on the convergence criteria; When the maximum number of iterations is reached or the convergence condition is met, S84 outputs the final result and an iteration analysis report.

9. A SQL statement generation system based on execution feedback iterative correction, characterized in that, A method for generating SQL statements based on execution feedback iteration correction as described in any one of claims 1-8, comprising: The request processing module is used to receive user query requests and obtain metadata information from the target database; The SQL generation module, connected to the request processing module, is used to generate the current SQL statement based on the query request and metadata information; The sandbox execution module, connected to the SQL generation module, is used to pre-execute the current SQL statement in the database sandbox environment and obtain the pre-execution result; An error analysis module, connected to the sandbox execution module, is used to analyze the error information in the pre-execution result when pre-execution fails, determine the error type, and select the corresponding correction strategy according to the error type; The correction generation module, connected to the error analysis module, is used to generate a corrected SQL statement based on the error information, correction strategy, and metadata information, and to feed the corrected SQL statement back to the SQL generation module as the new current SQL statement. An iteration control module is connected to the sandbox execution module, the error analysis module, and the correction generation module, respectively, and is used to control the iterative loop of sandbox execution, error analysis, and correction generation. The result output module, connected to the sandbox execution module, is used to output the verified current SQL statement as the target SQL statement when the pre-execution is successful.

10. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the SQL statement generation method based on execution feedback iterative correction as described in any one of claims 1-8.

Images