Text2SQL (Structured Query Language) system and method based on cue word

Abstract

The invention discloses a Text2SQL (Structured Query Language) system and method based on cue words. The system comprises a natural language input module for receiving and preprocessing a natural language query statement; the metadata management module is used for managing database metadata information; the cue word generation module is used for generating cue words containing database modes, user problems and SQL requirements on the basis of the query statements and the metadata; the large language model module is used for generating a preliminary SQL based on the cue word; the SQL grammar verification module is used for verifying grammar and feeding back errors; the manual auditing module is used for auditing and editing the SQL statements passing the verification; and the SQL execution module is used for executing the SQL passing the audit and returning a result. According to the method, the accuracy is improved by combining cue word guidance and a large model, the field adaptability is enhanced by relying on metadata, the data security is ensured by introducing manual auditing, the accuracy and security of SQL generation in a complex query scene are remarkably improved, and the development and maintenance cost of the system is reduced.

Description

Technical Field

[0001] This application belongs to the field of data governance and database query technology, and specifically relates to a Text2SQL system, method, computer-readable storage medium, and electronic device based on prompt words. Background Technology

[0002] In the field of Natural Language to SQL (Text2SQL) technology, early solutions focused on rule engines and semantic parsing, mapping natural language to SQL through pre-defined grammatical rules and semantic templates. With breakthroughs in deep learning and Large Language Models (LLM) technology, neural network-based Text2SQL systems have become mainstream. These systems leverage the understanding and analysis capabilities of pre-trained language models to generate SQL code. Trained on large-scale datasets of natural language problems and SQL statements, these systems learn the mapping rules between the two and can generate query statements based on input natural language. Currently, they can handle simple, routine queries and have been initially applied in specific fields and limited scenarios such as enterprise data analysis systems, improving data acquisition efficiency to some extent.

[0003] However, existing technologies still have the following defects and shortcomings: 1. Insufficient accuracy: For complex natural language scenarios involving multi-table joins, complex condition combinations, and nested queries, the model struggles to accurately understand user intent due to the diversity and ambiguity of natural language expressions. This results in generated SQL with syntax errors, logical deviations, or failure to meet actual needs, leading to low accuracy.

[0004] 2. Weak domain adaptability: Although general models can handle a wide range of problems, their professionalism and accuracy in specific fields such as finance, healthcare, and e-commerce are lacking; models trained in specific fields have a narrow scope of application, and their performance drops significantly when dealing with problems outside the specific field, making it difficult to balance generality and domain adaptability.

[0005] 3. Lack of manual review mechanism: Existing systems often directly generate and execute SQL statements without a proper manual review process. SQL execution is crucial to data security and integrity; unreviewed automatic execution may lead to serious consequences such as data leaks and erroneous updates.

[0006] 4. High technical threshold: Although it aims to lower the query threshold, non-technical personnel still need to have certain database knowledge and natural language expression ability to accurately express their requirements; and when problems occur in the generated SQL, users have difficulty in effectively debugging and modifying it.

[0007] 5. High development and maintenance costs: Building and maintaining an efficient and accurate system requires a lot of development work, covering data annotation, model training, optimization and deployment. As business develops and the database structure changes, the system needs to be continuously updated and adjusted, further increasing the workload of development and maintenance. Summary of the Invention

[0008] To address the aforementioned problems in the existing technology, this application proposes a novel Text2SQL system and method based on prompt words.

[0009] Terminology Explanation: Text2SQL, short for "Text to SQL," refers to the technology that converts natural language text into SQL (Structured Query Language) statements. Users describe their query requirements in natural language, and the system automatically converts them into SQL statements that can be executed in the database, enabling queries and operations on the database.

[0010] Large Language Model (LLM): An abbreviation for "Large Language Model," it is an artificial intelligence model based on deep learning. Trained on large-scale text data, it learns the syntax, semantics, and pragmatics of natural language, possessing powerful natural language understanding and generation capabilities. It can handle various natural language tasks such as text generation, question answering systems, and machine translation.

[0011] Metadata: Data about data, used to describe information such as the data's structure, content, source, quality, and usage. In databases, metadata includes table structure (such as field names, data types, primary keys, foreign keys, etc.) and relationships between tables.

