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()