mark：通过python实现从mysqldump全量导出中导出某个库的所有行

环境

python3

 

mysqldump_export_one_db.py

# encoding=utf-8
'''
用于mysqldump --all-databases...全库导出数据后，需要从SQL文件中抽取某个库的信息生成新SQL文件，如果是多个库则需要重复运行导出即可。
'''
import os,time,platform

class export_one_db_sql():
    #从SQL文件中找出“Crruent Database:”对应的数据库名和行数，最终生成字典
    def __init__(self,filename):
        global db_page_dict
        db_page_dict = {}
        global db_list
        db_list = []
        if os.path.isfile(filename):
            with open(filename,mode='r',encoding='utf-8') as file:
                linenum = 0
                for line in file.readlines():
                    linenum+=1
                    if 'Current Database:' in line:
                        db = line.split()[3:4]
                        for dbname in db:
                            db_list.append(dbname.strip('`'))
                            db_page_dict[dbname.strip('`')]=linenum
                db_page_dict["last_page"]=linenum
        else:
            print ("未找到“{}”文件...".format(filename))
    
    def export_db_sql(self,filename,dbname):
        if dbname in db_page_dict.keys():
            #计算数据库在SQL文件中行的范围
            for num in range(0,len(db_page_dict)-1):
                if (list(db_page_dict.keys())[num]) == dbname:
                    start_pos = db_page_dict[list(db_page_dict.keys())[num]]
                    stop_pos = db_page_dict[list(db_page_dict.keys())[num + 1]] - 1
                    #用于Linux环境导出需要的行
                    if platform.system().lower() == 'linux':
                        print ('正在生成“{}.sql_{}”文件...'.format(dbname,time.strftime('%Y-%m-%d',time.localtime())))
                        os.popen("sed -n '{},{}p' all_db.sql > {}.sql_{}".format(start_pos,stop_pos,dbname,time.strftime('%Y-%m-%d',time.localtime())))
                        print ('“{}.sql_{}”文件已生成，请验证。'.format(dbname,time.strftime('%Y-%m-%d',time.localtime())))
                    #用于Windows环境导出需要的行
                    elif platform.system().lower() == 'windows':
                        with open(filename,mode='r',encoding='utf-8') as file:
                            with open('{}.sql_{}'.format(dbname,time.strftime('%Y-%m-%d',time.localtime())),mode='w+',encoding='utf-8') as sql_file:
                                print ('正在生成“{}.sql_{}”文件...'.format(dbname,time.strftime('%Y-%m-%d',time.localtime())))
                                num = 0
                                for line in file.readlines():
                                    num+=1
                                    if start_pos <= num <= stop_pos:
                                        print (line[0:len(line) - 1],file=sql_file)
                                print ('“{}.sql_{}”文件已生成，请验证。'.format(dbname,time.strftime('%Y-%m-%d',time.localtime())))
        else:
            print ('“{}”文件中未能找到“{}”数据库的信息。'.format(filename,dbname))

#测试程序
if __name__ == '__main__':
    filename = input("请输入需要解析的SQL文件名称：")
    export = export_one_db_sql(filename)
    print ('数据库列表：',db_list)
    dbname = input("请选择您需要导出的数据库：")
    export.export_db_sql(filename,dbname)

 

怎么使用?

[root]# python mysqldump_export_one_db.sql