Asynchronous IO concurrent processing method for adding relational database to SQLAlchemy

Abstract

The invention relates to an asynchronous IO concurrent processing method for adding a relational database in SQLAlchemy, which comprises the following steps of: packaging a calling method of the relational database to obtain a packaging module; configuring a deployment environment; based on the deployment environment, creating an operation instance according to the packaging module; and executing the to-be-executed SQL through the running instance to obtain an execution result. According to the method and the system, on the basis of reserving and using an SQLAlchemy native function approach, concurrent operation is performed on the database through the provided interface, so that the database maintenance efficiency and compatibility are improved.

Description

Technical Field

[0001] The invention belongs to the field of data processing, and in particular relates to a method for increasing asynchronous IO concurrent processing of a relational database by using SQLAlchemy. Background Art

[0002] The existing SQLAlchemy, as a Python relational database ORM open source framework, does not have concurrent operation capabilities and timeout protection mechanisms, making it incapable of database CRUD operation scenarios with large data volumes or high concurrency requirements.

[0003] Directly using SQLAlchemy in the field of automated operation and maintenance or test script development will greatly reduce the efficiency of the program's database operations. At the same time, native SQLAlchemy cannot respond and handle timeout operations in a timely and clear manner, which also makes the program lack the necessary robustness to a certain extent. Summary of the invention

[0004] In view of the above deficiencies in the prior art, the object of the invention is to provide a method for increasing asynchronous IO concurrent processing of relational databases using SQLAlchemy.

[0005] The present invention provides a method for increasing asynchronous IO concurrent processing of a relational database by using SQLAlchemy, comprising:

[0006] S1: Encapsulate the calling method of the relational database and obtain the encapsulation module;

[0007] S2: Configure the deployment environment;

[0008] S3: Based on the deployment environment, create a running instance according to the encapsulated module;

[0009] S4: Execute the SQL to be executed through the running instance to obtain an execution result.

[0010] According to a method for increasing asynchronous IO concurrency processing of a relational database using SQLAlchemy provided by the present invention, the encapsulation module in step S1 includes:

[0011] DBTool class, which is used to operate and maintain a relational database;

[0012] DBResult class, which is the return result of the SQL to be executed;

[0013] DBResultData class, which is the return data table of the SQL to be executed.

[0014] According to a method for increasing asynchronous IO concurrency processing of a relational database in SQLAlchemy provided by the present invention, the methods included in the DBTool class specifically include:

[0015] Execute a single SQL, execute multiple SQLs concurrently, and execute multiple SQLs serially.

[0016] According to a method for increasing asynchronous IO concurrency processing of a relational database using SQLAlchemy provided by the present invention, when the method selected by the DBTool class is to execute a single SQL, the execution steps specifically include:

[0017] S111: Execute a single SQL statement with timeout protection;

[0018] S112: Execute a single SQL statement without timeout protection.

[0019] According to a method for increasing asynchronous IO concurrent processing of a relational database using SQLAlchemy provided by the present invention, when the method selected by the DBTool class is to execute multiple SQLs concurrently, the execution steps specifically include:

[0020] S121: Determine the thread type of the execution method, the thread type includes multithreading and coroutine, when the thread type is multithreading, execute step S122, when the thread type is coroutine, execute step S123;

[0021] S122: Execute multiple SQL statements respectively through multiple threads, wherein each thread executes a single SQL statement according to a method selected by the DBTool class;

[0022] S123: Execute the main function and the working function through the coroutine;

[0023] S124: Based on the coroutine, the SQL to be executed is proxied to an execution method that executes a single SQL for execution.

[0024] According to a method for increasing asynchronous IO concurrent processing of a relational database in SQLAlchemy provided by the present invention, when the method selected by the DBTool class is to execute multiple SQLs in series, the execution steps specifically include:

[0025] S131: Execute multiple SQL statements in sequence without timeout protection.

[0026] According to a method for increasing asynchronous IO concurrency processing of a relational database in SQLAlchemy provided by the present invention, step S2 further comprises:

[0027] S21: Prepare Python environment;

[0028] S22: Based on the Python environment, install the SQLAlchemy dependency package and the func-timeout dependency package;

[0029] S23: placing the encapsulation module in the python project directory.

[0030] According to a method for increasing asynchronous IO concurrency processing of a relational database in SQLAlchemy provided by the present invention, step S3 further comprises:

[0031] S31: Based on the Python environment, install a data analysis database dependency package, where the data analysis database is used to receive the return result of the SQL to be executed;

