A Python-based ORM design method and device

Abstract

A Python-based ORM design method and device, the user passes the entity object to the SUID API; the entity object is converted into SQL and placeholder parameter values; the SQL statement and the value of the placeholder parameter are used to call the underlying method to operate the DB; the SUID operation has a return value. When the entity object is converted into SQL, it is processed by the device of the present invention, and the object attribute-value dictionary and class attribute are first obtained by parsing the entity object, and then SQL is further obtained; anti-SQL injection is supported; native SQL is supported; the columns of the row result set correspond to the specific attributes of the object. Common entity types are supported, and the entity class does not need to inherit other classes and does not need to be bound to the device of the present invention; the entity attributes can use the basic types of Python; at least 7 common entity types are supported. The database to be used can be specified by configuration; the driver required by a certain database is loaded in a dynamic manner; built-in support: MYSQL, SQLite, ORACLE and PostgreSQL. The device is responsible for managing connections, transaction submission, rollback, etc. The coding complexity is O(1).

Description

Technical Field

[0001] The present invention relates to the fields of computer technology and software technology, and in particular to an ORM design method and device based on Python language. Background Art

[0002] Description of terms or abbreviations:

[0003] ORM: Object Relational Mapping. ORM is the mapping of object concepts in object-oriented programming with the concepts of tables in relational databases; thereby changing direct operations on the database to operations in an object-oriented way, thereby indirectly operating the database tables through operating objects. The device that implements OPM should have the function of automatically converting the data format or data structure between object-oriented and relational databases.

[0004] DB: database.

[0005] SUID: The abbreviation for the four SQL command operations of querying, updating, inserting and deleting (Select, Update, Insert, Delete) the database (SUID is the first letters of the four operations).

[0006] Coding complexity C(n):

[0007] Coding complexity refers to the changing trend of the amount of work required to write code when solving a problem as the problem size n increases. If the amount of work required to write code increases in direct proportion to the problem size n, then the coding complexity of the problem is O(n); if the amount of work required to write code is independent of the problem size n and remains constant, then the coding complexity of the problem is O(1). Therefore, the coding complexity C(n) can be expressed by the function O, which reflects the relationship between the amount of coding work required to solve the problem and the problem size. Obviously, to solve the same problem, a device, framework or solution with a coding complexity of O(1) will require less coding.

[0008] Problems with existing Python-based ORMs:

[0009] The entity classes defined based on Django, SQLAlchemy, etc. must inherit the classes of their frameworks and be bound to the frameworks; the entity class definitions are too complex, especially for scenarios that do not require database tables to be created based on entity classes, the table structure-related information defined in the entity does not bring any benefits; these two frameworks cannot handle ordinary entity classes.

[0010] Java's ORM only needs to parse the objects of the entity class, but Python is a dynamic language, and the attributes of the class are relatively isolated from the attributes of the object; therefore, Java's ORM parsing principle is not universal in Python. According to Python's language characteristics, it is not possible to parse the information required by SQL only from the object. Summary of the invention

[0011] The present invention aims to at least solve the above technical problems and make related improvements.

[0012] To this end, one object of the present invention is to provide a method for solving the above-mentioned problem; another object of the present invention is to provide a device for solving the above-mentioned problem.

[0013] In order to achieve the above first object, the present invention proposes the following technical solution:

[0014] The user passes the entity object to the SUID API; the object and the corresponding class are converted into SQL and placeholder parameter values, and SQL can prevent injection; when interacting with the DB, the SQL statement and the value of the placeholder parameter are used to call the underlying method to operate the DB (if it is a query operation, the entity type must also be passed in), and the result set is converted into appropriate structure information and returned. When converting objects to SQL, according to the language characteristics of Python, the information required by SQL cannot be parsed only from the object, but from the object and its corresponding class. You can specify which database to use based on the configuration information.

[0015] In order to achieve the above second purpose, the present invention proposes the following technical solution:

