A data query method and system based on text recognition

Abstract

The application provides a data query method and system based on text recognition, and relates to the technical field of database query. The method comprises the following steps: constructing a knowledge base for storing power field database metadata; using a pre-trained vector embedding model to convert data table names, field names and Chinese descriptions into high-dimensional vectors and storing the association relationship between the high-dimensional vectors and the metadata; after obtaining a query request, using the pre-trained vector embedding model to convert the query request into a query vector, and obtaining a target field and a target description according to the query vector; combining a preset business logic rule to construct a dynamic prompt word; inputting the dynamic prompt word and the query request into a large language model to generate a thinking chain reasoning process and a target SQL statement; and finally executing a query task. The application improves the query efficiency.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database query, in particular to a data query method and system based on text recognition. BACKGROUND

[0002] In the operation and management of power systems, databases play a crucial role in storing vast amounts of power-related data, covering information from power generation, transmission, distribution, and consumption, such as equipment parameters, operating status, power metering, user information, etc. These data are indispensable for the safe and stable operation of power systems, dispatching decisions, fault troubleshooting, and customer service. With the continuous development and intelligent upgrading of power systems, data volume is growing exponentially, and data structures are becoming increasingly complex. How to quickly and accurately query the required data from such a large and complex database has become a key problem faced by the efficient operation and scientific management of power systems. Efficient data query methods can improve the response speed of power systems, providing timely and effective decision support for power dispatching and maintenance personnel, thereby ensuring the reliability and stability of power supply.

[0003] A Chinese invention patent with publication number CN115329047A discloses a method and system for regulating and controlling multi-element data comprehensive query, a computer device, and a medium. The scheme mainly uses natural language processing technology to obtain natural query language and performs natural language processing to identify entity information and query intent. Then, according to the query intent, the target rule is matched from the preset rule. Finally, based on the entity information and the target rule, data query and processing are performed in the preset database to obtain the query result.

[0004] This scheme relies on the coverage and accuracy of the preset rules. For queries that are not preset or unconventional, it may not be able to effectively handle them, leading to query failure or inaccurate results. SUMMARY

[0005] To improve query efficiency and convenience, the present application provides a data query method and system based on text recognition.

[0006] In the first aspect, the present application provides a data query method based on text recognition, which adopts the following technical solution:

[0007] A data query method based on text recognition includes the following steps:

[0008] A knowledge base storing power domain database metadata is constructed. A pre-trained vector embedding model is used to convert data table names, field names, and Chinese descriptions in the power domain database metadata into high-dimensional vectors. The association between each high-dimensional vector and the power domain database metadata is constructed and stored.

[0009] obtaining a query request in a query task, converting the query request into a query vector through a pre-trained vector embedding model, obtaining a target field and a target description based on the query vector, a high-dimensional vector and an association relationship, constructing a dynamic prompt word containing metadata and a preset power business logic rule based on the target field and the target description, and combining the preset power business logic rule;

[0010] inputting the dynamic prompt word and the query request into a large language model to generate a thought chain reasoning process and a target SQL statement, and executing the query task by using the target SQL statement.

[0011] The application can more accurately capture semantic information by converting the power field database metadata and the query request into high-dimensional vectors through the pre-trained vector embedding model, and using similarity calculation in the vector space, so as to find fields and descriptions that are semantically related but not completely consistent in expression, thereby improving the universality of the query result.

[0012] After obtaining the target field and the description, the application constructs a dynamic prompt word in combination with a preset power business logic rule, and then inputs the large language model to generate a target SQL statement, so that the generated target SQL statement not only conforms to the database structure, but also follows the business logic of the power field, thereby further improving the accuracy and practicality of the query result. The application uses vector embedding and similarity calculation to quickly locate the target field and the description related to the query request in a large-scale power field database metadata, reduces the cumbersome process of browsing and matching one by one, and shortens the query time.

[0013] The application automatically generates a thought chain reasoning process and a target SQL statement by using a large language model, thereby reducing the workload and error rate of manually writing SQL statements. For users who are not familiar with the database structure and SQL syntax, this method is more friendly and efficient, and can quickly obtain the required query result. The data in the power field is constantly updated and changed, and new data tables, fields and business rules will constantly appear. The application can flexibly adapt to these changes by constructing a knowledge base and a dynamic prompt word mechanism. When the database metadata is updated, only the vector embedding and the association relationship need to be reconstructed, without the need for large-scale modification of the entire query system; for new business logic rules, only adding or adjusting in the preset rule library can be applied in subsequent queries.

[0014] The application adopts a semantic-based query mode, which can support more diversified query requirements. Users can use natural language to describe query intentions without accurately mastering database structures and SQL syntax, and the application can automatically understand and generate corresponding query statements, which is very convenient for power business personnel without technical backgrounds and can improve their data acquisition efficiency. The thought chain reasoning process generated by the large language model can clearly show the generation process from the query request to the target SQL statement, helping users understand how the system reasons and decides based on the query intention and database structure, thereby improving user satisfaction.

[0015] Optionally, the target field and the target description are obtained based on the query vector, the high-dimensional vector, and the association relationship, including:

[0016] The similarity between the query vector and the high-dimensional vector corresponding to the data table name is calculated, N data tables are selected as a candidate table set based on the similarity, and the similarity between the query vector and the high-dimensional vector corresponding to the field name is calculated within the candidate table set to obtain the target field. The target description is obtained based on the association relationship and the target field.

[0017] The application converts the data table name, the field name, and the Chinese description into a high-dimensional vector through a pre-trained vector embedding model, and converts the query request into a query vector, thereby capturing the semantic information behind the text, reducing the omission caused by pure keyword accurate matching, and improving the fit degree of the query result and the user intention.

