Database field importing method and device, equipment and storage medium

Abstract

The invention provides a database field importing method and device, equipment and a storage medium, and the method comprises the steps: in an importing preparation stage, defining a standardized Excel data template, and defining a field mapping region in the standardized Excel data template; specifying a database field to be imported in the field mapping area; in an import execution stage, reading the to-be-imported database field based on the field mapping region, and generating a database insertion statement based on the to-be-imported database field; and executing the database insertion statement to import the to-be-imported database field into a database table. By adopting the method, the database field importing efficiency and flexibility are improved, and the workload of development and maintenance is reduced.

Description

Technical Field

[0001] The present invention relates to the field of computer data processing, and in particular to a method, device, equipment and storage medium for importing database fields. Background Art

[0002] In the manufacturing execution system, the basic tables and business tables carry a large amount of data necessary for the operation of the system. Before the system goes online and during daily production operations, the import of this data is an indispensable link. Excel is usually used as a data template to simplify the preparation and organization of data. The data in these Excel data templates, as the core information for the operation of the system, is of vital importance for its accuracy and completeness.

[0003] The traditional data import method usually involves writing an independent import program for each Excel template and defining specific DTO (Data Transfer Object) and Model objects for each template. The DTO object is used to encapsulate the column data in the Excel template, while the Model object is used to encapsulate the data to be written into the database table. By writing a program that maps the relationship between DTO and Model, the data in the Excel template can be imported into the database table.

[0004] However, in the research, it was found that this method would lead to a large number of DTO and Model objects being defined and corresponding import programs being written when facing multiple Excel templates and database tables. This is not only a large workload and high repetition, but also when the position of the field column in the Excel template changes, the DTO, Model object and import program need to be adjusted accordingly, which undoubtedly increases the complexity of development and maintenance and reduces the efficiency and flexibility of database field import. Summary of the invention

[0005] In view of this, an object of the present invention is to provide a database field import method, apparatus, device and storage medium to improve the efficiency and flexibility of database field import and reduce the workload of development and maintenance.

[0006] In a first aspect, an embodiment of the present application provides a method for reducing the workload of development and maintenance, the method comprising:

[0007] In the import preparation stage, a standardized Excel data template is defined, and a field mapping area is defined in the standardized Excel data template;

[0008] Specify the database fields to be imported in the field mapping area;

[0009] In the import execution phase, the database fields to be imported are read based on the field mapping area, and a database insert statement is generated based on the database fields to be imported;

[0010] The database insert statement is executed to import the database field to be imported into the database table.

[0011] Optionally, the standardized Excel data template includes at least one Sheet tab, and the name of each Sheet tab matches the name of each importable database table.

[0012] Optionally, defining a field mapping area in the standardized Excel data template includes:

[0013] A field mapping area of ​​each Sheet tab is defined at a preset position of each Sheet tab of the standardized Excel data template.

[0014] Optionally, the preset position is the first line or a comment.

[0015] Optionally, reading the to-be-imported database field based on the field mapping area includes:

[0016] For each Sheet tab, determine the database table to be imported from each importable database table based on the name of the Sheet tab;

[0017] Determine the target database field based on the field mapping area of ​​the Sheet tab;

[0018] The target database field in the database table to be imported is read as the database field to be imported.

[0019] Optionally, specifying the database fields to be imported in the field mapping area includes:

[0020] In the field mapping area of ​​each Sheet tab, specify the database fields to be imported for each Sheet tab.

[0021] In a second aspect, an embodiment of the present application provides a database field import device, the device comprising:

[0022] A field mapping area definition module is used to define a standardized Excel data template in the import preparation stage, and define a field mapping area in the standardized Excel data template;

[0023] A database field specifying module, used to specify the database fields to be imported in the field mapping area;

[0024] An insert statement generating module, used for reading the database field to be imported based on the field mapping area and generating a database insert statement based on the database field to be imported during the import execution phase;

[0025] The database field import module is used to execute the database insert statement to import the database field to be imported into the database table.

[0026] Optionally, the standardized Excel data template includes at least one Sheet tab, and the name of each Sheet tab matches the name of each importable database table.

[0027] Optionally, defining a field mapping area in the standardized Excel data template includes:

[0028] A field mapping area of ​​each Sheet tab is defined at a preset position of each Sheet tab of the standardized Excel data template.