[0032] S32: According to the data analysis database, connect to the URL interface corresponding to the data analysis database;

[0033] S33: Specify the database driver and create a running instance.

[0034] According to a method for increasing asynchronous IO concurrent processing of a relational database in SQLAlchemy provided by the present invention, the data analysis database in step S31 is pandas.

[0035] According to a method for increasing asynchronous IO concurrency processing of a relational database in SQLAlchemy provided by the present invention, step S4 further comprises:

[0036] S41: Passing variables into SQL through parameterization method to obtain SQL to be executed;

[0037] S42: Execute the SQL to be executed through the running instance to obtain an execution result.

[0038] The beneficial effects of the present invention are as follows:

[0039] The present invention provides a method for SQLAlchemy to add asynchronous IO concurrent processing of relational databases. By encapsulating the calling method of relational databases, the complexity of database operations is simplified, so that developers can execute SQL statements more efficiently. At the same time, it supports the concurrent execution of multiple SQL statements, which significantly improves the efficiency of database operations, especially when processing large amounts of data or complex queries. The present invention uses multi-threading and coroutine technology to achieve asynchronous IO concurrent processing, effectively avoiding the blocking problem of database operations, which can not only improve the response speed of the system, but also better utilize system resources and improve overall performance. The present invention provides multiple execution modes such as executing a single SQL, executing multiple SQLs concurrently, and executing multiple SQLs serially, which meet different scenarios. The system can be easily run in different environments by configuring the deployment environment in detail, and the system deployment process is simplified by installing necessary dependency packages and placing the encapsulated modules in the Python project directory. The present invention can directly use SQL execution results for data analysis by connecting to a data analysis database, thereby providing developers with a convenient data processing and analysis tool, which can not only improve the efficiency of data analysis, but also help developers better understand and use data. In addition, the present invention can encapsulate the database operation method and provide clear execution steps, so that the code structure of the present invention is clearer and easier to understand, which can not only reduce the difficulty of code maintenance, but also improve the readability and reusability of the code. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] The accompanying drawings are only used to illustrate specific embodiments and are not considered to limit the present invention. In the entire drawings, the same reference symbols represent the same components. Obviously, the drawings described below are only some embodiments recorded in the embodiments of the present invention. For those of ordinary skill in the art, other drawings can also be obtained based on these drawings.

[0041] Figure 1 A flowchart of a method for adding asynchronous IO concurrency processing to a relational database using SQLAlchemy provided by an embodiment of the present invention;

[0042] Figure 2 A schematic diagram of the encapsulation calling flow of the DBTool class included in the encapsulation module provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0043] In order to enable those skilled in the art to better understand the technical solutions in the embodiments of the present invention, the technical solutions of the present invention will be clearly and completely described below in conjunction with the accompanying drawings. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. It should be understood that these descriptions are only exemplary and are not intended to limit the scope of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in the field without making creative work should fall within the scope of protection of the present invention.

[0044] Furthermore, in the following description, descriptions of well-known structures and technologies are omitted to avoid unnecessarily obscuring the concepts disclosed in the present invention.

[0045] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the orientation or position relationship indicated by the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inside", "outside", etc. is based on the orientation or position relationship shown in the drawings, which is only for the convenience of describing the present invention and simplifying the description, and does not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as a limitation of the present invention. In addition, the terms "first", "second", and "third" are only used for descriptive purposes and cannot be understood as indicating or implying relative importance. The terms "installed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be a connection between the two elements. For those of ordinary skill in the art, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0046] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Instead, they are merely examples of methods and systems consistent with some aspects of the present invention as detailed in the appended claims.

[0047] In order to better understand the present invention, the terms appearing in the embodiments of the present invention are first explained below.

[0048] SQLAlchemy: SQLAlchemy is an open source software for Python programming language, which provides SQL toolkit and object-relational mapping (ORM) tool.

[0049] Asynchronous IO: Asynchronous I / O (asynchronous input / output) means that the program will not block the current thread when performing I / O operations, but will use asynchronous callbacks or event loops to handle I / O operations, which can improve the program's concurrent processing capabilities and performance.

[0050] Coroutine: Coroutine is a mechanism in Python that allows a program to pause and resume the execution of a function during execution without the need for traditional function calls. Coroutines are also called microthreads or fibers, and their English name is Coroutine. From a technical point of view, a coroutine is a function that can "pause execution". That is, during the execution of a function, the current state information can be saved at any location, and then switch to another function for execution without the need for traditional function calls. The switch is controlled by the programmer, not automatically by the system. The switching of coroutines does not involve system-level thread switching, so its performance overhead is much less than traditional thread switching. In Python, coroutines are implemented through the asyncio library, which provides an asynchronous programming model that allows programs to not occupy CPU resources while waiting for certain operations (such as IO operations) to complete, thereby improving the efficiency of the program.