[0018] The application first selects data tables with high similarity to the query vector as a candidate table set, and then calculates the similarity between the query vector and the field name vector within this relatively small range to determine the target field. The step-by-step screening method reduces the search range, reduces interference factors, makes the positioning of the target field more accurate, and further improves the accuracy of the query result.

[0019] Optionally, after the target SQL statement is generated, the method further includes:

[0020] The target SQL statement is converted into an abstract syntax tree through a pre-set SQL parser, each node of the abstract syntax tree is traversed, and it is judged whether the target SQL statement involves time series data,

[0021] If yes, the abstract syntax tree is analyzed, and corresponding actions are taken based on the analysis result, including:

[0022] If there is no time constraint condition in the target SQL statement and it belongs to full-time domain query, the query task is blocked;

[0023] If the target SQL statement contains a time constraint condition and the time length of the time constraint condition is greater than the preset time window, a replacement statement of the target SQL statement is generated, and the replacement statement is updated as the target SQL statement, wherein the difference between the replacement statement and the target SQL statement is that the time length of the time constraint condition in the replacement statement is equal to the preset time window.

[0024] If not, no processing is performed.

[0025] The application can effectively prevent the overexposure of sensitive data by judging whether the target SQL statement is a full-time domain query without time constraints and blocking such tasks, so as to make the data query operation meet the safety specification and privacy protection requirements. For example, malicious users are prevented from obtaining user long-term power consumption data through full-time domain queries, and then analyzing user privacy information such as living habits.

[0026] The application helps to limit the time range of data query by setting a preset time window and adjusting the time constraint condition that exceeds the window, so as to avoid high system load or large data transmission volume due to too large query time span, and also meet the time limit for data access in the power industry.

[0027] Blocking the full-time domain query task can avoid unnecessary scanning and processing of a large amount of historical data, thereby saving computing resources and storage resources. When the time length of the time constraint condition is greater than the preset time window, the replacement statement adjusts the time range to the preset window, which can effectively control the data volume of the query result, reduce the data transmission volume, and improve the data transmission efficiency.

[0028] Optionally, the method further comprises:

[0029] Recording feedback data of the user on the execution result of the query task, dividing the target SQL statement into positive samples and negative samples according to the feedback data, the query request and the dynamic prompt word, and constructing a preference data set based on the positive samples and the negative samples;

[0030] Extracting a sample pair from the preference data set, the sample pair comprising one positive sample and one negative sample, and adjusting model parameters of the large language model by minimizing a loss function.

[0031] The application can more accurately capture user intent by recording feedback data of the user on the execution result of the query task and combining it with the query request and the dynamic prompt word to divide positive and negative samples. If the user feedback query result does not cover key fault information, the application can mark the related SQL statement as a negative sample, and subsequently adjust the model to make the generated SQL statement more accurately obtain fault details, thereby improving the matching degree of the query result and user demand.

[0032] The application constructs a preference dataset based on positive and negative samples, and extracts sample pairs therefrom to adjust the parameters of the large language model, so that the model can learn a SQL statement generation pattern that better meets user expectations and reduces the occurrence of incorrect or inaccurate query results. The model parameters are adjusted based on user feedback to enable the model to better understand the query habits and preferences of each user, thereby providing personalized query services to users and improving user satisfaction.

[0033] Optionally, the method further comprises:

[0034] The SQL statement of the negative sample is subjected to semantic perturbation processing, the processed negative sample is added to the training set to obtain a new training set, and an adversarial loss term is added to the loss function to obtain a new loss function.

[0035] In the field of power data query, the query request input by the user may have various forms of interference, such as spelling errors, ambiguous expressions, etc. The application can simulate these possible interference conditions by subjecting the negative sample SQL statement to semantic perturbation processing, so that the large language model is exposed to more diverse input samples during the training process, improving the model's tolerance to input interference in actual application, thereby improving the model's robustness.

[0036] The application can increase the number and diversity of negative samples by subjecting the negative samples to semantic perturbation processing and adding them to the training set, balance the distribution of positive and negative samples, and enable the large language model to more comprehensively learn the feature differences between positive and negative samples, avoid overfitting of the model to positive samples, and improve the model's training effect. The application can guide the model to pay more attention to samples that are easily misclassified by adding an adversarial loss term to the loss function. The negative samples subjected to semantic perturbation processing can provide more challenging samples, enabling the adversarial loss term to play a greater role. By optimizing the new loss function, the model can better learn the essential features of the data, improving the model's classification accuracy and stability.

[0037] Optionally, after converting the target SQL statement into an abstract syntax tree, the method further comprises:

[0038] Each node of the abstract syntax tree is traversed to determine whether the target SQL statement involves sensitive data. If so, the preset keywords are matched through a regular expression, and the operation risk level is marked according to the level to which the sensitive data belongs; if not, no processing is performed.

[0039] The application can accurately locate the part of the target SQL statement involving sensitive data by traversing the nodes of the abstract syntax tree, and avoid misjudgment of non-sensitive data as much as possible, so as to control the access to sensitive data and reduce the risk of data leakage. According to the operation risk level of the sensitive data, the application can set different permissions and approval processes for different data access operations. By marking the risk level of a large number of SQL statements, the data access risk of the power system can be comprehensively evaluated and analyzed. The frequency and trend of query operations of different risk levels are counted, potential security risks are found in time, and warning information is sent.

[0040] Optionally, when the target SQL statement involves sensitive data, the method further comprises:

[0041] If the operation risk level meets the expectation, the user ID, query period and device location are combined and encoded, and a real-time permission token is generated and sent based on the encoding result;

[0042] If the operation risk level does not meet the expectation, a data desensitization function is inserted into the target SQL statement.

[0043] When the operation risk level meets the expectation, a real-time permission token is generated. The application can achieve fine-grained permission management by combining and encoding the user ID, query period and device location, effectively preventing unauthorized access and reducing the risk of data leakage.

[0044] If the operation risk level does not meet the expectation, a data desensitization function is inserted into the target SQL statement, which can deform the sensitive data and improve the security of the data. The application can provide a certain degree of data service for users under the premise of ensuring data security. For some scenarios that do not require high data precision but have query requirements, such as statistical analysis of power users' electricity habits by market researchers, the data after inserting the desensitization function can meet their analysis needs and protect user privacy, so that users can obtain and use data in a safe environment, improving user satisfaction.

[0045] Optionally, the method further comprises: recording historical query requests and corresponding target SQL statements, and establishing a query vector-SQL mapping cache; when the cosine similarity of the query vector and the historical query vector in the cache exceeds a preset similarity threshold, the cached SQL statement is preferentially returned, and the query task is executed using the cached SQL statement.

[0046] The application establishes a query vector-SQL mapping cache, when a new query request has a cosine similarity with a historical query vector in the cache exceeding a preset threshold, the cached SQL statement can be directly returned and the query is executed without re-parsing and generating the SQL statement, thereby shortening the query response time and improving the query efficiency. The application caches commonly used SQL statements, reduces the actual query times of the database, reduces the usage rate of CPU, memory and other resources of the database server, reduces the risk of system performance degradation or even crash caused by excessively high query load, and improves the stability of the power data query system.

[0047] Optionally, before obtaining the target field and the target description based on the query vector, the high-dimensional vector and the association relationship, the method further comprises:

[0048] determining whether there is a function function corresponding to the query vector, if yes, calling the function function to execute the query task, and if no, not processing.

[0049] When there is a function function corresponding to the query vector, the application directly calls the function function to execute the query task, and skips the subsequent complex process of obtaining the target field and the target description based on the high-dimensional vector and the association relationship. By predefining and storing the corresponding function function, when the same query vector is encountered again, the application can quickly respond, shorten the query response time and improve the query efficiency.

[0050] In a second aspect, the application provides a data query system based on text recognition, which adopts the following technical scheme:

[0051] A data query system based on text recognition, comprising a processor and a memory in communication connection with the processor;

[0052] The memory is provided with a computer readable storage medium, and the computer readable storage medium stores a computer program;

[0053] When the processor processes the computer program stored on the computer readable storage medium, the method of the first aspect is realized.

[0054] In summary, the application has at least one of the following beneficial technical effects:

[0055] 1. The application converts the power field database metadata and the query request into high-dimensional vectors through a pre-trained vector embedding model, and uses similarity calculation in the vector space to more accurately capture semantic information, thereby finding fields and descriptions that are semantically related but not completely consistent in expression, and improving the universality of the query result.

[0056] 2. This application utilizes a large language model to automatically generate the thought chain reasoning process and target SQL statement, reducing the workload and error rate of manually writing SQL statements. For users unfamiliar with database structures and SQL syntax, this approach is more user-friendly and efficient, enabling them to quickly obtain the required query results. Data in the power sector is constantly updated and changing, with new data tables, fields, and business rules continuously emerging. This application, through the construction of a knowledge base and a dynamic prompting word mechanism, can flexibly adapt to these changes. When database metadata is updated, only vector embeddings and relationships need to be reconstructed, without requiring large-scale modifications to the entire query system; for new business logic rules, they can be added or adjusted in the preset rule base and applied in subsequent queries.

[0057] 3. This application employs a semantic-based query approach, supporting more diverse query needs. Users can describe their query intent using natural language, without needing precise knowledge of database structure and SQL syntax. The system automatically understands and generates corresponding query statements, which is highly convenient for power industry personnel without a technical background, improving their data acquisition efficiency. The reasoning process generated by the large language model clearly demonstrates the process from query request to target SQL statement generation, helping users understand how the system reasons and makes decisions based on query intent and database structure, thereby improving user satisfaction. Attached Figure Description

[0058] Figure 1 This is a flowchart of Embodiment 1 of this application;

[0059] Figure 2 This is a flowchart of Embodiment 2 of this application;

[0060] Figure 3 This is a flowchart of Embodiment 3 of this application. Detailed Implementation

[0061] The following combination Figures 1 to 3 This application will be described in further detail.

[0062] Example 1: This example discloses a data query method based on text recognition, referring to... Figure 1The method comprises: S11 data processing, S12 constructing a prompt word, and S13 generating an SQL statement. First, a knowledge base storing power field database metadata is constructed. A pre-trained vector embedding model is used to convert data table names, field names, and Chinese descriptions into high-dimensional vectors and store their association with metadata. After obtaining a query request, a pre-trained vector embedding model is used to convert it into a query vector, and the target field and target description are obtained accordingly. Dynamic prompt words are constructed in combination with preset business logic rules. The dynamic prompt words and the query request are input into a large language model to generate a thinking chain reasoning process and a target SQL statement. Finally, the query task is executed. The execution process of each step of the embodiment is as follows:

[0063] S11 data processing, the database metadata of the power field is different from the general business system, and has the characteristics of strong industry attribute, dense professional terms, and complex table / field association logic. In the embodiment, the database metadata of the power field comprises:

[0064] Data table name: such as power_grid_real_time_data, transformer_fault_record, and power_consumption_user.

