A method and system for automatically generating a report based on a database

Abstract

The application provides a method for automatically generating a report based on a database, and relates to the technical field of data processing and report generation. The method comprises the following steps: S1, performing database connection configuration and environment initialization; S2, performing active clarification type semantic interaction and demand structure analysis operation; S3, performing SQL generation solidification and standardized checking operation; S4, evaluating SQL execution cost and performing dynamic flow limiting and fusing; S5, executing SQL based on a security sandbox and obtaining data; S6, performing intelligent visualization recommendation and report rendering; S7, performing report updating and maintenance. The beneficial effects are as follows: the active clarification type semantic interaction mechanism can greatly improve the natural language understanding accuracy; the SQL certainty generation method can eliminate randomness; the dynamic flow limiting strategy is adopted for the medium risk SQL, and the fusing interception mechanism is started for the high risk SQL, so that the query resource occupation, execution time length and returned data volume can be strictly controlled.

Description

Technical Field

[0001] This invention relates to the field of data processing and report generation technology, specifically to a method and system for automatically generating reports based on a database. Background Technology

[0002] In today's information age, organizations generate massive amounts of data in their daily operations and work. This data is typically stored in various databases, such as relational databases (MySQL, Oracle, etc.) and non-relational databases (MongoDB, Redis, etc.). To extract valuable information from this massive amount of data to support decision-making, business analysis, and results presentation, it is often necessary to organize the data in the database into intuitive and clear reports. Currently, report generation largely relies on manual operation. Staff need to manually query, filter, and format data from the database, and then create reports using tools such as Excel. This method is not only inefficient and prone to human error, but also has a high operational threshold for non-technical personnel, making it difficult to quickly and conveniently obtain the required reports. Therefore, there is an urgent need for a method that can automatically generate reports based on databases to solve the problems of low efficiency, error susceptibility, and high operational threshold in the existing report generation process. Summary of the Invention

[0003] The main objective of this invention is to provide a method and system for automatically generating reports based on a database, thereby solving the problems mentioned in the background section.

[0004] To solve the above-mentioned technical problems, the technical solution adopted by the present invention is: a method for automatically generating reports based on a database, comprising the following steps: S1. Configure database connection and initialize the environment; S2. Perform proactive clarification semantic interaction and demand structure parsing operations; S3. Perform SQL generation, solidification, and standardized verification operations; S4. Evaluate the SQL execution cost and perform dynamic rate limiting and circuit breaking; S5. Execute SQL and retrieve data based on a security sandbox; S6. Perform intelligent visual recommendations and report rendering; S7. Perform report updates and maintenance.

[0005] Furthermore, the detailed process of step S1 is as follows: The detailed process is as follows: S101, User enters database connection parameters; S102. Verify the validity of the parameters. The verification rules are as follows: the address format must conform to the IP or domain name specifications; the port number must be within the range of 0~65535 and be an integer; the username, password, and database name must not be empty, and special injection characters must be excluded to prevent SQL injection risks. If the verification fails, the system prompts the user to re-enter the correct connection parameters. If the verification passes, proceed to step S103. S103. The system stores the database connection information in the system configuration file and generates a unique identifier ID for the database so that the database connection can be quickly called during subsequent report generation. S104. Perform environment initialization operations.

[0006] Furthermore, the detailed process of step S2 is as follows: S201, User inputs report generation requirements; S202, Perform ambiguity and missing detection on the report generation requirements; S203, Execute the proactive clarification interaction process; The activation condition for the proactive clarification interaction process is: set a semantic integrity threshold, and activate the proactive clarification interaction process when the semantic integrity score is less than the semantic integrity threshold; The process of proactively clarifying interactions is as follows: The system generates standardized questions based on missing or ambiguous items. After the user completes the clarification, the system merges the completed information with the original requirements to form a complete, unambiguous, and structured standard requirement text. S204. Perform structured parsing of requirements. Specifically, the large model parses the standard requirements and outputs structured query objects, which are then used as the sole basis for SQL generation.