[0029] Optionally, the preset position is the first line or a comment.

[0030] Optionally, reading the to-be-imported database field based on the field mapping area includes:

[0031] For each Sheet tab, determine the database table to be imported from each importable database table based on the name of the Sheet tab;

[0032] Determine the target database field based on the field mapping area of ​​the Sheet tab;

[0033] The target database field in the database table to be imported is read as the database field to be imported.

[0034] Optionally, specifying the database fields to be imported in the field mapping area includes:

[0035] In the field mapping area of ​​each Sheet tab, specify the database fields to be imported for each Sheet tab.

[0036] In a third aspect, an embodiment of the present application provides a computer device, comprising: a processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the computer device is running, the processor communicates with the memory through the bus, and when the machine-readable instructions are executed by the processor, the steps of the database field import method described in any optional implementation manner of the first aspect are performed.

[0037] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the steps of the database field importing method described in any optional implementation manner in the above-mentioned first aspect are executed.

[0038] The technical solution provided by this application includes but is not limited to the following beneficial effects:

[0039] This application maps the field configuration in the database table to the first row of the Excel data template before data import, and then parses the Excel data template to obtain the fields to be imported during data import, dynamically constructs the Insert SQL statement, and then realizes the import of the corresponding fields in the database. The above method is used to deeply decouple the Excel data template from the import program, so that when modifying the import template, the data import work can be easily completed without any adjustment or modification to the import program, which not only significantly reduces the workload of developers and improves work efficiency, but also ensures the accuracy and consistency of data import.

[0040] In addition, this application also fully considers the changes in demand in actual applications. By dynamically constructing Insert SQL statements, it is easy to cope with the import requirements of different database tables and fields without having to write a separate import program for each template. This flexibility enables better adaptation to various complex business scenarios and provides users with a more convenient and efficient data import experience.

[0041] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, preferred embodiments are given below and described in detail with reference to the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0043] Figure 1 A flowchart of a method for importing database fields provided in the first embodiment of the present invention is shown;

[0044] Figure 2 A flowchart of database field import based on the first row configuration of a table provided in the first embodiment of the present invention is shown;

[0045] Figure 3 A flowchart of database field import based on table annotation configuration provided by the first embodiment of the present invention is shown;

[0046] Figure 4 A flowchart of a method for reading a database field provided by the first embodiment of the present invention is shown;

[0047] Figure 5A schematic diagram of the structure of a database field import device provided in the second embodiment of the present invention is shown;

[0048] Figure 6 A schematic diagram of the structure of a computer device provided in Embodiment 3 of the present invention is shown. DETAILED DESCRIPTION

[0049] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. The components of the embodiments of the present invention generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present invention.

[0050] Embodiment 1

[0051] To facilitate understanding of this application, Figure 1 The content described in the flowchart of a database field import method provided in the first embodiment of the present invention is described in detail in the first embodiment of the present application.

[0052] See also Figure 1 As shown, Figure 1 A flowchart of a method for importing database fields provided in the first embodiment of the present invention is shown, wherein the method comprises steps S101 to S104:

[0053] S101: In the import preparation stage, a standardized Excel data template is defined, and a field mapping area is defined in the standardized Excel data template.

[0054] Specifically, when preparing an Excel template to import data into a database, a standardized Excel template is defined, and the template is used to prepare the data to be imported.

[0055] For example, taking the tbl_cnvrsn_factors table as an example, configure a template of the basic data format. The content fields of this template will contain the following information: serial number, model size, resistance range (>=), resistance range (<=), manufacturer code, production type, number of single pieces, specification code, current unit, factor, product classification. These fields will be accurately mapped to the corresponding columns in the tbl_cnvrsn_factors table to ensure the correctness and consistency of the data. By following this standardized template design, the efficiency and accuracy of data import can be improved. The tbl_cnvrsn_factors table is as follows:

[0056]

[0057] S102: Specify the database fields to be imported in the field mapping area.

[0058] Specifically, define the database fields to be imported in the field mapping area of ​​Excel.

[0059] For example, in the field mapping area (first row) of the tbl_cnvrsn_factors table, define the database fields id, mdl_size, prdt_type, blck_grn_qty, spcfcn_cd, factor, and prdt_ctgry to be imported, which correspond to the column number, model size, production type, unit grain number, specification code, factor, and production classification in the Excel data template respectively. The tbl_cnvrsn_factors table after importing the database fields in the field mapping area (first row) is as follows:

[0060]

[0061] S103: In the import execution phase, the to-be-imported database fields are read based on the field mapping area, and a database insert statement is generated based on the to-be-imported database fields.

[0062] Specifically, all relevant information in the Excel data template is read according to the preset logic, and the Insert SQL database insertion statement to be inserted is constructed one by one according to the read information. The specific method of generating the database insertion statement and the constructed insertion statement are set according to the actual code language logic used.

[0063] S104: Execute the database insert statement to import the database field to be imported into the database table.

[0064] Specifically, executing the constructed Insert SQL database insert statement can successfully write the data in the Excel data template into the database, thereby completing the entire Excel data template import process. This method not only improves the automation of data import, but also ensures the accuracy and consistency of the data, providing strong data support for the normal operation of the system.

[0065] In an optional embodiment, the standardized Excel data template includes at least one Sheet tab, and the name of each Sheet tab matches the name of each importable database table.

[0066] Specifically, the name of each Sheet tab matches the name of the database table to be imported so that the program can automatically identify and associate. This information includes each Sheet tab (or worksheet) in the Excel workbook, and the table name in a specific Sheet tab is identified and read based on the database field mapping that is dynamically and flexibly configured in Excel.

[0067] In an optional embodiment, the defining of the field mapping area in the standardized Excel data template includes:

[0068] A field mapping area of ​​each Sheet tab is defined at a preset position of each Sheet tab of the standardized Excel data template.

[0069] Specifically, in order to ensure the consistency and accuracy of data and facilitate data import, export or data exchange with other systems, the field mapping area of ​​each Sheet tab is defined in the standardized Excel data template. This is usually to ensure the consistency and accuracy of data. The specific process includes:

[0070] Create multiple Sheet tabs in a standardized Excel data template, each corresponding to a different data set or data table. At the top or left side of the Sheet tab (usually the first few rows or columns), reserve an area for defining field names. These reserved rows or columns will be used to place field names to clearly indicate the meaning of each column or row of data. At the preset position, enter the field name corresponding to the data set. Make sure the field name is clear, accurate, and consistent with business logic. Below or to the right of the field name, reserve an area for entering the actual data. Make sure the data area corresponds to the field name, that is, there is a corresponding data column or row below or to the right of each field name.

[0071] In an optional implementation, the preset position is the first line or a comment.

[0072] Specifically, the preset position may also be the first column, which may be specifically defined according to actual needs.

[0073] When the default position is the first line, see Figure 2 As shown, Figure 2 The flowchart of a database field import based on the first row configuration of a table provided by the first embodiment of the present invention is shown, wherein the database field is first configured in the first row of Excel, and the data content to be imported is filled in the Excel template to define the Excel import template file. Then a backend import program is written for the Excel template, and the import program automatically identifies the database field configured in the first row of Excel, dynamically generates SQL insert statements, and executes the import instructions to import the data in the Excel table into the database table.

[0074] When the preset position is a comment, see Figure 3 As shown, Figure 3 A flowchart of a database field import based on table annotation configuration provided by the first embodiment of the present invention is shown, wherein an annotation function is added to the first row of Excel titles, and these annotations are used to define the fields that need to be imported into the database. In an Excel worksheet, when the title field of a column is set to a state that requires "annotation", the system automatically adds a small red dot as a mark in the upper right corner of the column title. This small red dot is used as a visual identifier to quickly identify which column fields have been annotated. If the title field of a column does not display this small red dot, it can be inferred that the field has not yet been configured to a state that requires "annotation". Therefore, when viewing an Excel table, the user can quickly determine which columns are configured with database fields by observing these small red dots. The specific process is: first, add an annotation to the first row of Excel titles, configure the database field in the annotation, and fill the Excel template with the data content to be imported to define the Excel import template file. Then fill the Excel template with the data content to be imported to write a backend import program for the Excel template. Next, the import program reads the database fields configured in the approval on the first row of headers in Excel, dynamically generates SQL insert statements, and finally executes the import instructions to import the data in the Excel table into the database table.