[0065] Field name: the name of each column in the data table, used to identify the data type or attribute stored in the column, such as fault_type, grid_voltage, transformer_temperature, and user_monthly_kwh.

[0066] Chinese description: such as the Chinese description of power_grid_real_time_data, which is an electric grid real-time data table, the Chinese description of transformer_fault_record, which is a transformer fault record table, the Chinese description of power_consumption_user, which is an electricity user table, the Chinese description of fault_type, which is a fault type, the Chinese description of grid_voltage, which is an electric grid voltage, the Chinese description of transformer_temperature, which is a transformer temperature, and the Chinese description of user_monthly_kwh, which is a user monthly electricity consumption.

[0067] A knowledge base storing the above-mentioned power field database metadata is constructed.

[0068] In the embodiment, a pre-trained vector embedding model (such as bge-m3) is deployed on a local server through Hugging Face Transformers or a deployment tool (such as bge-inference) provided by BAAI.

[0069] The embodiment designs a three-level embedding strategy for the hierarchical structure of the power field database metadata, as follows:

[0070] The first-level embedding strategy is to embed the data table name in the passage mode to generate a table-level high-dimensional vector V_table, which is a 768-dimensional coarse-grained semantic vector.

[0071] The second-level embedding strategy is to splice the field name and the data table name to which the field belongs, and then embed it in the passage mode to generate a field-level high-dimensional vector V_field, which is a 768-dimensional medium-grained semantic vector.

[0072] The third-level embedding strategy is to perform word segmentation on the sentence in the Chinese description, and then generate a word-level high-dimensional vector V_token in the passage mode, which is a 768-dimensional fine-grained semantic vector.

[0073] In the knowledge base storing the power field database metadata, a vector type field is added, and the association between each high-dimensional vector and the power field database metadata is marked, and the vector is marked as the table-level high-dimensional vector V_table, the field-level high-dimensional vector V_field, or the word-level high-dimensional vector V_token output by bge-m3. In the embodiment, the added vector type field and the association between each high-dimensional vector and the power field database metadata are shown in Table 1.

[0074] Table 1

[0075] Vector ID 1001 1002 1003 Vector Type V_table V_field V_token Metadata Type Data Table Name Field Name Chinese Description Metadata Content transformer_fault fault_type Fault Type Association Table ID 0501 0501 0501 Association Field ID - 801 801

[0076] S12 constructs a prompt word and obtains a query request in a query task. When receiving a query request input by a user (such as querying the load rate of all main transformer high-voltage windings in a certain substation), a SearchDataVec module is called, the SearchDataVec module loads a pre-trained vector embedding model (such as bge-m3) by using SentenceTransformer, and converts the query request of the user into a high-dimensional vector, that is, a query vector.

[0077] The embodiment designs a two-level retrieval strategy for the multi-grained semantic vector of bge-m3, so that the power professional terms (such as main transformer high-voltage winding and load rate) and the metadata (data table name and field name) in the query request can be accurately matched.

[0078] Primary search: The cosine similarity algorithm is used to calculate the similarity between the query vector and the high-dimensional vector corresponding to the data table name. Based on the similarity from high to low, the data table names are sorted to obtain the top-N most relevant V_table (such as transformer_winding, substation, etc.). The top-N most relevant V_table is taken as the candidate table set.

[0079] Secondary search: Based on the candidate table set, all field-level high-dimensional vectors V_field are traversed. The cosine similarity algorithm is used to calculate the similarity between the query vector and the field-level high-dimensional vector. The field names with a similarity greater than the preset similarity threshold (such as load_ratio, winding_type, etc.) are selected as the target fields.

[0080] The field-level screening needs to be more stringent than the table-level screening. Because the power domain query needs precise fields (such as matching fault_type instead of fault_time when querying the fault type), the preset similarity threshold needs to be set to 0.9 or even a higher value in the secondary search process. In this embodiment, the preset similarity threshold is 0.95.

[0081] By setting the secondary search strategy, this embodiment can effectively filter irrelevant database schema information and only pass the core metadata into the large language model, thereby solving the problem of context window limitation of the large language model.

[0082] According to the associated field ID of the target field, the corresponding Chinese description, i.e., the target description, is queried in the knowledge base. For example, the associated field ID of the vector ID 1002 is 801, and the vector ID of the target description queried according to the associated field ID 801 is 1003.

[0083] Based on the target field and the target description, the dynamic prompt word containing the metadata and the preset power business logic rule is constructed by combining the preset power business logic rule. The construction process is as follows:

[0084] The SELECT * FROM V_table LIMIT 1 query is performed on the knowledge base to obtain a row of real data as a sample to inject the dynamic prompt word, so that the large language model can understand the real format of the field (for example, the time type field is in the '2023-10-01' format, and the value of the enumeration type field status is '2' instead of 'audit passed').

[0085] Pre-defined power business logic rules are forcibly injected into the dynamic prompts. For example, when defining the query process status in the code, the commitflag field in the spotcheck_apply table defaults to 1, infotype=2 represents remote signaling, and warninglevel=2 represents anomalies. The time filtering benchmark uses spotcheck_apply.applytime, etc.

[0086] S13 generates SQL statements by inputting dynamic prompts and query requests into a large language model (such as DeepSeek-V3 or Qwen-Max), generating the thought chain reasoning process and the target SQL statement, using an SQL parsing tool (such as sqlparse) to verify the structure of the target SQL statement, and connecting to the database through SQLAlchemy to execute the query task.

