python写的mysql清库脚本，可根据表名分类

之前测试创建数据有时候会有出错的时候，所以就简单写了一个清库的脚本，主要是为了根据表的名称分类清除，不用到navicat去那么多表里面找。

#清库脚本
import pymysql
import re
if __name__ == '__main__':
    type = 3#1只删除订单；2删除订单，基础信息和库存
    database = 'wms4.3'
    # 打开数据库连接，不指定数据库
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',passwd='root', db=database)
    # 获取游标
    cursor = conn.cursor()
    sql_select =f"select table_name from information_schema.tables where table_schema='{database}' and table_type='base table'"
    cursor.execute(sql_select)
    all_table = cursor.fetchall()
    #print(all_table)
    base_table=[]
    order_table=[]
    pmsn_table=[]
    stock_table=[]
    unver_table=[]
    report_table = []
    all_table_new = []
    diff_table = []
    for b in all_table:
        all_table_new.append(b[0])
    for table in all_table_new:
        if re.findall('^rec', table) or re.findall('^dict', table) or re.findall('^sys_parameter', table)  or re.findall('^sys_upgrade', table):
            unver_table.append(table)
        elif re.findall('^charge', table) or re.findall('^inbound', table) or re.findall('^outbound', table):
            order_table.append(table)
        elif re.findall('^pmsn', table) or re.findall('^sys_dept', table):
            pmsn_table.append(table)
        elif re.findall('^stock', table):
            stock_table.append(table)
        elif re.findall('^report', table) or re.findall('^sys_log', table):
            report_table.append(table)
        elif re.findall('^base', table):
             base_table.append(table)
    #print(f"unver_table {len(unver_table)}:\n{unver_table}\norder_table {len(order_table)}:\n{order_table}\n"
          #f"pmsn_table {len(pmsn_table)}:\n{pmsn_table}\nstock_table {len(stock_table)}:\n{stock_table}\nbase_table {len(base_table)}:\n{base_table}")
    if type==1:
        really_table = order_table
    if type==2:
        really_table=order_table+stock_table+base_table
    if type ==3:
        really_table = base_table
    i=0
    for name in really_table:
        sql = f'TRUNCATE TABLE {name}'
        cursor.execute(sql)
        conn.commit()
        i=i+1
        print(f'sql执行成功{name}({i}):{len(really_table)}')
    cursor.close()
    conn.close()
    print('sql执行成功****')