#!/usr/bin/python3Import TimeImportJSONImportReImportdatetimeImportOSImportUUIDImportZipFileImportSYS fromConfigparserImportRawconfigparserImportioImportCopyImportPSYCOPG2ImportPsycopg2.extrasImportRedisdefzipdir (Path, zipf): forRoot, dirs, filesinchOs.walk (path): forFileinchFiles:zipf.write (Os.path.join (root, file), Os.path.basename (file))defMain ():ifLen (SYS.ARGV) < 2: Print("You must provide config file!", file=sys.stderr) Sys.exit (-1) Ini_str='[root]\n'+ open (Sys.argv[1],'R'). Read () ini_fp=io. Stringio (ini_str) config= Rawconfigparser (strict=false,allow_no_value=True) CONFIG.READFP (INI_FP)ifLen (SYS.ARGV) > 2: Cdr_down_key= Sys.argv[2] Else: Cdr_down_key='Cdr_down'pattern= Re.compile (r"""time\s*between\s* ' (. *?) ' \s*and\s* ' (. *?) '""", Re. I |Re. M) whileTrue:r= Redis. Strictredis (Host=config.get ('Root','Redis_host'), Port=config.getint ('Root','Redis_port'), db=0) received=R.lpop (Cdr_down_key)ifReceived:data= Json.loads (Received.decode ('Utf-8')) SQL= data['SQL'] Db_export_path= data['Db_export_path'] Db_web_path= data['Db_web_path'] Print(Db_export_path) log_id= data['log_id'] #print ("sql:", SQL)Match =pattern.search (SQL)ifmatch:canceled=False#Connect DBconn = Psycopg2.connect (host=data['Db_info']['Host'], port=data['Db_info']['Port'], database=data['Db_info']['Database'], user=data['Db_info']['Login'], password=data['Db_info']['Password']) Conn.autocommit=True backend_pid=conn.get_backend_pid () cursor= Conn.cursor (cursor_factory=psycopg2.extras.DictCursor) Cursor.execute ("SELECT * from Cdr_export_log WHERE id =%s", (log_id,)) Job=Cursor.fetchone ()ifjob['Status'] = = 5: Canceled=TrueContinueCursor.execute ("UPDATE cdr_export_log SET status = 1, Backend_pid =%s WHERE id =%s", (Backend_pid, log_id)) Start_time= Match.group (1) End_time= Match.group (2) start_time2= start_time[:19] End_time2= end_time[:19] TimeZone= start_time[20:] Start_dt= Datetime.datetime.strptime (Start_time2,"%y-%m-%d%h:%m:%s") End_dt= Datetime.datetime.strptime (End_time2,"%y-%m-%d%h:%m:%s") Days= (END_DT-START_DT). Days + 1Current_day=0Print("Start:", Start_dt,"End:", End_dt,"Time Zone:", TimeZone,"Days :", days) Loop_dt=copy.copy (START_DT) Delta= Datetime.timedelta (Days=1) Cdr_down_path= Os.path.join (Db_export_path,'Cdr_download') Cdr_web_path= Os.path.join (Db_web_path,'Cdr_download') if notos.path.exists (Cdr_web_path): Os.mkdir (Cdr_web_path,511) #Create temp dirUnique_path =Str (UUID.UUID4 ()) TempDir=Os.path.join (Cdr_down_path, Unique_path) Tempwebdir=Os.path.join (Cdr_web_path, Unique_path) os.mkdir (tempwebdir) os.chmod (Tempwebdir, 511) Cursor.execute ("UPDATE cdr_export_log SET status =%s, Total_days =%s, Completed_days =%s, File_dir =%s WHERE id =%s", (2, Days, Current_day, Tempwebdir, log_id)) whileLoop_dt.date () <=end_dt.date ():ifLoop_dt.date () = =start_dt.date (): Current_start_datetime= Loop_dt.strftime ("%y-%m-%d%h:%m:%s") Else: Current_start_datetime= Loop_dt.strftime ("%y-%m-%d 00:00:00") ifLoop_dt.date () = =end_dt.date (): Current_end_datetime= End_dt.strftime ("%y-%m-%d%h:%m:%s") Else: Current_end_datetime= Loop_dt.strftime ("%y-%m-%d 23:59:59") Print("%s ~%s"%(Current_start_datetime, current_end_datetime)) Replaced_sql= Pattern.sub ("Time between '%s%s ' and '%s '%s '"%(Current_start_datetime, timezone, Current_end_datetime, timezone), SQL) #print ("Replaced SQL:", Replaced_sql)filename ="%s%s_%s%s.csv"%(Current_start_datetime, timezone, Current_end_datetime, timezone) FilePath=os.path.join (tempdir, filename) webfilepath=os.path.join (tempwebdir, filename) copy_sql="COPY (%s) to '%s ' DELIMITER ', ' CSV HEADER"%(Replaced_sql, filepath)Try: Cursor.execute (copy_sql)except(Psycopg2.extensions.QueryCanceledError, psycopg2. Operationalerror):Print("canceled!") Canceled=True Break Print("SQL:", Copy_sql) Loop_dt+=Delta Current_day+ = 1Cursor.execute ("UPDATE cdr_export_log SET completed_days =%s WHERE id =%s", (Current_day, log_id))ifcanceled:cursor.close () conn.close ( )ContinueCursor.execute ("UPDATE cdr_export_log SET status = 3 WHERE id =%s", (log_id,)) Zipfile_path= Os.path.join (Cdr_web_path,"%s.zip"%(str (UUID.UUID4 () ))) Zipf= ZipFile. ZipFile (Zipfile_path,'W', allowzip64=True) Zipdir (Tempwebdir, Zipf) zipf.close ()Print("ZIP File:", Zipfile_path) Cursor.execute ("UPDATE cdr_export_log SET status = 4, File_path =%s WHERE id =%s", (Zipfile_path, log_id)) Cursor.close () Conn.close () Time.sleep (1)if __name__=="__main__": Main ()
Report on generating CDR (by Python3)