[0087] In this embodiment, the large language model sets temperature=0 during inference, disables sampling (do_sample=False), and sets top_k to 1, thereby outputting the answer with the highest probability. This improves the determinism and stability of the large language model's output and minimizes the possibility of divergence in the output results. To further enhance the large language model's ability to understand electrical engineering terminology, this embodiment employs a supervised fine-tuning (SFT) process, as follows:

[0088] (1) Dataset construction: Based on the historical query logs of the power system, construct a fine-tuning instruction dataset in the format of (Question, Context, Answer).

[0089] Example data:

[0090] {

[0091] "question": "Query the load rate of all main transformer high-voltage windings in a substation in a certain area",

[0092] "context": "The table transformer_winding contains fields load_ratio (load ratio), winding_type (winding type)...",

[0093] "answer": "SELECT load_ratio FROM transformer_winding WHEREstation='somewhere' AND winding_type='high'"

[0094] }

[0095] (2) Fine-tuning: The goal of fine-tuning is to enable large language models to map ambiguous concepts in natural language to specific status code combinations in the database and directly associate professional terms to corresponding calculation formulas or fields. The process is as follows:

[0096] Establish a static mapping relationship between fuzzy concepts and database metadata, such as mapping "reviewed" to status IN ('2', '2B', '3').

[0097] Utilize the semantic understanding capabilities of large language models to analyze the contextual meaning of fuzzy concepts and dynamically generate query conditions. For example, based on the display of high-value orders reviewed last week, generate a complex query statement containing time range (create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)) and amount condition (amount > 10000).

[0098] Bind business terms (such as load rate) directly to database fields (such as used_capacity and total_capacity) and associate calculation formulas ((used_capacity / total_capacity)*100).

[0099] For professional terms that require cross-table calculations (such as department average load rate), this embodiment associates multi-table data through SQL JOIN operations and applies calculation formulas.

[0100] If the query task is executed successfully, return the query result; if the query task fails, capture error information.

[0101] When the query task fails, this embodiment can also choose to perform automatic correction and retry. Specifically, the captured error information (such as syntax error, table / field does not exist, business logic conflict) will be fed back to the large language model, and a new SQL statement will be generated based on dynamic prompt words and query requests until the query is executed successfully or the maximum number of retries is reached.

[0102] The following further illustrates this embodiment with specific cases.

[0103] User input query request: How many abnormal remote signaling signals were added to a certain substation last month?

[0104] This embodiment recalls the spotcheck_info (signal record table), spotcheck_apply (application process table), and station (station table), with the Chinese descriptions of each data table in parentheses.

[0105] According to the pre-set power business logic rules, emphasize in the dynamic prompt words:

[0106] New signal needs to associate spotcheck_apply table to get time.

[0107] infotype field must be 2 (remote information).

[0108] warninglevel field must be 2 (abnormal).

[0109] Time filter reference uses spotcheck_apply.applytime.

[0110] The thinking chain generated by the large language model is as follows:

[0111] The number of user intent statistics needs to use COUNT (*).

[0112] Filter the station name by LIKE '% somewhere %'.

[0113] The time range of "last month" uses DATE_SUB and DATE_FORMAT functions to process the current time.

[0114] The main table spotcheck_apply and the sub-table spotcheck_info need to be connected.

[0115] The final generated target SQL statement is as follows:

[0116] SELECT COUNT (*) FROM grid_remark.spotcheck_info si JOIN grid_remark.spotcheck_apply sa ON si.applyid = sa.id WHERE si.infotype = 2 AND si.warninglevel = 2 AND sa.stationname LIKE '% somewhere %'AND sa.applytime >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01') AND sa.applytime < DATE_FORMAT(NOW(), '%Y-%m-01')

[0117] Use sqlparse to format the generated target SQL statement, and execute the query task through SQLAlchemy to connect the database.

[0118] Example 2: Refer to Figure 2The embodiment differs from embodiment 1 in that, after the target SQL statement is generated, the following steps are further included:

[0119] S21 constructs an abstract syntax tree, and converts the target SQL statement into an abstract syntax tree (AST) through a preset SQL parser.

[0120] Each node of the abstract syntax tree corresponds to a syntax unit of the target SQL statement, including a keyword, a table name, a field name, a condition expression, a function call, etc. The embodiment can accurately locate each component of the target SQL statement through the abstract syntax tree, and try to avoid errors caused by direct fuzzy matching of text strings.

[0121] The parsing process of the target SQL statement is as follows:

[0122] The target SQL statement to be processed is input, and the SQL parser performs lexical analysis on the target SQL statement, that is, the target SQL statement is split into lexical units (Token), such as SELECT, COUNT(*), FROM, grid_remark.spotcheck_info, etc.

[0123] The SQL parser combines the lexical units into hierarchical AST nodes according to the SQL syntax rules, for example:

[0124] The root node is SELECTStatement (SELECT statement);

[0125] The child nodes include SelectClause (query clause: COUNT(*)), FromClause (source clause: grid_remark.spotcheck_info), JoinClause (connection clause: JOIN grid_remark.spotcheck_applysa ON si.applyid = sa.id), WhereClause (condition clause: si.infotype = 2 AND...), etc.

[0126] Each child node can be further split (for example, WhereClause includes multiple Condition nodes).

[0127] S22 sensitivity judgment, which uses depth-first traversal (DFS) to traverse each node of the abstract syntax tree. In other embodiments, breadth-first traversal (BFS) algorithm can also be used to traverse each node of the abstract syntax tree.

[0128] The judgment on whether the target SQL statement involves sensitive data is performed by matching the node with a predefined sensitive data dictionary during the traversal of the abstract syntax tree node. If the matching is successful, it is determined that the target SQL statement involves sensitive data.

