System and method for synchronously collecting data of multiple database tables

Abstract

The invention discloses a system and method for synchronously collecting data of multiple database tables. The system comprises an executable file, a configuration file and a data file. Related information of the source database and the target database is configured in the configuration file; the data file comprises one or more predefined text tags and data tags; the predefined text label has a name attribute, and the predefined text label comprises a customized sql text; the customized sql text is configured to query a source database, process data and insert the processed data into a target database. The data label controls acquisition conditions, and the acquisition conditions comprise a source database table name, a target database table name, a primary key and name attributes of a predefined text label; the executable file is configured to execute the actions of calling the predefined text label, querying the source database and finally inserting the target database according to the data label.

Description

Technical Field

[0001] The present invention belongs to the field of informatization, and in particular relates to a system and method for synchronously collecting data from multiple database tables. Background Art

[0002] With the gradual improvement of the level of informatization in modern society, information systems have become synonymous with cost reduction and efficiency improvement in all walks of life. However, the basis for the operation of these systems is the interconnection of various devices, and data collection from these devices has become an indispensable part of establishing the system.

[0003] The significance of modern enterprise information systems is to process a large amount of data in a centralized manner, improve the production efficiency of enterprises, and manage and utilize various resources through data visualization and convenient operation. However, to achieve these functions, it is necessary to collect and interact with data from various devices. Among them, table-to-table collection is one of the most common data interaction methods. However, an information system may have multiple associated devices, and the required formats and database types may be different. Therefore, when collecting such data, multiple programs will be written for customized collection, which will undoubtedly increase the workload of information system manufacturers, and multiple files will also be difficult to maintain later. There is a lack of a unified management method. Summary of the invention

[0004] In view of the above technical problems, the present invention provides a system and method for synchronously collecting data from multiple database tables, thereby reducing the amount of collection development and the number of programs and improving management efficiency.

[0005] In order to achieve the above-mentioned invention object, the technical solution adopted is as follows: A system for synchronously collecting data from multiple database tables, including executable files, configuration files and data files; Configure the source and target databases in the configuration file.

[0006] The data file includes one or more predefined text labels, data labels; The predefined text tag has a name attribute, and the predefined text tag includes a customized SQL text; the customized SQL text is configured to query the source database and insert the data into the target database after processing.

[0007] Data tags control the collection conditions, which include the source database table name, target database table name, primary keys, and the name attribute of the predefined text tag; The executable file is configured to perform actions based on data tags, call predefined text tags, query the source database, and finally insert into the target database. The executable file also contains a default collection method that directly queries the source database and inserts into the target database. When there is no customized SQL text, the default collection method is used to collect data.

[0008] Data tags customize collection standards in XML format, and use different tag names and their attributes to control the execution of executable files to obtain the required data. This is the key to collecting information configuration.

[0009] Furthermore, the relevant information including database type, name, host address, user, password, and port is saved in the form of a json string so that the executable file can correctly connect to the relevant database.

[0010] Furthermore, a custom query sql text and / or a custom insert sql text is set under the collection conditions of the data tag to achieve the expansion of the data collection method.

[0011] Furthermore, the executable file is configured to support runtime expressions, and the runtime expressions are configured as predefined text labels. A custom SQL text and a runtime expression are both defined as a predefined text label.

[0012] Furthermore, the runtime expression includes one or more of the following functions: join(field list:iter) function, joins the elements in the field list together with commas as separators; join_quot(field list:iter) function adds single quotes to the elements in the field list and connects them together with commas as separators. If the element is None, it is replaced with null. get_where() function generates a where expression based on keys and last_data; get_insert_sql(query number, source database table name = target database table name) function is used to obtain the insert statement; get_replace_sql(query number, source database table name = target database table name) function is used to obtain the replacement statement.

[0013] The present invention also discloses a method for synchronously collecting data from multiple database tables, comprising the following steps: S1, configure the relevant information of the source database and the target database in the configuration file; configure one or more predefined text tags, data tags, in the data file; S2, obtain relevant information of the source database and the target database from the configuration file, and establish a connection between the executable file and the source database and the target database; S3, the executable file obtains the query conditions of the data tags in the data file, determines the source database, the target database, and the predefined text tags to be called; S4, query the execution record of the last collection, and perform collection on the new data after the last collection: query the source database according to the customized SQL text of the predefined text tag, insert the data into the target database after processing; wait for the next execution.