[0016] The device of the present invention at least comprises: an object-oriented SUID API module, an object-to-sql statement module, a DB interaction module and a context module. The object-oriented SUID API module is an API for developers to operate DB in an object-oriented manner, and the API provides simple and easy-to-use query, update, insert and delete methods; the module also includes the implementation of the API. The object-to-sql statement module converts the object passed into the SUID method into a corresponding sql statement and a placeholder parameter value. The DB interaction module uses the sql statement and the value of the placeholder parameter to call the underlying method to operate the DB (if it is a query operation, the entity type must also be passed in), and converts the obtained result set into appropriate structural information for return; when it is a query type, the query result can be assembled into a corresponding entity model structure result according to the passed in entity type. The context module is used to store the relevant configuration information of the device of the present invention and the upper and lower file information used; for example, when the object-to-sql statement module converts non-standard sql statements, it needs to obtain the specific database name currently used so as to convert it into a sql statement of the corresponding database. The DB interaction module can use the corresponding database driver according to the configuration information; the user can also prepare the Connection object and pass it to the device.

[0017] The beneficial effects achieved by the present invention are:

[0018] The coding complexity C(n) of ORM is O(1). The device of the present invention uses a unified API to operate the DB. Developers only need to focus on the use of the object-oriented SUID API of the device of the present invention. When the scale of the problem is large, no additional labor is required. The entity class corresponding to the table can only use ordinary entity classes, without adding additional table structure information and framework-related information. The database to be used can be specified based on the configuration information. Supports anti-sql injection; supports native sql; the device is responsible for managing the connection, transaction submission, rollback, and other implementation logic. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 , object-oriented way to operate DB steps;

[0020] Figure 2 , modules and their call relationship graph;

[0021] Figure 3 , dynamically load the database driver process;

[0022] Figure 4 , the process of obtaining object attribute-value dictionary and class attributes through entity object parsing. DETAILED DESCRIPTION

[0023] The specific implementation of the present invention provides a Python-based ORM design method and device.

[0024] The embodiment described below in conjunction with the accompanying drawings (when involving specific language description, Python language description is used) is only one implementation method of the present invention and should not be understood as a limitation of the present invention.

[0025] The specific embodiment of the present invention provides a method for ORM to perform object-oriented operation on DB, and the flow chart of the steps is as follows: Figure 1 The specific steps are as follows.

[0026] Step 1, the user passes the entity object to the SUID API method;

[0027] Step 2: Parse the entity to get SQL, and generate SQL statements and placeholder parameter values ​​according to the entity object and the corresponding class. To prevent SQL injection attacks, the SQL statement uses placeholders and puts the value of the corresponding placeholder parameter into a variable.

[0028] Step 3, when interacting with the DB, use the SQL statement and the value of the placeholder parameter to call the underlying method to operate the DB (if it is a query operation, the entity type must also be passed in), convert the result set into an object of the appropriate type and return it as the result.

[0029] Step 4: Return the generated result.

[0030] In the ORM device provided by the present invention, steps 2 to 4 are automatically completed by the device; the developer only needs to use the API method in step 1.

[0031] The device of the present invention at least comprises: an object-oriented SUID API module, an object-to-sql statement module, a DB interaction module and a context module. The module and its call relationship diagram are shown in FIG. Figure 2As shown, the object-oriented SUID API module calls the object-to-sql statement module to obtain the sql and the corresponding placeholder parameter values, and then calls the DB interaction module to obtain the result after operating the DB. The object-oriented SUID API module is an API for developers to operate the DB in an object-oriented manner. The API provides a simple and easy-to-use SUID method. The object-to-sql statement module generates sql statements and placeholder parameter values ​​according to the object and the corresponding class passed into the SUID method. The DB interaction module uses the sql statement and the value of the placeholder parameter to call the underlying method to operate the DB (if it is a query operation, the entity type must also be passed in), and the obtained result set is converted into an object of a suitable type and used as the returned result; when it is a query type, the query result can be assembled into the result of the corresponding entity model list according to the passed-in entity type. The context module is used to store the relevant configuration information of the device of the present invention and the upper and lower file information used; for example, when the object-to-sql statement module converts non-standard sql statements, it needs to obtain the specific database name currently used so as to convert it into the sql statement of the corresponding database; the DB interaction module selects the database of the object according to the configuration information and loads the corresponding driver.

