def resetCursor(self):
try:
self.cursorQ.close()
self.cursor.close()
except Exception:
self.logger.error( "the cursor doesn't exist: going to create it" )
self.cursorQ = cx_Oracle.Cursor(self.connection)
self.cursor = cx_Oracle.Cursor(self.connection)
self.cursorQ.execute("alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'")
self.cursor.execute("alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'")
def connect(self): import cx_Oracle try: connection_string = self.user + "/" + self.password + "@" + self.host + ":" + str(self.port) + "/" + self.databaseName if self.cursor: self.cursor.close() if self.conn: self.conn.close() self.conn = cx_Oracle.Connection(connection_string) self.cursor = cx_Oracle.Cursor(self.conn) self.dictCursor = cx_Oracle.Cursor(self.conn) except Exception as inst: print >> sys.stderr, "Cannot connect to Oracle database: ", inst,
def getVendorData(sql):
host = "140.117.69.58"
port = "1521"
sid = "ORCL"
dsn = cx_Oracle.makedsn(host, port, sid)
connection = cx_Oracle.connect("Group3", "groupgroup33", dsn)
cursor = cx_Oracle.Cursor(connection)
res = cursor.execute(sql)
data = res.fetchall()
fields = res.description
res.close()
connection.close()
str_result = ''
column_list = []
for i in fields:
column_list.append(i[0])
result = []
datas = {}
for row in data:
rowdata = {}
for idx, i in enumerate(fields, start = 0):
value = row[idx]
if type(value) is datetime.datetime:
rowdata[column_list[idx]] = myconverter(row[idx])
else:
rowdata[column_list[idx]] = row[idx].strip()
result.append(rowdata)
datas['1'] = result
str_result = json.dumps(datas)
return str_result
def insertCleanedDataset(self, rundId, siteName, dsn, cont, rcdate, dsSize,
nAcc, cpuTime):
cursor = cx_Oracle.Cursor(self.__connection)
try:
query = '''
INSERT INTO CMS_CLEANING_AGENT.t_cleaned_dataset (runId, siteName, dsn, cont, rcdate, dsSize, nAcc, cpuTime)
VALUES(:runId, :siteName, :dsn, :cont, :rcdate, :dsSize, :nAcc, :cpuTime)
'''
bindVar = {
'runId': runId,
'siteName': siteName,
'dsn': dsn,
'cont': cont,
'rcdate': rcdate,
'dsSize': dsSize,
'nAcc': nAcc,
'cpuTime': cpuTime
}
cursor.execute(query, bindVar)
self.__connection.commit()
self.__logger.info('Inserted cleaned dataset %s for %s' %
(dsn, siteName))
except:
self.__logger.critical(
'Unable to insert a new cleaned replica: %s' %
traceback.format_exc())
finally:
cursor.close()
return
def connectOracle(self, database):
import cx_Oracle
cx_Oracle.threaded = True
self.conn = cx_Oracle.connect(database)
self.cursor = cx_Oracle.Cursor(self.conn)
print "Connected to ALERT"
def oracle_query(oradb, sql_cmd):
print("Retrieving data from Oracle ...")
ora_cursor = cx_Oracle.Cursor(oradb)
print("# SQL: '%s'" % sql_cmd)
ora_cursor.prepare(sql_cmd)
ora_cursor.execute(sql_cmd)
return ora_cursor
def saveDataToOracle(tableName, tableColList):
"""
入库
根据表名，及表列配置信息, 批量存储配置
程序主动提交
"""
ip = '192.168.100.80'
port = 1521
SID = 'ORCL'
dsn = cx_Oracle.makedsn(ip, port, SID)
# 连接数据库
connection = cx_Oracle.connect('PAY_34', '1', dsn)
try:
# 测试连接用——输出数据库版本
print(connection.version)
# 获取游标
cursor = cx_Oracle.Cursor(connection)
# 写入操作
cursor.prepare('insert into ' + tableName + ' (table_name, col_code, col_name, type, length, mo, required) '
'values '
'(:1, :2, :3, :4, :5, :6, :7)')
# 执行入库
cursor.executemany(None, tableColList)
connection.commit()
cursor.close()
connection.close()
except Exception as e:
print('Oracle 写入失败，Exception:{0}'.format(e))
connection.rollback()
connection.close()
def __createJob__(self, cmd):
'''
Create a job for DBMS_SCHEDULER
Be Careful: Special chars are not allowed in the command line
'''
logging.info('Create a job named {0}'.format(self.jobName))
splitCmd = cmd.split()
parameters = {
'job_name': self.jobName,
'job_type': 'EXECUTABLE',
'job_action': splitCmd[0],
'number_of_arguments': len(splitCmd) - 1,
'auto_drop': False
}
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try:
if self.args['show_sql_requests'] == True:
logging.info(
"SQL request executed: DBMS_SCHEDULER.create_job with these parameters: {0}"
.format(parameters))
cursor.callproc(name="DBMS_SCHEDULER.create_job",
keywordParameters=parameters)
except Exception, e:
logging.info('Error with DBMS_SCHEDULER.create_job:{0}'.format(
self.cleanError(e)))
return ErrorSQLRequest(e)
def get_conn(repeat_time=3):
"""
获取数据库连接
:return {tuple}: conn, cursor
"""
conn = None
cursor = None
global db_conn, CONFIG
# 允许出错时重复提交多次,只要设置了 repeat_time 的次数
while repeat_time > 0:
try:
if db_conn is None:
db_conn = oracle.connect('{user}/{password}@{host}:{port}/{sid}'.format(**CONFIG))
if db_conn:
conn = db_conn
# 尝试连接数据库
conn.ping()
cursor = oracle.Cursor(conn)
return conn, cursor
# 数据库连接,默认8小时没有使用会自动断开,这里捕获这异常
except Exception as e:
repeat_time -= 1
logging.error('oracle connection error:%s', e, exc_info=True)
try:
if cursor:
cursor.close()
except:
pass
try:
if conn:
conn.close()
except:
pass
return conn, cursor
def insertAccountingRecord(self, rundId, siteName, total, used,
toBeDeleted, inDeletionQueue, newlyCleaned):
cursor = cx_Oracle.Cursor(self.__connection)
try:
query = '''
INSERT INTO CMS_CLEANING_AGENT.t_accounting_record (runId, siteName, total, used, toBeDeleted, inDeletionQueue, newlyCleaned)
VALUES(:runId, :siteName, :total, :used, :toBeDeleted, :inDeletionQueue, :newlyCleaned)
'''
bindVar = {
'runId': runId,
'siteName': siteName,
'total': total,
'used': used,
'toBeDeleted': toBeDeleted,
'inDeletionQueue': inDeletionQueue,
'newlyCleaned': newlyCleaned
}
cursor.execute(query, bindVar)
self.__connection.commit()
self.__logger.info('Inserted accounting record for %s' %
(siteName))
except:
self.__logger.critical(
'Unable to insert a new accounting record: %s' %
traceback.format_exc())
finally:
cursor.close()
return
def __execPLSQLwithDbmsOutput__(self, request, addLineBreak=False):
'''
Execute the request containing dbms_output
'''
responsedata = ""
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try:
cursor.callproc("dbms_output.enable")
try:
cursor.execute(request)
except Exception as e:
logging.info(
"Impossible to execute the query `{0}`: {1}".format(
request, self.cleanError(e)))
return ErrorSQLRequest(e)
else:
statusVar = cursor.var(cx_Oracle.NUMBER)
lineVar = cursor.var(cx_Oracle.STRING)
while True:
cursor.callproc("dbms_output.get_line",
(lineVar, statusVar))
if statusVar.getvalue() != 0:
break
line = lineVar.getvalue()
if line == None:
line = ''
responsedata += line
if addLineBreak == True: responsedata += '\n'
cursor.close()
except Exception as e:
logging.info("Error with the request: {0}".format(str(e)))
return ErrorSQLRequest(e)
return responsedata
def Ins_OracleCommend(self, severName, df, tableName=''):
"""serveranme , dataFrame , TableName"""
try:
connection = self.GetOrConn(severName)
cursor = cx_Oracle.Cursor(connection)
# Insert SQL
cols = df.columns.tolist()
strsql = "INSERT INTO " + tableName + "(" + ','.join(
cols) + ") VALUES ( :" + ",:".join(cols) + ")"
for ix in df.index:
data = df.iloc[ix, :].tolist()
#rt={strsql:data}
cursor.execute(strsql, data)
connection.commit()
#return rt
except Exception as err:
print("Unexpected error: Ins_OracleCommend", str(err))
msg = "Ins_OracleCommend Err" + err + "\r\n"
msg += strsql + "\r\n"
msg += data
self.WiteLog(msg)
return msg
def grava_alarme(target, alarme, descricao):
"""Grava um novo alarme"""
#conectando
config_dbmon = Settings()
dbmon_string = config_dbmon.conn_dbmon
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
#check se nao existe
check = check_alarme_ativo(target, alarme)
if check == 0:
#cadastro novo alarme
strinsert = """
INSERT INTO TARGET_ALARME (TARGET, ALARME, DATA_ALARME, DESCRICAO, ISRECONHECIDO)
VALUES (:binTarget,
:binAlarme,
sysdate,
:binDescricao,
0)
"""
cur_dbmon.execute(strinsert,
binTarget=target,
binAlarme=alarme,
binDescricao=descricao)
conn_dbmon.commit()
if check == 1:
#atualizo o alarme informando a data do alarme atualizada
strupdate = """ UPDATE TARGET_ALARME SET
DATA_ULTIMO_ALARME=SYSDATE
WHERE TARGET=:binTarget
AND ALARME=:binAlarme
AND ISRECONHECIDO=0 """
cur_dbmon.execute(strupdate, binTarget=target, binAlarme=alarme)
conn_dbmon.commit()
cur_dbmon.close()
conn_dbmon.close()
def IsValidOracleName(self, name):
"""Return true if the name is valid for use within Oracle."""
cursor = cx_Oracle.Cursor(self)
try:
cursor.execute("select 1 as %s from dual %s" % (name, name))
return True
except:
return False
def getcursor(self):
if not self.conn:
self.connect()
try:
return db.Cursor(self.conn)
except db.DatabaseError, e:
raise STARSdbError("STARS database connection error: %s" % str(e))
def __init__(self):
try:
import cx_Oracle
connection_string = config.host_info_user + "/" + config.host_info_password + "@" + config.host_info_host + ":" + str(
config.host_info_port) + "/" + config.host_info_name
self.conn = cx_Oracle.Connection(connection_string)
self.cursor = cx_Oracle.Cursor(self.conn)
except Exception, e:
print >> sys.stderr, "Could not connect to HostInfoDB: ", e
sys.exit(-1)
def prepara_fila(self):
""" Procedimento para geracao da fila de ambientes para serem realizados o checklist """
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
try:
strsql="""
SELECT TARGET,TIPO_AMBIENTE
FROM ALL_TARGETS_MONITORAMENTO
WHERE TIPO_AMBIENTE in (SELECT TIPO_AMBIENTE
FROM CHECKLIST_TIPOAMBIENTE
WHERE CHECKLIST='"""+self.checklist+"""'
) """
cur_dbmon.execute(strsql)
strIns=""" INSERT INTO CONTROLE_CHECKLIST_FILA (
ID_CHECKLIST
,TARGET
,TIPO_AMBIENTE
,STATUS)VALUES
( :binID_CHECKLIST
,:binTARGET
,:binTIPO_AMBIENTE
,'AGUARDANDO'
)"""
cur_insDbmon = cx_Oracle.Cursor(conn_dbmon)
for row in cur_dbmon.fetchall():
cur_insDbmon.execute(strIns
,binID_CHECKLIST=self.id_checklist
,binTARGET=row[0]
,binTIPO_AMBIENTE=row[1]
)
conn_dbmon.commit()
cur_dbmon.close()
cur_insDbmon.close()
conn_dbmon.close()
print("Fila concluida com sucesso!")
except cx_Oracle.DatabaseError as error :
print("\n\n[ERRO] Problemas na gravacao da fila")
print("Oracle-Error-Code:", error.code)
print("Oracle-Error-Message:", error.message)
print("\n\n[FIM_ERRO]")
def get_monitoramentos(self):
""" Pega lista de monitoramentos """
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
strsql="select monitoramento from checklist_monitoramento where checklist='" + self.checklist + "' order by seq "
monitoramentos=[]
cur_dbmon.execute(strsql)
for row in cur_dbmon.fetchall():
monitoramentos.append(row[0])
return(monitoramentos)
def check_quantidade(self):
""" Procedimento para validar quantidade de objetos a serem processados - Tabela CONTROLE_CHECKLIST_FILA"""
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
strsql=""" select count(1) from controle_checklist_fila where id_checklist=:binIDCHECKLIST"""
cur_dbmon.execute(strsql,
binIDCHECKLIST=self.id_checklist)
for row in cur_dbmon.fetchall():
quant=row[0]
return(int(quant) )
def get_targets(self):
""" Pega os targets que serao executados no checklist"""
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
strsql="select target from controle_checklist_fila where id_checklist='" + str(self.id_checklist) +"' and status='AGUARDANDO'"
targets=[]
cur_dbmon.execute(strsql)
for row in cur_dbmon.fetchall():
targets.append(row[0])
return(targets)
def _getDBConnection(self):
try:
self.cursor.close()
self.connection.close()
except Exception as inst:
a = 1
#print inst # __str__ allows args to printed directly
self.connection = cx_Oracle.Connection(self.connection_string)
self.cursor = cx_Oracle.Cursor(self.connection)
def check_pendente_fila(self):
""" Procedimento para retornar a quantidade de itens pendente no checklist """
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
strsql=""" select count(1) from controle_checklist_fila where ID_CHECKLIST=:binIDCHECKLIST and status not in ('PROCESSADO','CANCELADO') """
cur_dbmon.execute(strsql,
binIDCHECKLIST=str(self.id_checklist) )
for row in cur_dbmon.fetchall():
quant=row[0]
cur_dbmon.close()
conn_dbmon.close()
def set_checklist(self):
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
strsql="select CHECKLIST, IDENTIFICACAO, TIPO_EXECUCAO, STATUS, TAREFA from controle_checklist where id_checklist='"+str(self.id_checklist)+"'"
cur_dbmon.execute(strsql)
for row in cur_dbmon.fetchall():
self.checklist=row[0]
self.ident_checklist=row[1]
self.tipo_execucao=row[2]
self.status=row[3]
self.tarefa=row[4]
def pega_target(id_checklist,id_fila):
dbmon_string = "dbmon/dbmon123@scandbmon:1521/dbmon"
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
target = cur_dbmon.var(cx_Oracle.FIXED_CHAR)
cur_dbmon.callproc("NXT_TARGET_CHECKLIST",(int(id_checklist),int(id_fila),target))
target=target.getvalue()
target=target.lstrip()
target=target.rstrip()
return target
cur_dbmon.close()
conn_dbmon.close()
def open(self):
try:
self.conn = cx_Oracle.connect(self.str, mode=cx_Oracle.SYSDBA)
self.cursor = cx_Oracle.Cursor(self.conn)
self.isClosed = False
except Exception as e:
print('连接出错')
self.cursor = None
self.isClosed = True
return self.cursor
def EditData(sql):
host = "140.117.69.58"
port = "1521"
sid = "ORCL"
dsn = cx_Oracle.makedsn(host, port, sid)
connection = cx_Oracle.connect("Group3", "groupgroup33", dsn)
cursor = cx_Oracle.Cursor(connection)
cursor.execute(sql)
connection.commit()
connection.close()
str_result = 'Success'
return str_result
def __runJob__(self):
'''
run the job named self.jobName
'''
logging.info('Run the job')
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try:
cursor.callproc(name="DBMS_SCHEDULER.enable",
keywordParameters={'name': self.jobName})
except Exception, e:
logging.info('DBMS_SCHEDULER.enable:{0}'.format(
self.cleanError(e)))
return ErrorSQLRequest(e)
def __init__(self, **list_first_data):
logger.debug("类：Oracle，接收数据list first data：%s" % str(list_first_data))
self.password = None
if not isinstance(list_first_data, dict):
raise ValueError('%s do not a dict.' % list_first_data)
try:
dns = cx_Oracle.makedsn(list_first_data['url'], list_first_data['port'], list_first_data['oracle_name'])
self.oracle_connection = cx_Oracle.Connection(list_first_data['username'], list_first_data['password'], dns)
self.oracle_cursor = cx_Oracle.Cursor(self.oracle_connection)
except Exception as err:
logger.error('Oracle():%s' % err)
else:
logger.debug('类：Oracle，完成初始化')
def __init__(self,username,password,ip,port,servicename):
try:
if username=='sys':
self.con=db.connect("{0}/{1}@{2}:{3}/{4}".format(username, password, ip,port, servicename),mode=db.SYSDBA)
else:
self.con=db.connect("{0}/{1}@{2}:{3}/{4}".format(username, password, ip,port, servicename))
self.cur=db.Cursor(self.con)
except Exception as errmsg:
print('connect error the error msg is:',str(errmsg))
sys.exit()
else:
pass
def _refresh_T_CorruptedFiles():
start_time = datetime.now()
connection = cx_Oracle.Connection(connection_string)
cursor = cx_Oracle.Cursor(connection)
cursor.callproc('CMS_POPULARITY_SYSTEM.CORRUPTEDFILEREFRESH')
stop_time = datetime.now()
printStats(start_time, stop_time, 'CORRUPTEDFILEREFRESH')
connection.commit()
cursor.close()
connection.close()