[0012] Specifically, this application provides the following technical solutions: The first aspect of this application provides a Text2SQL system based on prompt words, the system comprising: The natural language input module is used to receive natural language query questions input by the user and to preprocess the natural language query questions; The metadata management module is used to collect, store, and manage database metadata information, which includes database table structure, field names, data types, and relationships between tables. The prompt word generation module, connected to the natural language input module and the metadata management module, is used to generate prompt words for the large language model based on the preprocessed natural language query question and the metadata information. The prompt words include database schema description, key information of the user question and SQL generation requirements. The large language model module is connected to the prompt word generation module and is used to generate preliminary SQL statements based on the prompt words; The SQL syntax validation module is connected to the large language model module and the prompt word generation module. It is used to perform syntax validation on the initial SQL statement. If the validation fails, it will send error information to the prompt word generation module. The manual review module, connected to the SQL syntax validation module, is used to receive SQL statements that have passed the syntax validation, allowing manual reviewers to check the logical correctness, business compliance, and data security, and supports manual modification. The SQL execution module is connected to the manual review module. After the manual review is passed, the SQL statement is sent to the database execution engine for execution and the execution result is returned.

[0013] Furthermore, in the system of this application, the preprocessing of the natural language input module includes removing special characters, unifying uppercase and lowercase formats, and converting punctuation marks.

[0014] Furthermore, in the system of this application, the prompt words generated by the prompt word generation module specifically include: a database table structure description, the original text of the user's natural language query question, the tables and fields that are restricted to use, and the SQL syntax specifications to be followed.

[0015] Furthermore, in the system of this application, the large language model module adopts one or more large language models from the GPT series models and the LlaMA series models.

[0016] Furthermore, in the system of this application, the SQL syntax verification module checks the keyword spelling, bracket matching, and field and table name accuracy of the preliminary SQL statement through the SQL syntax parser, and verifies the validity of the table name and field name in the metadata information.

[0017] Furthermore, in this application system, the manual review module provides a visual review interface, which allows reviewers to view the SQL statement to be reviewed, the associated natural language query statement and the corresponding database metadata information, and supports online editing and modification of the SQL statement; The manual review module is also used to record the reviewers' modification operations and review results, including whether the review is passed, failed, and the reason for the failure.

[0018] Furthermore, in the system of this application, the SQL execution module includes a database connection unit, an SQL execution unit, and a result formatting unit, wherein the result formatting unit is used to display the execution results in the form of tables or charts.

[0019] Furthermore, in this application system, the metadata management module periodically scans the database to update metadata information, ensuring that the metadata is consistent with the actual database structure.

[0020] A second aspect of this application provides a prompt-based Text2SQL method, applied to the aforementioned prompt-based Text2SQL system, such as... Figure 4 As shown, the method includes the following steps: S1. System Initialization: Configure the large language model parameters, connect to the database and obtain metadata information, and initialize the SQL syntax validator. The metadata information includes the database table structure, field names, data types and relationships between tables. S2. User Input and Processing: Receive natural language query questions input by the user and preprocess the natural language query questions; S3. Prompt word generation and optimization: Based on the preprocessed natural language query question and the metadata information, prompt words are generated. The prompt words include a database schema description, key information of the user question, and SQL generation requirements. The prompt word template is optimized based on historical generation records and user feedback. S4. SQL Generation and SQL Syntax Validation: Input the prompt words into the large language model to generate a preliminary SQL statement; validate the preliminary SQL statement through the SQL syntax validator; if the validation fails, adjust the prompt words according to the error information and regenerate the SQL statement until the validation passes or the maximum number of attempts is reached. S5. Manual Review Process: Submit the SQL statement that has passed the syntax check for manual review. Reviewers will check the logical correctness, business compliance, and data security, and manual modification is supported. If the review is successful, proceed to the next step; if the review fails, return to step S3 to regenerate the prompt words. S6. SQL Execution and Result Display: The SQL statements that have been manually approved are sent to the database execution engine for execution, and the execution results are formatted and displayed to the user.

[0021] Furthermore, in the method of this application: The preprocessing described in step S2 includes removing special characters, unifying uppercase and lowercase formats, and converting punctuation marks; The prompt word generation in step S3 specifically involves: parsing metadata to construct a table structure description, and integrating the table structure description, user questions, and SQL generation requirements according to the template; The SQL syntax verification in step S4 checks keyword spelling, bracket matching, and field and table name accuracy through the SQL syntax parser, and verifies the validity of table names and field names in the metadata information; The manual review in step S5 includes the reviewer viewing the SQL statement to be reviewed, the corresponding natural language questions and metadata information, directly editing and modifying it before submitting the review result; The result formatting process described in step S6 includes displaying the data list as a table and the statistical data as a chart.

