def download_from_ftp(needed_file_name, type_of_network): """ for downloading max 2g or 3g files and modifies filenames """ global needed_2g_file_name global needed_3g_file_name filename = needed_file_name if type_of_network == '2g': TO_COPY_DIR = TO_COPY_DIR_2G needed_2g_file_name = filename.replace(' ', '_') elif type_of_network == '3g': #3g file TO_COPY_DIR = TO_COPY_DIR_3G needed_3g_file_name = filename.replace(' ', '_') else: logger.critical('Its matched but I couldt find nighter Report_GSM nor Report_UMTS at filename') logging_mail.send('Its matched but I couldt find nighter Report_GSM nor Report_UMTS at filename %s' % (EMAIL_TEXT)) filename_new = filename.replace(' ', '_') ftp.retrbinary('RETR %s' % filename, open(TO_COPY_DIR+filename_new, 'w').write) logger.info('file: %s --> %s %s copied. ', filename, TO_COPY_DIR, filename_new )
def get_needed_filename(list_of_items, type_of_network): """this function finds the file within of /opt/oss/server/var/fileint/cm/Report' where the max date and returns one this file""" pattern = HOUR + '_' + YEAR + MONTH + DAY logger.info('Pattern is %s', pattern ) for each_item in list_of_items: match = re.findall(pattern, each_item) ##Report_GSM_Cell_Report_gsm_cell_status_report_hour_15_20150722.csv - hours-year-month-day if match: if type_of_network == '2g': match2 = re.match(r'Report_GSM', each_item) if match2: needed_file_name = each_item return needed_file_name elif type_of_network == '3g': match2 = re.match(r'Report_UMTS', each_item) if match2: needed_file_name = each_item return needed_file_name else: logger.critical('There is not set type_of_network to 2g or 3g!!!') logging_mail.send('There is not set type_of_network to 2g or 3g!!! %s' % (EMAIL_TEXT))
def cwd_ftp_dir(dir): """ Enter to the folders, calculates needed further folder and executing a download function""" global dirname dirname = dir try: ftp.cwd(dirname) except: logger.critical('Error changing to ftp directory %s', dirname) logging_mail.send('Error changing to ftp directory: %s %s' % (dirname, EMAIL_TEXT)) logger.info('*** Listed the directory: %s', dirname) if dirname == '/opt/oss/server/var/fileint/cm/Report': logger.info('You are within of: %s', ftp.pwd()) try: list_of_items = ftp.nlst() except: logger.critical('An exception occurs during list of ftp dir: %s', dirname) logging_mail.send('An exception occurs during list of ftp dir: %s %s' % (dirname, EMAIL_TEXT)) raise # to download only 1 GSM file with maximum date at filename logger.debug('HOUR: %s', HOUR) needed_file_name = get_needed_filename(list_of_items, '2g') if needed_file_name is None: logger.critical('The function get_needed_filename occurs TypeError exeption') logging_mail.send('The function get_needed_filename 2g returns None. GSM/UMTS data will not be updated: %s' % (EMAIL_TEXT)) exit(1) logger.info('I will download the 2g file from ftp') logger.info('needed_file_name: %s', needed_file_name) download_from_ftp(needed_file_name, '2g') # to download only 1 UMTS file with maximum date at filename logger.debug('HOUR: %s', HOUR) needed_file_name = get_needed_filename(list_of_items, '3g') if needed_file_name is None: logger.critical('The function get_needed_filename occurs TypeError exeption') logging_mail.send('The function get_needed_filename 2g returns None. GSM/UMTS data will not be updated: %s' % (EMAIL_TEXT)) exit(1) logger.info('I will download the 3g file from ftp') logger.info('needed_file_name: %s', needed_file_name) download_from_ftp(needed_file_name, '3g')
def connect_ftp(hostname=HOST, username=USER, password=PASSWD): '''connect to ftp-server specified in arguments. if no arguments specified than uses defaults values from constants ''' global ftp try: ftp = FTP(hostname, timeout=15) except: logger.critical('Cannot reach FTP-server: %s', hostname) logging_mail.send('Cannot reach FTP-server: %s %s' % (HOST, EMAIL_TEXT)) raise logger.info('*** Connected to host: %s ***', hostname) try: ftp.set_pasv(True) except: logger.critical('Cannot change ftp mode to passive: %s', ftp.set_pasv) logging_mail.send('Cannot change ftp mode to passive: %s %s %s' % (HOST, ftp.set_pasv, EMAIL_TEXT)) raise try: ftp.login(username, password) except: logger.critical('Cannot login to ftp server: %s', hostname) logging_mail.send('Cannot login to FTP-server: %s %s' % (HOST, EMAIL_TEXT)) raise return ftp
def insert_sql(filename, type_of_network): data_from_db = my_cursor.execute("SELECT TO_TIMESTAMP('09.07.2015 16:30:27') FROM DUAL") logger.info('data_from_db: %s', data_from_db) logger.info('local_time: %s', localtime) logger.info('the full file path: %s', filename) if type_of_network == '2g': s_filename = m2000_cell_status_ftp.needed_2g_file_name elif type_of_network == '3g': s_filename = m2000_cell_status_ftp.needed_3g_file_name else: logger.error('There is unknown type of network! not 2G not 3G') line_count = 0 with open(filename, 'rb') as csvfile: reader = csv.reader(csvfile, delimiter=',', quotechar='|') for row in reader: line_count +=1 if line_count == 1: logger.info('I will miss 1st line!') continue #we need to cut here the lentgh of nodeb_name if len(row[2]) > 6: temp_row = row[2] row[2] = temp_row[:6] # zdes ukorotili do 6 symbols DN2217@DN2217 -> DN2217 if type_of_network == '2g': try: sql = "INSERT INTO m2000_2g_cell_status a (a.insert_time, a.file_name, a.bsc_name, a.site_id, a.site_name, a.cell_id, a.cell_name, a.activity_status, a.ci, a.basei, \ a.ni, a.bcchno, a.freqseg, a.blk_status, a.hop_hsn, a.hop_tsc, a.hop_id) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') " \ % (localtime, s_filename, row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14]) except: logger.error('The error ocured during 2g sql-statement creation!') logging_mail.send('The error ocured during 2g sql-statement creation! %s %s' % (sql, EMAIL_TEXT)) elif type_of_network == '3g': try: sql = "INSERT INTO m2000_3g_cell_status a (a.insert_time, a.file_name, a.rnc_name, a.nodeb_id, a.nodeb_name, a.cell_id, a.cell_name, a.activity_status, a.blk_status, a.lac, \ a.sac, a.rac, a.ul_freq, a.dl_freq, a.max_power, a.cellcbsstate, a.cellmbmsstate, a.hsda_opstate, a.hsupa_opstate) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') " \ % (localtime, s_filename, row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16]) except: logger.error('The error ocured during 3g sql-statement creation!') logging_mail.send('The error ocured during 3g sql-statement creation! %s %s' % (sql, EMAIL_TEXT)) else: logger.error('There is unknown type of network! not 2G not 3G') logger.debug('The number of line: %s', line_count) logger.debug('The sql-query: %s', sql) try: logger.debug('Finish inserting values and commit') my_cursor.execute(sql) my_cursor.execute("COMMIT") except IndexError as msg: logger.error('IndexError occured during sql-query ecxecuting: %s %s:' % (msg, sql)) logging_mail.send('IndexError occured during sql-query ecxecuting: %s %s:' % (msg, EMAIL_TEXT)) raise except cx_Oracle.DatabaseError, msg: logger.error('The DB-Error occured during sql-query ecxecuting: %s' % msg) logging_mail.send('The DB-Error occured during sql-query ecxecuting: %s %s' % (msg, EMAIL_TEXT)) pass except TypeError as msg: #except Exception as msg: logger.error('The Type-Error occured during sql-query ecxecuting: %s' % msg) logging_mail('The Type-Error occured during sql-query ecxecuting: %s %s' % (msg, EMAIL_TEXT)) my_cursor.execute("ROLLBACK") except:
global my_cursor logger.info('Start connecting to database') try: con = cx_Oracle.connect(db_user, db_passwd, db_host_sid) except cx_Oracle.DatabaseError,msg: logger.error('The Logon to DB Error occured: %s', msg) sys.exit(1) my_cursor = con.cursor() try: my_cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS'") my_cursor.execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD.MM.YYYY HH24:MI:SS.FF'") logger.info("The NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT for DB SESSION are SET") my_cursor.execute("COMMIT") except: logger.error('The other error related to DB occured') logging_mail.send('There is error ALTER SESSION SET occured!!! %s' % (EMAIL_TEXT)) my_cursor.execute("ROLLBACK") def insert_sql(filename, type_of_network): data_from_db = my_cursor.execute("SELECT TO_TIMESTAMP('09.07.2015 16:30:27') FROM DUAL") logger.info('data_from_db: %s', data_from_db) logger.info('local_time: %s', localtime) logger.info('the full file path: %s', filename) if type_of_network == '2g': s_filename = m2000_cell_status_ftp.needed_2g_file_name elif type_of_network == '3g': s_filename = m2000_cell_status_ftp.needed_3g_file_name else: logger.error('There is unknown type of network! not 2G not 3G') line_count = 0