[0051] Asynchronous IO concurrent operations on relational databases: Use Python asynchronous IO concurrent execution of tasks to concurrently execute CRUD (create, delete, modify, and query) operations on relational databases.

[0052] The embodiments of the present invention are described below with reference to the accompanying drawings.

[0053] like Figure 1 As shown, the present invention provides a method for increasing asynchronous IO concurrency processing of a relational database by using SQLAlchemy, comprising:

[0054] S1: Encapsulate the calling method of the relational database and obtain the encapsulation module.

[0055] Wherein, the encapsulation module in step S1 includes:

[0056] The DBTool class is used to operate and maintain a relational database.

[0057] Furthermore, the encapsulated DBTool is a relational database operation tool based on SQLAlchemy, among which the methods opened based on the Core part of SQLAlchemy are: "Execute single SQL", "Execute multiple SQL (serial, single transaction)", "Execute multiple SQL (concurrent, multiple connections)"; if you want to use the ORM part of SQLAlchemy, you can directly use the attributes "engine" and "session".

[0058] The methods included in the DBTool class specifically include:

[0059] Execute a single SQL, execute multiple SQLs concurrently, and execute multiple SQLs serially.

[0060] like Figure 2 As shown, when the method selected by the DBTool class is to execute a single SQL, the execution steps specifically include:

[0061] S111: Execute a single SQL statement with timeout protection.

[0062] S112: Execute a single SQL statement without timeout protection.

[0063] Furthermore, when executing a single SQL statement, the user is allowed to execute a single SQL statement. It can be synchronous or asynchronous, and a timeout protection mechanism is also provided in this embodiment, so in steps S111 to S112, a single SQL statement with timeout protection is executed first to prevent the SQL statement from being executed too long and causing system resources to be occupied for a long time.

[0064] like Figure 2 As shown, when the method selected by the DBTool class is to execute multiple SQLs concurrently, the execution steps specifically include:

[0065] S121: Determine the thread type of the execution method, the thread type includes multi-threading and coroutine, when the thread type is multi-threading, execute step S122, when the thread type is coroutine, execute step S123.

[0066] In step S121, it is first necessary to determine the thread type used when executing SQL concurrently. Multithreading refers to multiple threads managed and scheduled by the operating system kernel, which can be executed in parallel, but is limited by the influence of the global interpreter lock (GIL) in Python, and the real parallelism may be limited (especially in CPU-intensive tasks), while coroutines are lightweight threads scheduled by user mode, which can achieve concurrency within a single thread and avoid blocking through asynchronous IO operations, thereby improving the responsiveness and throughput of the program.

[0067] S122: Execute multiple SQLs respectively through multiple threads, wherein each thread executes a single SQL according to a method selected by the DBTool class.

[0068] If multi-threading is selected as the execution method, the system will create an independent thread for each SQL statement to execute. The threads will run concurrently, and each thread will call the method in the DBTool class to execute a single SQL to complete its task. This method can fully utilize the advantages of multi-core processors and improve the parallelism of database operations.

[0069] S123: Execute the main function and the working function through the coroutine.

[0070] In step S123, if coroutine is selected as the execution method, the system will use coroutine to manage concurrent tasks. In this step, the system will execute the main function and the work function. The main function is usually used to set the execution environment of the coroutine, such as initializing the event loop, configuring the coroutine scheduler, etc., while the work function contains the specific SQL execution logic, which will be scheduled by the coroutine scheduler to be executed at different time points to achieve concurrency.

[0071] It should be noted that when executing the main function and the working function, the main function and the working function are not executed sequentially, but they are arranged for execution within the execution framework of the coroutine. Due to the asynchronous nature of the coroutine, the code in the main function and the working function may be executed alternately, depending on the strategy of the coroutine scheduler and the completion of the IO operation.

[0072] S124: Based on the coroutine, the SQL to be executed is proxied to an execution method that executes a single SQL for execution.

[0073] After selecting coroutine as the execution method and setting the coroutine execution environment, the system will proxy the SQL statement to be executed to the single SQL execution method in the DBTool class for execution. The core of this step of coroutine concurrent execution of SQL mainly utilizes the asynchronous IO processing capability of coroutine to avoid blocking, thereby improving the responsiveness and throughput of the program.

