Python元类实现ORM对象关系映射

Python元类实现ORM对象关系映射

类 对应 表
属性 对应 字段
实例 对应 记录

其中基类Model，实现表的创建和插入功能；由元类MTemp动态创建，主要打包表类DB的表名和字段、主键等信息；另外实现子类Where，提供表的选择、更新和删除功能；Field类对表的字段进行配置，以后可以继续拓展子类；DBase类实现sqlite3的接口，由表类DB属性__db__申明调用。

刚开始学习Python ORM，对数据库字段类型不甚了解，仅因兴趣使然；如若有误，还望斧正！

#!/usr/bin/env python3
# _*_ coding: utf-8 _*_

# Author: Xingbang Jiang
# E-mail: 1278561590@qq.com
# HomePage: http://www.xingbangsharing.tech

import sqlite3

class DBase(object):
	def __init__(self, db_name):
		super(DBase, self).__init__()
		self.db_name = db_name

	def exec(self, sql, *args):
		self.conn = sqlite3.connect(self.db_name)
		curs = self.conn.cursor()
		if not args:
			curs.execute(sql)
		else:
			curs.execute(sql, *args)
		self.conn.commit()
		return curs

	def quit(self):
		self.conn.close()

class Field(object):
	def __init__(self, col, dtp=None, val=None, pkey=False, fkey=False):
		super(Field, self).__init__()
		self.col, self.dtp, self.val, self.pkey, self.fkey = col, dtp, val, pkey, fkey

class MTemp(type):
	def __new__(temp, name, base, attr):
		if name.lower() == 'model':
			return type.__new__(temp, name, base, attr)

		cols, pkey, fkey = dict(), list(), list()
		for k, v in attr.items():
			if isinstance(v, Field):
				if v.pkey: pkey.append(v.col)
				if v.fkey: fkey.append(v.col)
				cols[k] = v

		if len(pkey) > 1: 
			raise Exception('primary key must be unique!')

		for k, v in cols.items(): attr.pop(k)

		attr['__pkey__'] = pkey
		attr['__fkey__'] = fkey
		attr['__cols__'] = cols
		if '__tabn__' not in attr:
			attr['__tabn__'] = name.lower()

		return type.__new__(temp, name, base, attr)


class Model(object, metaclass=MTemp):
	def __init__(self, **kwargs):
		super(Model, self).__init__()

	@classmethod
	def create(self):
		cols = list()
		for k, v in self.__cols__.items():
			col = list()
			col.append(v.col)
			if v.dtp: col.append(v.dtp)
			if v.col in self.__pkey__: col.append('primary key')

			cols.append(' '.join(col))

		sql = 'create table %s(%s)' % (self.__tabn__, ','.join(cols))
		if self.__db__:
			self.__db__.exec(sql)
			self.__db__.quit()
			print('%s successfully!' % sql)

	@classmethod
	def insert(self, **kwargs):
		cols, args, pkey = list(), list(), list()
		for k, v in self.__cols__.items():
			cols.append(v.col)
			args.append(kwargs.get(v.col, ''))
			if v.col in self.__pkey__: pkey.append(v.col)

		if len(pkey) < 1:
			raise Exception("The inserted field must contain the primary key '%s'!" % self.__pkey__[0])

		sql = 'insert into %s(%s) values(%s)' % (self.__tabn__, ','.join(cols), ','.join(['?' for a in args]))
		if self.__db__:
			self.__db__.exec(sql, tuple(args))
			self.__db__.quit()
			print('insert into %s(%s) values(%s) successfully!' % (self.__tabn__, ','.join(cols), ','.join([str(a) for a in args])))

	@classmethod
	def where(self, where='and', **kwargs):
		return Where(self, where, **kwargs)

class Where(object):
	def __init__(self, tobj, where, **kwargs):
		super(Where, self).__init__()
		self.tobj, self.where = tobj, ''
		if kwargs:
			if where in ['and','or']: 
				temp = list()
				for k, v in kwargs.items():
					if k not in [v.col for k, v in self.tobj.__cols__.items()]:
						raise Exception("Field '%s' does not exist in table '%s'!" % (k, self.tobj.__tabn__))
					temp.append(' %s = %s ' % (k, repr(v)))
				self.where = 'where' + where.join(temp).rstrip()
			else:
				self.where = where

	def select(self, *args):
		if args:
			for a in args:
				if a not in [v.col for k, v in self.tobj.__cols__.items()]:
					raise Exception("Field '%s' does not exist in table '%s'!" % (a, self.tobj.__tabn__))
			sql = 'select %s from %s %s' %(', '.join(args), self.tobj.__tabn__, self.where)
		else:
			sql = 'select %s from %s %s' %('*', self.tobj.__tabn__, self.where)
		print(sql)
		if self.tobj.__db__:
			db_list = self.tobj.__db__.exec(sql).fetchall()
			self.tobj.__db__.quit()
			return db_list

	def update(self, **kwargs):
		for k, v in kwargs.items():
			if k not in [v.col for k, v in self.tobj.__cols__.items()]:
				raise Exception("Field '%s' does not exist in table '%s'!" % (k, self.tobj.__tabn__))
		args = ', '.join(['%s = %s' % (k, v) for k, v in kwargs.items()])
		sql = 'update %s set %s %s' %(self.tobj.__tabn__, args, self.where)
		if self.tobj.__db__:
			self.tobj.__db__.exec(sql)
			self.tobj.__db__.quit()
			print('%s successfully!' % sql)

	def delete(self):
		sql = 'delete from %s %s' % (self.tobj.__tabn__, self.where)
		if self.tobj.__db__:
			self.tobj.__db__.exec(sql)
			self.tobj.__db__.quit()
			print('%s successfully!' % sql)

class DB(Model):
	# __tabn__ = 'test'
	a = Field('a', pkey=True)
	b = Field('b')
	c = Field('c')
	d = Field('d')
	e = Field('e')
	f = Field('f')
	__db__ = DBase('test.db')


DB.create()

for i in range(100):
	DB.insert(a=i, b=i*i, c='abc', d='xyz', e='pq', f=5)

for i in DB.where().select():
	print(i)

print(DB.where(a=10).select())

print(DB.where(c='abc', f=5).select('a'))

DB.where(a=10,b=100).update(b=10,c=555)

DB.where(a=67).delete()