[0032] The corresponding relationship between module names and implementation class names is shown in the following table:

[0033]

[0034] The methods provided by the Suid class include the basic select, update, insert, and delete methods. In addition, select_paging is a query method with paging function. The Suid class can also provide more abundant select, update, insert, and delete related operation methods as needed, such as specifying query columns, specifying sorting, grouping, using comparison operators other than equal signs, etc.; but the source code only provides some examples, and related methods can be added according to actual needs. The ObjToSQL interface is responsible for generating the corresponding sql statement for Suid. BeeSql is responsible for providing methods for operating the DB based on the generated sql statement. Developers can complete object-oriented DB operations using only the methods of the Suid class, and the rest of the work is completed by the device provided by the present invention. The specific code is shown in the source code in the appendix. The following is a brief description of the purpose of the code:

[0035] paging.py is related to sql paging;

[0036] HoneyUtil in util.py is the relevant tool class;

[0037] The BeeFactory class in factory.py is used to get and set Connection;

[0038] In addition, the Key class of key.py is some keyword classes used;

[0039] The HoneyConfig class in config.py is the configuration class;

[0040] The HoneyContext class in context.py is the context class, which returns the connection, the placeholder used by the database driver, etc.

[0041] Logger in logger.py is the log class;

[0042] DatabaseConst in const.py is a constant class for the database name.

[0043] The py files introduced are placed in the bee directory.

[0044] Before using the ORM device, you need to determine the database to be used. The corresponding configuration information of the database is managed by the HoneyConfig class. The ConnectionBuilder class is responsible for driver management and generates connections. The source code is shown in Appendix 8.bee.conn_builder.py. The flowchart of dynamically loading database drivers is shown in Figure 3 The specific process is as follows:

[0045] 1) Users can specify the DB module name dbModuleName. Even if the framework has a built-in driver for the corresponding database, users can use their own;

[0046] 2) If the user does not specify dbModuleName, check whether dbName is a database supported by the framework by default. If not, the driver loading fails;

[0047] 3) If it is a database supported by the framework by default, use the dbModuleName of the corresponding driver preset by the framework;

[0048] 4) Use importlib.import_module(dbModuleName) to load (import) the corresponding driver.

[0049] dbName is used to specify the database used, such as MySQL, Oracle, etc.; dbModuleName is used to specify the driver dependency library used by the data.

[0050] ConnectionBuilder supports four databases by default: MYSQL, SQLite, ORACLE and PostgreSQL.

[0051] Through the above dynamic loading of database driver process, it can be seen that the invention device supports loading the driver required by a database in a dynamic way, and can support any database through configuration. In addition, the BeeFactory class is used to obtain and set the Connection. Therefore, the invention device is decoupled from the database and can easily support any database.

[0052] In api.py, according to the entity object entity, the parsing method of obj2sql.py is called and the sql and the corresponding placeholder parameter values ​​are returned; then the method of the DB interaction module sqllib.py is called to obtain the processed result; and the corresponding result is returned. All four types of SUID can have return values.

[0053] In obj2sql.py, we first need to parse the entity object to get the object attribute-value dictionary and class attributes, and then parse the table name to get the sql and the corresponding placeholder parameter value. Because Python is a dynamic language, the class attributes are relatively isolated from the object attributes. If an attribute is added to the object, even if it is None, the attribute with the same name defined in the class will be blocked. The flowchart of obtaining the object attribute-value dictionary and class attributes through entity object parsing is as follows: Figure 4 As shown, the specific steps are as follows:

[0054] 1) Get the entity type corresponding to the object;

[0055] 2) Get the class attribute field classField according to the entity type;

[0056] 3) Get the dictionary structure object of the class attributes and values ​​(including private variables starting with _) according to the entity type, and delete the _ or _ prefix;

[0057] 4) Get the dictionary structure object fieldAndValue of the object's attributes and values ​​according to the object and delete the _ or _ prefix;

[0058] 5) Delete the dynamically added attributes of the object, that is, the attributes that the class attributes do not have but the object attributes have;

