def connectToOracle(url, username, password, mode=None): if mode is not None: connection = cx.Connection(user=username, password=password, dsn=url, mode=mode) else: connection = cx.Connection(user=username, password=password, dsn=url) return connection
def connectToOracle(url, username, password, mode=None): try: if mode is not None: connection = cx_Oracle.Connection(user=username, password=password, dsn=url, mode=mode) else: connection = cx_Oracle.Connection(user=username, password=password, dsn=url) except cx_Oracle.DatabaseError as ex: raise return connection
def connect(username, password, connstr): parts = connstr.split(':') if parts[0] == 'oracle': # oracle:HOST:1521:SID host = parts[1] port = int(parts[2]) sid = parts[3] import cx_Oracle try: conn = cx_Oracle.Connection(username, password, dsn=cx_Oracle.makedsn(host, port, sid)) except cx_Oracle.DatabaseError: conn = cx_Oracle.Connection(username, password, dsn=cx_Oracle.makedsn(host, port, service_name=sid)) else: raise NotImplementedError("Unknown database type '%s'" % parts[0]) return conn
def fetch_from_db(start_time: datetime.datetime, end_time: datetime.datetime, dmdm: str, table): connection = cx_Oracle.Connection("CYZX", "CYZX", "10.118.84.24/PDCP") connection.ping() logging.debug("connect db ok") cursor = connection.cursor() cursor.arraysize = 100 # TODO: add limit? cursor.execute(""" SELECT JGSJ,DMDM,HPHM,HPYS,CLSD,CDBH,TPID,CSYS FROM {} T WHERE T.HPYS = '01' AND T.JGSJ >= :start_time AND T.JGSJ < :end_time AND (T.DMDM LIKE :dmdm ) """ .format(table), start_time=start_time, end_time=end_time, dmdm=dmdm) r = [] for row in cursor: row_json = { "JGSJ": row[0].timestamp(), "JGSJ_STR": row[0].isoformat(), "DMDM": row[1], "HPHM": row[2], "HPYS": row[3], "CLSD": row[4], "CDBH": row[5], "TPID": row[6], "CSYS": row[7] } # logging.debug("fetch row %s", json.dumps(row_json, ensure_ascii=False)) r.append(row_json) logging.info("fetch over, row len %s", len(r)) connection.close() return r
def __init__(self, dsn): '''Constructor, it opens the connection with the database''' self._dsn = dsn self._user = self._get_dbuser() self._connection = cx_Oracle.Connection(self._dsn) self._cursor = self._connection.cursor()
def __oracleConnection(self,username, password, database): import cx_Oracle self.text_t = self.__text_oracle self.int_t = self.__integer_oracle self.float_t = self.__float_oracle self.double_t = self.__double_oracle return cx_Oracle.Connection(username, password, database)
def _oracle_action(*args, condition, credentials_file=ora_conf): """ Fonction generique permettant de se connecter a Oracle sur Platon en utilisant les parametres ora_conf. ATTENTION les attributs de confs sont notes en specifique, si vous changez les parametres, assurez vous de changer les attributs de conf si necessaire. :param args: :param condition: :return: """ conf = _file_config(credentials_file) # log en premier car sinon on n'a pas ces infos dans rf print('Oracle connection -> user:{} - pwd:{} - {}:{}/{}'.format( conf.P_USRTRT, conf.P_PWDTRT, conf.P_HOSTTRT, conf.P_PORTTRT, conf.P_SIDTRT)) # les attributs dynamiques sont notés en brut, l'ide n'aime pas ça, mais creer un intermediaire # prendrait des heures et obligerai à ajouter un fichier de parametres, je concidere donc la methode en specifique connection = cx_Oracle.Connection( conf.P_USRTRT, conf.P_PWDTRT, "{}:{}/{}".format(conf.P_HOSTTRT, conf.P_PORTTRT, conf.P_SIDTRT)) print('Oracle connection -> CONNECTED') # generation du cursor cursor = connection.cursor() # utilisation conditionnelle du cursor, cette methode 1e classe est necessaire a l'utilistaion de cette fonction result = condition(cursor, *args) connection.commit() print('Oracle connection -> COMMITED') connection.close() print("Oracle connection -> CLOSED") # Le retour est generique, si pas besoin de retour, renvoie None return result
def call_proc(db,proc,*para): found=False curs=None connection=None print('Processing procedure '+proc) if para!=None : connection = cx_Oracle.Connection(db) try: curs = connection.cursor() curs.callproc("dbms_output.enable", (None,)) # enable output, if procedure has dbms_output.put_line, the message will output as well #return_val=curs.callfunc( para[0],cx_Oracle.STRING,para[1:]) #This for call oracle function. curs.callproc(proc,para) statusVar = curs.var(cx_Oracle.NUMBER) lineVar = curs.var(cx_Oracle.STRING) while True: curs.callproc("dbms_output.get_line", (lineVar, statusVar)) if statusVar.getvalue() != 0: break outmsg=(lineVar.getvalue()) print('MSG From %s => %s'%(proc,outmsg)) except Exception as e: connection.rollback() print('call_proc:'+str(e), file=sys.stderr) finally: if curs!=None: curs.close() if connection!=None: connection.commit() connection.close()
def get_selected_pel_term_desc(term, ptrm): """get the selected pel term displayed on course_search_pel.html / localsite views / course_search_pel def :param term: :param ptrm: :rtype: tuple :return: result - a tuple containing the name of the pel term """ logger = logging.getLogger('django') try: con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL) cursor = con.cursor() cursor.prepare( "SELECT PTRM_DESC FROM SWVPTRM_UP_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm" ) cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm}) result = cursor.fetchone() cursor.close() con.close() logger.info( "get_selected_pel_term_desc - term %s - ptrm %s - against Oracle" % (term, ptrm)) except cx_Oracle.DatabaseError as e: print(e) result = None return result
def get_campus(term='', ptrm=''): """get the campus locations displayed on course_search_pel.html / localsite views / course_search_pel def :param term: :param ptrm: :rtype: list :return: results - a list of tuples containing the campus locations """ logger = logging.getLogger('django') try: con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL) cursor = con.cursor() cursor.prepare( "SELECT TERM, PTRM, CAMP, CAMP_DESC FROM SWVCAMP_UP_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm" ) cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm}) results = cursor.fetchall() cursor.close() con.close() logger.debug("get_campus - term %s - ptrm %s - against Oracle" % (term, ptrm)) except cx_Oracle.DatabaseError as e: print(e) results = None return results
def get_all_instructors(term='', ptrm=''): """get all the instructors displayed on course_search_res.html / localsite views / course_search_res def displayed on course_search_pel.html / localsite views / course_search_pel def :param term: :param ptrm: :rtype: list :return: results - a list of tuples containing all of the instructors """ logger = logging.getLogger('django') try: con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL) cursor = con.cursor() cursor.prepare( "SELECT TERM, PTRM, PREF_NAME, CA_EMAIL, PREF_FIRST_NAME, PIDM FROM SWVINST_ASGN_PTRM_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm" ) cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm}) results = cursor.fetchall() cursor.close() con.close() logger.debug( "get_all_instructors - term %s - ptrm %s - against Oracle" % (term, ptrm)) except cx_Oracle.DatabaseError as e: print(e) results = None return results
def ssh_oracle(): sql = str( "select location_type from tlb_rent_request t where t.rent_request_code='S2009180003'" ) conn = cx.Connection('NFRZZL/[email protected]/YWDFNFRZZL') cur = conn.cursor() print('我是oracle数据库')
def select_db(sql): connection = cx_Oracle.Connection(DBLINE) cursor = connection.cursor() cursor.execute(sql) data = cursor.fetchall() return data connection.close()
def handle(self, *args, **options): # For making the logs easier to interpret later, log out when we started. self.stdout.write("Nightly Force Aggregation started at: {}".format( datetime.datetime.now())) # Get banner connection settings. banner = settings.ORACLE_MANAGEMENT['banner'] oracle_dsn = cx_Oracle.makedsn(banner['HOST'], banner['PORT'], banner['SID']) oracle_connection = cx_Oracle.Connection(banner['USER'], banner['PASS'], oracle_dsn) read_cursor = oracle_connection.cursor() read_cursor.execute( settings.ORACLE_MANAGEMENT['nightly_force']['EXPIRE_SQL']) write_cursor = oracle_connection.cursor() write_cursor.prepare( settings.ORACLE_MANAGEMENT['nightly_force']['INSERT_SQL']) write_cursor.setinputsizes(udc_id=225) for record in read_cursor: udc_id = record[0] self.stdout.write("Adding forced delta record for: " + udc_id) write_cursor.execute(None, {'udc_id': udc_id}) oracle_connection.commit() # Finally, print out when we finished. self.stdout.write("Nightly Force Aggregation finished at: {}".format( datetime.datetime.now()))
def pgDetails(self, tableName, pgId, pgName, timeStamp, status): try: dbConnection = cx_Oracle.Connection(self.connString) cur = dbConnection.cursor() #2020-02-02 08:39:39.211 cur.prepare( " insert into " + tableName + " values ( :pgId ,:pgName,TO_TIMESTAMP(:timeStamp,'YYYY-MM-DD HH24:MI:SS.FF'),:status) " ) cur.execute( None, { 'pgId': pgId, 'pgName': pgName, 'timeStamp': timeStamp, 'status': status }) dbConnection.commit() cur.close() dbConnection.close() except (IOError, OSError) as e: print("While reading data from database process got failed " + e) exit()
def _connect(self): dbh = cx_Oracle.Connection(self.username, self.password, self.database) if hasattr(sys, "argv"): dbh.cursor().execute( "BEGIN DBMS_APPLICATION_INFO.SET_MODULE('%s',NULL); END;" % sys.argv[0]) return dbh
def __init__(self, configfile): config = configparse() config.read(configfile) self.sourceDb = cx_Oracle.Connection( "%s/%s@%s:%s/%s" % (config.get("sourcedb", "user"), config.get( "sourcedb", "password"), config.get( "sourcedb", "host"), config.get("sourcedb", "port"), config.get("sourcedb", "instance") or config.get("sourcedb", "database"))) self.targetDb = pymysql.connect( host=config.get("targetdb", "host"), port=int(config.get("targetdb", "port")), user=config.get("targetdb", "user"), password=config.get("targetdb", "password"), database=config.get("targetdb", "database"), charset=config.get( "targetdb", "charset")) #, cursorclass = pymysql.cursors.SSCursor) self.sourceCursor = self.sourceDb.cursor() self.targetCursor = self.targetDb.cursor() if "oracle" in str(type(self.sourceCursor)).lower(): self.sourceSchema = config.get("sourcedb", "schema") or config.get( "sourcedb", "user") if "mysql" in str(type(self.sourceCursor)).lower(): self.sourceSchema = config.get("sourcedb", "database") if "mysql" in str(type(self.targetCursor)).lower(): self.targetCursor.execute("SET @@SQL_MODE='ANSI_QUOTES'") self.targetCursor.execute("set autocommit=0") self.targetSchema = config.get("targetdb", "database") self.tablelist = config.get("targetdb", "tablelist").split("\n") self.sourceTimestampField = config.get("sourcedb", "timestamp") self.targetTimestampField = config.get("targetdb", "timestamp")
class Consumer(): def __init__(self,v_str,v_type,v_sid): self.host=v_str.strip().split(':')[0] self.port=v_str.strip().split(':')[1] self.user=v_str.strip().split(':')[2] self.passwd=v_str.strip().split(':')[3] self.db=v_str.strip().split(':')[4] self.table=v_str.strip().split(':')[5] self.type=v_type.strip() self.sid=v_sid.strip() def get_type(self): type=self.type return type def get_cursor(self): db_type = self.get_type() if db_type == 'o2m' or db_type == 'm2m': try: conn = pymysql.connect(host=self.host, port=int(self.port), user=self.user, passwd=self.passwd, db=self.db, charset='UTF8') except Exception , e: print e return conn elif db_type == 'o2o' or db_type == 'm2o': try: v_dsn = cx_Oracle.makedsn(self.host, self.port, self.sid) conn = cx_Oracle.Connection(user=self.user, password=self.passwd, dsn=v_dsn) except Exception , e: print e
def save_report(user, periodo, report): ora = cx_Oracle.Connection(settings.DATABASES['default']['USER'] + '/' + settings.DATABASES['default']['PASSWORD'] + '@' + settings.DATABASES['default']['HOST'] + ':' + settings.DATABASES['default']['PORT'] + '/' + settings.DATABASES['default']['NAME']) cursor = ora.cursor() model = HistorialCreacionReporte() model.usuario = user model.periodo_id = periodo model.actualizacion = datetime.now() model.reporte_id = report model.save() if report == 1: cursor.callproc('MARGEN_MENSUAL') elif report == 2: cursor.callproc('PESO_TIPO_CLIENTE') elif report == 3: cursor.callproc('PRECIO_PROMEDIO') elif report == 4: cursor.callproc('DESCUENTOS') elif report == 5: cursor.callproc('COSTOS_UNITARIOS') elif report == 6: cursor.callproc('CARGA_FORMULA_INGRESO') cursor.callproc('FORMULA_INGRESOS') cursor.callproc('TIMELINE') cursor.close()
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 __enter__(self): #self.__db = cx_Oracle.Connection( "{USER}usercine/[email protected]:1521/orcl.example.com") db_conf = getattr(settings, "DATABASES", None)['default'] self.__db = cx_Oracle.Connection( "usuaroi/contrase@servidor:puerto/nombreorcl".format(**db_conf)) self.__cursor = self.__db.cursor() return self
def get_all_subjects(term='', ptrm=''): """get all the subjects for a term and ptrm displayed on course_search_res.html / localsite views / course_search_res def displayed on course_search_pel.html / localsite views / course_search_pel def :param term: :param ptrm: :rtype: list :return: results - a list of tuples containing all of the subjects """ logger = logging.getLogger('django') try: con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL) cursor = con.cursor() cursor.prepare( "SELECT TERM, SUBJ, NVL(SUBJ_DESC, 'n/a'), PTRM FROM SWVSUBJ_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm" ) cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm}) results = cursor.fetchall() cursor.close() con.close() logger.debug( "get_all_subjects - term %s - ptrm %s - against Oracle" % (term, ptrm)) except cx_Oracle.DatabaseError as e: print(e) results = None return results
def setUp(self): import cx_Oracle import TestEnv self.connection = cx_Oracle.Connection(TestEnv.MAIN_USER, TestEnv.MAIN_PASSWORD, TestEnv.CONNECT_STRING, encoding = TestEnv.ENCODING, nencoding = TestEnv.NENCODING) self.cursor = self.connection.cursor() self.cursor.arraysize = TestEnv.ARRAY_SIZE
def __enter__(self): if self.__tns_connection: self.__db = self.__connection_string else: self.__db = cx_Oracle.Connection(self.__connection_string) self.__cursor = self.__db.cursor() return self
def __newConnection( self ): """ Create a New connection and put it in the Queue """ self.logger.debug( '__newConnection:' ) connection = cx_Oracle.Connection( self.__userName, self.__passwd, self.__tnsName, threaded = True ) self.__putConnection( connection )
def get_ora_db(): import cx_Oracle #conn = cx_Oracle.Connection('lims_read/[email protected]:1521/naut90.mpimp-golm.mpg.de') #conn = cx_Oracle.Connection('TROST_USER/[email protected]:1521/naut81.mpimp-golm.mpg.de') conn = cx_Oracle.Connection('lims_read/jsbach@limsdb2/naut90.mpimp-golm.mpg.de') conn.current_schema = 'LIMS'; return conn
def getConnection(self, **kwargs): import cx_Oracle import TestEnv return cx_Oracle.Connection(TestEnv.MAIN_USER, TestEnv.MAIN_PASSWORD, TestEnv.CONNECT_STRING, encoding=TestEnv.ENCODING, nencoding=TestEnv.NENCODING, **kwargs)
def __getDBCfg(self): self.dbname=os.environ['YKT_DBNAME'] self.dbuser=os.environ['YKT_USER'] self.dbpwd=os.environ['YKT_PWD'] try: self.connect = cx_Oracle.Connection(self.dbuser + "/" + self.dbpwd + "@" + self.dbname) except cx_Oracle.DatabaseError, exc: print "连接数据库失败 :" , self.__parseOraExpt(exc) raise exc
def get_cursor(self): type = self.get_type() if type == 'o2o' or type == 'o2m': try: v_dsn = cx_Oracle.makedsn(self.host, self.port, self.sid) conn = cx_Oracle.Connection(user=self.user, password=self.passwd, dsn=v_dsn) except Exception , e: print e return conn
def LogMessages(connection, pipeName): """Log messages using the cx_Logging module.""" cx_Logging.Debug("logging messages from pipe %s", pipeName) debugConnection = cx_Oracle.Connection(connection.username, connection.password, connection.tnsentry, threaded=True) for message in MessageGenerator(debugConnection, pipeName): cx_Logging.Trace("%s", message)