[0022] A third aspect of this application provides an electronic device, including: a memory and a processor; Memory: Used to store computer programs; Processor: Used to execute the computer program to implement the steps of the aforementioned prompt-based Text2SQL method.

[0023] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the aforementioned prompt-based Text2SQL method.

[0024] In summary, compared with the prior art, the present invention has the following technical advantages: 1. Improve Generation Accuracy: By constructing a modular prompt word generation mechanism and integrating database metadata information to provide professional semantic guidance to the large language model, the quality of SQL generation in complex query scenarios is significantly improved. Simultaneously, a human-machine collaborative review mechanism is introduced to achieve secondary verification of the generated results, effectively avoiding syntax errors and logical deviations, and ensuring the correctness and business compliance of SQL statements.

[0025] 2. Enhance domain adaptability: Relying on the dynamic adaptation feature of the metadata management module, the system can parse the schema structure features and terminology system of different business domains, providing context-aware semantic understanding capabilities for vertical domains (such as finance, healthcare, etc.), thereby improving the accuracy of professional queries while ensuring generalization.

[0026] 3. Strengthen data security management and control: Establish a human-machine collaborative review process to avoid the risks of unauthorized access, data leakage and misoperation caused by the direct execution of unverified SQL statements, ensure the controllability and compliance of data operations, and protect the integrity and confidentiality of the database system.

[0027] 4. Lower the technical barrier for users: Supports query requests in natural language and automatically generates standardized SQL statements, allowing users to obtain data without needing to master database query languages ​​or underlying table structures. It also provides visual auditing and intervention capabilities, making it easy for non-technical personnel to understand, verify, and correct the generated results.

[0028] 5. Optimized System Maintainability: Based on a mature large language model-based suggestion engineering framework, the system avoids the high costs of large-scale data annotation and model retraining in traditional solutions. The system adopts a configuration-driven modular architecture, enabling rapid adaptation to heterogeneous database schema changes and business evolution, significantly reducing deployment and maintenance complexity.

[0029] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description

[0030] To more clearly illustrate the technical solutions of this application, the accompanying drawings involved in the description of the embodiments will be briefly introduced below. It should be noted that the drawings only show some embodiments of this application. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0031] Figure 1 This is a diagram showing the overall system architecture of the Text2SQL system based on prompt words in this application.

[0032] Figure 2 This is a flowchart illustrating the implementation of the Text2SQL method based on prompt words in this application.

[0033] Figure 3 This is a sequence diagram of the operation of the Text2SQL method based on prompt words in this application.

[0034] Figure 4 This is a flowchart illustrating the overall implementation of the Text2SQL method based on prompt words in this application.

[0035] Figure 5 This is a structural diagram of the Text2SQL system based on prompt words in this application.

[0036] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

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

[0038] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0039] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0040] Example: A Text2SQL system and method based on prompt words (I) Overall System Architecture Design This system adopts a layered architecture, such as Figure 1 and Figure 5 As shown, it includes seven core modules: 1. Natural Language Input Module: Responsible for receiving natural language query questions input by users and performing preliminary preprocessing on the text, including removing special characters and unifying capitalization, to improve the accuracy and consistency of subsequent processing.

[0041] 2. Prompt Generation Module: This module generates prompts for the large language model based on natural language input and database metadata. Metadata includes database table structure, field names, data types, and relationships between tables. The prompts are designed to guide the large language model in generating SQL statements that meet user needs and the database structure. The content includes a database schema description, key information about the user's question, and SQL generation requirements (such as restrictions on the tables and fields used, adherence to specific SQL syntax rules, etc.).

[0042] 3. Large Language Model Module: Employing advanced large language models such as the GPT series and LLaMA, this module generates preliminary SQL statements based on prompt words. After training on large-scale text data, the large language model possesses powerful natural language understanding and code generation capabilities. Through carefully designed prompt words, it can effectively convert natural language into SQL code.

[0043] 4. SQL Syntax Validation Module: Performs syntax validation on the initial SQL statements generated by the large language model. Using an SQL syntax parser, it checks the correctness of the statement's grammatical structure, including keyword spelling, bracket matching, and the accuracy of field and table names. If a syntax error is found, the error message is sent to the prompt word generation module so that it can adjust the prompt words and regenerate the SQL statement.