[0007] Furthermore, ambiguity and missing information detection is achieved using semantic integrity scores, which are expressed as follows: (1); in, The semantic integrity score. Scoring is based on the time dimension; Scoring based on indicator dimensions; Scoring based on grouping dimensions; Scoring based on filter criteria; Scoring is given for the logical dimension.

[0008] Furthermore, the detailed process of step S3 is as follows: S301, Perform SQL generation and solidification operations; SQL generation uses a deterministic mechanism, specifically: The structured query object is matched with the semantic-SQL fixed template library. When the matching result is a complete match, the template is directly called and parameters are filled to generate SQL. When the matching result is a partial match, the initial SQL is generated through the large model and stored in the temporary template library. This SQL generation mechanism can ensure that the same / similar requirements are forced to reuse the same template to ensure consistent results. The matching process between structured query objects and the semantic-SQL fixed template library is as follows: Find the historical structured query object with the highest similarity value to the structured query object from the semantic-SQL fixed template library as the reference object. Set a similarity threshold. When the similarity between the structured query object and its reference object is greater than the similarity threshold, it is a complete match; otherwise, it is an incomplete match. The expression for similarity value is as follows: (2); in, The similarity value is... The first element in the vector of the structured query objects corresponding to the current user query. dimensional components, The first vector of the comparison object dimensional components, For vector dimensions; S302. Standardize and calibrate the generated SQL; S303, Perform multi-dimensional standardized verification.

[0009] Furthermore, the detailed process of step S4 is as follows: S401. Extract the execution cost factors of the SQL; execution cost factors include: number of rows scanned, number of join tables, index hit value, aggregation complexity, and data volume level; S402, Calculate the execution cost; S403. Risk classification is based on the execution cost; risk levels include: low risk, medium risk, and high risk. S404. Dynamic current limiting and circuit breaking are implemented based on risk level, specifically as follows: For low-risk SQL queries, it runs normally; For medium-risk SQL queries, rate limiting rules are automatically applied. For high-risk SQL queries, the circuit breaker mechanism will be triggered directly.

[0010] Furthermore, the expression for the execution cost value is as follows: (3); in, To execute the value, , , , , These are respectively the number of rows scanned, the number of related tables, the index hit value, the aggregation complexity, and the data volume level.

[0011] Furthermore, the detailed process of step S6 is as follows: S601. Analyze the characteristics of the dataset; S602. Make visual recommendations based on matching rules; S603, Perform report rendering.

[0012] Furthermore, the detailed process of step S7 is as follows: S701. Perform automatic report updates; S702, Modify report configuration; S703, Report Version Management; S704, System Monitoring and Log Recording.

[0013] The present invention also provides a system for automatically generating reports based on a database. This system runs on a computer and is used in the steps of the aforementioned method for automatically generating reports based on a database.

[0014] Beneficial effects: (1) Adopting an active clarification semantic interaction mechanism and automatically detecting fuzziness, missing items and ambiguity based on semantic integrity score, it can actively initiate multiple rounds of clarification interaction to complete the user input when the user input is incomplete, the expression is vague or there is logical ambiguity, which greatly improves the accuracy of natural language understanding and avoids errors in report generation due to unclear requirements. (2) The deterministic SQL generation method based on vector similarity to achieve similar queries can eliminate the randomness of SQL generation in large models, ensure that SQL statements with consistent structure, unified logic and reproducible results are always generated for the same business needs, and significantly improve business credibility and system stability. (3) For medium-risk SQL, a dynamic rate limiting strategy is adopted, and a circuit breaker interception mechanism is initiated for high-risk SQL. This can strictly control the query resource consumption, execution time and returned data volume, prevent slow queries and large queries from dragging down the business database from the root, and ensure the stable operation of the production environment. Attached Figure Description

