Python源代码(改进版)---模板类型表的增删改查

import pymysql


##  连接数据库
def connection():
    # 连接数据库
    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='westos',
        db='bdp',
        charset='utf8'
    )


##  增加
def insert(create_time, update_time, id, name, type_level, father):
    # 调用connection()函数
    connect = connection()
    # 使用cursor()方法获取游标
    cur = connect.cursor()

    # 判断类型等级是否合法
    if type_level==2 or type_level==3:
        # 根据插入数据中的type_level（类型等级）和father（父类id）查找数据库表中是否有这样的信息存在（等于1表示存在）
        sql1 = "select count(*) from alteration_type where type_level=%s and id=%s"
        cur.execute(sql1, [type_level-1, father])
        results = cur.fetchone()
        # print(results)
        # 判断父类id是否合法
        if results[0] == 1:
            sql2 = "insert into alteration_type values(%s,%s,%s,%s,%s,%s);"
            cur.execute(sql2, (create_time, update_time, id, name, type_level, father))
        else:
            print("非法父类型id")
    elif type_level == 1:
        # 判断父类id是否合法
        if father==-1:
            sql2 = "insert into alteration_type values(%s,%s,%s,%s,%s,%s);"
            cur.execute(sql2, (create_time, update_time, id, name, type_level, father))
        else:
            print("非法父类型id")
    else:
        print("非法level")

    # 提交事务
    connect.commit()
    # 关闭游标对象
    cur.close()
    # 关闭数据库连接
    connect.close()

# 以实际参数的数据类型为主
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','1','北京省', 1,-1)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','2','朝阳区', 2,1)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','3','大兴区', 2,1)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','4','海淀区', 2,1)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','5','上庄镇', 3,4)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','6','温泉镇', 3,4)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','7','广东省', 1,-1)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','8','南山区', 2,7)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','9','保安区', 2,7)
# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','10','南山街道', 3,8)


# insert('2020-08-03 09:31:59', '2020-08-09 10:40:32','11','南头街道', 5,8)


##  删除
def delete(id):
    # 调用connection()函数
    connect = connection()
    # 使用cursor()方法获取游标
    cur = connect.cursor()

    # 如果类型等级为3，直接删除即可
    sql1 = "delete from alteration_type where id=%s "
    # 执行sql语句
    cur.execute(sql1, id)

    # 如果类型等级为2，则需要找到层级关系下的所有元素再删除
    # 找到父类id为i对应的所有id并存入l列表中
    sql2 = "select id from alteration_type where father=%s;"
    cur.execute(sql2, id)
    results = cur.fetchall()

    l = []
    result = list(results)
    for r in result:
        l.append('%s' % r)

    # 删除层级关系下的所有数据
    for k in l:
        sql3 = "delete from alteration_type where id=%s or father=%s or father=%s;"
        # 执行sql语句
        cur.execute(sql3, (id, id, k))

    # 提交事务
    connect.commit()
    # 关闭游标对象
    cur.close()
    # 关闭数据库连接
    connect.close()

# delete(7)


##  修改
def update(name, id):
    # 调用connection()函数
    connect = connection()
    # 1.使用cursor()方法获取游标
    cur = connect.cursor()
    # 2.编写删除sql语句
    sql = " update alteration_type set name=%s where id=%s;"
    # 3.执行sql语句
    cur.execute(sql, (name, id))
    # 4.提交事务
    connect.commit()
    # 关闭游标对象
    cur.close()
    # 关闭数据库连接
    connect.close()

# update('北京','1')


##  查询
def select():
    # 调用connection()函数
    connect = connection()
    # 使用cursor()方法获取游标
    cur = connect.cursor()

    # 找到等级为1的所有元素
    sql = "select * from alteration_type where father='-1' "
    # 执行sql语句
    cur.execute(sql)
    # 获取查询的所有记录
    results1 = cur.fetchall()

    for i in results1:
        print("%s-%s" % (i[2], i[3]))
        # 找到1级元素分别对应的所有2级元素
        sql = "select * from alteration_type where father= %s"
        # 执行sql语句
        cur.execute(sql, i[2])
        # 获取查询的所有记录
        results2 = cur.fetchall()

        for j in results2:
            print(" %s-%s" % (j[2], j[3]))
            # 找到2级元素分别对应的所有3级元素
            sql = "select * from alteration_type where father= %s"
            # 执行sql语句
            cur.execute(sql, j[2])
            # 获取查询的所有记录
            results3 = cur.fetchall()

            for k in results3:
                print("  %s-%s" % (k[2], k[3]))

    # 关闭游标对象
    cur.close()
    # 关闭数据库连接
    connect.close()

select()