python db类

import pymysql

class DB:
    __host = 'localhost' # 服务器地址
    __username = 'root' # 用户名
    __password = '' # 密码
    __database = 'test' # 数据库
    __field = '*' # 查询字段
    __where = '' # 条件
    __sql = False # 是否返回sql
    __join = '' # 联表
    __order = '' # 排序
    __limit = '' # 数量

    # 构造函数，在生成对象时调用
    def __init__(self, table):
        try:
            # 打开数据库连接 host, username, password, database
            self.db = pymysql.connect(self.__host, self.__username, self.__password, self.__database)
        except Exception as e:
            print(e)
            exit()

        # 使用 cursor() 方法创建一个游标对象 cursor
        self.cursor = self.db.cursor()
        self.table = table

    # 析构函数，释放对象时使用
    def __del__(self):
        try:
            # 关闭数据库连接
            self.db.close()
        except Exception as e:
            print(e)

    # 得到当前sql语句
    def getSql(self):
        self.__sql = True
        return self

    # 字段
    def field(self, str):
        self.__field = str
        return self

    # 联表
    def join(self, table, where):
        self.__join = ' LEFT JOIN ' + table + ' ON ' + where + ' '
        return self

    # 条件
    def where(self, param):
        self.__where = ' WHERE '
        if isinstance(param, list):
            for i in param:
                if isinstance(i[2], list):
                    tmp = '('
                    for j in i[2]:
                        tmp += str(j) + ','
                    tmp += ')'
                    self.__where += '`' + i[0] + '` ' + i[1] + ' ' + tmp + ' AND '
                else:
                    self.__where += '`' + i[0] + '` ' + i[1] + ' ' + str(i[2]) + ' AND '
            else:
                self.__where = self.__where[0:-4]
        else:
            self.__where += param

        return self

    # 排序
    def order(self, str):
        self.__order = ' ORDER BY ' + str
        return self

    # 数量
    def limit(self, str):
        self.__limit = ' LIMIT ' + str
        return self

    # 增加
    def insert(self, dict):
        key = value = ''
        for k, v in dict.items():
            key += '`' + k + '`,'
            value += '"' + v + '",'

        key = key[0:-1]
        value = value[0:-1]

        sql = 'INSERT INTO ' + self.table + ' (' + key + ') VALUES (' + value + ')'
        if self.__sql:
            return sql

        try:
            # 执行sql语句
            ret = self.cursor.execute(sql)
            # 提交到数据库执行
            self.db.commit()
            return ret
        except Exception as e:
            # 如果发生错误则回滚
            self.db.rollback()
            print(e)
            return 0

    # 删除
    def delete(self):
        if self.__where:
            sql = "DELETE FROM " + self.table + self.__where
            if self.__sql:
                return sql

            try:
                # 执行sql语句
                ret = self.cursor.execute(sql)
                # 提交到数据库执行
                self.db.commit()
                return ret
            except Exception as e:
                # 如果发生错误则回滚
                self.db.rollback()
                print(e)
                return 0

        else:
            raise BaseException('没有条件') # 抛异常

    # 修改
    def update(self, dict):
        str = ''
        for k, v in dict.items():
            str += '`' + k + '`="' + v + '",'

        str = str[0:-1]
        sql = 'UPDATE ' + self.table + ' SET ' + str

        if self.__where:
            sql += self.__where
        if self.__sql:
            return sql

        try:
            # 执行sql语句
            ret = self.cursor.execute(sql)
            # 提交到数据库执行
            self.db.commit()
            return ret
        except Exception as e:
            # 如果发生错误则回滚
            self.db.rollback()
            print(e)
            return 0

    # 查询
    def select(self):
        sql = "SELECT " + self.__field + " FROM " + self.table

        if self.__join:
            sql += self.__join

        if self.__where:
            sql += self.__where

        if self.__order:
            sql += self.__order

        if self.__limit:
            sql += self.__limit

        if self.__sql:
            return sql

        # 使用 execute()  方法执行 SQL 查询
        self.cursor.execute(sql)

        # 使用 fetchall() 方法获取所有数据.
        data = self.cursor.fetchall()

        return data


'''
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '名字',
  `sex` varchar(7) DEFAULT '' COMMENT '性别',
  `job` varchar(6) DEFAULT '' COMMENT '工作',
  `age` varchar(6) DEFAULT '' COMMENT '年龄',
  `height` varchar(6) DEFAULT '' COMMENT '身高',
  `weight` varchar(6) DEFAULT '' COMMENT '体重',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `people` VALUES ('1', '赵一', '男', '学生', '8', '120', '35');
INSERT INTO `people` VALUES ('2', '钱二', '女', '学生', '9', '111', '31');
INSERT INTO `people` VALUES ('3', '孙三', '男', '学生', '10', '123', '34');
INSERT INTO `people` VALUES ('4', '李四', '女', '学生', '11', '100', '30');
'''

db = DB('people')

# 增加
dict = {'name': '周五', 'sex': '男', 'job': '学生', 'age': '8', 'height': '121', 'weight': '32'}
data = db.insert(dict)
print(data)

# 删除
# data = db.where('id=6').delete()
# print(data)

# 修改
# dict = {'age': '9', 'height': '121', 'weight': '31'}
# data = db.where('id=7').update(dict)
# print(data)

# 查询 优化where条件 'id<11'
# data = db.field('id,name,age,job').where([['id', '>', 1]]).order('id desc').limit('3').select()
# print(data)