[0015] The present invention will be further described below with reference to the accompanying drawings and embodiments: Figure 1 This is a flowchart of the steps of the present invention. Detailed Implementation

[0016] Example 1 like Figure 1As shown in the figure, this embodiment provides a method for automatically generating reports based on a database, including the following steps: S1. Configure database connection and initialize the environment. The detailed process is as follows: S101. User enters database connection parameters; database connection parameters include: database type, connection address, port number, database name, username, password, connection mode, and connection pool parameters; Database types include, but are not limited to: MySQL, Oracle, PostgreSQL, SQL Server, MongoDB, Redis, etc. The connection address can be in the form of an IP address or a domain name; The valid range for port numbers is 0 to 65535; The connection mode is forced to use read-only mode by default; Connection pool parameters include: maximum number of connections, minimum number of idle connections, and timeout. S102. Verify the validity of the parameters. The verification rules are as follows: the address format must conform to the IP or domain name specifications; the port number must be within the range of 0~65535 and be an integer; the username, password, and database name must not be empty, and special injection characters must be excluded to prevent SQL injection risks. If the verification fails, the system prompts the user to re-enter the correct connection parameters. If the verification passes, proceed to step S103. S103. The system stores the database connection information in the system configuration file and generates a unique identifier ID for the database; S104. Perform environment initialization operations; Environment initialization includes: deploying a reporting system that supports AI assistants on the server; and accessing the API Key and endpoint address provided by the AI ​​large model API in the system management backend.

[0017] S2. Perform proactive clarification-based semantic interaction and structured requirement parsing operations. The detailed process is as follows: S201. User input report generation requirements; report input methods include: voice input and text input; S202, Perform ambiguity and missing detection on the report generation requirements; Ambiguity and missing information detection is achieved using semantic integrity scores, which are expressed as follows: (1); in, The semantic integrity score. The time dimension score is 1 for completeness and 0 for omission. For each indicator dimension, a score of 1 is given for explicitness, 0.5 for ambiguity, and 0 for missing. For the grouping dimension score, 1 is assigned if the information is explicit and 0 is assigned if it is missing. The score is determined by the filtering condition: 1 if the condition exists, and 0 if it is missing. For the logical dimension, a score of 1 is given for clear logic, 0.5 for logic that can be deduced by default, and 0 for logic that is missing or ambiguous. For ease of understanding, feature words for each dimension are provided. It should be noted that the feature words listed here are not all feature words, but rather some examples. Feature words for the time dimension include: this year, last month, recent, etc.; feature words for the indicator dimension include: sales data, performance, etc.; feature words for the grouping dimension include: region, product, department, store, etc.; feature words for filtering conditions include: status, type, channel, etc. S203, Execute the proactive clarification interaction process; The activation condition for the proactive clarification interaction process is: set a semantic integrity threshold, and activate the proactive clarification interaction process when the semantic integrity score is less than the semantic integrity threshold; The process of proactively clarifying interactions is as follows: The system generates standardized questions based on missing or ambiguous items. After the user completes the clarification, the system merges the completed information with the original requirements to form a complete, unambiguous, and structured standard requirement text. Standardized question formats include: single-choice, multiple-choice, and fill-in-the-blank formats; S204. Perform structured parsing of requirements, specifically: the large model parses the standard requirements and outputs structured query objects, which are then used as the sole basis for SQL generation. Structured query objects include: database ID, list of related tables, indicator fields, grouping dimensions, filtering conditions, sorting rules, and calculation rules.

