#!/usr/bin/python
import os, sys
from datetime import datetime, timedelta
import cx_Oracle as orcl
import mysql.connector as pymysql
from pprint import pprint
alarms = { 198087342 : '2G' }
db= orcl.connect ( 'sys' , 'oracle' , 'minos' , mode= orcl.SYSDBA )
cursor = db.cursor ( )
cursor.execute ( "select position1,customattr5,to_char(alarmraisedtime_gmt,'YYYY-MM-DD HH24:MI:SS'),code from CAFFM4X.currentalarm where code in ('198087342')" )
p= cursor.fetchall ( )
print p
conn = pymysql.connect ( user= 'omcr' , password= 'omcr' , host= '117.239.216.50' , database= 'omcr' )
mycursor = conn.cursor ( )
for j in p:
values = [ j[ 1 ] [ 0 : 17 ] , j[ 1 ] [ 0 : 15 ] , j[ 0 ] .split ( '@' ) [ - 2 ] , 'Zte' , alarms[ j[ 3 ] ] , datetime.strptime ( j[ 2 ] , '%Y-%m-%d %H:%M:%S' ) ]
#bts_name=j[1].split('_')[-2]
print values
sql= ( "insert ignore into omcr.currentbtsdown_sectors(sector_name,bts_name,bsc_name,make,tech,down_time) values(%s,%s,%s,%s,%s,%s)" )
mycursor.execute ( sql, ( values) )
cursor.execute ( "select position1,customattr5,to_char(alarmraisedtime_gmt,'YYYY-MM-DD HH24:MI:SS'),to_char(alarmclearedtime,'YYYY-MM-DD HH24:MI:SS'),code from CAFFM4X.historyalarm \
where code in ('198087342') and alarmclearedtime>sysdate-3/24")
outage = cursor.fetchall ( )
for k in outage:
values = [ k[ 1 ] [ 0 : 17 ] , k[ 1 ] [ 0 : 15 ] , k[ 0 ] .split ( '@' ) [ - 2 ] , 'Zte' , alarms[ k[ 4 ] ] , datetime.strptime ( k[ 2 ] , '%Y-%m-%d %H:%M:%S' ) , datetime.strptime ( k[ 3 ] , '%Y-%m-%d %H:%M:%S' ) ]
#print values
sql = ( "replace into omcr.currentbtsdown_sectors(sector_name,bts_name,bsc_name,make,tech,down_time,up_time) values(%s,%s,%s,%s,%s,%s,%s)" )
mycursor.execute ( sql, ( values) )
conn.commit ( )
conn.close ( )
# your code goes here
IyEvdXNyL2Jpbi9weXRob24KCgppbXBvcnQgb3Msc3lzCmZyb20gZGF0ZXRpbWUgaW1wb3J0IGRhdGV0aW1lLHRpbWVkZWx0YQppbXBvcnQgY3hfT3JhY2xlIGFzIG9yY2wKaW1wb3J0IG15c3FsLmNvbm5lY3RvciBhcyBweW15c3FsCmZyb20gcHByaW50IGltcG9ydCBwcHJpbnQKCmFsYXJtcyA9IHsxOTgwODczNDI6JzJHJ30KCmRiPW9yY2wuY29ubmVjdCgnc3lzJywnb3JhY2xlJywnbWlub3MnLG1vZGU9b3JjbC5TWVNEQkEpCmN1cnNvciA9ZGIuY3Vyc29yKCkKY3Vyc29yLmV4ZWN1dGUoInNlbGVjdCBwb3NpdGlvbjEsY3VzdG9tYXR0cjUsdG9fY2hhcihhbGFybXJhaXNlZHRpbWVfZ210LCdZWVlZLU1NLUREIEhIMjQ6TUk6U1MnKSxjb2RlIGZyb20gQ0FGRk00WC5jdXJyZW50YWxhcm0gIHdoZXJlIGNvZGUgaW4gKCcxOTgwODczNDInKSIpCnA9Y3Vyc29yLmZldGNoYWxsKCkKCnByaW50IHAKCgpjb25uID0gcHlteXNxbC5jb25uZWN0KHVzZXI9J29tY3InLHBhc3N3b3JkPSdvbWNyJyxob3N0PScxMTcuMjM5LjIxNi41MCcsZGF0YWJhc2U9J29tY3InKQpteWN1cnNvciA9IGNvbm4uY3Vyc29yKCkKCmZvciBqIGluIHA6Cgl2YWx1ZXMgPVtqWzFdWzA6MTddLGpbMV1bMDoxNV0salswXS5zcGxpdCgnQCcpWy0yXSwnWnRlJyxhbGFybXNbalszXV0sZGF0ZXRpbWUuc3RycHRpbWUoalsyXSwnJVktJW0tJWQgJUg6JU06JVMnKV0KCSNidHNfbmFtZT1qWzFdLnNwbGl0KCdfJylbLTJdCglwcmludCB2YWx1ZXMKCXNxbD0oImluc2VydCBpZ25vcmUgaW50byBvbWNyLmN1cnJlbnRidHNkb3duX3NlY3RvcnMoc2VjdG9yX25hbWUsYnRzX25hbWUsYnNjX25hbWUsbWFrZSx0ZWNoLGRvd25fdGltZSkgdmFsdWVzKCVzLCVzLCVzLCVzLCVzLCVzKSIpCiAgICAgICAgbXljdXJzb3IuZXhlY3V0ZShzcWwsKHZhbHVlcykpCgoJCmN1cnNvci5leGVjdXRlKCJzZWxlY3QgcG9zaXRpb24xLGN1c3RvbWF0dHI1LHRvX2NoYXIoYWxhcm1yYWlzZWR0aW1lX2dtdCwnWVlZWS1NTS1ERCBISDI0Ok1JOlNTJyksdG9fY2hhcihhbGFybWNsZWFyZWR0aW1lLCdZWVlZLU1NLUREIEhIMjQ6TUk6U1MnKSxjb2RlIGZyb20gQ0FGRk00WC5oaXN0b3J5YWxhcm0gIFwKd2hlcmUgY29kZSBpbiAoJzE5ODA4NzM0MicpIGFuZCBhbGFybWNsZWFyZWR0aW1lPnN5c2RhdGUtMy8yNCIpCgpvdXRhZ2UgPSBjdXJzb3IuZmV0Y2hhbGwoKQoKZm9yIGsgaW4gb3V0YWdlOgoJdmFsdWVzID1ba1sxXVswOjE3XSxrWzFdWzA6MTVdLGtbMF0uc3BsaXQoJ0AnKVstMl0sJ1p0ZScsYWxhcm1zW2tbNF1dLGRhdGV0aW1lLnN0cnB0aW1lKGtbMl0sJyVZLSVtLSVkICVIOiVNOiVTJyksZGF0ZXRpbWUuc3RycHRpbWUoa1szXSwnJVktJW0tJWQgJUg6JU06JVMnKV0KCSNwcmludCB2YWx1ZXMKCXNxbCA9ICgicmVwbGFjZSBpbnRvIG9tY3IuY3VycmVudGJ0c2Rvd25fc2VjdG9ycyhzZWN0b3JfbmFtZSxidHNfbmFtZSxic2NfbmFtZSxtYWtlLHRlY2gsZG93bl90aW1lLHVwX3RpbWUpIHZhbHVlcyglcywlcywlcywlcywlcywlcywlcykiKQoJbXljdXJzb3IuZXhlY3V0ZShzcWwsKHZhbHVlcykpCgoKY29ubi5jb21taXQoKQpjb25uLmNsb3NlKCkKIyB5b3VyIGNvZGUgZ29lcyBoZXJl