模板类型表的增删改查

    1. import pymysql
    3. SQL_Select = "SELECT * FROM alteration_type WHERE id = %d"
    5. SQL_Insert = """INSERT INTO alteration_type(
    6.             create_time,update_time,id,name,type_level,father) 
    7.             VALUES(NOW(),NOW(),%d,'%s',%d,%d)
    8.             """
    10. SQL_Update = "UPDATE alteration_type SET update_time=NOW(), name='%s',type_level=%d,father=%d where id=%d"
    12. SQL_Delete = " DELETE FROM alteration_type WHERE id = %d "
    14. SQL_TypeLevel = "SELECT type_level FROM alteration_type where name = '%s'; "
    16. SQL_Id = "SELECT id FROM alteration_type where name = '%s'; "
    18. # 嵌套查询，找出子节点father号为该id的条目。
    19. SQL_IdSon =  "SELECT id FROM alteration_type WHERE father =  (SELECT id FROM alteration_type where name = '%s');" 
    21. SQL_IdGrandSon = "SELECT id FROM alteration_type WHERE father = %d;" 
    23. def main():
    24.     # 连接数据库
    25.     # UpdateData("LianHu", 3, 107, 108)
    26.     # InsertData(101,'北京', 1, 1)
    27.     # InsertData(102,'深圳', 1, 1)
    28.     # InsertData(103,'陕西 ', 1, 1)
    29.     # InsertData(104,'朝阳区', 2, 101)
    30.     # InsertData(105,'海淀区', 2, 101)
    31.     # InsertData(106,'宝安区', 2, 102)
    32.     # InsertData(107,'南山区', 2, 102)
    33.     # InsertData(108,'西安', 2, 103)
    34.     # InsertData(109,'莲湖区', 3, 108)
    35.     # InsertData(110,'蓝田县', 3, 108)
    36.     # InsertData(111,'周至县', 3, 108)
    37.     # InsertData(112,'咸阳', 2, 103)
    38.     # InsertData(113,'秦都区', 3, 112)
    40.     #DeleteData('西安')
    41.     #SelectData('蓝田县')
    42.    # SelectData('西安')
    43.     #SelectData('陕西')
    46. def DB():
    47.     db = pymysql.connect(host='127.0.0.1', user='root', password='redhat',
    48.                          db='bdp', port=3506, charset='utf8')
    49.     return db
    52. def InsertData(id,name, type_level, father):  # ok
    53.     db = DB()
    54.     cursor = db.cursor()
    55.     try:
    56.         cursor.execute(SQL_Insert %(id,name,type_level,father))
    57.         db.commit()
    58.     except:
    59.         print("Insert Error!")
    60.         db.rollback()
    61.     db.close()
    64. def SelectData(name):
    65.     db = DB()
    66.     # 创建游标，创建一个cursor执行一条SQL
    67.     cursor = db.cursor()
    69.     cursor.execute(SQL_TypeLevel % name)
    70.     type_level = cursor.fetchone()[0]
    72.     cursor.execute(SQL_Id % name)
    73.     id = cursor.fetchone()[0]
    75.     id_son=[]
    76.     cursor.execute(SQL_IdSon % name)
    77.     for i in range(cursor.rowcount):
    78.         id_son.append(cursor.fetchone()[0])
    80.     id_grandson=[]
    81.     for i in range(len(id_son)):
    82.         cursor.execute(SQL_IdGrandSon % id_son[i])
    83.         for j in range(cursor.rowcount):
    84.             id_grandson.append(cursor.fetchone()[0])
    86.     #try:    # 判断typelevel，1、2、3。再进行删除
    87.     if type_level == 1:
    88.         res_grand=[]
    89.         for i in id_grandson:
    90.              cursor.execute(SQL_Select %i)
    91.              res_grand.append(cursor.fetchone())
    94.         res_son=[]
    95.         for j in id_son:
    96.             cursor.execute(SQL_Select %j)
    97.             res_son.append(cursor.fetchone())
    99.         res_self=[]
    100.         cursor.execute(SQL_Select % id)
    101.         res_self=cursor.fetchone()
    103.         res=[]
    104.         res.append(res_self)
    105.         res.append(res_son)
    106.         res.append(res_grand)
    107.         print(res)
    108.     elif type_level == 2:
    109.         res_son=[]
    110.         res_self=[]
    111.         res=[]
    112.         for i in id_son:
    113.              cursor.execute(SQL_Select % i)
    114.              res_son.append(cursor.fetchone())
    115.         cursor.execute(SQL_Select % id)
    116.         res_self=cursor.fetchall()
    117.         res.append(res_self)
    118.         res.append(res_son)
    119.         print(res)
    120.     else:
    121.         res_self=[]
    122.         cursor.execute(SQL_Select % id)
    123.         res_self=cursor.fetchall()
    124.         print(res_self)
    125.     #except:
    126.     #    print("Select Error!")
    127.     db.close()
    131. def UpdateData(name,type_level,father,id):  # ok
    132.     db = DB()
    133.     cursor = db.cursor()
    134.     try:
    135.         cursor.execute(SQL_Update % (name ,type_level,father,id))
    136.         db.commit()
    137.     except:
    138.         print("Update Error!")
    139.     db.close()
    142. def DeleteData(name): # ok
    143.     db = DB()
    144.     cursor = db.cursor()
    146.     cursor.execute(SQL_TypeLevel % name)
    147.     type_level = cursor.fetchone()[0]
    149.     cursor.execute(SQL_Id % name)
    150.     id = cursor.fetchone()[0]
    152.     id_son=[]
    153.     cursor.execute(SQL_IdSon % name)
    154.     for i in range(cursor.rowcount):
    155.         id_son.append(cursor.fetchone()[0])
    157.     id_grandson=[]
    158.     for i in range(len(id_son)):
    159.         cursor.execute(SQL_IdGrandSon % id_son[i])
    160.         for j in range(cursor.rowcount):
    161.             id_grandson.append(cursor.fetchone()[0])
    163.     # 判断typelevel，1、2、3。再进行删除
    164.     if type_level == 1:
    165.         for i in id_grandson:
    166.              cursor.execute(SQL_Delete %i)
    167.         for j in id_son: 
    168.             cursor.execute(SQL_Delete %j)
    169.         cursor.execute(SQL_Delete % id)
    170.     elif type_level == 2:
    171.         cursor.execute(SQL_Delete % id_son)
    172.         cursor.execute(SQL_Delete % id)
    173.     else:
    174.         cursor.execute(SQL_Delete % id)
    175.     db.commit()
    176.     db.close()
    179. if __name__ == "__main__":
    180.     main()