[0075] In an alternative embodiment, see Figure 4 As shown, Figure 4 A flowchart of a method for reading a database field provided by the first embodiment of the present invention is shown, wherein the method for reading the database field to be imported based on the field mapping area includes steps S401 to S403:

[0076] S401: For each Sheet tab, determine a database table to be imported from each importable database table based on the name of the Sheet tab.

[0077] Specifically, in this step, the system or user will check each Sheet tab in the Excel file. Each Sheet tab has a name, which is used to identify a specific database table from a set of importable database tables. In other words, there is a mapping relationship between the name of the Sheet tab and the database table, through which the corresponding database table can be found as the database table to be imported.

[0078] S402: Determine the target database field based on the field mapping area of ​​the Sheet tab.

[0079] Specifically, after determining the database table to be imported, the system will check the field mapping area of ​​the Sheet tab. The field mapping area is usually a table or a series of marks that indicate which columns (fields) in Excel should be mapped to which fields in the database table. Through this mapping relationship, the target database fields can be determined, that is, which data in Excel needs to be imported into which fields in the database table.

[0080] S403: Read the target database field in the database table to be imported as the database field to be imported.

[0081] Specifically, after determining the database table to be imported and the target database field, the system will start reading the data in the corresponding Sheet tab of the Excel file. When reading, it will pay special attention to the Excel columns marked as target database fields. This data will then be imported into the previously determined database table to be imported, and ensure that the data of each Excel column is correctly placed in the corresponding field of the database table.

[0082] In an optional implementation, specifying the database fields to be imported in the field mapping area includes:

[0083] In the field mapping area of ​​each Sheet tab, specify the database fields to be imported for each Sheet tab.

[0084] Specifically, determine the field mapping area in each Sheet tab, which is usually a specific table or a series of cells that clearly identifies which columns (or fields) in Excel should correspond to which fields in the database table. In the field mapping area, specify which field of the database table each Excel column should be mapped to, by filling in the mapping table, selecting a drop-down menu item, or directly entering the field name in the cell. Once the database fields to be imported are specified, the system will establish a connection between the Excel columns and the database fields based on these mapping relationships. During the data import process, each value in Excel will be accurately placed in the corresponding field of the database table.

[0085] Embodiment 2

[0086] See also Figure 5 As shown, Figure 5 A schematic diagram of the structure of a database field import device provided in Embodiment 2 of the present invention is shown, wherein the device comprises:

[0087] A field mapping area definition module 501 is used to define a standardized Excel data template in the import preparation stage, and define a field mapping area in the standardized Excel data template;

[0088] A database field specifying module 502, used to specify the database field to be imported in the field mapping area;

[0089] An insert statement generating module 503 is used to read the database field to be imported based on the field mapping area and generate a database insert statement based on the database field to be imported during the import execution phase;

[0090] The database field import module 504 is used to execute the database insert statement to import the database field to be imported into the database table.

[0091] In an optional embodiment, the standardized Excel data template includes at least one Sheet tab, and the name of each Sheet tab matches the name of each importable database table.

[0092] In an optional embodiment, the defining of the field mapping area in the standardized Excel data template includes:

[0093] A field mapping area of ​​each Sheet tab is defined at a preset position of each Sheet tab of the standardized Excel data template.

[0094] In an optional implementation, the preset position is the first line or a comment.

[0095] In an optional implementation manner, reading the to-be-imported database field based on the field mapping area includes:

[0096] For each Sheet tab, determine the database table to be imported from each importable database table based on the name of the Sheet tab;

[0097] Determine the target database field based on the field mapping area of ​​the Sheet tab;

[0098] The target database field in the database table to be imported is read as the database field to be imported.

[0099] In an optional implementation, specifying the database fields to be imported in the field mapping area includes:

[0100] In the field mapping area of ​​each Sheet tab, specify the database fields to be imported for each Sheet tab.

[0101] Embodiment 3

[0102] Based on the same application concept, see Figure 6 As shown, Figure 6 FIG. 4 shows a schematic diagram of the structure of a computer device provided by Embodiment 3 of the present invention, wherein: Figure 6 As shown, a computer device 600 provided in Embodiment 3 of the present application includes:

[0103] A processor 601, a memory 602 and a bus 603, wherein the memory 602 stores machine-readable instructions executable by the processor 601. When the computer device 600 is running, the processor 601 communicates with the memory 602 via the bus 603. When the processor 601 is running, the machine-readable instructions execute the steps of the database field import method shown in the first embodiment.