[0044] 5. Manual Review Module: This module presents SQL statements that have passed syntax validation to human reviewers. Reviewers can further examine and modify the statements to ensure they are logically correct, secure, reliable, and meet business requirements. They can also optimize and improve the statements based on their experience and understanding of the business.

[0045] 6. SQL Execution Module: After manual review and approval, the final SQL statement is sent to the database execution engine for execution. The execution result is returned to the user so that the user can obtain the required data query results.

[0046] 7. Metadata Management Module: Responsible for collecting, storing, and managing database metadata information, regularly updating metadata to reflect changes in the database structure, and providing accurate metadata support for the prompt word generation module to ensure that the generated prompt words are consistent with the actual database situation.

[0047] (II) Technical Implementation of Core Modules 1. Natural Language Input Processing: When users input natural language query questions into the system interface, the natural language input module cleans and preprocesses the text. For example, it converts "query the order volume in Shanghai in the past 7 days" into standard text format and removes extra spaces and special characters.

[0048] 2. Prompt Message Generation: The prompt message generation module combines natural language questions with metadata information to generate prompt messages. Assuming the database contains an "orders" table with fields such as "region," "create_time," and "order_amount," the prompt message for the above query could be designed as: "The database has a table named 'orders,' with fields including 'region,' 'create_time,' and 'order_amount.' Based on the user's requirement of 'querying the order volume in Shanghai for the past 7 days,' generate a query statement that conforms to SQL syntax, using only the relevant fields from the 'orders' table." A code example is shown below: def generate_prompt(natural_language, metadata): # Parse metadata to construct table structure description table_desc = [ ] for table in metadata['tables']: fields = [f"{f['name']}({f['description']})" for f in table['fields']] table_desc.append(f"There is a table {table['name']} in the database, with fields including {', '.join(fields)}") # Build prompt word template prompt_template = """ {table_description} Please generate a query statement that conforms to SQL syntax specifications based on the user's requirement '{user_query}', and it must meet the following requirements: 1. Use only the relevant fields from the table above. 2. The statement must include the necessary filtering conditions. 3. Follow standard SQL syntax

[0049] 3. SQL Generation: After receiving the prompt words, the large language model module uses its internal language understanding and generation mechanism to generate a preliminary SQL statement. For example, it generates "SELECT order_amount FROM orders WHERE region = 'Shanghai' AND create_time>= CURDATE () - INTERVAL 7 DAY". Code example is as follows: import openai # Taking GPT as an example, other models are similar def generate_sql(prompt, model="gpt-3.5-turbo"): response = openai.ChatCompletion.create( model=model, messages=[ {"role": "system", "content": "You are an SQL generation expert who can generate correct SQL statements based on the database structure"}, {"role": "user", "content": prompt} ], temperature=0.3 # Reduce randomness and improve stability ) return response.choices[0].message['content'].strip() # Generate SQL sql_statement = generate_sql(prompt) 4. Syntax Validation: The SQL syntax validation module uses an SQL syntax parser to validate the generated SQL statements. If syntax errors such as misspelled keywords or mismatched parentheses are found, the error message is sent to the prompt word generation module. The prompt word generation module adjusts the prompt words based on the error message, such as emphasizing the correct keyword usage and grammatical structure, and then resends it to the large language model module to generate the SQL statement. A code example is shown below: from sqlglot import parse_one, ParseError def validate_sql_syntax(sql, metadata): try: # Parse SQL statements parsed = parse_one(sql) # Verify table name existence tables_used = [t.name for t in parsed.find_all("Table")] valid_tables = [t['name'] for t in metadata['tables']] for table in tables_used: if table is not in valid_tables: return False, f"Table {table} does not exist. Valid table name: {valid_tables}" # Validate field existence (simplified version) columns_used = [c.name for c in parsed.find_all("Column")] all_columns = [ ] for table in metadata['tables']: all_columns.extend([f["name"] for f in table["fields"]]) for col in columns_used: if col is not in all_columns: return False, f"Field {col} does not exist. Valid fields: {all_columns}" Return True, "Syntax validation passed". except ParseError as e: return False, f"Syntax error: {str(e)}" # Validate SQL is_valid, error_msg = validate_sql_syntax(sql_statement, metadata) 5. Manual Review: SQL statements that pass syntax validation are sent to the manual review module. Reviewers examine the statements, checking their logical correctness and compliance with business requirements, such as the accuracy of query conditions and the presence of data security risks. If modifications are needed, reviewers can edit the statements directly on the review interface.