[0059] 6) If the value of the object's attribute is None, the class-level attribute and value are used to replace it (put into fieldAndValue); if the class-defined attribute is set with a specific value, all objects can share it. When the object does not set the value, the class's value is used;

[0060] 7) When the object's attribute has no corresponding attribute item, but the class's attribute has and is not None, the class-level attribute and value are used (put into fieldAndValue);

[0061] 8) Exclude items whose value in fieldAndValue is None; by default, only attributes with values ​​are required;

[0062] 9) Return fieldAndValue and classField.

[0063] If you want to use the same SQL statement for multiple objects of the same entity with different values ​​in batch insert, step 8) can be omitted.

[0064] The process of generating SQL through entity objects:

[0065] 1) Through the above steps, get fieldAndValue and classField;

[0066] 2) Get the table name tableName;

[0067] 3) Generate SQL and the corresponding placeholder parameter values ​​based on the values ​​obtained in the above two steps; where:

[0068] Select classField as the query field and fieldAndValue as the filter condition to query the records that meet the conditions in the table tableName;

[0069] Update, insert, and delete only use fieldAndValue;

[0070] update uses the primary key field of fieldAndValue as the filter condition and other values ​​as the set value;

[0071] Insert converts fieldAndValue into field name and corresponding field value, and inserts the value into the corresponding table;

[0072] delete uses fieldAndValue as the filtering condition and deletes the records that meet the condition in the table tableName.

[0073] When ObjToSQL converts sql, in addition to returning sql, it also returns the value of the corresponding placeholder parameter. Because the invention device supports anti-sql injection, sql uses a placeholder method (Appendix source code 2.bee.obj2sql.py), and the corresponding parameter values ​​need to be separated.

[0074] When assembling the query results into an object list, Python does not provide a method for obtaining the value of a specified column in a row of records based on the column name, so it is necessary to know what column name the value of each column in the row of records to be queried corresponds to. The inventive device achieves the correspondence between each column of the row record and the column name, that is, the columns of the row result set correspond to the specific attributes of the object.

[0075] First, get the column names:

[0076] column_names=[description[0]for description in cursor.description]

[0077] The order of the column name array corresponds to each column in a row of records; therefore, by traversing the column name array and using the same subscript index, you can get the value corresponding to the column and set the value to the specified attribute of the entity object. For details, see the select method of BeeSql and the transform_result method of the HoneyUtil class in Appendix 4.bee.sqllib.py.

[0078] The invention device supports directly using native SQL to operate the database, and directly calls the method of the BeeSql class of the DB interaction module and passes in the corresponding parameters. The invention device is responsible for managing connections, transaction submission, rollback, etc., and users do not need to encode related content. In the DB interaction module, especially in the select and modify methods of the BeeSql class, connections and transactions have been managed, and the details of specific use are shielded. Users do not need to care about these details and can complete the operation of the database. Modify means modification, including three operations: update, insert, and delete.

[0079] The following are examples of entity classes defined with the SQLAlchemy and Django ORM frameworks.

[0080] Example of an entity defined by SQLAlchemy:

[0081]

[0082] Example of an entity defined by Django:

[0083]

[0084] From the above two examples, we can see that the entity classes defined by Django and SQLAlchemy must inherit the classes of the specific framework and are strongly bound to the specific framework. The attribute types cannot use the basic types of Python, but the specific types of the framework, and the table structure information is also added. For scenarios where database tables do not need to be created based on entity classes, the table structure information defined in the entity does not bring any benefits; these two frameworks cannot handle ordinary entity classes.

[0085] The invention device supports common entity classes, which do not need to inherit other classes and are not strongly bound to the framework; and supports multiple types of entities. The following are 8 entity examples of different forms. Entity Example 1 is the most common format, and its attributes do not have an underscore prefix; in Entity Example 2, the attributes use a single underscore; in Entity Example 3, the attributes use two underscores; Entity Example 4 has a constructor more than Entity Example 1; Entity Example 5 is an entity using get / set, in which, in addition to get / set, the class can have attributes or not (but it is recommended that when defining an entity using the get / set format, it is recommended that the class level no longer put attributes), and the constructor can also be there or not; Entity Example 6 is an example of an entity constructor supporting dynamic parameters of a dictionary structure. Entity attributes can use Python's basic types, and can be explicitly declared using specific basic type names. Entity Examples 7 and Entity Examples 8 are specific examples. In addition, _tablename_ is the table name corresponding to the specified entity, which can be omitted. If omitted, the class name is used for conversion; _pk_ can be used to specify the primary key column name of the table corresponding to the entity, and id is used as the primary key name by default.