[0074] Specifically, the system will encapsulate each SQL statement into a coroutine task and submit it to the coroutine scheduler. The coroutine scheduler will schedule these tasks according to the current execution status and the completion status of the IO operation to ensure that they can be executed concurrently. When a SQL statement is executed, its result will be returned to the caller for subsequent processing.

[0075] In a specific embodiment, the concept of "queue" and related code syntax are introduced in step S124, and then the asynchronous IO task queue is created by calling the aforementioned work function, and the "await" and "async with" related code syntax are mainly used to concurrently execute various tasks. Executing a task is essentially calling the "Execute Single SQL-With Timeout Protection-Proxy" method, and finally adding the word "async" to the head of the method name, and then using "asyncio.get_running_loop()" and "await loop.run_in_executor" these asynchronous IO syntaxes in the method, and by calling "Execute Single SQL-With Timeout Protection" to specifically complete the execution of a task in the asynchronous task queue.

[0076] like Figure 2 As shown, when the method selected by the DBTool class is to execute multiple SQL statements serially, the execution steps specifically include:

[0077] S131: Execute multiple SQL statements in sequence without timeout protection.

[0078] In step S1, for the DBTool class, no matter what SQL task is executed, after executing the above steps, SQLAlchem ​​will be called at the end to perform CURD operations according to SQL.

[0079] DBResult class, which is the return result of the SQL to be executed.

[0080] DBResultData class, which is the return data table of the SQL to be executed.

[0081] Furthermore, the DBResult class is the overall result returned by a SQL task execution, including the execution ID, success flag, error message, data table, etc. The DBResultData class is the data table returned by a SQL task execution, including the column names of the result set and each row of data.

[0082] S2: Configure the deployment environment.

[0083] Wherein, step S2 further comprises:

[0084] S21: Prepare Python environment;

[0085] S22: Based on the Python environment, install the SQLAlchemy dependency package and the func-timeout dependency package;

[0086] Steps S21 to S22 first require the preparation of a Python environment. In this embodiment, version 3.7.5 is used, and dependent packages SQLAlchemy and func-timeout are installed: pip install SQLAlchemy==1.3.18, pip install func-timeout==4.3.5, where func-timeout is used to implement timeout protection.

[0087] S23: placing the encapsulation module in the python project directory.

[0088] In step S23, we put the aforementioned encapsulated module source code file into a directory of the Python project, which is placed under RoySQLAlchemy in this embodiment, and then reference the module encapsulated by this patent.

[0089] S3: Based on the deployment environment, create a running instance according to the encapsulation module.

[0090] Wherein, step S3 further comprises:

[0091] S31: Based on the Python environment, a data analysis database dependency package is installed, where the data analysis database is used to receive the return results of the SQL to be executed.

[0092] Wherein, the data analysis database in step S31 is pandas.

[0093] When referencing the encapsulated module, the specific code is: from RoySQLAlchemy import DBTool, DBConcurrencyMode, import pandas as pd. Pandas is introduced to receive and process the returned result set. When actually using it, the pandas dependency package must be installed in advance.

[0094] S32: According to the data analysis database, connect to the URL interface corresponding to the data analysis database.

[0095] S33: Specify the database driver and create a running instance.

[0096] In step S33, first prepare the URL for connecting to the database (for the sqlalchemy method create_engine()), specify the database driver (such as specifying the pymysql driver for mysql), and then create an instance of DBTool.

[0097] Different database drivers may have different ways to specify encoding (such as UTF-8). For example, the 'mysql+pymysql' method is to add "?charset=utf8mb4" to the end of the URL. When specifying the database driver, please follow the corresponding dependency package, such as pymysql specified above, and execute the command "pip install PyMySQL" to install it.

[0098] S4: Execute the SQL to be executed through the running instance to obtain an execution result.

[0099] Wherein, step S4 further comprises:

[0100] S41: Passing variables into SQL through parameterization method to obtain SQL to be executed.

[0101] S42: Execute the SQL to be executed through the running instance to obtain an execution result.

[0102] The purpose of step S41 is to prepare the SQL list to be executed. In order to prevent SQL injection, please use parameterized method to pass variables to SQL. For specific syntax, refer to SQLAlchemy. Then, in step S42, the SQL list is executed and the returned result set is parsed.

[0103] The present invention provides a method for increasing the asynchronous IO concurrent processing of a relational database by using SQLAlchemy, which can help automation script developers who need to use SQLAlchemy to operate the database to quickly develop automation scripts that can perform concurrent operations on the database by using the provided interface method, while also retaining the way to use the native functions of SQLAlchemy.

