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
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
