批量还原sql文件

需求：单位里的睿智不会批量还原sql文件，这些文件在同一个数据库，单表的数据量不算大
解决：批量读取文件，然后运行sql语句就可以了，此方法适用于MySQL数据库

# -*- coding: utf-8 -*-
# @Time    : 2018/8/8 22:14
# @Author  : xiangchaoming
# @QQ      : 239036082

import os, subprocess
import pymysql
import pymysql.cursors


def create_sql():
    sql_list = []
    for root, dirs, files in os.walk("D:/temp"):
        for file in files:
            if file.endswith("sql"):
                file_path = os.path.join(root, file)
                sql_list.append(file_path)
    return sql_list


def mysql_handle():
    db = pymysql.connect(host="192.168.11.117", user="root",
                         password="", db="xiang", port=3306)

    # 使用cursor()方法获取操作游标
    cur = db.cursor()


    try:
        for sql_path in create_sql():
            try:
                with open(sql_path, "r", encoding="utf-8") as f:
                    sqls = f.read().replace("<--db-prefix-->", "").split(";\n")
                    ##执行sql语句，使用循环执行sql语句
                for sql in sqls:
                    print(sql)
                    if sql.__contains__("CREATE DATABASE `") or sql.__contains__("USE `"):
                        pass
                    else:
                        cur.execute(sql+";")
            except Exception as e:
                print(e)
                pass

    except Exception as e:
        print(e)
        pass
        # 错误回滚
        # db.rollback()
    finally:
        cur.close()
        db.commit()
        db.close()


if __name__=="__main__":
    mysql_handle()