Method for efficiently and adaptively converting Python native SQL (Structured Query Language) into domestic database

Abstract

The invention belongs to the technical field of databases, and discloses a method for efficiently and adaptively converting Python native SQL (Structured Query Language) into a domestic database, which comprises the following steps of: S1, analyzing a Python native SQL code, and identifying the type of an original database; s2, selecting a corresponding conversion rule set according to the original database type and the target database type; s3, converting the SQL statement of the original database by using the conversion rule set; s4, adjusting original database connection information according to the target database type; s5, integrating the converted SQL statement and the adjusted database connection information to generate a new operation code; and S6, testing the new operation code. The method has the advantages that the native SQL can be automatically converted into the grammar suitable for the domestic database, a large amount of modification and debugging work is reduced, the project migration efficiency is improved by 95% or above, and the Python project migration localization efficiency is greatly improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method for efficiently and adaptively converting Python native SQL to a domestic database. Background Art

[0002] With the rapid development of information technology and the deepening of digital transformation, the market demand for databases, as the core infrastructure for data storage and management, will continue to grow. At the same time, the replacement process of domestic databases is accelerating through the dual promotion of policy guidance and support and market demand. In particular, driven by the "2+8+N" policy (that is, the party and government and eight major industries must achieve 100% domestic substitution of databases), the replacement rate of domestic databases in the party and government fields and the eight major industries is expected to further increase. Therefore, under the support of various factors, more and more companies and developers have begun to adopt domestic databases to improve data security and autonomous controllability. However, during the migration process, due to the differences in SQL syntax and characteristics between different databases, the original database operation code written based on Python native SQL needs to be manually modified and debugged in large quantities, which is not only time-consuming and labor-intensive, but also prone to errors. The existing traditional conversion method requires familiarity with the syntax rules of domestic databases, and then rewrites each line of native SQL code, thereby reducing development efficiency and increasing the code error rate.

[0003] Therefore, it is necessary to provide an efficient and adaptive method for converting Python native SQL to domestic databases, which can automatically convert according to the syntax characteristics of the target domestic database and generate Python database operation codes suitable for the domestic database. Summary of the invention

[0004] The present invention discloses a method for converting Python native SQL to a domestic database with high efficiency and adaptability, which can effectively solve the technical problems involved in the background technology.

[0005] To achieve the above object, the technical solution of the present invention is:

[0006] A method for efficiently and adaptively converting Python native SQL to a domestic database includes the following steps:

[0007] S1. Parse Python native SQL code and identify the original database type;

[0008] S2. Select a corresponding conversion rule set according to the original database type and the target database type;

[0009] S3, transform the original database SQL statement using the transformation rule set;

[0010] S4. Adjust the original database connection information according to the target database type;

[0011] S5, integrating the converted SQL statement and the adjusted database connection information to generate a new operation code;

[0012] S6. Test the new operation code.

[0013] The present invention relates to the technical field of database migration and conversion, and in particular to the problem that the business logic of Python projects is relatively complex and ORM cannot be implemented and native SQL database operation codes need to be written. A method is provided that can automatically and efficiently convert SQL statements of multiple mainstream database types (such as MySQL, PostgreSQL, SQLite, etc.) into those suitable for domestic databases (such as DAMO database, Jincang database, Huawei openGauss, etc.). An adapter for converting domestic databases based on multiple database types of Python native SQL, the adapter automatically converts according to the grammatical characteristics of the target domestic database by parsing the SQL statements and database connection information in the Python native SQL code, and generates Python database operation codes suitable for the domestic database. Parsing Python native SQL code: extracting database connection information, a configuration file or environment variable can be used, depending on how the application manages the database connection information to read and parse the SQL statements and database connection information contained in the Python code, and identify the original database type (such as MySQL, PostgreSQL, SQLite, etc.). Database type identification and conversion rule selection: according to the parsed original database type, a corresponding conversion rule set is selected, and the conversion rule set includes SQL syntax conversion rules from the original database type to the target domestic database type. SQL statement conversion: Apply the selected conversion rule set to convert SQL statements one by one to ensure that the converted SQL statements can be correctly executed on the target domestic database. The conversion process includes but is not limited to data type conversion, function replacement, operator adjustment, etc. Database connection information adjustment: According to the connection requirements of the target domestic database (such as DAMO database, Jincang database, Huawei openGauss, etc.), make corresponding adjustments to the database connection information, including database type, address, port, user name and password, etc. Generate converted code: Integrate the converted SQL statements and adjusted database connection information into the new Python code to generate database operation code suitable for domestic databases. Testing and verification: Test the generated converted code to ensure that it can be correctly executed on the domestic database and has stable performance. Testing includes but is not limited to unit testing, integration testing and stress testing.

