Method for updating and checking numerical precision of transaction business based on MySQL (Structured Query Language) database

Abstract

The invention relates to a method for updating and checking transaction business numerical precision based on a MySQL database, which comprises the following steps of: creating a python script, acquiring a MySQL database object, and establishing a query view; inputting an item ID and precision example data; defining a vernier, accessing the column table through the query view, and obtaining all data in the column table; traversing the column table in the cursor, searching each piece of business data with the field type being decimal, and updating the precision of the business data by taking the precision example data as a reference; and checking whether the precision of the business data is successfully updated. The problem that the business data precision updating efficiency is affected due to lengthy and complex transaction business process is avoided, the business data precision in the project can be quickly adjusted, the process of accessing the business data in the MySQL database object by the system is simplified, possible irrelevant function rule constraints during data searching are reduced, and the business data precision updating efficiency is improved. The service data precision adjustment efficiency is improved, and the adjusted service data precision can be automatically checked.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method for updating and checking the precision of numerical values ​​of transaction services based on a MySQL database. Background Art

[0002] In the current trading environment, numerical accuracy testing faces multiple challenges. First, the rapid iteration of product versions and the continuous updating and optimization of various modules have led to increasing system complexity. Project requirements, user interaction experience, and product security and stability have become key areas of improvement, significantly increasing the workload required for numerical accuracy testing. Differences between versions have impacted the uniformity and applicability of testing standards, introducing further uncertainty and difficulty into the testing process.

[0003] Secondly, the complexity of the transaction process is also a major challenge. The transaction process is lengthy and involves numerous business branches, with strong dependencies between upstream and downstream businesses. The transaction process involves multiple business modules, including bid acceptance, bid compilation, announcements, bid submission, bid opening, bid evaluation, bid determination, contracts, and archives. The business logic between these modules is complex and intertwined. During product evolution, even if the same business module is supported by different services and the underlying business is interconnected, the differences between modules are significant due to different product designs and implementation solutions. As a result, numerical accuracy testing cannot simply apply existing rules and requires targeted adjustments and optimization based on different situations.

[0004] For example, if the unit price data field type is defined in the database as DECIMAL(20,3), an input of 100.566 should be stored as 100.566. However, because the transaction process involves multiple business modules, such as bid acceptance, bid compilation, announcement, bid submission, bid opening, bid evaluation, bid determination, contracts, and archives, and these modules vary significantly, the problem may arise in a business module's query tool or front-end application, where data is limited to two decimal places by default. This causes the unit price data to be displayed as 100.56 during storage or output, failing to display all decimal places. Alternatively, rounding or other operations may be performed during data insertion in a business module, causing 100.56 to be stored or output as 100.57. Summary of the Invention

[0005] The purpose of the present invention is to provide a method for updating and checking the accuracy of numerical values ​​of transaction business based on MySQL database, which can automatically avoid the problem of affecting testing efficiency due to lengthy and complex transaction business processes, improve the execution efficiency of use cases of quotation data retrieval rules and accuracy checks of each business module, and reduce the consumption of time and labor costs.

[0006] The present invention is implemented through the following technical solution: a method for updating and checking the precision of transaction value classes based on a MySQL database, which includes the following steps:

[0007] Step 1: Create a Python script to obtain the MySQL database object, establish a query view, and reference various information in the MySQL database object in the query view, simplifying the process of the Python script accessing the information database built into the MySQL database object, information_schema;

[0008] Step 2: Enter the project ID and precision sample data decimal_temp for which the numerical precision needs to be updated in the Python script.

[0009] Step 3: Define the cursor table_list in the Python script. In the cursor table_list, access the column table in the information database information_schema through the query view to obtain all the data in the column table.

[0010] Step 4: In the cursor table_list, traverse the data in the column table, search for each business data with a decimal field type according to the project ID, and dynamically update the precision of each business data using the precision sample data decimal_temp as a reference value;

[0011] In step 5, use the web framework in the Python script to present the project ID and related business data in the MySQL database object as a visual structure to check whether the accuracy of the business data has been successfully updated.

[0012] Compared with the prior art, the present invention has the following beneficial effects:

[0013] 1. This invention avoids the problem of lengthy and complex transaction processes affecting the efficiency of business data accuracy updates. It can quickly adjust the accuracy of business data in the project, simplify the process of the system accessing business data in MySQL database objects, and reduce the constraints of irrelevant functional rules that may be encountered when searching for data. It not only improves the efficiency of business data accuracy adjustment, but also can automatically check the accuracy of the adjusted business data.

[0014] 2. The present invention utilizes dynamic SQL statements to achieve the effect of quickly covering different precision use cases on the same representative process use case, further improving the execution efficiency of precision update and precision check of business data in each business module. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 is a flow chart of the present invention;