[0104] Embodiment 4

[0105] Based on the same application concept, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the database field import method described in any one of the above embodiments are executed.

[0106] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the system and device described above can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.

[0107] The computer program product for importing database fields provided in the embodiment of the present invention includes a computer-readable storage medium storing program code. The instructions included in the program code can be used to execute the method described in the previous method embodiment. The specific implementation can be found in the method embodiment and will not be repeated here.

[0108] The database field import device provided in the embodiment of the present invention can be specific hardware on the device or software or firmware installed on the device. The implementation principle and technical effects of the device provided in the embodiment of the present invention are the same as those of the aforementioned method embodiment. For the sake of brief description, the parts not mentioned in the system embodiment can refer to the corresponding contents in the aforementioned method embodiment. It can be clearly understood by technicians in the relevant field that for the convenience and simplicity of description, the specific working processes of the systems, devices and units described above can all refer to the corresponding processes in the aforementioned method embodiment, and will not be repeated here.

[0109] In the embodiments provided by the present invention, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, and the indirect coupling or communication connection of devices or units can be electrical, mechanical or other forms.

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

[0111] In addition, each functional unit in the embodiment provided by the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0112] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program codes.

[0113] It should be noted that similar numbers and letters represent similar items in the following figures. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures. In addition, the terms "first", "second", "third", etc. are only used to distinguish the description and are not to be understood as indicating or implying relative importance.

[0114] Finally, it should be noted that the above-described embodiments are only specific implementations of the present invention, which are used to illustrate the technical solutions of the present invention, rather than to limit them. The protection scope of the present invention is not limited thereto. Although the present invention is described in detail with reference to the above-described embodiments, those skilled in the art should understand that any person skilled in the art can still modify the technical solutions described in the above-described embodiments within the technical scope disclosed by the present invention, or can easily think of changes, or perform equivalent replacements on some of the technical features thereof; and these modifications, changes or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present invention. They should all be covered within the protection scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A method for importing database fields, characterized in that: The method comprises: In the import preparation stage, a standardized Excel data template is defined, and a field mapping area is defined in the standardized Excel data template; Specify the database fields to be imported in the field mapping area; In the import execution phase, the database fields to be imported are read based on the field mapping area, and a database insert statement is generated based on the database fields to be imported; The database insert statement is executed to import the database field to be imported into the database table.

2. The method according to claim 1, characterized in that The standardized Excel data template includes at least one Sheet tab, and the name of each Sheet tab matches the name of each importable database table.

3. The method according to claim 2, characterized in that Defining a field mapping area in the standardized Excel data template includes: A field mapping area of ​​each Sheet tab is defined at a preset position of each Sheet tab of the standardized Excel data template.

4. The method according to claim 3, characterized in that The preset position is the first line or comment.

5. The method according to claim 3, characterized in that: The reading the to-be-imported database field based on the field mapping area includes: For each Sheet tab, determine the database table to be imported from each importable database table based on the name of the Sheet tab; Determine the target database field based on the field mapping area of ​​the Sheet tab; The target database field in the database table to be imported is read as the database field to be imported.

6. The method according to claim 2, characterized in that The specifying of the database fields to be imported in the field mapping area includes: In the field mapping area of ​​each Sheet tab, specify the database fields to be imported for each Sheet tab.

7. A database field import device, characterized in that: The device comprises: A field mapping area definition module is used to define a standardized Excel data template in the import preparation stage, and define a field mapping area in the standardized Excel data template; A database field specifying module, used to specify the database fields to be imported in the field mapping area; An insert statement generating module, used for reading the database field to be imported based on the field mapping area and generating a database insert statement based on the database field to be imported during the import execution phase; The database field import module is used to execute the database insert statement to import the database field to be imported into the database table.

8. The device according to claim 7, characterized in that The standardized Excel data template includes at least one Sheet tab, and the name of each Sheet tab matches the name of each importable database table.

9. A computer device, characterized in that: include: A processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the computer device is running, the processor and the memory communicate via the bus, and when the machine-readable instructions are executed by the processor, the steps of the database field import method as described in any one of claims 1 to 6 are performed.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the database field importing method as described in any one of claims 1 to 6 are executed.

Images