[0014] As a preferred improvement of the present invention: the original database includes but is not limited to MySQL, PostgreSQL, and SQLite.

[0015] As a preferred improvement of the present invention: the target database type includes but is not limited to the DAMO database, the Jincang database, and Huawei openGauss.

[0016] As a preferred improvement of the present invention: in step S3, the conversion process includes but is not limited to data type conversion, function replacement, and operator adjustment.

[0017] As a preferred improvement of the present invention: in step S4, the connection information includes database type, address, port, user name and password.

[0018] As a preferred improvement of the present invention: in step S6, the test includes but is not limited to unit test, integration test and stress test.

[0019] As a preferred improvement of the present invention: the conversion rule set uses hash coding to perform data compression to reduce the number of bytes of the rule set.

[0020] As a preferred improvement of the present invention: the original database is MySQL, the target database is DAMO, and during the conversion process, SELECT LENGTH('hello world') becomes SELECT LENGTHB('hello world'), and SELECTCONCAT('Hello,','world!') becomes SELECT'Hello,'||'world!'.

[0021] As a preferred improvement of the present invention: the conversion rule set is obtained by comparing the differences in SQL syntax and characteristics between the original database and the target database.

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

[0023] All native SQL in this project can be automatically converted into syntax suitable for domestic databases, reducing a lot of modification and debugging work, improving the efficiency of migration projects by more than 95%, and greatly improving the efficiency of localization of Python project migration. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for describing the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work, among which:

[0025] Figure 1 It is a schematic diagram for comparing the method of the present invention with the existing method. DETAILED DESCRIPTION

[0026] The technical solutions in the embodiments of the present invention will be described clearly and completely below in combination with 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. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0027] It should be noted that all directional indications in the embodiments of the present invention (such as up, down, left, right, front, back, etc.) are only used to explain the relative position relationship, movement status, etc. between the components under a certain specific posture (as shown in the accompanying drawings). If the specific posture changes, the directional indication will also change accordingly.

[0028] In addition, in the present invention, descriptions such as "first", "second", etc. are only used for descriptive purposes and cannot be understood as indicating or implying their relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" or "second" may explicitly or implicitly include at least one of the features. In the description of the present invention, the meaning of "plurality" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined.

[0029] In the present invention, unless otherwise clearly specified and limited, the terms "connection", "fixation", etc. should be understood in a broad sense. For example, "fixation" 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 an indirect connection through an intermediate medium, it can be the internal connection of two elements or the interaction relationship between two elements, unless otherwise clearly defined. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0030] In addition, the technical solutions between the various embodiments of the present invention can be combined with each other, but it must be based on the fact that ordinary technicians in the field can implement it. When the combination of technical solutions is contradictory or cannot be implemented, it should be deemed that such combination of technical solutions does not exist and is not within the scope of protection required by the present invention.

[0031] See also Figure 1 As shown, the present invention provides a method for efficiently and adaptively converting Python native SQL to a domestic database, comprising the following steps:

[0032] S1. Parse Python native SQL code and identify the original database type;

[0033] S2. Select a corresponding conversion rule set according to the original database type and the target database type;

[0034] S3, transform the original database SQL statement using the transformation rule set;

[0035] S4. Adjust the original database connection information according to the target database type;

[0036] S5, integrating the converted SQL statement and the adjusted database connection information to generate a new operation code;

[0037] S6. Test the new operation code.

[0038] In this embodiment, the original database includes but is not limited to MySQL, PostgreSQL, and SQLite, and the target database type includes but is not limited to DAMO database, Jincang database, and Huawei openGauss. In step S3, the conversion process includes but is not limited to data type conversion, function replacement, and operator adjustment. In step S4, the connection information includes database type, address, port, user name, and password. In step S6, the test includes but is not limited to unit testing, integration testing, and stress testing. The original database is MySQL, and the target database is DAMO. During the conversion process, SELECT LENGTH('hello world') becomes SELECT LENGTHB('hello world'), and SELECT CONCAT('Hello,','world!') becomes SELECT'Hello,'||'world! '. The conversion rule set is obtained by comparing the differences in SQL syntax and characteristics between the original database and the target database. The conversion rule set uses hash coding to compress data to reduce the number of bytes in the rule set.