[0129] Exemplarily, the sensitive data dictionary in the embodiment is shown in Table 2.

[0130] Table 2

[0131] Sensitive Type Sensitive Table Name Sensitive Field Name User Power Consumption Data power_consumption_user user_iduser_monthly_kwh Equipment Fault Data transformer_fault_record fault_detailfault_time Substation Core Parameters substation_core_param grid_voltageload_ratio

[0132] If the target SQL statement involves sensitive data, the query operation (such as SELECT sensitive field and UPDATE sensitive table) on the sensitive data is further refined in the risk level by matching the preset keyword through a regular expression.

[0133] In the embodiment, the first-level risk is the non-query operation word, such as the words of modification and deletion; the second-level risk is the word involving the device model and device state; and the third-level risk is the word involving the user information, such as the user ID card number, account number, and account name.

[0134] In other embodiments, the operation risk level can also be set according to the needs.

[0135] For example, the preset keywords include DELETE (delete), UPDATE (modify), DROP (delete table), SELECT * (full field query), COUNT(user_id) (count the number of users), and the like. If the target SQL statement is DELETE FROM power_consumption_user WHERE user_id = '123', the DELETE keyword is matched, and the operation risk level is the first level.

[0136] A regular expression matching mode is written for each type of sensitive data. For example, the regular expression is designed in the embodiment as follows:

[0137] ID card number: \b[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|

[12] \d|3

[01] )\d{3}[\dXx]\b

[0138] Bank card number: \b(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3

[47] [0-9]{13}|3(?:0[0-5]|

[68] [0-9])[0-9]{11}|(?:2131|1800|35\d{3})\d{11})\b

[0139] Mobile phone number: \b1[3-9]\d{9}\b

[0140] Email: \b[\w.-]+@[\w.-]+\.\w+\b

[0141] Associated risk level: Label the corresponding risk level for each regular expression (e.g., ID number → three-level risk).

[0142] In this embodiment, the pre-set regular expression is matched with the pre-set keyword, and the level of sensitive data is determined by the level of the pre-set keyword. If the target SQL statement involves multiple levels, the highest level is the final result, and the operation risk level is marked according to the level of the sensitive data.

[0143] If the operation risk level meets the expectation, the user ID, query period, and device location are combined and encoded by a hash algorithm (such as SHA-256) to obtain an encoding result. In other embodiments, a custom encoding rule (such as user ID + query period + location ID) can also be used to generate an encoding result.

[0144] Based on the encoding result, a real-time permission token (such as a JWT token) containing permission information (such as accessible sensitive data range, operation validity period) is generated and sent to the database access layer. Only when the real-time permission token is verified to be valid, the target SQL statement is allowed to be executed.

[0145] If the operation risk level does not meet the expectation, a data desensitization function is inserted into the target SQL statement, as follows:

[0146] According to the type of sensitive data, select the corresponding desensitization function, for example:

[0147] Numeric data (such as power consumption, voltage value): mask_numeric (keep the first two digits, and replace the rest with *, such as 1200→12**);

[0148] String data (such as user name, device address): mask_string (keep the first and last characters, and replace the middle with *, such as XY substation→X* substation);

[0149] Date type data (such as fault time): mask_date (only keep year and month, hide day, such as 2025-12-18→2025-12-**);

[0150] Special desensitization function mask_grid_voltage in power field, which can keep voltage level and hide specific value, such as 110kV→110kV (desensitization).

[0151] The insertion method of desensitization function is realized by modifying the nodes of abstract syntax tree, for example:

[0152] Locate the ColumnRef node of sensitive field (such as user_monthly_kwh), replace the node with FunctionCall node, and the parameter is the original sensitive field mask_numeric(user_monthly_kwh), generate new SQL statement (such as SELECT mask_numeric(user_monthly_kwh) FROM power_consumption_user).

[0153] If the SQL statement does not involve sensitive data, no processing is done.

[0154] S23 timing judgment, time series data is the core data type in power field (such as real-time voltage data of power grid, transformer temperature time series data, user electricity time period data), this kind of data has the characteristics of large data volume, poor full time domain query performance, high frequency query easy to cause database pressure, therefore, it is necessary to make timing judgment based on abstract syntax tree and make corresponding processing.

[0155] Traverse each node of abstract syntax tree, judge whether the target SQL statement involves time series data, the specific process is as follows:

[0156] Use DFS / BFS algorithm to traverse abstract syntax tree, focus on the following nodes:

[0157] TableRef node: predefine time series table name (such as power_grid_real_time_data (power grid real-time data table), transformer_temperature_log (transformer temperature log table));

[0158] ColumnRef node: time field (such as sampling_time (sampling time), applytime (application time), fault_time (fault time));

[0159] WhereClause node: contains the expression of time condition (e.g. sampling_time >= '2025-12-01').

[0160] If the target SQL statement involves the time series table or references the time field and performs data query, it is determined that it involves time series data.

[0161] If the target SQL statement involves time series data, based on the WhereClause node (condition clause) and SelectClause node (query clause) of the abstract syntax tree, the existence and duration of the time constraint condition are analyzed, and corresponding actions are taken based on the analysis results, including:

[0162] If the target SQL statement does not contain any time constraint condition and queries the full data of the time series table, the query task is blocked by destination_hostgroup=99, and the intercepted target SQL statement is routed to an empty cluster or an error is returned, for example, error code 4003: prohibit full table scan time series table, please add time constraint condition.

[0163] If there is a time constraint condition in the target SQL statement, the time condition in the WhereClause node of the abstract syntax tree is parsed to calculate the duration of the time constraint, for example:

[0164] The content of the WhereClause node is: sampling_time >= '2025-12-01' AND sampling_time<= '2025-12-20', and the duration of the time constraint condition is 20 days.

[0165] If the duration of the time constraint condition is greater than the preset time window (e.g. 1 week), the time condition in the WhereClause node of the abstract syntax tree is modified to limit the duration to the preset time window, for example:

[0166] Original time condition: sampling_time >= '2025-12-01' AND sampling_time <= '2025-12-20';

[0167] Alternative time condition: sampling_time >= '2025-12-13' AND sampling_time <= '2025-12-20';

[0168] A new SQL statement (i.e. replacement statement) is generated based on the modified abstract syntax tree, and the original target SQL statement is replaced.

[0169] If the time constraint condition is a dynamic time (such as sampling_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)), the 30 DAY is directly modified into a preset time window (such as 7 DAY), and a replacement statement is obtained.

[0170] The replacement statement is different from the SQL statement in that the length of the time constraint condition in the replacement statement is equal to the preset time window.

[0171] If the target SQL statement contains a time constraint condition and the length of the time constraint condition is not greater than the preset time window, no processing is performed.

[0172] If the target SQL statement does not involve time series data, no processing is performed.

[0173] Embodiment 3: Refer to Figure 3 The method further comprises:

[0174] S31, a preference data set is constructed, feedback data of a user on a query task execution result is recorded, the SQL statement is divided into a positive sample and a negative sample according to the feedback data, the query request and the dynamic prompt word, the positive sample refers to the SQL statement with positive feedback data, and the negative sample refers to the SQL statement with negative feedback data, and the preference data set is constructed based on the positive sample and the negative sample.

[0175] The SQL statement in this step contains all target SQL statements generated in the historical query process.

[0176] S32, a sample pair is extracted in the preference data set, the sample pair includes one positive sample and one negative sample, the positive sample and the negative sample in the sample pair are SQL statements with different feedback data for the same query request and dynamic prompt word, that is, the sample pair contains one SQL statement with positive feedback data and one SQL statement with negative feedback data under the premise that the query request and the dynamic prompt word are the same.

[0177] The feedback data being positive refers to the SQL statement approved by the user, for example, the user feedback is satisfied or correct. The feedback data being negative refers to the SQL statement not approved by the user, for example, the user feedback is not satisfied or incorrect.

[0178] S33, an adversarial training set and a loss function are constructed, the SQL statement of the negative sample is subjected to semantic disturbance processing, including synonym replacement, near-synonym replacement and conditional replacement, the processed negative sample is added to the training set, and a new training set is obtained.

[0179] Exemplarily, the process of the semantic disturbance processing of the SQL statement of the negative sample is as follows:

[0180] Conditional replacement: replace the time range WHERE t>='2024-01-01' with WHERE t>='2023-01-01';

[0181] Field confusion: replace the field name of voltage with the synonym potential difference.

[0182] An adversarial loss term is added to the loss function to obtain a new loss function, and the calculation model of the new loss function is as follows:

[0183]

[0184] wherein, is an adversarial loss function; is an adversarial weight coefficient, the initial value is 0.1, and the value range is 0.1-0.3; is a negative sample after semantic disturbance processing; is a negative sample before semantic disturbance processing; is an expectation operation; is a similarity measure function, in the embodiment, is a negative sample after semantic disturbance processing and a negative sample before semantic disturbance processing edit distance.

[0185]

[0186] wherein, is an original loss function; is an expectation operation; is an activation function; is a logarithmic operation on the function value of the activation function; is a temperature coefficient, the value range is 0.2-0.5; is a logarithmic operation; is the output result of the large language model with unoptimized parameters; is the output result of the large language model after parameter optimization; is a positive sample; x is a query request.

[0187] is the probability of generating a positive sample by the large language model after parameter optimization given that the input is the query request x; is the probability of generating a positive sample by the large language model with unadjusted parameters given that the input is the query request x; is the probability of generating a negative sample by the large language model after parameter optimization given that the input is the query request x; is the probability of generating a negative sample by the large language model with unadjusted parameters given that the input is the query request x.

[0188] New loss function The calculation model is as follows:

[0189]

[0190] The following examples further explain the above steps.

[0191] Original negative sample: SELECT voltage FROM transformer WHERE capacity>100

[0192] Perturbation operation: Replace capacity>100 with capacity>1000

[0193] Generate negative samples after semantic perturbation. :SELECT voltage FROM transformer WHERECapacity>1000

[0194] Calculated using edit distance Negative samples after semantic perturbation Negative samples before semantic perturbation The edit distance is 1, and the similarity after normalization is 0.8. If the value of is 0.2, then the function value of the adversarial loss function is equal to 0.16.

[0195] The value is 0.3. It is 0.6. It is 0.5. It is 0.2. The value is 0.3, and the calculated loss value of the original loss function is 0.45.

[0196] The new loss function has a value of 0.45 + 0.16 = 0.61.

[0197] S34 adversarial training uses the gradient descent algorithm to calculate the gradient of the loss function with respect to the parameters of the large language model. It then uses backpropagation to adjust the parameters of the large language model and minimize the new loss function until the value of the new loss function no longer decreases or the preset number of iterations (such as 100 iterations) is reached, at which point training stops.

[0198] Example 4: This example differs from Example 1 in that the method further includes:

[0199] Record the history query request and the corresponding target SQL statement, and establish a query vector-SQL mapping cache; when the cosine similarity between the query vector and the historical query vector in the cache exceeds a preset similarity threshold (such as 0.95), the cached SQL statement is returned preferentially, and the query task is executed using the cached SQL statement.