[0014] Furthermore, if you need to set up a separate query for a certain collection, set up a custom query sql text under the current data tag; if you need to set up a separate insertion for a certain collection, set up a custom insertion sql text under the current data tag.

[0015] Furthermore, if there is no customized SQL text, the executable file will query the source database by default only according to the information provided in the data tag and insert it into the target database.

[0016] In general, the present invention has the following beneficial effects: 1. Program reuse improves work efficiency. Data collection does not require program changes. You only need to configure relevant items in the data files and configuration files in the directory where the program is located. When the program is running, it obtains relevant information from the two configuration files, performs query and insertion operations, and completes data collection. 2. Reduce the number of collected files to facilitate later maintenance; the program supports the definition of multiple table-to-table collection, saving working time in the equipment collection stage and facilitating unified management. It also supports debugging functions, which can accurately locate the location of the error and improve work efficiency; 3. Simple operation and shortened construction period; 4. There are many application scenarios and it can be referenced in different projects; 5. Combining multiple collections into one can reduce server pressure. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 The present invention is a schematic diagram of a system deployment process for synchronously collecting data from multiple database tables according to an embodiment of the present invention.

[0018] Figure 2 The present invention is a schematic diagram of the operation flow of the method for synchronously collecting data from multiple database tables according to an embodiment of the present invention. DETAILED DESCRIPTION

[0019] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific examples described herein are only used to explain the present invention and are not intended to limit the present invention.

[0020] like Figure 1 As shown, this is the overall process of system deployment for synchronously collecting data from multiple database tables in this embodiment. Create a configuration file config.json and a data file data_info.xml in the directory where the executable file is located, and write the relevant configuration information. Run the executable file to collect relevant data from the source data table to the target data table. At the same time, multiple collections can be configured in the configuration file. Before execution, the executable file will obtain relevant information in the local database configuration table and locate the location at the end of the last executable file execution, thereby avoiding repeated collection of the same data. Use Windows scheduled tasks to trigger the executable file at a fixed time to achieve the function of data collection from multiple different devices.

[0021] If you encounter an error in the executable file, you can add the suffix: debug=true to the command line to debug it, and finally deploy it to the scheduled task.

[0022] First, place the executable file and dependent files in any directory, and create config.json and data_info.xml files in the directory at the same time.

[0023] Collect the database type, name, host address, user, password, and port information of the source and target databases, and configure them in the config.json file.

[0024] Configure the data_info.xml file, where <root>The tag is the root tag. <define>The tag is a predefined text tag. You can write any SQL text you want to customize the query under this tag. <define>The tag has a name attribute, which can be used to name the tag. <data>Tags are data tags that control the collection conditions. <data>Tags are divided into multiple attributes. table_name is the source database table name for the query, des_table_name is the target database table name for the insertion, keys is the primary key, and limit is the number of queries at one time. If there is no customized SQL text, the executable file is <data>The information provided in the tag executes the default query defined in the system and performs the insert operation.

[0025] If you need to query a collection individually, you can <data>Settings under the label <sql>Tags are used to customize query statements.

[0026] If you need to set up a separate insert for one of the acquisitions, you can do so in the current <data>Settings under the label<insert_sql> Tags for custom insert statements.

[0027] exist <define>The predefined texts can be found in <data>The format is #{define_name}, where define_name is the <define>The name attribute name defined in the tag, example: {{';'.join(f'insert into xxx values({join(x)})' for x in data)}}, ${';'.join(f'insert into xxx values({join(x)})' for x in data)}.

[0028] In addition, executable files also support runtime expressions, which are marked with {{express}} or ${express}, where express is a Python expression. The runtime expression is configured as <define>Tags can be found in <data>Tag. When executing query and insert statements, dynamically execute the expression content and replace the result into the SQL statement. The runtime expressions {{express}} and ${express} are functionally the same. If there are curly braces in the expression, you should choose to use {{express}}.

[0029] The functions accessible in runtime expressions are as follows: join(ls:iter) joins the elements in ls together with "," as separators; oin_quot(ls:iter) adds "single quotes" to the elements in ls and connects them together with "," as the separator. If the element is None, it will be replaced with null. get_where() generates a where expression based on keys and last_data; get_insert_sql(limit:int=1000,table_name:str=dec_table_name); get_replace_sql(limit:int=1000,table_name:str= dec_table_name).

