Python学习：对MySQL实现简单的无限极分类操作

Python代码如下：

import pymysql
import time

class UnlimitedClass(object):
    # 初始化数据库连接
    def __init__(self):
        self.conn = pymysql.connect(
            host='localhost',
            user='你的数据库用户名',
            passwd='你的数据库密码',
            port=3306,
            db='你的数据库名',
            charset='utf8'
        )
        self.cur = self.conn.cursor()


    # 判断分类名称是否已经存在
    def isExists(self, name):
        sql = 'select * from type where name = %s' % repr(name)
        result = self.cur.execute(sql)

        # 如果存在返回True
        if result > 0:
           return True
        # 如果不存在则返回False
        else:
            return False

    # 插入类别
    def insertClass(self, name, pid, path):
        sql = 'insert into type (name, pid, path) values (%s, %s, %s)' % (repr(name), repr(pid), repr(path))
        self.cur.execute(sql)
        # 提交修改，否则数据库数据不更新
        self.conn.commit()

    # 添加顶级分类
    def addTopClass(self):
        topClassName = input('请输入顶级分类名称：')
        topClassPid = input('请输入父id：')
        topClassPath = '0' + ','

        # 如果存在类别名，添加失败
        if self.isExists(topClassName):
            print('该类别已存在，添加失败！')
            return -1
        # 否则执行添加操作
        else:
            self.insertClass(topClassName, topClassPid, topClassPath)
            print('添加成功！')
            return 0

    # 添加子分类
    def addSubClass(self):
        subClassName = input('请输入子分类名称：')
        subClassPid = int(input('请输入父id：'))

        # 如果存在类别名，添加失败
        if self.isExists(subClassName):
            print('该类别已存在，添加失败！')
            return -1
        # 否则执行添加操作
        else:
            sqlIsExistsTopid = 'select name from type where id = %d' % subClassPid
            result = self.cur.execute(sqlIsExistsTopid)

            # 如果父类id存在，则进行插入操作
            if result > 0:
                # 子分类路径
                sql = 'select path from type where id = %d' % subClassPid
                self.cur.execute(sql)
                subClassPath = self.cur.fetchall()[0][0] + str(subClassPid) + ','
                self.insertClass(subClassName, subClassPid, subClassPath)
                self.conn.commit()
                print('添加成功！')
                return 0
            else:
                print('父类id不存在，添加失败！')
                return -1

    # 查找父类
    def getParentClass(self):
        subClassName = input('请输入想要查询的子类名称：')

        if not self.isExists(subClassName):
            print('该子类名称不存在，查询失败！')
            return -1
        else:
            sql = 'select path from type where name = %s' % repr(subClassName)
            self.cur.execute(sql)

            # 获得子类的path，也就是该子类存在的父类id
            result = self.cur.fetchall()[0][0]

            # 该子类的父类id所构成的列表（最后一个元素是空元素）
            pidList = result.split(',')

            if len(pidList) == 2:
                print('该类别已经是最高级别父类，无向上父类！')
                return 0
            else:
                args = ','.join(pidList)

                # 拼接sql查询语句
                sqlGetParent = 'select name from type where id in (%s)' % (args[0 : -1])
                resultCount = self.cur.execute(sqlGetParent)
                resultList = self.cur.fetchall()

                print('该类有%d个父类！' % resultCount)
                print(resultList)


    # 删除分类
    def deleteClass(self):
        deleteName = input('请输入要删除的分类名称：')

        # 判断要删除的类别是否存在
        if not self.isExists(deleteName):
            print('该类别不存在，删除失败！')
            return -1
        else:
            # 如果存在，判断该类下是否有子类
            sqlIsSon = 'select id from type where pid in (select id from type where name = %s)' % repr(deleteName)
            result = self.cur.execute(sqlIsSon)

            if result > 0:
                print('该类下还有子类，删除失败！')
                return -1
            else:
                sql = 'delete from type where name = %s' % repr(deleteName)
                self.cur.execute(sql)
                self.conn.commit()
                print('删除成功！')
                return 0

    # 操作选项界面
    def funcView(self):
        print('')
        print('* ' * 20)
        print('1.添加顶级分类名称')
        print('2.添加子分类名称')
        print('3.删除类别')
        print('4.查找父类')
        print('5.退出')
        print('* ' * 20)
        print('')

if __name__ == '__main__':
    # 实例化类别对象
    unlimit = UnlimitedClass()

    # 循环操作
    while True:
        # 显示选项
        unlimit.funcView()

        option = input('请输入操作选项（数字）：')

        if option == '1':
            unlimit.addTopClass()
            time.sleep(1)
        elif option == '2':
            unlimit.addSubClass()
            time.sleep(1)
        elif option == '3':
            unlimit.deleteClass()
            time.sleep(1)
        elif option == '4':
            unlimit.getParentClass()
            time.sleep(1)
        elif option == '5':
            print('Bye!')
            time.sleep(1)
            break
        else:
            print('选项无效，操作失败，请重新选择！')
            time.sleep(1)

    # 关闭数据库
    unlimit.cur.close()
    unlimit.conn.close()

MySQL表结构和数据如下：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `type`
-- ----------------------------
DROP TABLE IF EXISTS `type`;
CREATE TABLE `type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `pid` int(11) DEFAULT '0',
  `path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of type
-- ----------------------------
INSERT INTO `type` VALUES ('14', '季节', '0', '0,');
INSERT INTO `type` VALUES ('18', '春天', '14', '0,14,');
INSERT INTO `type` VALUES ('20', '动物', '0', '0,');
INSERT INTO `type` VALUES ('21', '许金涛', '20', '0,20,');
INSERT INTO `type` VALUES ('22', '白天', '18', '0,14,18,');
INSERT INTO `type` VALUES ('23', '早晨', '22', '0,14,18,22,');