[0200] The similarity between the query vector and the historical query vector in the cache is calculated by a cosine similarity algorithm.

[0201] For high-frequency and extremely complex logic queries (such as "generate a monthly inspection report"), it may be too complex and inefficient to directly generate a target SQL statement. Before obtaining the target field and target description based on the query vector, high-dimensional vector and association relationship, the embodiment further includes:

[0202] It is judged whether there is a function function corresponding to the query vector, if yes, the function function is called to execute the query task; if not, no processing is performed.

[0203] The embodiment pre-packages common function functions (Python methods), and writes a function description document for each common function and a semantic vector corresponding to each function description document.

[0204] When the user inputs a query request, the embodiment first obtains a query vector using a pre-trained vector embedding model, and calculates the semantic similarity between the query vector and each semantic vector.

[0205] If the semantic similarity is higher than a preset similarity threshold (such as 0.95), the corresponding common function function is directly called to return the processed structured data or chart, thereby reducing the risk of generating a complex SQL statement and realizing double-path response of query and function execution.

[0206] Embodiment 5: The embodiment discloses a data query system based on text recognition, which comprises a processor and a memory connected with the processor in communication;

[0207] The memory is provided with a computer readable storage medium, and the computer readable storage medium stores a computer program.

[0208] When the processor processes the computer program stored on the computer readable storage medium, the data query method based on text recognition can be realized.

[0209] The above are preferred embodiments of the present application, and do not limit the protection scope of the present application, therefore: all equivalent changes made according to the structure, shape, principle of the present application should be covered within the protection scope of the present application.

Claims

1. A data query method based on text recognition, characterized in that, include: A knowledge base storing metadata of power industry databases is constructed. A pre-trained vector embedding model is used to convert the table names, field names and Chinese descriptions in the metadata of power industry databases into high-dimensional vectors. The association between each high-dimensional vector and the metadata of power industry databases is constructed and stored. The system retrieves query requests from query tasks, converts them into query vectors using a pre-trained vector embedding model, and obtains target fields and descriptions based on query vectors, high-dimensional vectors, and relationships. Based on the target fields and descriptions, and combined with preset power business logic rules, the system constructs dynamic prompts containing metadata and preset power business logic rules. Input the dynamic prompts and query requests into the large language model to generate the thought chain reasoning process and the target SQL statement, and then execute the query task using the target SQL statement. After generating the target SQL statement, the method further includes: The target SQL statement is converted into an abstract syntax tree using a pre-defined SQL parser. Each node of the abstract syntax tree is traversed, and it is determined whether the target SQL statement involves time-series data. If so, the abstract syntax tree is analyzed, and corresponding actions are taken based on the analysis results, including: If the target SQL statement does not contain time constraints and is a full-time query, then the query task is blocked. If the target SQL statement contains a time constraint and the duration of the time constraint is greater than the preset time window, a replacement statement for the target SQL statement is generated and the replacement statement is updated to the target SQL statement. The difference between the replacement statement and the target SQL statement is that the duration of the time constraint in the replacement statement is equal to the preset time window. If not, no action will be taken; Determine whether the target SQL statement involves sensitive data. If so, match preset keywords using regular expressions and mark the operation risk level according to the sensitivity level of the data. If not, do not take any action.

2. The data query method based on text recognition according to claim 1, characterized in that, The process of obtaining target fields and target descriptions based on query vectors, high-dimensional vectors, and relationships includes: Calculate the similarity between the query vector and the high-dimensional vector corresponding to the table name. Based on the similarity, select N tables as a candidate table set. Within the candidate table set, calculate the similarity between the query vector and the high-dimensional vector corresponding to the field name to obtain the target field. Based on the association relationship and the target field, obtain the target description.

3. The data query method based on text recognition according to claim 1, characterized in that, The method further includes: Record user feedback data on the results of query task execution, and divide the target SQL statement into positive and negative samples based on the feedback data, query requests and dynamic prompts, and construct a preference dataset based on the positive and negative samples; Sample pairs, consisting of a positive sample and a negative sample, are extracted from the preference dataset. The model parameters of the large language model are then adjusted by minimizing the loss function.

4. The data query method based on text recognition according to claim 3, characterized in that, The method further includes: The SQL statements of the negative samples are semantically perturbed, and the processed negative samples are added to the training set to obtain a new training set. An adversarial loss term is added to the loss function to obtain a new loss function.

5. The data query method based on text recognition according to claim 1, characterized in that, When the target SQL statement involves sensitive data, the method further includes: If the operational risk level meets expectations, the user ID, query period, and device location will be combined and coded, and a real-time permission token will be generated and sent based on the coding result. If the operational risk level does not meet expectations, insert a data masking function into the target SQL statement.

6. The data query method based on text recognition according to claim 1 or 2, characterized in that, The method further includes: recording historical query requests and corresponding target SQL statements, and establishing a query vector-SQL mapping cache; when the cosine similarity between the query vector and the historical query vector in the cache exceeds a preset similarity threshold, the cached SQL statement is returned first, and the query task is executed using the cached SQL statement.

7. The data query method based on text recognition according to claim 6, characterized in that, Before obtaining the target field and target description based on query vector, high-dimensional vector, and association relationship, the method further includes: Determine if a function exists that corresponds to the query vector. If it does, call the function to execute the query task; otherwise, do nothing.

8. A data query system based on text recognition, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory is provided with a computer-readable storage medium, and a computer program is stored on the computer-readable storage medium. When the processor processes a computer program stored on the computer-readable storage medium, it implements the method as described in any one of claims 1-7.

Images