对象关系映射(Object-Relational Mapping)提供了概念性的、易于理解的模型化数据的方法。ORM方法论基于三个核心原则: 简单:以最基本的形式建模数据。 传达性:数据库结构被任何人都能理解的语言文档化。 精确性:基于数据模型创建正确标准化的结构。 典型地,建模者通过收集来自那些熟悉应用程序但不熟练的数据建模者的人的信息开发信息模型。建模者必须能够用非技术企业专家可以理解的术语在概念层次上与数据结构进行通讯。建模http://write.blog.csdn.net/postedit?ref=toolbar者也必须能以简单的单元分析信息,对样本数据进行处理。ORM专门被设计为改进这种联系。
对于我的简单理解就是在面向对象语言(主要是类和继承的运用)和关系型数据库(MySQL之类)之间建立映射关系,具体来说应该是一个类(比如user)对应一个数据库的表table(比如User表),使得运用面向对象的特点,通过封装继承类和实例来实现对关系型数据库的操作
2、举例分析预备知识:Python协程和异步IO(yield from的使用)、SQL数据库操作、元类、面向对象知识、Python语法
参考:
异步IO和协程:http://blog.csdn.net/gvfdbdf/article/details/49254037
aiomysql参考文档:http://aiomysql.readthedocs.org/en/latest/connection.html
元类:http://www.cnblogs.com/ifantastic/p/3175735.html
类方法:http://blog.csdn.net/carolzhang8406/article/details/6856817
建立一个web访问的ORM,每一个web请求被连接之后都要接入数据库进行操作。在web框架中,采用基于asyncio的aiohttp,这是基于协程的异步模型,所以整个ORM的框架采用异步操作,采用aiomysql作为数据库的异步IO驱动。
思路分析:
Ⅰ. 首先需要建议一个全局的连接池,使得每一个HTTP请求都能从连接池中取得连接,然后接入数据库,这样就不会频繁的打开和关闭数据库
Ⅱ. 封装数据库操作函数(SELECT、INSERT、UPDATE、DELETE等)。每一个来自连接池的连接都可以通过生成游标的形式调用数据库操作函数,而这些操作函数是对数据库操作语句的封装。
Ⅲ. 封装数据库表中的每一列,定义Field类保存每一列的属性(包括数据类型,列名,是否为主键和默认值)
Ⅳ. 定义每一个数据库表映射类的元类ModelMetaclass,通过元类来控制数据库表映射的基类的生成。
ModelMetaclass的工作:一、读取具体子类(user)的映射信息(也就是User表)。二、在当前类中查找所有的类属性(attrs),如果找到Field属性,就将其保存到__mappings__的dict中,同时从类属性中删除Field(防止实例属性遮住类的同名属性)。三、将数据库表名保存到__table__中
Ⅴ. 定义ORM所有映射的基类:Model# Model类的任意子类可以映射一个数据库表。Model类可以看作是对所有数据库表操作的基本定义的映射,Model从dict继承,拥有字典的所有功能,同时实现特殊方法__getattr__和__setattr__,能够实现属性操作,实现数据库操作的所有方法,并定义为class方法,所有继承自Model都具有数据库操作方法。
-
-
- __auth__ = 'peic'
-
-
- ''
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- import asyncio, logging
-
-
- import aiomysql
-
-
- def log(sql, args=()):
- logging.info('SQL: %s' %(sql))
-
-
-
- @asyncio.coroutine
- def create_pool(loop, **kw):
- logging.info('create database connection pool...')
-
-
- global __pool
- __pool = yield from aiomysql.create_pool(
-
-
- host = kw.get('host', 'localhost'),
- user = kw['user'],
- password = kw['password'],
- db = kw['db'],
- port = kw.get('port',3306),
- charset = kw.get('charset', 'utf8'),
- autocommit = kw.get('autocommit', True),
-
- maxsize = kw.get('maxsize', 10),
- minsize = kw.get('minisize', 1),
-
-
- loop = loop
- )
-
-
-
- def select(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 = fetchmany(size)
- else:
-
- rs = fetchall()
-
- yield from cur.close()
- logging.info('rows return: %s' %(len(rs)))
- return rs
-
-
-
-
-
- @asyncio.coroutine
- def execute(sql, args):
- log(sql, args)
- global __pool
- with (yield from __pool) as conn:
- try:
-
- cur = yield from conn.cursor()
- cur.execute(sql.replace('?', '%s'), args)
- affectedLine = cur.rowcount
- yield from cur.close()
- except BaseException as e:
- raise
- return affectedLine
-
-
-
- def create_args_string(num):
- L = []
- for n in range(num):
- L.append('?')
-
-
- return (','.join(L))
-
-
-
- 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))
-
-
-
-
-
-
- class StringField(Field):
- def __init__(self, name=None, primary_key=False, default=None, column_type='varchar(100)'):
- super().__init__(name, column_type, primary_key, default)
-
- class BooleanField(Field):
- def __init__(self, name=None, default=None):
- super().__init__(name, 'boolean', False, default)
-
- class IntegerField(Field):
- def __init__(self, name=None, primary_key=False, default=0):
- super().__init__(name, 'bigint', primary_key, default)
-
- class FloatField(Field):
- def __init__(self, name=None, primary_key=False, default=0.0):
- super().__init__(name, 'real', primary_key, default)
-
- class TextField(Field):
- def __init__(self, name=None, default=None):
- super().__init__(name, 'Text', False, default)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- class ModelMetaclass(type):
-
-
-
-
-
- def __new__(cls, name, bases, attrs):
-
-
- if name == 'Model':
- return type.__new__(cls, name, bases, attrs)
-
-
- tableName = attrs.get('__table__', None) or name
- logging.info('found model: %s (table: %s)' %(name, tableName))
-
-
- mappings = dict()
- fields = []
- primaryKey = None
- for k,v in attrs.items():
-
- if isinstance(v, Field):
-
- logging.info(' found mapping: %s --> %s' %(k, v))
- mappings[k] = v
-
-
- if v.primary_key:
-
-
- if primaryKey:
- raise StandardError('Duplicate primary key for field: %s' %k)
-
- primaryKey = k
- else:
- fields.append(k)
-
-
- if not primaryKey:
- raise StandardError('Primary key is nor founnd')
-
-
- for k in mappings.keys():
- attrs.pop(k)
-
-
- escaped_fields = list(map(lambda f:'`%s`' %f, fields))
-
-
- attrs['__mappings__'] = mappings
-
- attrs['__table__'] = tableName
-
- attrs['__primary_key__'] = primaryKey
-
- attrs['__fields__'] = fields
-
-
-
- attrs['__select__'] = 'select `%s`, %s from `%s`' %(primaryKey, ', '.join(escaped_fields), tableName)
- attrs['__insert__'] = 'insert into `%s` (%s, `%s`) values(%s)' %(tableName, ', '.join(escaped_fields), primaryKey, create_args_string(len(escaped_fields) + 1))
- attrs['__update__'] = 'update `%s` set `%s` where `%s` = ?' %(tableName, ', '.join(map(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)
-
-
-
-
-
-
-
-
-
-
-
-
-
- class Model(dict, metaclass=ModelMetaclass):
- def __init__(self, **kw):
- super(Model, self).__init__(**kw)
-
- def __getattr__(self, key):
- try:
- return self[key]
- except KeyError:
- raise AttributeError(r'"Model" object has no attribute:%s' %(key))
-
- def __setattr__(self, key, value):
- self[key] = value
-
- def getValue(self, key):
-
- return getattr(self, key, None)
-
- def getValueOrDefault(self, key):
- value = getattr(self, key, None)
- if not value:
- field = self.__mappings__[key]
- if field.default is not None:
- value = field.default() if callable(field.default) else field.default
- logging.debug('using default value for %s: %s' %(key, str(value)))
- setattr(self, key, value)
- return value
-
-
-
- @classmethod
-
- @asyncio.coroutine
- def findAll(cls, where=None, args=None, **kw):
- ''
- sql = [cls.__select__]
-
- if where:
- sql.append('where')
- sql.append(where)
-
- if args is None:
- args = []
-
- orderBy = kw.get('orderBy', None)
- if orderBy:
- sql.append('order by')
- sql.append(orderBy)
-
- limit = kw.get('limit', None)
- if limit is not None:
- sql.append('limit')
- if isinstance(limit, int):
- sql.append('?')
- args.append(limit)
- elif isinstance(limit, tuple) and len(limit) == 2:
- sql.append('?,?')
- args.extend(limit)
- else:
- raise ValueError('Invalid limit value: %s' %str(limit))
- rs = yield from select(' '.join(sql), args)
- return [cls(**r) for r in rs]
-
-
- @classmethod
- @asyncio.coroutine
- def findNumber(cls, selectField, where=None, args=None):
- ''
- sql = ['select %s __num__ from `%s`' %(selectField, cls.__table__)]
- if where:
- sql.append('where')
- sql.append(where)
- rs = yield from select(' '.join(sql), args, 1)
- if len(rs) == 0:
- return None
- return rs[0]['__num__']
-
-
- @classmethod
- @asyncio.coroutine
- def find(cls, primarykey):
- ''
- rs = yield from select('%s where `%s`=?' %(cls.__select__, cls__primary_key__), [primarykey], 1)
- if len(rs) == 0:
- return None
- return cls(**rs[0])
-
- @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)
-
- @asyncio.coroutine
- def update(self):
- args = list(map(self.getValue, self.__fields__))
- args.append(self.getValue(self.__primary_key__))
- rows = yield from execute(self.__updata__, args)
- if rows != 1:
- logging.warn('failed to update by primary key: affected rows: %s' %rows)
-
- @asyncio.coroutine
- def remove(self):
- args = [self.getValue(self.__primary_key__)]
- rows = yield from execute(self.__updata__, args)
- if rows != 1:
- logging.warn('failed to remove by primary key: affected rows: %s' %rows)
-
-
-
- if __name__ == '__main__':
-
- class User(Model):
-
- id = IntegerField('id',primary_key=True)
- name = StringField('username')
- email = StringField('email')
- password = StringField('password')
-
-
- u = User(id=12345, name='peic', email='peic@python.org', password='password')
- print(u)
-
- u.save()
- print(u)
运行结果:
- <span style="font-size:12px;">root@ming:~/Desktop/pip-7.1.2
- {'email': 'peic@python.org', 'name': 'peic', 'password': 'password', 'id': 12345}
- {'email': 'peic@python.org', 'name': 'peic', 'password': 'password', 'id': 12345}</span>