[0030] After the data_info.xml file is configured, you can run the debugger through the command line. The variables are as follows: "SRC": "Source Database"; "DES":"Destination database"; "BATCH_NAME":"Task name (default data_to_app_db)"; "DATA_INFO": "Specify data synchronization configuration, xml file (default data_info.xml)"; "DEBUG": "Whether to enable DEBUG [true|false] mode to print insertsql but not execute"; "TABLE_NAME":"Specify the table_name to be executed (optional)".

[0031] Example: python data_to_app_db.py src=0 des=1 debug = true.

[0032] like Figure 2 As shown, the schematic diagram of the process of the present invention is as follows.

[0033] Step 1: The executable file obtains the variables in the command line to match database information and debugging information, etc. Step 2: Use the matching information to find the database details in the config file for connection; Step 3: The executable file obtains the configuration information in XML and performs related operations according to the configuration information; Step 4: Query the local database information table to obtain the last collected information, and pass it into the executable file as a condition for the next collection.

[0034] Step 5: After the collection is completed, new collection information is generated and updated to the local database information table.

[0035] The above embodiments are only for illustrating the operation process of the present invention, and cannot be used to limit the protection scope of the present invention. Any changes made on the basis of the technical solution in accordance with the technical ideas proposed by the present invention shall fall within the protection scope of the present invention.< / data> < / define> < / define> < / data> < / define> < / data> < / sql> < / data> < / data> < / data> < / data> < / define> < / define> < / root>

Claims

1. A system for synchronously collecting data from multiple database tables, characterized by: Includes executable files, configuration files, and data files; Configure the source database and target database related information in the configuration file; The data file includes one or more predefined text labels, data labels; The predefined text tag has a name attribute, and the predefined text tag includes a custom SQL text; the custom SQL text is configured to query the source database and insert the data into the target database after processing; Data tags control the collection conditions, which include the source database table name, target database table name, primary keys, and the name attribute of the predefined text tag; The executable file is configured to perform actions that, based on the data tags, call predefined text tags, query the source database and finally insert into the target database.

2. The system for synchronously collecting data from multiple database tables according to claim 1, characterized in that: Relevant information includes database type, name, host address, user, password, and port.

3. The system for synchronously collecting data from multiple database tables according to claim 1, characterized in that: Set custom query sql text and / or custom insert sql text under the collection conditions of the data tag.

4. The system for synchronously collecting data from multiple database tables according to claim 1, characterized in that: The executable is configured to support runtime expressions, which are configured as predefined text labels.

5. The system for synchronously collecting data from multiple database tables according to claim 4, characterized in that: A runtime expression consists of one or more of the following functions: join(field list:iter) function, joins the elements in the field list together with commas as separators; join_quot(field list:iter) function adds single quotes to the elements in the field list and connects them together with commas as separators. If the element is None, it is replaced with null. get_where() function generates a where expression based on keys and last_data; get_insert_sql(query number, source database table name = target database table name) function is used to obtain the insert statement; get_replace_sql(query number, source database table name = target database table name) function is used to obtain the replacement statement.

6. A method for synchronously collecting data from multiple database tables, characterized in that: The system for synchronously collecting data from multiple database tables according to any one of claims 1 to 5 comprises the following steps: S1, configure the relevant information of the source database and the target database in the configuration file; configure one or more predefined text tags, data tags, in the data file; S2, obtain relevant information of the source database and the target database from the configuration file, and establish a connection between the executable file and the source database and the target database; S3, the executable file obtains the query conditions of the data tags in the data file, determines the source database, the target database, and the predefined text tags to be called; S4, query the execution record of the last collection, and perform collection on the new data after the last collection: query the source database according to the customized SQL text of the predefined text tag, insert the data into the target database after processing; wait for the next execution.

7. The method for synchronously collecting data from multiple database tables according to claim 6, characterized in that: If you need to set up a separate query for a certain collection, set the custom query SQL text under the current data tag; If you need to perform separate insertion settings for a certain collection, set the custom insertion SQL text under the current data tag.

8. The method for synchronously collecting data from multiple database tables according to claim 7, characterized in that: If there is no customized SQL text, the executable file will query the source database by default according to the information provided in the data tag and insert it into the target database.

Images