[0018] S3. Perform SQL generation, solidification, and standardized validation operations. The detailed process is as follows: S301, Perform SQL generation and solidification operations; SQL generation uses a deterministic mechanism, specifically: The structured query object is matched with the semantic-SQL fixed template library. When the matching result is a complete match, the template is directly called and parameters are filled to generate SQL. When the matching result is a partial match, the initial SQL is generated through the large model and stored in the temporary template library. This SQL generation mechanism can ensure that the same / similar requirements are forced to reuse the same template to ensure consistent results. The matching process between structured query objects and the semantic-SQL fixed template library is as follows: Find the historical structured query object with the highest similarity value to the structured query object from the semantic-SQL fixed template library as the reference object. Set a similarity threshold. When the similarity between the structured query object and its reference object is greater than the similarity threshold, it is a complete match; otherwise, it is an incomplete match. The expression for similarity value is as follows: (2); in, The similarity value is... The first element in the vector of the structured query objects corresponding to the current user query. dimensional components, The first vector of the comparison object dimensional components, For vector dimensions; S302. Standardize and calibrate the generated SQL; The standardized format is as follows: keywords are capitalized; field and table names are uniformly enclosed in backticks or square quotes; indentation is consistent and line breaks are standardized; redundant spaces, blank lines, and comments are removed; the standardized SQL is more readable, easier to verify, and easier to log audit. S303, Perform multi-dimensional standardized verification; The verification dimensions include: Syntax validation to ensure that the SQL conforms to the SQL syntax specifications of the target database; Logical validation is performed to ensure that the grouping fields match the aggregate functions, the association conditions are valid, and the filtering conditions do not conflict. Permission verification is implemented to ensure that only SELECT queries are allowed, and operations such as DELETE, UPDATE, INSERT, ALTER, and DROP are prohibited. Field validation is performed to ensure that the fields exist in the schema knowledge base and that there are no illegal fields. Injection verification is performed to ensure that there are no dangerous functions or injection signature characters.

[0019] S4. Evaluate the SQL execution cost and implement dynamic rate limiting and circuit breaking. The detailed process is as follows: S401. Extract the execution cost factors of the SQL statement. Execution cost factors include: number of rows scanned, number of joined tables, index hit rate, aggregation complexity, and data volume level. Specifically: number of rows scanned represents the expected number of rows to be read; number of joined tables represents the number of tables joined; index hit rate is 1 when the index is hit, and 0 when it is not hit; aggregation complexity is 1 for simple aggregations and 2 for multi-level nesting; data volume level is 1 for small data volumes, 2 for medium data volumes, and 3 for large data volumes. S402, Calculate the execution cost; The expression for the execution cost value is as follows: (3); in, To execute the value, , , , , These are respectively: number of rows scanned, number of related tables, index hit value, aggregation complexity, and data volume level; S403. Risk classification is based on the execution cost; risk levels include: low risk, medium risk, and high risk. The grading criteria are as follows: When the execution cost is less than 3, it is considered low risk; When the execution cost is greater than 6, it is considered high-risk. Other situations are classified as medium risk; S404. Dynamic current limiting and circuit breaking are implemented based on risk level, specifically as follows: For low-risk SQL queries, it runs normally; For medium-risk SQL queries, the following rate-limiting rules are automatically implemented: maximum number of returned rows: no more than 10,000 rows; execution timeout no more than 5 seconds; a maximum of one medium-risk query per user; reduced CPU and memory allocation weight. For high-risk SQL queries, the circuit breaker mechanism is triggered directly. Specifically, the query is intercepted and a message is returned: "The current query data volume is too large and the execution cost is too high. The security circuit breaker has been triggered. Please narrow down the query scope." If the same user triggers the circuit breaker multiple times in a short period of time, their query privileges are temporarily restricted for 5 minutes. The circuit breaker record is written to the log for administrator auditing.

[0020] S5. Execute SQL and retrieve data based on a security sandbox; The security sandbox includes: the permissions sandbox and the syntax sandbox; The permission sandbox is used to restrict the executing account to execute with only read-only permissions; The syntax sandbox is used to re-verify dangerous operations, ensuring that no write, delete, or modify commands are executed. Data retrieval is related to the SQL execution result: when execution is successful, a standard dataset is returned, including field names, field types, and data rows; when execution fails, an error code and reason are returned, including: syntax error, insufficient permissions, timeout, and circuit breaker.