[0016] Figure 2 This is the specific flow chart of the cursor table_list. DETAILED DESCRIPTION

[0017] The present invention is described in detail below with reference to the accompanying drawings, but the protection content of the present invention is not limited to the following:

[0018] like Figures 1 to 2 As shown in the figure, the method for updating and checking the precision of transaction numerical values ​​based on the MySQL database includes the following steps:

[0019] Step 1: Create a Python script to obtain the MySQL database object, establish a query view, and reference various information in the MySQL database object in the query view, simplifying the process of the Python script accessing the information database built into the MySQL database object, information_schema;

[0020] Step 2: Enter the project ID and precision sample data decimal_temp for which the numerical precision needs to be updated in the Python script.

[0021] Step 3: Define the cursor table_list in the Python script. In the cursor table_list, access the column table in the information database information_schema through the query view to obtain all the data in the column table.

[0022] Step 4: In the cursor table_list, traverse the data in the column table, search for each business data with a decimal field type according to the project ID, and dynamically update the precision of each business data using the precision sample data decimal_temp as a reference value;

[0023] In step 5, the project ID and its associated business data in the MySQL database object are visualized in a Python script using a web framework to check whether the business data accuracy has been successfully updated. This method can quickly adjust the accuracy of business data in a project, simplifying the system's access process for business data in MySQL and reducing the constraints of irrelevant functional rules that may be encountered when searching for data. This not only improves the efficiency of business data accuracy adjustment but also automatically checks the accuracy of the adjusted business data.

[0024] It should be noted that information_schema is a system database in the MySQL database object. information_schema can access all table information in the MySQL database object and is used to query and obtain detailed metadata about the MySQL database object, including all tables and their structures in the MySQL database object. However, due to the large amount of data, directly calling information_schema to find data takes a long time.

[0025] After referencing various information in the MySQL database objects in the query view, information_schema can more quickly find and access all table information in the database through the query view. Therefore, in step 3, the Python script first accesses the query view to simplify the query statement, and then accesses the column table in the information database information_schema to improve access efficiency.

[0026] The query view information includes project code, project name, project ID, package ID, package business status, package review status, bid evaluation version identifier, price format, number of old and new bid identifiers, unit price, total price, price rate, bid price, and bid evaluation price. Unit price, total price, price rate, bid price, and bid evaluation price are business data with a decimal field type.

[0027] Furthermore, the main fields of the query view are:

[0028]

[0029]

[0030] It should be noted that the present invention only updates and checks the precision. Taking the unit price data as an example: if the precision of the unit price data stored in the MySQL database object is displayed as 0.01 during the precision check, and the business data decimal_temp of the precision example data is 0.001, the precision of the unit price data in step 4 will be updated from 0.01 to 0.001; at the same time, in order to better prevent the precision of the unit price data from being incorrect during the display process or data insertion process, the decimal place of its precision display can be forcibly modified to 9, that is, the final precision will be displayed as 0.999;

[0031] Furthermore, the present invention does not modify the field type of the unit price data. If the field type of the unit price data is originally decimal(20, 2), the field type will still be retained as decimal(20, 2) in the MySQL database.

[0032] like Figure 2As shown, in step 4, it also includes the following steps:

[0033] Step 401: Define a loop statement in the cursor table_list to traverse the column table and search for each business data with a field type of decimal;

[0034] Step 402: Whenever a piece of business data with a decimal field type is found, the precision sample data decimal_temp is used as a reference value, and a dynamic SQL statement including the modification of the field data type is prepared into an executable format through a PREPARE statement;

[0035] Step 403: Execute the prepared SQL statement through the EXECUTE statement to update the precision of the business data pointed to by the cursor table_list;

[0036] Step 404 releases the resources occupied by each execution of the related dynamic SQL statement through the DEALLOCATE PREPARE statement. In the MySQL database object, the PREPARE statement + EXECUTE statement + DEALLOCATE PREPARE statement constitutes a complete dynamic SQL statement. Dynamic SQL statements allow SQL statements to be constructed and executed according to actual needs. This flexibility enables the dynamic generation of data reset scripts for different tables and fields, thereby handling different data reset requirements. In the present invention, the method of combining cursors with dynamic SQL statements can improve code reusability and reduce the need to repeatedly write and maintain reset scripts for different tables.

[0037] Furthermore, the parameter / variable information used in the cursor table_list is:

[0038] Parameter / variable name Parameter / variable type describe projectId varchar(50) Project ID decimal_temp decimal(12,2) Accuracy example data flag INT Cycle logo tableschema varchar(255) database tablename varchar(255) surface columnname varchar(255) Field @sql varchar Dynamic SQL

[0039] It should be noted that the parameter decimal(12,2) of the precision sample data decimal_temp is only used as an example. Its parameter can also be decimal(12,4). The specific parameters can be modified according to needs.

