笔记：Python进行数据库文件导出备份

2019独角兽企业重金招聘Python工程师标准>>>

示范环境：
centos 6.4系统，oracle数据库服务器：192.168.1.112；用户/口令：ora/oracle
ftp文件备份服务器：192.168.1.113；用户/口令：databak/databak

 #!/usr/bin/python

#coding=utf-8

import os

import time, sys

import traceback

import ftplib

reload(sys)

sys.setdefaultencoding('utf-8')

IFS="/" #路径分割符，linux用"/" win32用"\\"

#设置数据库字符集

#os.putenv('NLS_LANG','AMERICAN_AMERICA.AL32UTF8')

nowdate=time.strftime("%Y%m%d%H%M") #当前日期

homedir = os.getcwd() #当前路径

workdir="/home/oracle/databak" #本地备份文件夹

errlogfile="/home/oracle/databak/PROGerror"+nowdate+".log" #错误日志名

user="orcl"   #数据库用户名

password="orcl"   #数据库密码

sid = "@192.168.1.112"  #数据库sid

dblog="/home/oracle/databak/EXP"+nowdate+".log " #DB备份过程中的exp日志 

dbconst="FULL=Y" #DB的其他参数字串

dbnamelist=["orcl"] #需要备份那些数据库

expCommand= "/opt/oracle/102/bin/exp " #oracle的exp导出文件所在目录

bufsize = 1024 #FTP的缓存，不要改

ftp_addr="192.168.1.113" #ftp地址

ftp_port="21" #ftp端口

ftp_user="databak" #ftp用户名

ftp_pwd="databak" #ftp密码

ftp_path="/112oracle" #远端ftp路径

def dumpdb(dbname):

    filename=dbname+"_"+nowdate #备份文件名

    #sqlvalformat ="%s userid=%s pwd=%s file=%s log=%s %s"

    dumpfile=os.path.join(workdir,filename+".dmp") #DUMP文件名

    #sqlval=sqlvalformat % (expCommand,user,password,dumpfile,dblog,dbconst)

    #sqlval="expCommand"+user+"/"+password,dumpfile,dblog,dbconst)

    sqlval=expCommand+user+"/"+password +"@ora112 file="+dumpfile+ " log=" + dblog 

    result=os.system(sqlval)

    print sqlval

def getfilename(path):

    pt=path.rfind(IFS)

    return path[pt+1:]

#def rarfile(destfile,sourcefile):

#    rarprogram=os.path.join(homedir,"RAR.exe") #rar for dos放在本地目录

#    rarvalformat = "%s a \"%s\" \"%s\" -r" #rar 的语法格式

#    rarfilename=os.path.join(workdir,destfile+"_"+nowdate+".rar") #压缩文件名

#    sourcefilename=os.path.join(workdir,sourcefile+"_"+nowdate+".dmp") #备份源文件

#    rarval= rarvalformat % (rarprogram,rarfilename,sourcefilename)

#    result=os.system(rarval)

    #print rarval

def ftpstor(ftpfile):

    ftp=ftplib.FTP()

    try:

        ftp.connect(ftp_addr,ftp_port)

        ftp.set_pasv(True)

        ftp.login(ftp_user,ftp_pwd)

        print ftp.getwelcome()

        ftp.cwd(ftp_path)

        f = open(ftpfile,'rb')

        print getfilename(ftpfile)," uploading..."

        ftp.storbinary("STOR %s "% getfilename(f.name) ,f,bufsize) #上传文件

        print getfilename(ftpfile)," upload completed."

        ftp.close()

        f.close() #关闭文件

        ftp.quit()

    except:

        info=sys.exc_info()

        path=os.path.join(workdir,errlogfile)

        traceback.print_exc(file=open(path,"a"))

def main():

    for dbname in dbnamelist:

        print time.strftime('%Y-%m-%d:%H:%M:%S')

        print "-----------------------------------------------"

        print dbname[:], "DB is backup now..." 

        dumpdb(dbname)

        print dbname[:], "DB backup completed."

        #print "-----------------------------------------------"

        #print "dmpfile compress now..."

        #rarfile(dbname,dbname)

        #print "dmpfile compress completed."

        print "-----------------------------------------------"

        ftpfilename=os.path.join(workdir,dbname+"_"+nowdate+".dmp")

        print ftpfilename," FTP session starting..."

        ftpstor(ftpfilename)

        print ftpfilename," FTP session completed."

        print "-----------------------------------------------"

        #dumpfile=os.path.join(workdir,dbname+"_"+nowdate+".dmp")

        #os.remove(dumpfile)

        #print dumpfile," remove completed." 

        #print "-----------------------------------------------"

main()

转载于:https://my.oschina.net/youway/blog/540601