[0039] Creating a SQL rule set is similar to the instruction set for AI training. Using specific encoding (hash encoding) to compress data reduces the number of bytes in the rule set. Introducing heuristic algorithms, beam search, binary search and other algorithms to optimize search matching (search matching corresponding rule sets). Using caching mechanisms and multi-threading for concurrent processing in high-concurrency scenarios to improve conversion efficiency, native SQL searches for the same type of syntax that matches the corresponding database type through the SQL rule set. Correspondingly, the present invention also provides an adapter that stores the specific operation process and rule set of the method of the present invention. The adapter is installed in the development environment that needs to be converted to complete the conversion of the database.

[0040] Ruleset overview and examples

[0041] Overview: A rule set is a custom rule engine that combines various rules into a single set. These rules can be about data processing, system behavior, user interaction, business logic, etc. The rule set allows users or developers to customize how to handle and respond to different situations or events according to specific needs.

[0042] Specific description and examples: 1. The SQL rule set is divided into multiple subsets, such as the rule set for the difference between MySQL and DAMO database functions, the rule set for the difference between MySQL and Jinchang database functions, the rule set for the difference between MySQL and Huawei openGauss database functions, etc. 2. Take the rule set for the difference between MySQL / PostgreSQL and the three major domestic databases (DAMO database, Jincang database, Huawei openGauss) as an example (partial rule data).

[0043] 1. String processing

[0044] Rule 1.1: String length calculation

[0045] PostgreSQL: Use the LENGTH() function to return the byte length of a string.

[0046] MySQL: Use the LENGTH() function to return the byte length of a string.

[0047] DAMO: Use the LENGTH() function to return the character length of a string. If you need the byte length, use specific functions such as LENGTHB().

[0048] Gold warehouse: Use CHAR_LENGTH() or LENGTH().

[0049] openGauss: Use the CHAR_LENGTH() function to return the character length of a string, and the LENGTH() function to return the byte length.

[0050] Rule 1.2: String concatenation

[0051] PostgreSQL: Use the CONCAT() function to return the byte length of a string.

[0052] MySQL: Use the CONCAT() function to concatenate strings.

[0053] DAMO: Use the || operator to concatenate strings.

[0054] Jinchang: Use the CONCAT() function, but you can also use the || operator.

[0055] openGauss: Use the CONCAT() function, or use the || operator.

[0056] 2. Date and time processing

[0057] Rule 2.1: Get the current date and time

[0058] PostgreSQL: Use CURRENT_TIMESTAMP or NOW() function.

[0059] MySQL: Use the NOW() function.

[0060] DAMO: Use SYSDATE function or CURRENT_TIMESTAMP.

[0061] Cash Warehouse: Use CURRENT_TIMESTAMP or CURRENT_DATE.

[0062] openGauss: Use CURRENT_TIMESTAMP or LOCALTIMESTAMP.

[0063] Rule 2.2: Date addition and subtraction

[0064] PostgreSQL: Use the + and - operators with date interval types (such as INTERVAL '1DAY') to add and subtract dates.

[0065] MySQL: Use the DATE_ADD() and DATE_SUB() functions to add and subtract dates.

[0066] DAMO: Use + and - operators with date interval types (such as INTERVAL'1DAY') to add and subtract dates.

[0067] Jincang: Use + and - operators with date intervals (such as INTERVAL '1DAY') to add and subtract dates.

[0068] openGauss: Use + and - operators with date intervals (such as INTERVAL'1DAY') to add or subtract dates, or use DATE_TRUNC() and DATE_PART().

[0069] Rule 2.3: Converting strings to dates

[0070] PostgreSQL: Use the TO_DATE() function.

[0071] MySQL: Use the STR_TO_DATE() function.

[0072] DAMO: Use the TO_DATE() function and specify the date format.

[0073] Gold warehouse: Use the TO_DATE() function and specify the date format.

[0074] openGauss: Use the TO_DATE() function and specify the date format.

[0075] Rule 2.4: Date to string

[0076] PostgreSQL: Use the TO_CHAR() function.

[0077] MySQL: Use the DATE_FORMAT() function.

[0078] DAMO: Use the TO_CHAR() function and specify the date format.

[0079] Gold warehouse: Use the TO_CHAR() function and specify the date format.

[0080] openGauss: Use the TO_CHAR() function and specify the date format.

[0081] 3. Conditional judgment

[0082] Rule 3.1: Conditional expressions

[0083] PostgreSQL: Use CASE expressions for conditional judgment.

[0084] MySQL: Use the IF() function for conditional judgment.

[0085] DAMO: Use CASE expression or DECODE() function to make conditional judgments.

[0086] Jincang: Also supports IF() function and CASE expression.

[0087] openGauss: Also supports IF() function and CASE expression.

[0088] Basic rule set example (how to convert)

[0089] Example 1: String length calculation