[0086] Entity Example 1:

[0087]

[0088] Entity Example 2:

[0089]

[0090] Entity Example 3:

[0091]

[0092] Entity Example 4:

[0093]

[0094] Entity Example 5:

[0095]

[0096]

[0097] Entity Example 6:

[0098] Entity Example 7:

[0099] Entity Example 8:

[0100]

[0101] The following is an example of using the SUID of the invention. First, the entity object is obtained. According to the entities of various structures, different methods are used to define the entity object; then the entity object is passed to the method of the Suid class. When defining the object, the dynamically added attributes will not be converted to SQL.

[0102]

[0103] From the above usage examples, it can be seen that no matter what entity table is queried, it can be passed to the select() method, and the other update, delete, and insert methods are the same, and can receive any entity. Therefore, for the number of database tables, the coding complexity of the ORM tool provided by the invention device is O(1).

[0104] In addition, according to the entity class structure rules defined by Django and SQLAlchemy, methods compatible with them can be written by imitating the principles introduced in the above embodiments.

[0105] According to the principle of convention over configuration, the naming rules used by the database table name and field name and the entity class name and attribute name can be converted between the two names. For example, the name of the DB field my_bee and the entity attribute name myBee can be converted to each other, that is, the DB underscore naming and camel case naming can be automatically converted. In addition, it is recommended to use the same name for the corresponding database table name and entity class name, and the corresponding database table field name and entity attribute name, so that no name conversion is required between the database and Python. This can improve the running efficiency of the program. After using the naming convention, different naming conversions are required. If command conversion is to be supported, the name must be converted before use.

[0106] The coding complexity C(n) of ORM in the device of the present invention is O(1). The device of the present invention uses a unified dao to operate the DB. Even if the number of entities to be completed for the DB operation increases, the developer does not need to write any specific dao implementation. The developer only needs to pay attention to how to use the object-oriented SUID interface of the device of the present invention. When the scale of the problem is large, no additional labor is required.

[0107] For scenarios where the actual DB table structure or complex queries are not disclosed, you can define DB views and map the entity model to the views to solve the corresponding problems.

[0108] In the embodiment, Python uses the syntax of version 3.x. If there is any incompatibility with versions such as python 2.7, it can be replaced with the corresponding syntax expression.

[0109] Although the embodiments of the present invention have been shown and described, they are not intended to limit the present invention. For those skilled in the art, various changes and variations may be made in the present application, including different computer languages ​​for implementation. It is particularly important to note that although the present invention is based on the Python language, the Python language cannot be used as a language to limit the scope of the claims of the present invention. Dynamic languages ​​similar to Python can be easily rewritten using the language with reference to the embodiments of the present invention. Without departing from the principles and design ideas of the present invention, various changes, modifications, substitutions and variations of these embodiments should all be included in the scope of the claims of the present invention.

[0110] Appendix: Source code

[0111]

[0112]

[0113]

[0114]

[0115]

[0116]

[0117]

[0118]

[0119]

[0120]

[0121]

[0122]

Claims