[0050] 6. SQL Execution and Result Return: SQL statements approved by manual review are sent to the SQL execution module, which then forwards them to the database execution engine for execution. The execution results are returned to the user, who can view the queried order quantity data on the system interface. Code example is as follows: import pymysql import pandas as pd class SQLExecutionModule: def __init__(self, db_config): self.db_config = db_config def execute_sql(self, sql): try: # Establish database connection conn = pymysql.connect(**self.db_config) # Execute query df = pd.read_sql(sql, conn) conn.close() # Formatting results return { "success": True, "data": df.to_dict('records'), Message: "Query successful" } except Exception as e: return { "success": False, "data": None, "message": f"Execution failed: {str(e)}" } # Example Configuration db_config = { "host": "localhost", "user": "data_user", "password": "secure_password", "database": "business_db", "charset": "utf8mb4 } # Execute the approved SQL execution_module = SQLExecutionModule(db_config) if review_result: approved_sql = [t for t in review_system.pending_reviews if t["task_id"] == task_id][0]["modified_sql"] result = execution_module.execute_sql(approved_sql) (III) Implementation process and details of the Text2SQL method based on prompt words The implementation process and details of this method are as follows: Figure 2 , 3As shown, it includes: 1. System Initialization: After system deployment, initialization settings are performed, including configuring large language model parameters, connecting to the database and obtaining metadata information, and initializing the SQL syntax validator. The metadata management module periodically scans the database and updates metadata information to ensure that the metadata used by the system is always consistent with the actual database structure.

[0051] 2. User Input and Processing: Users open the system interface and enter their natural language query question in the input box. The natural language input module captures the input in real time and performs preprocessing, such as converting Chinese punctuation marks to English punctuation marks, to meet subsequent processing requirements.

[0052] 3. Prompt Word Generation and Optimization: The prompt word generation module generates prompt words based on natural language questions and metadata. During the generation process, the prompt word template and content are optimized based on historical generation records and user feedback. If previously generated prompt words resulted in a high error rate in SQL statements, the module will adjust the prompt word structure and wording to improve the probability of the large language model generating correct SQL statements.

[0053] 4. SQL Generation and Multiple Attempts: The large language model module generates an SQL statement after receiving the prompt words. If the generated statement fails the SQL syntax validation module, the system automatically performs multiple attempts. During each attempt, the prompt word generation module adjusts the prompt words based on the previous error information, and the large language model module regenerates the SQL statement based on the new prompt words, until the statement passes validation or the maximum number of attempts is reached.

[0054] 5. Manual Review Process: SQL statements that pass syntax validation will proceed to the manual review module. After logging into the system, reviewers can view the statements to be reviewed and their corresponding natural language issues. They can also click to view detailed metadata information to assist in the review. Reviewers can directly modify the statements on the review interface and submit the review results after modification. If the review passes, the statement will proceed to the next execution step; if the review fails, the reviewer must fill in the reason for the failure, and the statement will be returned to the prompt word generation module for regeneration.

[0055] 6. SQL Execution and Result Display: SQL statements that have passed manual review are sent to the database execution engine for execution by the SQL execution module. After the execution results are returned, the system formats the results and displays them to the user in an intuitive way, such as displaying data lists in tables and statistical data in charts.

[0056] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0057] like Figure 6 As shown, embodiments of this application also disclose an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the above-described prompt-based Text2SQL method.

[0058] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0059] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types: Object-oriented programming languages, such as Java, Smalltalk, C++, etc. Conventional procedural programming languages, such as "C" or similar programming languages.

[0060] The execution methods of program code include, but are not limited to: It runs entirely on the user's computer; Part of it executes on the user's computer, and part of it executes on a remote computer; Execute as a standalone software package; It is executed entirely on a remote computer or server.

[0061] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0062] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the prompt-based Text2SQL method disclosed in this application.

[0063] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following: (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0064] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0065] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0066] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0067] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0068] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0069] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0070] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0071] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0072] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0073] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0074] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the prompt-based Text2SQL method disclosed in this application. When the computer program is executed by a processing device, it can achieve the functions defined in the embodiments of this application.

