A business that you who operate application systems or have to perform is to push CDR between systems to serve the following tasks: reconciliation, report … etc. Today I will write a tutorial about using Python to do that.
Execution model:
- Database Oracle stores the data of the system applications it manages.
- My FTP server is given account / directory information by another party to push the CDR up.
- Data export from Oracle Database writes to a txt format file and pushes it to a folder on the FTP Server (the folder is formatted in yyyy-mm), in case it will automatically create a new folder in the next month before pushing the CDR file.
In the Python code I use libraries (how to install more libraries I have noted in the code):
- configparser for reading variable values in properties file
- cx_Oracle for Oracle Database connection
- ftplib connect to FTP Server
Contents properties file: config.properties
1 2 3 4 5 6 7 8 9 10 11 12 | [database] ip_db=xxxx port_db=1521 service_name=xxxx user_db=xxxx password_db=xxxx [ftp] ip_ftp=xxxx user_ftp=xxxx password_ftpd=xxxx |
Main.py file content Source code content: import library, read values from properties file
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ###Lib date### import time import datetime import os ##pip install cx_Oracle import cx_Oracle import csv ## pip install configparser import configparser ##pip install ftplib import ftplib ## today = datetime.date.today() date_export = today - datetime.timedelta(1) # Database connection variable. connect = None ## read and get values configparser config = configparser.ConfigParser() config.read('config.properties') db_url=config.get("database", "ip_db") service_name=config.get("database", "service_name") user_db=config.get("database", "user_db") password_db=config.get("database", "password_db") ## dsn_tns = cx_Oracle.makedsn(db_url, '1521', service_name=service_name) # File path and name. filePath = '/home/media/scripts/export_cdr_bigdatagw/cdr/' |
Source code content: check if the export file path on the server exists will make oracle database connection, run the command to export the defined data, write to the txt format cdr file (variable values in the cdr file are separated by commas. ,)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | # Check if the file path exists. if os.path.exists(filePath): try: # Connect to database. connect = cx_Oracle.connect(user=user_db, password=password_db, dsn=dsn_tns) except cx_Oracle.DatabaseError as e: # Confirm unsuccessful connection and stop program execution. print("Database connection unsuccessful.") quit() # Cursor to execute query. cursor = connect.cursor() try: gen_sql_export = "SELECT SOURCE, DEST, TO_CHAR(SENT_TIME,'YYYY-MM-DD HH24:MI:SS') FROM sms_ads.SMS_LOG partition (sms_log_"+str(date_export.strftime('%Y%m%d'))+") where status = 'SENT' and contract_type_id = 1 and telco_id = 1" print(gen_sql_export) fileName =str(date_export.strftime('%Y-%m-%d'))+'.txt' #print(fileName) print("Start export cdr "+str(date_export.strftime('%Y-%m-%d'))) # Execute query. cursor.execute(gen_sql_export) # Fetch the data returned. results = cursor.fetchall() # Extract the table headers. #headers = [i[0] for i in cursor.description] # Open text file for writing. textFile = csv.writer(open(filePath + fileName, 'w', newline=''), delimiter=',', lineterminator='rn', quoting=csv.QUOTE_NONE, escapechar='\') # Add the headers and data to the text file. #textFile.writerow(headers) textFile.writerows(results) # Message stating export successful. print("Data export successful.") except cx_Oracle.DatabaseError as e: # Confirm error retrieving person information and stop program execution. print("Error retrieving person information.") quit() finally: # Close database connection. connect.close() else: # Message stating file path does not exist. print("File path does not exist.") |
Source code content: push cdr file to ftp server, in case the directory on ftp server has not been initialized, will initialize the directory with the name ‘yyyy-mm’ before pushing the cdr file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | ##################### ip_ftp=config.get("ftp", "ip_ftp") user_ftp=config.get("ftp", "user_ftp") password_ftp=config.get("ftp", "password_ftpd") month_export=str(date_export.strftime('%Y-%m')) #ftp = FTP(ip_ftp) #ftp.login(user_ftp, password_ftp) os.chdir(filePath) with ftplib.FTP(ip_ftp) as ftp: filecdr = fileName try: ftp.login(user_ftp, password_ftp) if month_export in ftp.nlst() : #check if 'foo' month_export inside 'www' print('YES') ftp.cwd(month_export) # change into "foo" directory print("before uploadn", ftp.retrlines("LIST")) with open(filecdr, 'rb') as fp: res = ftp.storlines('STOR ' + filecdr, fp) if not res.startswith('226 Transfer complete'): print('Upload failed') print("after uploadn", ftp.retrlines("LIST")) ftp.close else : print('NO') ftp.mkd(month_export) #Create a new directory called foo on the server. ftp.cwd(month_export) # change into 'foo' directory print("before uploadn", ftp.retrlines("LIST")) with open(filecdr, 'rb') as fp: res = ftp.storlines('STOR ' + filecdr, fp) if not res.startswith('226 Transfer complete'): print('Upload failed') print("after uploadn", ftp.retrlines("LIST")) ftp.close except ftplib.all_errors as e: print('FTP error:', e) |
That’s the job done, when you put the crontab to run you can record logs for the monitor job to push the CDR on.