[0090] The source SQL (MySQL / PostgreSQL) is: SELECT LENGTH('hello world');

[0091] According to the above rule set definition, LENGTH in the source SQL can be replaced with LENGTHB in the DM database. If it is a Jincang / openGauss database, it can be replaced with CHAR_LENGTH(). The following is the converted SQL:

[0092] DAMO: SELECT LENGTHB('hello world'); Jincang / openGauss, use SELECT CHAR_LENGTH('hello world').

[0093] Example 2: String concatenation

[0094] Source SQL (MySQL / PostgreSQL): SELECT CONCAT('Hello,','world!');

[0095] According to the above rule set definition, CONCAT in the source SQL can be replaced with || in Jincang / openGauss / DaMeng database. The following is the converted SQL:

[0096] Jincang / openGauss / DaMeng: SELECT 'Hello,'|| 'world! '.

[0097] Example 3: Get the current date and time

[0098] Source SQL (MySQL / PostgreSQL): SELECT NOW();

[0099] According to the above rule set definition, NOW in the source SQL can be replaced with SYSDATE or CURRENT_TIMESTAMP in the DAMO database. If it is the Jinchang database, it can be replaced with CURRENT_TIMESTAMP or CURRENT_DATE. If it is openGauss, it can be replaced with CURRENT_TIMESTAMP or LOCALTIMESTAMP. The following is the converted SQL:

[0100] Dameng: SELECT SYSDATE FROM DUAL; or SELECT CURRENT_TIMESTAMP FROM DUAL. Gold warehouse: SELECT CURRENT_TIMESTAMP; or SELECT CURRENT_DATE. openGauss: SELECT CURRENT_TIMESTAMP or SELECT LOCALTIMESTAMP.

[0101] Example 4: Date addition and subtraction

[0102] Source SQL (MySQL): SELECT DATE_ADD('2023-01-01', INTERVAL 1DAY); or source SQL (PostgreSQL): SELECT'2023-01-01'+INTERVAL'1day';

[0103] According to the above rule set definition, DATE_ADD('2023-01-01', INTERVAL1DAY) in the source SQL (MySQL) can be replaced with '2023-01-01'+INTERVAL'1day' in the DAMO / Jinchang / openGauss database. If the source SQL is PostgreSQL, the statement does not need to be changed. The following is the converted SQL:

[0104] DAMO / Jinchang / openGauss: SELECT'2023-01-01'+INTERVAL'1day'.

[0105] Example 5: Convert date to string

[0106] Source SQL (MySQL / PostgreSQL): SELECT DATE_FORMAT('2023-01-01', '%d / %m / %Y');

[0107] According to the above rule set definition, DATE_FORMAT() in the source SQL can be replaced with TO_CHAR() in the DAMO / Jinchang / openGauss database. The following is the converted SQL:

[0108] DAMO / Jinchang / openGauss: SELECT TO_CHAR('2023-01-01','%d / %m / %Y').

[0109] Example 6: Conditional Expression

[0110] Source SQL (MySQL): SELECT IF(a>5000,'true_value','false_value')AS salary_level FROM table; Source SQL (PostgreSQL): SELECT CASE WHEN a>THEN'true_value'ELSE'false_value'END AS salary_level FROM table;

[0111] According to the above rule set definition, IF(a>5000,'true_value','false_value')AS salary_level in the source SQL (MySQL) can be replaced with the syntax CASE WHEN a>THEN'true_value'ELSE'false_value'END AS salary_level supported by the DAMO database. If it is Jinchang / openGauss database, no conversion is required because they support IF syntax. If the source SQL is PostgreSQL, then DAMO / Jinchang / openGauss databases do not need to be converted because they all support CASE expressions. The following is the SQL after the source SQL is converted from MySQL:

[0112] Dameng: SELECT CASE WHEN a>THEN'true_value'ELSE'false_value'END ASsalary_level FROM table; Jincang: SELECT IF(a>5000,'true_value','false_value')ASsalary_level FROM table; openGauss: SELECT IF(a>5000,'true_value','false_value')AS salary_level FROM table.

[0113] An adapter for converting domestic databases based on Python native SQL multiple database types includes the following steps: parsing Python native SQL code to identify the original database type; selecting a corresponding conversion rule set according to the original database type; applying the conversion rule set to convert SQL statements; adjusting database connection information to adapt to the target domestic database; generating converted Python database operation code; and testing and verifying the generated code. The specific operation of the step of parsing Python native SQL code and identifying the original database type is as follows: extracting database connection information can use configuration files or environment variables, which depends on how the application manages database connection information, and parsing the SQL statements therein is a regular expression, which can better handle the boundary problem of SQL statements by matching SQL statements. The conversion rule set includes data type conversion rules, function replacement rules, and operator adjustment rules, etc. The specific operation is to create a rule set and use a specific encoding to compress and reduce the file byte size. At the same time, a clustering algorithm is used in the search, and a binary query is used to search for the same syntax function of the database type corresponding to the native SQL in the rule set, and then convert it into the required SQL. The test and verification steps include unit testing, integration testing, and stress testing, etc.