[0075] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0076] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A Text2SQL system based on prompt words, characterized in that, The system includes: The natural language input module is used to receive natural language query questions input by the user and to preprocess the natural language query questions; The metadata management module is used to collect, store, and manage database metadata information, which includes database table structure, field names, data types, and relationships between tables. The prompt word generation module, connected to the natural language input module and the metadata management module, is used to generate prompt words for the large language model based on the preprocessed natural language query question and the metadata information. The prompt words include database schema description, key information of the user question and SQL generation requirements. The large language model module is connected to the prompt word generation module and is used to generate preliminary SQL statements based on the prompt words; The SQL syntax validation module is connected to the large language model module and the prompt word generation module. It is used to perform syntax validation on the initial SQL statement. If the validation fails, it will send error information to the prompt word generation module. The manual review module, connected to the SQL syntax validation module, is used to receive SQL statements that have passed the syntax validation, allowing manual reviewers to check the logical correctness, business compliance, and data security, and supports manual modification. The SQL execution module is connected to the manual review module. After the manual review is passed, the SQL statement is sent to the database execution engine for execution and the execution result is returned.

2. The system according to claim 1, characterized in that, The preprocessing of the natural language input module includes removing special characters, unifying uppercase and lowercase formats, and converting punctuation marks.

3. The system according to claim 1, characterized in that, The prompt words generated by the prompt word generation module specifically include: a description of the database table structure, the original text of the user's natural language query question, the tables and fields that are restricted to use, and the SQL syntax specifications to be followed.

4. The system according to claim 1, characterized in that, The large language model module adopts one or more large language models from the GPT series and LlaMA series.

5. The system according to claim 1, characterized in that, The SQL syntax verification module checks the keyword spelling, bracket matching, and field and table name accuracy of the preliminary SQL statement through the SQL syntax parser, and verifies the validity of the table name and field name in the metadata information.

6. The system according to claim 1, characterized in that, The manual review module provides a visual review interface, allowing reviewers to view the SQL statements to be reviewed, the associated natural language query statements, and the corresponding database metadata information, and also supports online editing and modification of the SQL statements; The manual review module is also used to record the reviewers' modification operations and review results, including whether the review is passed, failed, and the reason for the failure.

7. The system according to claim 1, characterized in that, The SQL execution module includes a database connection unit, an SQL execution unit, and a result formatting unit. The result formatting unit is used to display the execution results in the form of tables or charts.

8. The system according to claim 1, characterized in that, The metadata management module periodically scans the database to update metadata information, ensuring that the metadata is consistent with the actual database structure.

9. A Text2SQL method based on prompt words, characterized in that, Applied to the Text2SQL system based on prompt words as described in any one of claims 1-8, the method includes the following steps: S1. System Initialization: Configure the large language model parameters, connect to the database and obtain metadata information, and initialize the SQL syntax validator. The metadata information includes the database table structure, field names, data types and relationships between tables. S2. User Input and Processing: Receive natural language query questions input by the user and preprocess the natural language query questions; S3. Prompt word generation and optimization: Based on the preprocessed natural language query question and the metadata information, prompt words are generated. The prompt words include a database schema description, key information of the user question, and SQL generation requirements. The prompt word template is optimized based on historical generation records and user feedback. S4. SQL Generation and SQL Syntax Validation: Input the prompt words into the large language model to generate a preliminary SQL statement; validate the preliminary SQL statement using an SQL syntax validator; if the validation fails, adjust the prompt words according to the error message and regenerate the SQL statement until the validation passes or the maximum number of attempts is reached. S5. Manual Review Process: Submit the SQL statement that has passed the syntax check for manual review. Reviewers will check the logical correctness, business compliance, and data security, and manual modification is supported. If the review is successful, proceed to the next step; if the review fails, return to step S3 to regenerate the prompt words. S6. SQL Execution and Result Display: The SQL statements that have been manually approved are sent to the database execution engine for execution, and the execution results are formatted and displayed to the user.

10. The method according to claim 9, characterized in that, In the method: The preprocessing described in step S2 includes removing special characters, unifying uppercase and lowercase formats, and converting punctuation marks; The prompt word generation in step S3 specifically involves: parsing metadata to construct a table structure description, and integrating the table structure description, user questions, and SQL generation requirements according to the template; The SQL syntax verification in step S4 checks keyword spelling, bracket matching, and field and table name accuracy through the SQL syntax parser, and verifies the validity of table names and field names in the metadata information; The manual review in step S5 includes the reviewer viewing the SQL statement to be reviewed, the corresponding natural language questions and metadata information, directly editing and modifying it before submitting the review result; The result formatting process described in step S6 includes displaying the data list as a table and the statistical data as a chart.

Images