[0021] S6. Perform intelligent visualization recommendations and report rendering. The detailed process is as follows: S601. Analyze the characteristics of the dataset, including: Dimension types: time, region, category, numerical; Number of indicators: single indicator, multiple indicators; Data trends: stable, increasing, declining, fluctuating; S602. Make visual recommendations based on matching rules; The matching rules are as follows: For time-based metrics, use line charts or area charts. Use bar charts or column charts to combine category dimensions and indicators; Percentage indicators should be plotted using pie charts or donut charts. S603, Perform report rendering; Supported rendering formats include: Excel, PDF, HTML, images, and large-screen components; Customizable report content includes: title, font, color, header, pagination, watermark, and statistical summary.

[0022] S7. Perform report updates and maintenance. The detailed process is as follows: S701. Perform automatic report updates; specifically, for reports with a set timed generation frequency, the system automatically triggers steps S1 to S6 according to the preset time point, re-extracts the latest data from the database, generates a new report file, and outputs it according to the output method configured by the user, thereby realizing automatic report updates and ensuring that the report data obtained by the user is always the latest data. The time point can be a fixed frequency or a specific time. S702. Modify report configuration; Specifically: When a user's report requirements change, the user can find the corresponding report in the report configuration interface and modify the configuration parameters such as the report's basic information, data source, data filtering conditions, data statistics and calculation rules, report display format, and report output method; The system verifies the user's modified configuration parameters, and after successful verification, updates the report configuration information stored in the system; When the report generation operation is triggered again, the system will generate the report according to the modified configuration parameters; S703, Report Version Management; Specifically, the system manages the version of each modified report configuration and generated report file, recording information such as the modification time, modifier, modification content, and report file storage path for each version; users can query the historical version information of reports and support restoring to a previous historical version, making it convenient for users to trace the modification process of reports and avoid problems such as reports failing to be generated normally or data being inaccurate due to configuration modification errors. S704, System Monitoring and Log Recording: The system monitors each stage of the report generation process in real time. When an abnormal situation occurs, the system will issue an early warning. At the same time, the system records the operation logs in detail during the report generation process, including operation time, operator, operation content, operation result, abnormal information, etc. The log information is stored in the system's log database to facilitate system administrators in troubleshooting and problem analysis.

[0023] Example 2 This embodiment provides a system for automatically generating reports based on a database. The system runs on a computer and is used to implement the steps in the method for automatically generating reports based on a database in Embodiment 1.

[0024] The above embodiments are merely preferred technical solutions of the present invention and should not be considered as limitations on the present invention. The scope of protection of the present invention should be limited to the technical solutions described in the claims, including equivalent substitutions of the technical features described in the claims. That is, equivalent substitutions and improvements within this scope are also within the scope of protection of the present invention.

Claims

1. A method for automatically generating reports based on a database, characterized in that, Includes the following steps: S1. Configure database connection and initialize the environment; S2. Perform proactive clarification semantic interaction and demand structure parsing operations; S3. Perform SQL generation, solidification, and standardized verification operations; S4. Evaluate the SQL execution cost and perform dynamic rate limiting and circuit breaking; S5. Execute SQL and retrieve data based on a security sandbox; S6. Perform intelligent visual recommendations and report rendering; S7. Perform report updates and maintenance.

2. The method for automatically generating reports based on a database according to claim 1, characterized in that, The detailed process of step S1 is as follows: The detailed process is as follows: S101, User enters database connection parameters; S102. Verify the validity of parameters. The verification rules are as follows: the address format must conform to the IP or domain name specifications; the port number must be within the range of 0~65535 and be an integer; the username, password, and database name must not be empty, and special injection characters must be excluded to prevent SQL injection risks. If the verification fails, the system prompts the user to re-enter the correct connection parameters; if the verification succeeds, step S103 is executed. S103. The system stores the database connection information in the system configuration file and generates a unique identifier ID for the database; S104. Perform environment initialization operations.

