Using abstract SQL statements to build smooth Data Access

If a database is used as a persistence device, can I directly write SQL statements (Stored Procedures) or use Orm? Undoubtedly, the emergence of ORM gives us a new perspective on data operations: in object-oriented languages, we use object-oriented methods to access and operate data. However, after using ORM for a while, it is always a bit awkward, because relational databases abstract data in a hierarchical structure based on relational algebra, and the objectiform of data abstraction is a mesh structure, the ORM ing between the two abstract modes produces the impedance mismatch (impedance mismatch) phenomenon, so it feels awkward when the ing is improper. So I want to explain again, if the abstract data itself has the problem of impedance mismatch, what if we abstract the access to the data? Regardless of the Orm, the access to relational databases is still through SQL statements as the access interface, so if we abstract SQL statements, can we get rid of impedance imbalance, what if I can smoothly access operational data? In the previous article Article I made a small attempt in the Dal of a python release. Of course, I am not the first person to do this, but I want to summarize this method, it may be nice to provide an update perspective outside of The ORM to produce lightweight Data Access Components. It is implemented using Python because I only want to implement it in a dynamic language for the time being. If I need to use it in. net, I can use ironpython. If you think about how to implement it in C #, I hope to share it with you. Why do we use dynamic languages? First, we should not use entity classes, because we abstract the way data is accessed rather than the data itself. In a relational database, data is data rows and fields, in the data structure, a row of data can be represented by a dictionary. in Python, the dictionary can be represented in the following format: Class row (dict ): def _ getattr _ (self, propertyname): If self. has_key (propertyname): return self [propertyname] Return none is a row object. We can access the key value by listing the corresponding attributes, just like an object class, in this way, the problem of data abstraction is solved. Next, we need to abstract SQL. First, we need to analyze the SQL structure. We have four types of data operations: add, delete, modify, and query: the insert, delete, update, and select statements are implemented. Each SQL statement is composed of a clause. The clauses of the four SQL statements are exclusive to each other, there are also common clauses, such as the WHERE clause, select, update, and delete SQL operations all have the WHERE clause, and the same semantics indicates that the data to be operated is filtered to the condition, so I abstracted the filter condition and defined the conds class. Class conds: def _ init _ (self, field): Self. field_name = field self. _ SQL = "" self. _ Params = [] self. _ has_value = false self. _ sub_conds = [] self. _ no_value = false is used to map the relational operation logic of a language to an SQL statement. Fortunately, Python supports Operator overloading, therefore, we only need to add the corresponding buildin method to the conds class. For example, Def _ eq _ (self, value): return self. _ prepare ("". join (["'", self. field_name, "'= % s"]), value) def _ prepare (self, SQL, value): If not self. _ has_value: Self. _ SQL = SQL self. _ Params. append (value) self. _ has_value = true return self raise operationalerror, "multiple operate conditions" above Code It is defined that if conds ('col1') = 5, it will map to SQL col1 = % s and add a parameter with a value of 5. Similarly, the methods of other Relational operators are mapped to the corresponding buildin method. The two relations are and or, we use the _ and _ or _ methods to map the relationship. If the like operator cannot be found, we use the like method to represent the relationship, the result of the condition operation is still a condition, so the object itself is used as the return value. So (conds ('col1') = 5) & (conds ('col2') <6) the Union of condition col1 = % s and col2 <% s is mapped. It is inconvenient to use a string to represent the column name. Therefore, we define a table class to obtain the conds: Class tablequeryer: '''support for single table simple querys ''' def _ init _ (self, DB, tablename): Self. tablename = tablename self. DB = dB def _ call _ (self, query = none): Return operater (self. DB, self. tablename, query) def _ getattr _ (self, field_name): Return conds (field_name). Similarly, we add getattr, Def _ getattr _ (self, tablename): ''' return single table queryer For Select Table '''return tablequeryer (self, tablename), then we can use dB. tablename. colname to obtain the condition object. The previous example can be changed to (dB. tablename. col1 = 5) & (dB. tablename. col2 <6) If pre-TB = dB. tablename can also be rewritten to (TB. col1 = T) & (TB. col2 <6) Because inserting data only requires the data table name and does not need to be queried, the insert method is directly added to tablequeryer. Therefore, inserting data only requires dB. tablename. insert (col1 = value1, col2 = value2 ...) data Query, update, and deletion are all based on query conditions. Therefore, an operate operation object is abstracted: Class operater: def _ init _ (self, DB, tablename, que Ry): Self. count = count (dB, tablename, query) self. select = select (dB, tablename, query) self. update = Update (dB, tablename, query) self. delete = Delete (dB, tablename, query) each operation abstracts a class and saves the query conditions to the conditions of each operation. The final SQL statement is generated and specific clauses are added, all are completed in specific classes, such as select. Here, I made tablequeryer an object that can be called and added the _ call _ method to save a method name hierarchy. When the _ call _ method is passed in the query condition, an operator object is returned, and TB = dB is used. tablenameq = Tb (TB. col1 = 5) & (TB. col2 <6) Q. select () is used to query the results. The equivalent SQL statement is select * From tablename where col1 = 5 and col2 <6. After obtaining Q, you can directly call Q. delete () can delete the data Q. update (TB. col1 = 6) to update the filtered data Q. delete () can be used to directly delete data items that meet the filtering conditions and put them at http://bitbucket.org/alexander er_lee/flunt-sql-data-access-layerto study TXS with interest.