Python Pull RDS Audit log

This script can pull the RDS audit log and insert it into the local data.

#!/usr/bin/env  python2.6#coding=utf-8import osfrom aliyunsdkcore import  clientfrom aliyunsdkrds.request.v20140815 import describesqllogrecordsrequestimport  jsonimport urllibimport datetime,timeimport subprocessfrom   subprocess  Import callimport warningsimport mysqldbfrom math import ceilfrom retrying  import retrydbserver= "192.168.0.94" dbuser= "root" dbpwd= "[email protected]" dbport=3306dbname= " Audit "warnings.filterwarnings (" Ignore ") os.environ[" PATH "]="/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/ Bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin "dblist = [' rm-1111 ', ' rm-2222 ', ' rr-333 ', ' rm-444 ', ' rm-555 ' ]list = {"rm-1111": "Beizhu1", "rm-2222": "Beizhu2",  "rr-333": "Beizhu3", "rm-444": "Beizhu4"}#  mysqldb  Operation class Class db_operate (object):         def __init_ _ (Self,_hostname,_user,_pwd,_port,_db):                 Self.conn=mysqldb.connect (host=_hostname,user=_user,passwd=_pwd,port=_port,db=_db)                  self.conn.set_character_set (' UTF8 ')                 self.cur= Self.conn.cursor ()         def execsql_fetchall (self,sqlcmd):                  Self.cur.execute (sqlcmd)                  result=self.cur.fetchall ()                  return result        def execsql_ Fetchone (Self,sqlcmd):                self.cur.execute (sqlcmd)                  result=self.cur.fetchall ()                  return result         DEF EXECSQL_DML (Self,sqlcmd):                 self.cur.execute (sqlcmd)          def is_connection_usable (self):                 try:                          Self.conn.ping ()                  except exception:                         return False                 else:                         return True                                                   #  time range required for backup def getdate ():         global start_date         global end_date        current_time =  Datetime.datetime.now ()         END_DATE = LOCALTRFUTC (current_time)              # Current Time         start_date =  LOCALTRFUTC (Current_time - datetime.timedelta (minutes=5))       #拉取指定时间范围内的日志         print start_date,end_date         return 0                  #本地   Time UTCDEF LOCALTRFUTC (local_time):         utc_time = local_time - datetime.timedelta (hours=8)          return utc_time.strftime ("%y-%m-%dt%h:%m:%sz")                   @retry (wait_fixed=5000) Def geterrorlog (db_ Instanceid,pagenum,flag):         print db_instanceid,pagenum,flag         clt = client. Acsclient (' 1234 ', ' 123456 ', ' Cn-hangzhou ')         request =  Describesqllogrecordsrequest.describesqllogrecordsrequest ()          Request.set_accept_format (' json ')         request.set_action_name (' Describesqllogrecords ')         request.set_dbinstanceid (Db_instanceid)         request.set_starttime (start_date)          request.set_endtime (end_date)          Request.set_pagenumber (int (pagenum))         result = clt.do_ Action (Request)         s=json.loads (result)         if flag == 0:                 pagerecordcount = float (s["PageRecordCount")                  totalrecordcount  = float (s["Totalrecordcount")                  if TotalRecordCount == 0:                          return 0                 Result = int (Ceil (totalrecordcount/pagerecordcount))          Else:                result  = s[' Items ' [' Sqlrecord ']        return result                                              def installlog (dbid,auditlog):         for log in  auditlog:                 totalexecutiontimes = log["Totalexecutiontimes"]                 executetime = datetime.datetime.strptime (log[" Executetime "], '%y-%m-%dt%h:%m:%sz ')  + datetime.timedelta (hours=8)                  accountname = log["AccountName"]                 hostaddress = log["HostAddress"]                 threadid =  log["ThreadID"]                 dbname = log["DBName"]                 returnrowcounts = log["Returnrowcounts"]                 sqltext =  '/*   ' +log[' SQLText "].replace (" ' ", '" ') + '   */'                  serverid = dbid                 servermark = unicode (List.get (dbid), "Utf-8")        &nbsP;         sqlcmd= '  insert into yw_business ( Totalexecutiontimes,executetime,accountname,hostaddress,threadid,dbname,returnrowcounts,sqltext,serverid, Servermark)  values ('%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ')   '  %  ( Totalexecutiontimes,executetime,accountname,hostaddress,threadid,dbname,returnrowcounts,sqltext,serverid, Servermark) #               sqlcmd= ' " insert into yw_business (Totalexecutiontimes,executetime,accountname,hostaddress,threadid, Dbname,returnrowcounts,sqltext)  values ('%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ')   '  %  ( Totalexecutiontimes,executetime,accountname,hostaddress,threadid,dbname,returnrowcounts,sqltext)                                try:                         FO.EXECSQL_DML (sqlcmd)                  except Exception,e:                          print sqlcmd                         print e         fo.conn.commit ()                  #  inserting data from other pages Def insertdata (dbid,totalpagenum):         for pagenum in range (1,totalpagenum+1):            &nbSp;    result = geterrorlog (dbid,pagenum,1)                   if result ==  "0":                          continue                 installlog (Dbid,result)                                   try:        if __name__ ==  "__main__" :                 getdate ()                 global fo    &nbsP;            fo=db_operate (Dbserver,dbuser,dbpwd, Int (dbport), dbname)                  for i in dblist:                         print i                          totalpages=geterrorlog (i,1,0)                          print totalpages                          insertdata (i,totalpages)               &nbsP;  fo.conn.close () except exception,e:        cmd  =  '  echo  -e   ' audit log insert exception '  |  mail -s   '  %s    Audit log Exceptions   '   [email protected]  '  %  (List.get ("%s"  % i) )          call (cmd,shell=true)          print e

Python Pull RDS Audit log