3. The method for automatically generating reports based on a database according to claim 1, characterized in that, The detailed process of step S2 is as follows: S201, User inputs report generation requirements; S202, Perform ambiguity and missing detection on the report generation requirements; S203, Execute the proactive clarification interaction process; The activation condition for the proactive clarification interaction process is: set a semantic integrity threshold, and activate the proactive clarification interaction process when the semantic integrity score is less than the semantic integrity threshold; The process of proactively clarifying interactions is as follows: The system generates standardized questions based on missing or ambiguous items. After the user completes the clarification, the system merges the completed information with the original requirements to form a complete, unambiguous, and structured standard requirement text. S204. Perform structured parsing of requirements. Specifically, the large model parses the standard requirements and outputs structured query objects, which are then used as the sole basis for SQL generation.

4. The method for automatically generating reports based on a database according to claim 3, characterized in that, Ambiguity and missing information detection is achieved using semantic integrity scores, which are expressed as follows: （1）； in, The semantic integrity score. Scoring is based on the time dimension; Scoring based on indicator dimensions; Scoring based on grouping dimensions; Scoring based on filter criteria; Scoring is given for the logical dimension.

5. The method for automatically generating reports based on a database according to claim 1, characterized in that, The detailed process of step S3 is as follows: S301, Perform SQL generation and solidification operations; SQL generation uses a deterministic mechanism, specifically: The structured query object is matched with the semantic-SQL fixed template library. When the matching result is a complete match, the template is directly called and parameters are filled to generate SQL. When the matching result is a partial match, the initial SQL is generated through the large model and stored in the temporary template library. This SQL generation mechanism can ensure that the same / similar requirements are forced to reuse the same template to ensure consistent results. The matching process between structured query objects and the semantic-SQL fixed template library is as follows: Find the historical structured query object with the highest similarity value to the structured query object from the semantic-SQL fixed template library as the reference object. Set a similarity threshold. When the similarity between the structured query object and its reference object is greater than the similarity threshold, it is a complete match; otherwise, it is an incomplete match. The expression for similarity value is as follows: （2）； in, The similarity value is... The first element in the vector of the structured query objects corresponding to the current user query. dimensional components, The first vector of the comparison object dimensional components, For vector dimensions; S302. Standardize and calibrate the generated SQL; S303, Perform multi-dimensional standardized verification.

6. The method for automatically generating reports based on a database according to claim 1, characterized in that, The detailed process of step S4 is as follows: S401. Extract the execution cost factors of the SQL; execution cost factors include: number of rows scanned, number of join tables, index hit value, aggregation complexity, and data volume level; S402, Calculate the execution cost; S403. Risk classification is based on the execution cost; risk levels include: low risk, medium risk, and high risk. S404. Dynamic current limiting and circuit breaking are implemented based on risk level, specifically as follows: For low-risk SQL queries, it runs normally; For medium-risk SQL queries, rate limiting rules are automatically applied. For high-risk SQL queries, the circuit breaker mechanism will be triggered directly.

7. The method for automatically generating reports based on a database according to claim 6, characterized in that, The expression for the execution cost value is as follows: （3）； in, To execute the value, , , , , These are respectively the number of rows scanned, the number of related tables, the index hit value, the aggregation complexity, and the data volume level.

8. The method for automatically generating reports based on a database according to claim 1, characterized in that, The detailed process of step S6 is as follows: S601. Analyze the characteristics of the dataset; S602. Make visual recommendations based on matching rules; S603, Perform report rendering.

9. The method for automatically generating reports based on a database according to claim 1, characterized in that, The detailed process of step S7 is as follows: S701. Perform automatic report updates; S702, Modify report configuration; S703, Report Version Management; S704, System Monitoring and Log Recording.

10. A system for automatically generating reports based on a database, characterized in that, The system runs on a computer and is used to implement the steps of the method for automatically generating reports based on a database as described in any one of claims 1 to 9.

Images