[0104] The present invention can greatly empower database operations in the development fields of automated operation and maintenance and automated testing, especially the development of related programs with concurrent operation requirements.

[0105] At the same time, the present invention has compatibility encapsulation to provide "concurrent operation in multi-threaded mode", "serial operation in single-threaded mode" and "single operation" to meet various scenarios of relational database CRUD operations, thereby improving the coding efficiency of developers and reducing the difficulty of development.

[0106] In addition, the present invention uses the "func-timeout" open source module to introduce a timeout protection mechanism to respond and process timeout operations in a timely and clear manner, providing the program with the necessary robustness, and standardizing the packaging of data returned by database operations, which is beneficial to the subsequent use and processing of the database return data by the program, especially beneficial for cooperation with the "Pandas" open source module.

[0107] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the embodiments of the present invention, rather than to limit them. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions recorded in the above embodiments, or replace some of the technical features therein by equivalents; and these modifications 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. Any changes or replacements that can be easily thought of by those skilled in the art within the technical scope disclosed in the present invention should be covered within the protection scope of the present invention.

Claims

1. A SQLAlchemy method for increasing asynchronous IO concurrent processing of a relational database, characterized in that: include: S1: Encapsulate the calling method of the relational database and obtain the encapsulation module; S2: Configure the deployment environment; S3: Based on the deployment environment, create a running instance according to the encapsulated module; S4: Execute the SQL to be executed through the running instance to obtain an execution result.

2. According to claim 1, a SQLAlchemy method for increasing asynchronous IO concurrency processing of a relational database is characterized in that: The packaging module in step S1 includes: DBTool class, which is used to operate and maintain a relational database; DBResult class, which is the return result of the SQL to be executed; DBResultData class, which is the return data table of the SQL to be executed.

3. According to claim 2, a SQLAlchemy method for increasing asynchronous IO concurrency processing of a relational database is characterized in that: The methods included in the DBTool class specifically include: Execute a single SQL, execute multiple SQLs concurrently, and execute multiple SQLs serially.

4. According to claim 3, a SQLAlchemy method for increasing asynchronous IO concurrency processing of a relational database is characterized in that: When the method selected by the DBTool class is to execute a single SQL, the execution steps specifically include: S111: Execute a single SQL statement with timeout protection; S112: Execute a single SQL statement without timeout protection.

5. According to claim 3, a SQLAlchemy method for increasing asynchronous IO concurrency processing of a relational database is characterized in that: When the method selected by the DBTool class is to execute multiple SQL statements concurrently, the execution steps specifically include: S121: Determine the thread type of the execution method, the thread type includes multithreading and coroutine, when the thread type is multithreading, execute step S122, when the thread type is coroutine, execute step S123; S122: Execute multiple SQL statements respectively through multiple threads, wherein each thread executes a single SQL statement according to a method selected by the DBTool class; S123: Execute the main function and the working function through the coroutine; S124: Based on the coroutine, the SQL to be executed is proxied to an execution method that executes a single SQL for execution.

6. According to claim 3, a SQLAlchemy method for increasing asynchronous IO concurrency processing of a relational database is characterized in that: When the method selected by the DBTool class is to execute multiple SQL statements serially, the execution steps specifically include: S131: Execute multiple SQL statements in sequence without timeout protection.

7. According to claim 1, a SQLAlchemy method for increasing asynchronous IO concurrency processing of a relational database is characterized in that: Step S2 further comprises: S21: Prepare Python environment; S22: Based on the Python environment, install the SQLAlchemy dependency package and the func-timeout dependency package; S23: placing the encapsulation module in the python project directory.

8. A method for increasing asynchronous IO concurrency processing of a relational database using SQLAlchemy according to claim 1, characterized in that: Step S3 further comprises: S31: Based on the Python environment, install a data analysis database dependency package, where the data analysis database is used to receive the return result of the SQL to be executed; S32: According to the data analysis database, connect to the URL interface corresponding to the data analysis database; S33: Specify the database driver and create a running instance.

9. A method for increasing asynchronous IO concurrency processing of a relational database by SQLAlchemy according to claim 1, characterized in that: The data analysis database in step S31 is pandas.

10. A method for increasing asynchronous IO concurrency processing of a relational database using SQLAlchemy according to claim 1, characterized in that: Step S4 further comprises: S41: Passing variables into SQL through parameterization method to obtain SQL to be executed; S42: Execute the SQL to be executed through the running instance to obtain an execution result.

Images