python 导入数据库的脚本
- 2020 年 1 月 9 日
- 筆記
该脚本针对批量导入数据sql文件，data目录存放所要导入的sql文件，list.txt存放要导入的列表信息。
脚本内容如下：
#!/usr/bin/env python # -*- coding:utf-8 -*- import os, sys, logging, datetime # 日志文件配置 if not os.path.isdir('logs'): os.mkdir('logs') logging.basicConfig(level=logging.INFO, format='%(asctime)s %(name)-12s %(levelname)-8s %(message)s', datefmt='%Y-%m-%d %H:%M', filename='logs/importable.log', filemode='a') console = logging.StreamHandler() console.setLevel(logging.INFO) formatter = logging.Formatter('%(message)s') console.setFormatter(formatter) logging.getLogger('').addHandler(console) logger = logging.getLogger(__name__) # 从本地配置文件中读取主机列表信息 def readinfo(confile): info_list = [] if os.path.isfile(confile): with open(confile, 'r+') as f: for line in f.readlines(): if not line.startswith("#"): dict = {} dict['id'] = line.split()[0] dict['host'] = line.split()[1] dict['name'] = line.split()[2] info_list.append(dict) return info_list else: logger.error(u"本地配置文件%s不存在！" % confile) sys.exit(1) def readsqlfile(data): sql_list = [] # 判断本地的sql文件是否存在目录是否存在。 if os.path.isdir(data): for sql_file in os.listdir(data): if sql_file: sql_list.append(os.path.join(data,sql_file)) else: logger.error(u"本地%s目录中没有要导入的sql文件！"%data) else: logger.error(u"%s 不是一个目录。"%data) return sql_list # 循环info_list和sql_list将sql导入每一个服务器 def importhost(info_list, sql_list): succ_list = [] fail_list = [] for ser in info_list: for sql_file in sql_list: cmd = "%s/mysql -u%s -p%s -h'%s' %s < %s" % (mysql_path, mysql_user, mysql_passwd, ser['host'], ser['name'] ,sql_file) status = os.system(cmd) if status == 0: msg = "%s -- %s -- %s 导入sql：%s 完毕！"%(ser['id'],ser['host'],ser['name'],sql_file) succ_list.append(msg) logger.info(msg) else: msg = "%s -- %s -- %s 导入sql：%s 失败！" % (ser['id'], ser['host'], ser['name'], sql_file) fail_list.append(msg) logger.error(msg) sys.exit(1) return succ_list,fail_list def yesorno(confile,data): print "数据库列表如下:" info_list = readinfo(confile) for ser in info_list: print "%s --- %s ---- %s"%(ser['id'],ser['host'],ser['name']) iput = raw_input("你是否要导入sql文件 %s，请输入yes or no:" % (os.listdir(data))) while True: if iput.lower() in ['y','yes']: print "你选择了继续执行程序动作！" break elif iput.lower() in ['n','no']: print "你选择了退出程序动作!" sys.exit(1) else: print "你输入了非法的字符,脚本并未执行导入sql文件的任何动作，程序即将退出。" sys.exit(1) if __name__ == '__main__': # 基本信息配置变量 data = r'data' # 存放本地sql文件的目录 confile = 'list.txt' # 存放服务器列表信息,格式如： id host dbname ： 1 192.168.2.20 game_name_cn1 # MySQL变量 mysql_path = '/usr/local/mysql/bin' mysql_user = 'root' mysql_passwd = 'xirexrt,mf' yesorno(confile,data) startime = datetime.datetime.now() sql_list = readsqlfile(data) info_list = readinfo(confile) succ,fail= importhost(info_list,sql_list) logger.info(u'总共执行成功%s条. rn 总共执行失败%s条.'%(len(succ), len(fail))) endtime = datetime.datetime.now() logger.info(u"rnTotal time: %s ms" % ((endtime - startime).microseconds / 1000))
