Python Writing ORM

In a web app, all data, including user information, published logs, comments, and so on, are stored in the Database. In awesome-python3-webapp, we chose MySQL as the Database.

There are many places in the Web app to access the Database. Accessing the database requires creating a database connection, a cursor object, then executing the SQL statement, finally handling the exception, and cleaning up the resource. The code that accesses the database, if scattered among the functions, is bound to be impossible to maintain and not conducive to code REUSE.

so, we'll First wrap the usual select, INSERT, update, and delete operations with Functions.

Because the web framework uses asyncio-based aiohttp, This is the asynchronous model based on the CO-PROCESS. In the process, normal synchronous IO Operations cannot be invoked because all users are serviced by one thread, and the execution of the process must be very fast to handle a large number of user Requests. The time-consuming IO operation cannot be called synchronously in the process, otherwise the system cannot respond to any other user while waiting for an IO Operation.

This is one of the principles of asynchronous programming: once the decision is made to use asynchrony, each layer of the system must be asynchronous, with "no back arrow on the bow".

fortunately, the aiomysql asynchronous IO Driver is provided for the MySQL Database.

Create a connection pool

We need to create a global connection pool where each HTTP request can get a database connection directly from the connection Pool. The benefit of using connection pooling is that you do not have to open and close the database connection frequently, but reuse it as much as Possible.

The connection pool is stored by a global variable, which, by __pool default, is set to utf8 commit the transaction automatically:

@asyncio. CoroutineDefcreate_pool (loop, **kw): logging.info (global __pool __pool = yield from aiomysql.create_pool (host=kw.get (3306), user=kw[ ' user ', Password=kw[ ' password '], db=kw[ ' db '], Charset=kw.get (  ' charset ',  ' UTF8 '), autocommit=kw.get ( "autocommit ', true), maxsize=kw.get ( ' maxsize ', 10), minsize= Kw.get ( ' minsize ', 1), loop=loop)      

Select

To execute a select statement, we select execute it with a function that requires incoming SQL statements and SQL Parameters:

@asyncio. CoroutineDefSelect(sql, args, size=none): Log (sql, Args)global __pool with (yield  From __pool) as conn:cur = yield from conn.cursor ( Aiomysql. Dictcursor) yield from cur.execute (sql.replace ( '%s '), args or ()) if size:rs = yield from cur.fetchmany (size) else:rs = yield from cur.fetchall () yield from cur.close () logging.info (return rs              

The placeholder for the SQL statement is ? , and the MySQL placeholder is that the %s select() function is automatically replaced INTERNALLY. Note that SQL injection attacks can be prevented by always insisting on using SQL with parameters instead of stitching the SQL strings YOURSELF.

Notice that yield from a sub-process is called (that is, another process is called in one of the Threads) and the return result of the subroutine is directly Obtained.

If a parameter is passed in, it is obtained by obtaining size fetchmany() a maximum of a specified number of records, otherwise, by fetchall() getting all RECORDS.

Insert, Update, Delete

To execute an insert, UPDATE, delete statement, You can define a common execute() function because all 3 of these SQL executions require the same parameters and return an integer representing the number of rows affected:

  @asyncio. coroutine def execute (sql, args): log (sql) with (yield from __pool) as conn: try:cur = Span class= "keyword" >yield from conn.cursor () yield from cur.execute (sql.replace ( '%s '), Args) Affected = Cur.rowcount yield from cur.close ()  Except baseexception as e: raise return affected  

execute()Unlike functions and select() functions, A cursor object does not return a result set, but instead rowcount returns the number of Results.

Orm

With basic select() and execute() function, we can begin to write a simple orm.

Designing an ORM needs to be designed from the Upper-level Caller's Perspective.

Let's consider how to define an User object and then associate the database table users with it.

from orm import Model, StringField, IntegerFieldclass User(Model): __table__ = ‘users‘ id = IntegerField(primary_key=True) name = StringField()

Notice that the attributes defined in User the class __table__ , id and the class, name are not properties of the Instance. therefore, properties defined at the class level are used to describe User the mapping of objects and tables, and instance properties must be __init__() initialized by methods, so they do not interfere with each other:

# 创建实例:user = User(id=123, name=‘Michael‘)# 存入数据库:user.insert()# 查询所有User对象:users = User.findAll()

Define Model

The first thing to define is the base class for all ORM mappings Model :

ClassModel(dict, metaclass=modelmetaclass):Def__init__(self, **kw): Super (Model, self). __init__ (**kw)Def__getattr__(self, key):TryReturn self[key]Except Keyerror:Raise Attributeerror (R "' Model ' object has no attribute '%s '"% Key)Def__setattr__(self, key, value): self[key] = valuedef getvalue (self, key): none) def getvalueordefaultnone) Span class= "keyword" >if value is none:field = self.__mappings__[key] Span class= "keyword" >if field.default is not  None:value = Field.default () if callable (field.default) else Field.default logging.debug (return value           

Modeldictinherit from, so have all dict the functions, at the same time implement a special method __getattr__() and __setattr__() , so can be written as a reference to ordinary fields:

>>> user[‘id‘]123>>> user.id123

and a Field variety of Field subclasses:

class Field(object):    def __init__(self, name, column_type, primary_key, default): self.name = name self.column_type = column_type self.primary_key = primary_key self.default = default def __str__(self): return ‘<%s, %s:%s>‘ % (self.__class__.__name__, self.column_type, self.name)

Mapped varchar by StringField :

class StringField(Field):    def __init__(self, name=None, primary_key=False, default=None, ddl=‘varchar(100)‘): super().__init__(name, ddl, primary_key, default)

Notice that it's Model just a base class, how do you read the mapping information for a specific subclass User ? The answer is through Metaclass: ModelMetaclass :

ClassModelmetaclass(type):Def__new__(cls, name, bases, attrs):# Exclude the Model class Itself:If name==' Model ':return type.__new__ (cls, name, bases, attrs)# get table Name: tableName = Attrs.get (' __table__ ',None)or name Logging.info (' Found model:%s (table:%s) '% (name, TableName))# Get all field and primary key names: mappings = dict () fields = [] PrimaryKey =NoneFor k, VIn Attrs.items ():If Isinstance (v, Field): logging.info (' Found mapping:%s ==>%s '% (k, v)) mappings[k] = VIf v.primary_key:# Find Primary Key:If primaryKey:Raise RuntimeError (' Duplicate primary key for field:%s '% k ' PrimaryKey = kElse:fields.append (k)IfNot primaryKey:Raise RuntimeError (' Primary key not found. ')For KIn Mappings.keys (): attrs.pop (k) escaped_fields = List (map (Lambda F:'%s '% f, FIELDS)) attrs[' __mappings__ '] = mappings# Save the mapping relationship of properties and columns attrs[' __table__ '] = TableName attrs[' __primary_key__ '] = PrimaryKey# Primary Key property name attrs[' __fields__ '] = Fields# property names except primary key# constructs the default select, INSERT, update, and delete statements: attrs[ ' __select__ '] =  ' select '%s ',%s from '%s '% (primaryKey, ", '. Join (escaped_fields), tableName) attrs[ ' __insert__ '] =  ' INSERT INTO '%s ' (%s, '%s ') values (%s) '% (tableName, 1)) attrs[ ' __update__ '] =  ' update '%s ' set%s where '%s ' =? '% (tableName, lambda f:  '%s ' =? '% (mappings.get (f). name or f), fields), PrimaryKey) Attrs[ ' __delete__ '] =  ' delete from '%s ' where '%s ' =? '% ( tableName, primaryKey) return type.__new__ (cls, name, bases, attrs)    

In this way, any class that inherits from the model, such as user, is automatically scanned for mappings through Modelmetaclass and stored in its own class properties such as __table__ __mappings__ .

then, when we add the class method to the model class, we can have all the subclasses call the class Method:

class Model(dict):    ...    @classmethod @asyncio.coroutine def find(cls, pk): ‘ find object by primary key. ‘ rs = yield from select(‘%s where `%s`=?‘ % (cls.__select__, cls.__primary_key__), [pk], 1) if len(rs) == 0: return None return cls(**rs[0])

The user class can now implement a primary key lookup through a class method:

yield from User.find(‘123‘)

Adding an instance method to the model class allows all subclasses to invoke the instance Method:

class Model(dict):    ...    @asyncio.coroutine def save(self): args = list(map(self.getValueOrDefault, self.__fields__)) args.append(self.getValueOrDefault(self.__primary_key__)) rows = yield from execute(self.__insert__, args) if rows != 1: logging.warn(‘failed to insert record: affected rows: %s‘ % rows)

This allows a user instance to be stored in the Database:

user = User(id=123, name=‘Michael‘)yield from user.save()

The final step is to refine the orm, and for the lookup we can implement the following methods:

• FindAll ()-search by where condition;

• Findnumber ()-finds by the where condition, but returns an integer that applies to select count(*) the type of sql.

Well update() and remove() methods.

All of these methods must be @asyncio.coroutine decorated to become a co-process.

Special attention is required when calling:

user.save()

There is no effect, because the call save() simply creates a co-process and does not execute it. Be sure to use:

yield from user.save()

The insert operation was actually performed.

Finally look at how many lines of code do we implement the ORM module? Less than 300 lines are Accumulated. Isn't it easy to write an ORM in python?

http://www.pdfxs.com/cpgjhegfgncpcfefdfcfebegcfebdbcfefdfcfebdecfecdecfefdfcfdjefcfdidecfefdgcfdjdgcfebee/

Python Writing ORM