[0114] Suppose there is a database operation code written in Python native SQL, which was originally used to connect to the MySQL database. Now the code needs to be migrated to a domestic database, such as the DM database. First, the code is parsed using the adapter provided by the present invention to identify that the original database type is MySQL. Then, the conversion rule set from MySQL to the DM database is selected. Next, the SQL statement is converted using the conversion rule set, such as replacing AUTO_INCREMENT in MySQL with IDENTITY in the DM database. At the same time, the database connection information is adjusted, the database type is changed to the DM database, and the corresponding address, port, user name and password are modified. Finally, the converted SQL statement and the adjusted database connection information are integrated into the new Python code to generate a database operation code suitable for the DM database. After the migration is completed, the generated code is tested to ensure that it can be executed correctly on the DM database and has stable performance. It should be further explained that the use of other components to achieve the above-mentioned effects should all belong to the inventive concept of the present invention and should belong to the protection scope of the present invention.

[0115] Improved migration efficiency: The adapter is encapsulated as a third-party library in the Python project. You only need to install and execute the adapter to automatically convert all native SQL in the project into syntax suitable for domestic databases, reducing a lot of modification and debugging work. The efficiency of migration projects is improved by more than 95%, greatly improving the efficiency of localization of Python project migration.

[0116] Automatic adaptation of multiple databases: There are many mainstream database types for native SQL, such as MySQL, PostgreSQL, SQLite, etc. At the same time, domestic databases are also divided into DAMO, Jincang, Huawei openGauss and other domestic databases. This adapter can realize automatic conversion of multiple databases. You only need to configure the corresponding Python database adapter to realize the conversion of Mysql->DAMO / openGauss / Jincang, PostgreSQL->DAMO / openGauss / Jincang and other similar database types into multiple domestic database types.

[0117] Although the embodiments of the present invention have been disclosed as above, they are not limited to the applications listed in the specification and the embodiments. They can be fully applied to various fields suitable for the present invention. For those familiar with the art, additional modifications can be easily implemented. Therefore, without departing from the general concept defined by the claims and the scope of equivalents, the present invention is not limited to the specific details and the illustrations shown and described herein.

Claims

1. A method for efficiently and adaptively converting Python native SQL to domestic database, characterized in that: The following steps are involved: S1. Parse Python native SQL code and identify the original database type; S2. Select a corresponding conversion rule set according to the original database type and the target database type; S3, transform the original database SQL statement using the transformation rule set; S4. Adjust the original database connection information according to the target database type; S5, integrating the converted SQL statement and the adjusted database connection information to generate a new operation code; S6. Test the new operation code.

2. According to claim 1, a method for converting Python native SQL to domestic database is characterized in that: The original database includes but is not limited to MySQL, PostgreSQL, and SQLite.

3. According to claim 1, a method for converting Python native SQL to domestic database with high efficiency and adaptability, characterized in that: The target database types include but are not limited to DAMO database, Jincang database, and Huawei openGauss.

4. According to claim 1, a method for converting Python native SQL to domestic database with high efficiency and adaptability, characterized in that: In step S3, the conversion process includes but is not limited to data type conversion, function replacement, and operator adjustment.

5. The method of converting Python native SQL to domestic database according to claim 1 is characterized in that: In step S4, the connection information includes database type, address, port, user name and password.

6. The method of converting Python native SQL to domestic database according to claim 1 is characterized in that: In step S6, the testing includes but is not limited to unit testing, integration testing and stress testing.

7. The method of converting Python native SQL to domestic database according to claim 1 is characterized in that: The conversion rule set uses hash coding to perform data compression to reduce the number of bytes of the rule set.

8. The method of converting Python native SQL to domestic database according to claim 1 is characterized in that: The original database is MySQL, and the target database is DAMO. During the conversion process, SELECT LENGTH('helloworld') becomes SELECT LENGTHB('hello world'), and SELECT CONCAT('Hello,','world!') becomes SELECT'Hello,'||'world!'.

9. The method of converting Python native SQL to domestic database according to claim 1 is characterized in that: The transformation rule set is obtained by comparing the differences in SQL syntax and features between the original database and the target database.

Images