python 制作一个简单的orm框架

python 制作一个简单的orm框架

支持insert, update, select, delete操作

支持全表查询，全表删除，删除表操作

如需更复杂的sql操作，框架支持直接运行sql

orm.py:

#!/usr/bin/env python3

from copy import deepcopy

class Field(object):
        def __init__(self, name, ctype, *attrs):
                self._name = name
                self._ctype = ctype
                self._attrs = attrs

        @property
        def name(self):
                return self._name

        @property
        def ctype(self):
                return self._ctype

        @property
        def attrs(self):
                return self._attrs

class ModelMetaclass(type):
        def __new__(cls, name, based, attrs):
                if name == 'Model':
                        return type.__new__(cls, name, based, attrs)
                argsList = dict()
                for k, v in attrs.items():
                        if isinstance(v, Field):
                                argsList[k] = v
                for k in argsList.keys():
                        attrs.pop(k)
                attrs['__mapping__'] = argsList
                attrs['__table__'] = name
                attrs['__mapping_size__'] = len(argsList)
                return type.__new__(cls, name, based, attrs)

class Model(dict, metaclass = ModelMetaclass):
        def set(self, values):
                for i in range(len(self.__mapping__.keys())):
                        key = list(self.__mapping__.keys())[i]
                        self[key] = values[i]
                return self

        def create(self, cursor):
                ans = 'create table %s (' %self.__table__
                primaryKeys = []
                columns = []
                cnt = 0
                for k, v in self.__mapping__.items():
                        ans += v.name +' ' + v.ctype
                        for attr in v.attrs:
                                if attr == 'primary key':
                                        primaryKeys.append(v.name)
                                else:
                                        ans += ' ' + attr
                        cnt += 1
                        if cnt != self.__mapping_size__:
                                ans += ','
                if len(primaryKeys) != 0:
                        ans += ',primary key ('
                        cnt = 0
                        for key in primaryKeys:
                                ans += key
                                cnt += 1
                                if cnt != len(primaryKeys):
                                        ans += ','
                        ans += ')'
                ans += ')'
                cursor.execute(ans)
                return self

        def insert(self, cursor):
                columns = []
                values = []
                for k, v in self.__mapping__.items():
                        columns.append(v.name)
                        if 'primary key' in v.attrs and self[k] == '':
                                raise ValueError('primary key is empty')
                        if 'int' in v.ctype or 'float' in v.ctype or 'double' in v.ctype or 'decimal' in v.ctype:
                                values.append(str(self[k]))
                        else:
                                values.append('\'' + self[k] + '\'')
                cursor.execute('insert into %s (%s) values (%s)' %(self.__table__, ','.join(columns), ','.join(values)))
                return self

        def select(self, cursor):
                ans = 'select * from %s where ' %self.__table__
                columns = []
                values = []
                pstr = []
                for k, v in self.__mapping__.items():
                        columns.append(v.name)
                        if 'primary key' in v.attrs:
                                if self[k] == '':
                                        raise ValueError('primary key is empty')
                                if 'int' in v.ctype or 'float' in v.ctype or 'double' in v.ctype or 'decimal' in v.ctype:
                                        pstr.append(v.name + ' = ' + str(self[k]))
                                else:
                                        pstr.append(v.name + ' = \'' + self[k] + '\'')
                for i in range(len(pstr)):
                        ans += pstr[i]
                        if i != len(pstr) - 1:
                                ans += 'and '
                cursor.execute(ans)
                values = cursor.fetchall()
                for i in range(len(self.__mapping__.keys())):
                        self[list(self.__mapping__.keys())[i]] = values[0][i]
                return self

        def update(self, cursor):
                ans = 'update %s set ' %self.__table__
                pstr = []
                for k, v in self.__mapping__.items():
                        if 'primary key' in v.attrs:
                                if 'int' in v.ctype or 'float' in v.ctype or 'double' in v.ctype or 'decimal' in v.ctype:
                                        pstr.append(v.name + ' = ' + self[k])
                                else:
                                        pstr.append(v.name + ' = \'' + self[k] + '\'')
                        else:
                                if 'int' in v.ctype or 'float' in v.ctype or 'double' in v.ctype or 'decimal' in v.ctype:
                                        ans += '%s = %s, '%(v.name, str(self[k]))
                                else:
                                        ans += '%s = \'%s\', '%(v.name, self[k])
                ans = ans[:-2]
                ans += ' where '
                for i in range(len(pstr)):
                        ans += pstr[i]
                        if i != len(pstr) - 1:
                                ans += 'and '
                cursor.execute(ans)
                return self

        def delete(self, cursor):
                ans = 'delete from %s where ' %self.__table__
                columns = []
                values = []
                pstr = []
                for k, v in self.__mapping__.items():
                        columns.append(v.name)
                        if 'primary key' in v.attrs:
                                if self[k] == '':
                                        raise ValueError('primary key is empty')
                                if 'int' in v.ctype or 'float' in v.ctype or 'double' in v.ctype or 'decimal' in v.ctype:
                                        pstr.append(v.name + ' = ' + str(self[k]))
                                else:
                                        pstr.append(v.name + ' = \'' + self[k] + '\'')
                for i in range(len(pstr)):
                        ans += pstr[i]
                        if i != len(pstr) - 1:
                                ans += 'and '
                cursor.execute(ans)
                return self

class Database(object):
        def __init__(self, conn):
                self._conn = conn
                self._cursor = self._conn.cursor()

        def commit(self):
                self._conn.commit()

        def close(self):
                self._cursor.close()
                self._conn.close()

        @property
        def cursor(self):
                return self._cursor

        def insert(self, *models):
                for model in models:
                        model.insert(self.cursor)

        def create(self, *models):
                for model in models:
                        model.create(self.cursor)

        def update(self, *models):
                for model in models:
                        model.update(self.cursor)

        def delete(self, *models):
                for model in models:
                        model.delete(self.cursor)

        def select(self, *models):
                for model in models:
                        model.select(self.cursor)

        def deleteAll(self, model):
                self.cursor.execute('delete from ' + model.__table__)

        def drop(self, model):
                self.cursor.execute('drop table ' + model.__table__)

        def selectAll(self, model):
                self.cursor.execute('select * from ' + model.__table__)
                values = self.cursor.fetchall()
                ans = []
                for item in values:
                        temp = deepcopy(model)
                        temp.set(item)
                        ans.append(temp)
                return ans

        def execute(self, sql):
                self.cursor.execute(sql)
                return self.cursor.fetchall()

测试用：test.py

#!/usr/bin/env python3

from orm import *

if __name__ == '__main__':
        import mysql.connector
        class StringField(Field):
                def __init__(self, name, *attrs):
                        super().__init__(name, 'varchar(50)', *attrs)

        class IntField(Field):
                def __init__(self, name, *attrs):
                        super().__init__(name, 'int', *attrs)

        class User(Model):
                def __init__(self, *, id = '', userName = '', password = ''):
                        self['id'] = id
                        self['userName'] = userName
                        self['password'] = password
                id = IntField('id', 'primary key', 'not null')
                userName = StringField('username', 'not null')
                password = StringField('password', 'not null')

        database = Database(mysql.connector.connect(user = 'root', password = 'root', database = 'main'))
        database.create(
                User()
        )
        database.insert(
                User(id = 0, userName = 'aaa', password = 'aaa'),
                User(id = 1, userName = 'bbb', password = 'bbb')
        )
        users = database.selectAll(User())
        users.sort(key = lambda user: user['id'], reverse = True)
        for i in users:
                print(i)
        database.drop(User())
        database.commit()
        database.close()