[0040] Furthermore, the step 5 further includes the following steps:

[0041] Step 501: In the Python script, the project ID and its related business data in the MySQL database object are first presented as an HTML page through the Web framework;

[0042] Step 502, then load the Selenium module to automatically obtain the table body in the HTML page and capture the project ID and its related business data;

[0043] Step 503: Finally, the precision sample data decimal_temp is used as a reference value to check whether the data precision of the relevant business data is updated successfully, and a precision check report is sent.

[0044] Optionally, the specific operations of step 501 are:

[0045] Define the get_token() function in the Python script. This method accepts the username and password as input, performs the web page login operation, and returns an access token. Then, based on the test environment or test data, use the driver.add_cookie method to modify the cookie to meet the requirements of checking the accuracy of business data.

[0046] The specific operations of step 502 are:

[0047] Use screenshot = driver.get_screenshot_as_png() method to take a screenshot of the page object under test, generate a file with the specified file name and save it to the specified path;

[0048] Then use the table.find_element() and tbody.find_elements() methods to locate the table body in the HTML page, and traverse all cells in the table body, use the row[].find_elements[] method to find each cell and store them in the cell space;

[0049] The specific operations of step 503 are:

[0050] Use the assert method to verify whether each data in the cell space is consistent with the precision sample data decimal_temp. Finally, use the pytest-html plug-in to generate a test report on the HTML page.

[0051] The web framework is a Flask framework or a Django framework based on Python. Using a web framework to present visual results is a conventional technique, so no further details are given. The present invention also incorporates a Selenium module to automatically obtain the form body in the HTML page, which enables automatic checking of whether the accuracy of the business data has been successfully updated after the accuracy has been updated.

[0052] Use the assert method to verify that the actual result is consistent with the expected result, and use the pytest-html plugin to generate an HTML test report.

[0053] Although the present invention is illustrated and described using specific embodiments and their alternatives, it should be understood that various changes and modifications can be implemented without departing from the spirit and scope of the present invention. Therefore, it should be understood that the present invention is not limited in any sense except by the appended claims and their equivalents.

Claims

1. A method for updating and checking the precision of numerical values ​​of transaction services based on MySQL database, characterized in that: It includes the following steps: Step 1, create a python script, obtain the MySQL database object, establish a query view and reference various information in the MySQL database object in the query view, simplifying the process of the python script accessing the information database information_schema built into the MySQL database object; Step 2: Enter the project ID and precision sample data decimal_temp whose numerical precision needs to be updated in the Python script; Step 3, define the cursor table_list in the Python script, and access the column table in the information database information_schema through the query view in the cursor table_list to obtain all the data in the column table; Step 4, traverse the data in the column table in the cursor table_list, search for each business data with a field type of decimal according to the project ID, and dynamically update the precision of each business data using the precision sample data decimal_temp as a reference value; Step 5: Use the Web framework in the Python script to present the project ID and its related business data in the MySQL database object as a visual structure, and check whether the accuracy of the business data is successfully updated.

2. The method for updating and checking the precision of numerical values ​​of transaction services based on MySQL database according to claim 1, characterized in that: The information of the query view includes project code, project name, project ID, package ID, package business status, package review status, evaluation version identification, price form and new and old quotation identification numbers, unit price, total price, price rate, bid price and evaluation price.

3. The method for updating and checking the precision of numerical values ​​of transaction services based on MySQL database according to claim 1 is characterized in that: In step 4, it further comprises the following steps: Step 401, define a loop statement in the cursor table_list to traverse the column table to search for each business data with a field type of decimal; Step 402, whenever a piece of business data with a field type of decimal is found, the precision sample data decimal_temp is used as a reference value, and a dynamic SQL statement including a modification of the field data type is prepared into an executable format through a PREPARE statement; Step 403, through the EXECUTE statement, execute the prepared SQL statement to update the precision of the business data pointed to by the cursor table_list; Step 404: Release the resources occupied by each execution of the related dynamic SQL statement through the DEALLOCATE PREPARE statement.

4. The method for updating and checking the precision of numerical values ​​of transaction services based on MySQL database according to claim 1 is characterized in that: The step 5 further comprises the following steps: Step 501, in the python script, firstly present the project ID and its related business data in the MySQL database object as an HTML page through the Web framework; Step 502, then load the Selenium module to automatically obtain the table body in the HTML page and capture the project ID and its related business data; Step 503: Finally, the precision sample data decimal_temp is used as a reference value to check whether the data precision of the relevant business data is updated successfully, and a precision check report is sent.

5. The method for updating and checking the precision of numerical values ​​of transaction services based on MySQL database according to claim 4 is characterized in that: The web framework is a Flask framework or a Django framework implemented based on Python.

Images