1. A Python-based ORM design method, characterized in that: include: The user passes the entity object to the SUID API; the entity object and the corresponding class are converted into SQL and placeholder parameter values; When interacting with the DB, use SQL statements and placeholder parameter values ​​to call the underlying method to operate the DB. If it is a query operation, the entity type must be passed in, and the result set is converted into an entity list object and returned as the result. If it is a change operation, the number of affected rows is returned; SUID operations all have return values; To convert entity objects into SQL and placeholder parameter values, first parse the object to get the object attribute-value dictionary and class attributes, then parse the table name to get the SQL and the corresponding placeholder parameter values; The specific steps to obtain the object attribute-value dictionary and class attributes through entity object parsing are as follows: 1) Get the entity type corresponding to the object; 2) Get the class attribute field classField according to the entity type; 3) Get the dictionary structure object of the class attributes and values ​​(including private variables starting with _) according to the entity type, and delete the _ or _ prefix. 4) Get the dictionary structure object fieldAndValue of the object's attributes and values ​​according to the object and delete the _ or _ prefix; 5) Delete dynamically added attributes of an object; 6) If the value of the object's attribute is None, replace it with the class-level attribute and value; 7) When the object's attribute has no corresponding attribute item, but the class's attribute has and is not None, the class-level attribute and value are used; 8) Exclude items whose value in fieldAndValue is None; by default, only attributes with values ​​are required; 9) Return fieldAndValue and classField; Support anti-sql injection; support native sql; The columns of the row result set correspond to the specific attributes of the object; Support common entity types. Entity classes do not need to inherit other classes and do not need to be bound to the device of the present invention. Entity attributes can use Python's basic types. There are at least 7 common entity types supported. Entity types can be standard get / set format entities. Entity type attributes can use _ or _ prefixes. The entity construction method supports dictionary structure dynamic parameter mode. You can specify the database to be used through configuration; dynamically load the driver required by a database; The encoding complexity is O(1).

2. The method according to claim 1, characterized in that: Supported databases: MYSQL, SQLite, ORACLE and PostgreSQL.

3. The method according to claim 1, characterized in that: The device is responsible for managing connections, transaction commit, and rollback implementation logic, so users do not need to write this part of the code.

4. A Python-based ORM device, characterized in that: include: Object-oriented SUID API module, object to SQL statement module, DB interaction module and context module; The object-oriented SUID API module calls the object-to-sql statement module to obtain the sql and the corresponding placeholder parameter values, and then calls the DB interaction module to obtain the result after operating the DB; The object-oriented SUID API module is an API for developers to operate DB in an object-oriented manner. The API provides SUID methods. The object-to-sql statement module generates SQL statements and placeholder parameter values ​​according to the objects and corresponding classes passed into the SUID method. The DB interaction module uses SQL statements and placeholder parameter values ​​to call the underlying methods to operate DB. If it is a query operation, the entity type must also be passed in, and the result set is converted into an entity list object as the returned result. If it is a change operation, the number of affected rows is returned. SUID operations all have return values. The columns of the row result set correspond to the specific attributes of the object; Support common entity types. Entity classes do not need to inherit other classes and do not need to be bound to the device of the present invention. Entity attributes can use Python's basic types. There are at least 7 common entity types supported. Entity types can be standard get / t / set format entities. Entity type attributes can use _ or _ prefixes. The entity construction method supports dictionary structure dynamic parameter mode. You can specify the database to be used through configuration; dynamically load the driver required by a database; The encoding complexity is O(1).

5. The device according to claim 4, characterized in that include: To convert entity objects into SQL and placeholder parameter values, first parse the object to get the object attribute-value dictionary and class attributes, then parse the table name to get the SQL and the corresponding placeholder parameter values; The specific steps to obtain the object attribute-value dictionary and class attributes through entity object parsing are as follows: 1) Get the entity type corresponding to the object; 2) Get the class attribute field classField according to the entity type; 3) Get the dictionary structure object of the class attributes and values ​​(including private variables starting with _) according to the entity type, and delete the _ or _ prefix. 4) Get the dictionary structure object fieldAndValue of the object's attributes and values ​​according to the object and delete the _ or _ prefix; 5) Delete dynamically added attributes of an object; 6) If the value of the object's attribute is None, replace it with the class-level attribute and value; 7) When the object's attribute has no corresponding attribute item, but the class's attribute has and is not None, the class-level attribute and value are used; 8) Exclude items whose value in fieldAndValue is None; by default, only attributes with values ​​are required; 9) Return fieldAndValue and classField.

6. The device according to claim 4, characterized in that include: The device of the present invention can be used by the following steps: 1) Configure dbName; configure dbModuleName as needed; 2) Define common entity objects; 3) Pass the entity object to the method of the Suid class of the SUID API module; 4) Use the obtained results.

Images