def conn_actions(request): hostname = request.POST['hostname'] username = request.POST['username'] password = request.POST['password'] service = request.POST['servicename'] dsn_tns = cx_Oracle.makedsn(hostname, 1521, service) if request.POST['action'] == 'Test': message = 'TEST: ' + 'Hostname: ' + hostname + ' username: '******' password: '******' service: ' + service if username == 'sys': db = cx_Oracle.connect(username, password, dsn_tns, mode = cx_Oracle.SYSDBA) print db.version else: db = cx_Oracle.connect(username, password, dsn_tns) print db.version else: message = 'SAVE: ' + 'Hostname: ' + hostname + ' username: '******' password: '******' service: ' + service name = username + '@' + service try: n = DbConnections(conname=name,hostname=hostname,username=username,password=password,servicename=service) n.save() message = message + ' OK SAVED ...' return HttpResponseRedirect('/') except: pass return HttpResponse(message)
def __init__(self, host, port, username, password, db=None, service_name=None, charset="utf8", flag=True): self.host = host self.port = port self.username = username self.password = password self.db = db if flag: if int(self.port) != 1521: self.db = db self.conn = MySQLdb.connect(host=self.host, port=int(self.port), user=self.username, passwd=self.password, db=self.db, charset=charset) self.cursor = self.conn.cursor() else: import cx_Oracle # self.sid = sid self.service_name = service_name if self.db: self.dsn = cx_Oracle.makedsn(self.host, str(self.port), self.db) self.conn = cx_Oracle.connect( self.username, self.password, self.dsn) elif self.service_name: self.handle = "%s:%s/%s" % \ (self.host, str(self.port), self.service_name) self.conn = cx_Oracle.connect( self.username, self.password, self.handle) self.cursor = self.conn.cursor() else: self.db = db
def connect(self, conn_info): """ Method: connect Description: 实现数据库的连接 Parameters: 1. conn_info: 数据库连接信息 """ user = conn_info.info['username'] password = conn_info.info['password'] sid = None service_name = None if 'db' in conn_info.info: sid = conn_info.info['db'] if 'service_name' in conn_info.info: service_name = conn_info.info['service_name'] host = conn_info.info['host'] port = conn_info.info['port'] kargs = {} if conn_info.info['sysdba']: kargs['mode'] = cx_Oracle.SYSDBA if host is None: self._connection = cx_Oracle.connect(user=user, password=password, dsn=sid, **kargs) return dsn = self._makedsn(host, port, sid, service_name = service_name) #print("dsn=%s (%s:%s)" % (dsn, user, password)) self._connection = cx_Oracle.connect(user=user, password=password, dsn=dsn, **kargs)
def __connect(self): """ Function: __connect Summary: InsertHere Examples: self.__connect() Attributes: @param (self):Call __connect metod Oracle.__connect() Returns: connection """ # Set lang environment NLS_LANG = config.oracle['NLS_LANG'] os.environ['NLS_LANG'] = NLS_LANG if not cx_oracle_exists: msg = """The cx_Oracle module is required. 'pip install cx_Oracle' \ should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME \ & LD_LIBRARY_PATH is set""" logwrite.LogWrite(logmessage=msg, loglevel='errorLogger').write_log() dsn = cx_Oracle.makedsn(host=self.host, port=self.port, service_name=self.instance) try: if self.mode == 'sysdba' or self.username == 'sys': self.connection = cx_Oracle.connect(self.username, self.password, dsn, \ mode=cx_Oracle.SYSDBA) else: self.connection = cx_Oracle.connect(self.username, self.password, dsn) except cx_Oracle.DatabaseError as cx_msg: msg = 'Could not connect to database: %s, dsn: %s ' % (cx_msg, dsn) logwrite.LogWrite(logmessage=msg, loglevel='errorLogger').write_log() else: logwrite.LogWrite(logmessage="Connect oracle "+ self.connection.version+ \ " successfully!", loglevel='infoLogger').write_log() return self.connection
def __init__(self,user=None,pwd=None,tnsname=None,log=None): self.user = user self.pwd = pwd self.tnsname = tnsname self.log = log if not self.user : if os.path.isfile('setini.ini'): self.config = Configini('setini.ini') self.user,self.pwd,self.tnsname=self.config.Get('oracle',['user', 'pwd', 'tnsname']) else: self.user = '******' self.pwd = 'SIS' self.tnsname = 'SIS' if (self.user and self.pwd and self.tnsname): #print(type(self.user),type(self.tnsname),type(self.tnsname)) try: self.conn = cx_Oracle.connect(self.user, self.pwd, self.tnsname) except cx_Oracle.DatabaseError as e: if self.log: self.log.Info(e) else: raise OracleError(e) elif self.user: try: self.conn = cx_Oracle.connect(self.user) except cx_Oracle.DatabaseError as e: if self.log: self.log.Info(e) else: raise OracleError(e) if self.log: self.log.Info('Oracle初始化连接成功')
def get_connection(repo_name): SID = service_name = None if repo_name.lower() == 'odsi' : host = import_properties['odsi_host'] port = import_properties['odsi_port'] if 'odsi_sid' in import_properties : SID = import_properties['odsi_sid'] else : service_name = import_properties['odsi_service_name'] user_name = import_properties['odsi_username'] password = import_properties['odsi_password'] else : host = import_properties['discovery_host'] port = import_properties['discovery_port'] if 'discovery_sid' in import_properties : SID = import_properties['discovery_sid'] else : service_name = import_properties['discovery_service_name'] user_name = import_properties['discovery_username'] password = import_properties['discovery_password'] if service_name : connection_str = user_name + '/' + password + '@' + host + '/' + service_name con = cx_Oracle.connect(connection_str) else : dsn_tns = cx_Oracle.makedsn(host, port, SID) con = cx_Oracle.connect(user_name, password, dsn_tns) return con
def __init__(self, connectString, mode=""): # Instance attributs self.connection = None self.cursor = None # Read Conf self.conf = PysqlConf.getConfig() # Keep connection string to allow future connection self.connectString = connectString # Connect to Oracle try: if mode == "sysoper": try: self.connection = connect(self.connectString, mode=SYSOPER, threaded=True) except (DatabaseError) as e: print(CYAN + _("Connected to an idle instance") + RESET) self.connection = connect(self.connectString, mode=SYSOPER | PRELIM_AUTH, threaded=True) elif mode == "sysdba": try: self.connection = connect(self.connectString, mode=SYSDBA, threaded=True) except (DatabaseError) as e: print(CYAN + _("Connected to an idle instance") + RESET) self.connection = connect(self.connectString, mode=SYSDBA | PRELIM_AUTH, threaded=True) else: self.connection = connect(self.connectString, threaded=True) except (DatabaseError, RuntimeError, InterfaceError) as e: raise PysqlException(_("Cannot connect to Oracle: %s") % e)
def get_conn(self): """ Returns a oracle connection object Optional parameters for using a custom DSN connection (instead of using a server alias from tnsnames.ora) The dsn (data source name) is the TNS entry (from the Oracle names server or tnsnames.ora file) or is a string like the one returned from makedsn(). :param dsn: the host address for the Oracle server :param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS) You can set these parameters in the extra fields of your connection as in ``{ "dsn":"some.host.address" , "service_name":"some.service.name" }`` """ conn = self.get_connection(self.oracle_conn_id) dsn = conn.extra_dejson.get('dsn', None) sid = conn.extra_dejson.get('sid', None) mod = conn.extra_dejson.get('module', None) service_name = conn.extra_dejson.get('service_name', None) if dsn and sid and not service_name: dsn = cx_Oracle.makedsn(dsn, conn.port, sid) conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn) elif dsn and service_name and not sid: dsn = cx_Oracle.makedsn(dsn, conn.port, service_name=service_name) conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn) else: conn = cx_Oracle.connect(conn.login, conn.password, conn.host) if mod is not None: conn.module = mod return conn
def connection(self,threaded =True, stopIfError=False): ''' Connection to the database 'The threaded argument is expected to be a boolean expression which indicates whether or not Oracle should use the mode OCI_THREADED to wrap accesses to connections with a mutex. Doing so in single threaded applications imposes a performance penalty of about 10-15% which is why the default is False.' If stopIfError == True, stop if connection error ''' try: if self.args['SYSDBA'] == True : self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSDBA,threaded=threaded) elif self.args['SYSOPER'] == True : self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSOPER,threaded=threaded) else : self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'],threaded=threaded) self.args['dbcon'].autocommit = True if self.remoteOS == '' and self.oracleDatabaseversion=='' : self.loadInformationRemoteDatabase() return True except Exception, e: if self.ERROR_CONN_IMPOSS in str(e) or self.ERROR_UNABLE_TO_ACQUIRE_ENV in str(e): logging.critical("Impossible to connect to the remost host") exit(EXIT_BAD_CONNECTION) elif self.ERROR_NOT_SYSDBA in str(e): logging.info("Connection as SYS should be as SYSDBA or SYSOPER, try to connect as SYSDBA") self.args['SYSDBA'] = True return self.connection(threaded=threaded, stopIfError=stopIfError) elif self.ERROR_INSUFF_PRIV_CONN in str(e): logging.info("Insufficient privileges, SYSDBA or SYSOPER disabled") self.args['SYSDBA'] = False self.args['SYSOPER'] = False return self.connection(threaded=threaded, stopIfError=stopIfError) elif stopIfError == True: logging.critical("Impossible to connect to the remote database: {0}".format(self.cleanError(e))) exit(EXIT_BAD_CONNECTION) else : return ErrorSQLRequest(e)
def test_find_one_with_lock(self): db_conn = cx.connect( '%s/%s@%s' % ('risk', 'oracle123!', '180.2.31.130:1521/interdb1')) dao = RiskOrderDao(db_conn) order = dao.find_one_with_lock(local_order_no='81905070000009', seat_id='701211') db2 = cx.connect('%s/%s@%s' % ('risk', 'oracle123!', '180.2.31.130:1521/interdb1')) dao2 = RiskOrderDao(db2) try: order2 = dao2.find_one_with_lock(nowait=True, local_order_no='81905070000009', seat_id='701211') print(order2) order2.order_no = '200200' db_conn.commit() except Exception as e: print(e) db2.rollback() finally: db2.close() order.order_no = '100100' order.update() db_conn.commit()
def create_connect_oracle(self): """create and return a connection""" dsn = cx_Oracle.makedsn(host=self.host, port=self.port, sid=self.sid ) if self.mode: con = cx_Oracle.connect(user=self.user, password=self.passwd, dsn=dsn, threaded=True, mode=cx_Oracle.SYSDBA ) else: con = cx_Oracle.connect(user=self.user, password=self.passwd, dsn=dsn, threaded=True ) con.clientinfo = 'cx_Oracle in Python' con.module = 'cx_Oracle demo' con.action = 'BatchJob #1' return con
def importdata_text(dbstr,tablename,fileinfo,filesplit): if dbstr=="tran1" : connect=cx_Oracle.connect('tran1','bfbusicrm','132.80.247.18:1521/ywqydb') elif dbstr=="ngcrm" : connect=cx_Oracle.connect('uop_crm1','ailkcubj','132.80.241.214:11521/ngcrm') couttotal = len(open(fileinfo,'rU').readlines()) instance = open(fileinfo) lines =[] countpercent=0 countnow=0 column=0 column_str="" for line in instance.readlines(): column=len(line.split(filesplit)) lines.append(line.split(filesplit)) countpercent+=1 countcomp=countpercent*100/couttotal if countcomp>countnow : tem='%d' %countcomp print "load %s%s...." %(tem,'%') countnow=countcomp instance.close() for i in range(0,column): column_str+=":%d,"%(i+1) column_str = column_str[:-1] var = '''insert into %s values(%s)''' % (tablename,column_str) cursor = connect.cursor() cursor.executemany(var,lines); cursor.close(); connect.commit(); connect.close();
def conexion(ubicacion): ''' conexion Parametros de Entrada: String con el nombre de la base de datos. Parametros de salida: Objeto tipo ConexioDB ''' if ubicacion.lower() == 'pre-opendata-oracle': devolver = ConexionDB(cx_Oracle.connect(configuracion.OPENDATA_USR + "/" + configuracion.OPENDATA_PASS + "@" + configuracion.OPENDATA_CONEXION_BD_PRE),"oracle") elif ubicacion.lower() == 'pro-opendata-oracle': devolver = ConexionDB(cx_Oracle.connect(configuracion.OPENDATA_USR + "/" + configuracion.OPENDATA_PASS + "@" + configuracion.OPENDATA_CONEXION_BD),"oracle") elif ubicacion.lower() == 'des-opendata-oracle': devolver = ConexionDB(cx_Oracle.connect(configuracion.OPENDATA_USR + "/" + configuracion.OPENDATA_PASS + "@" + configuracion.OPENDATA_CONEXION_BD_DES),"oracle") elif ubicacion.lower() == 'app1': #Es la conexion de storage.py correspondiente a app1 devolver = ConexionDB(cx_Oracle.connect(configuracion.AST_USR + "/" + configuracion.AST_PASS + "@" + configuracion.AST1_CONEXION_BD),"oracle") elif ubicacion.lower() == 'app2': # Es la conexion de storage.py correspondiente a app2 devolver = ConexionDB(cx_Oracle.connect(configuracion.AST_USR + "/" + configuracion.AST_PASS + "@" + configuracion.AST2_CONEXION_BD),"oracle") elif ubicacion.lower() == 'app3':# Es la conexion de storage.py correspondiente a app3 que es la de senderos devolver = ConexionDB(psycopg2.connect(configuracion.AST3_CONEXION_BD),"postgre") elif ubicacion.lower() == 'opendata-postgre': devolver= ConexionDB(psycopg2.connect(configuracion.OPENDATA_POSTGRE_CONEXION_BD),"postgre") elif (ubicacion.lower() == 'app4'): # Es la conexion de storage.py correspondiente a app4 devolver = ConexionDB(pymssql.connect(host='bov-domus-01.aragon.local', user='******', password='******', database='BD_COLECCION_WEB'),"sqlserver") elif (ubicacion.lower() == 'app5'): # Conexion para MySLQ app5 devolver = ConexionDB(MySQLdb.connect(host='194.179.110.14',port=3306, user='******', passwd='0p3n-DATA',db='webiaf'),"mysql") else: print 'No hay conexion para el tipo para la ubicacion : ', ubicacion return None; return devolver
def _cursor(self, settings): cursor = None if not self._valid_connection(): if len(settings.DATABASE_HOST.strip()) == 0: settings.DATABASE_HOST = 'localhost' if len(settings.DATABASE_PORT.strip()) != 0: dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME) self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options) else: conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME) self.connection = Database.connect(conn_string, **self.options) cursor = FormatStylePlaceholderCursor(self.connection) # Set oracle date to ansi date format. This only needs to execute # once when we create a new connection. cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' " "NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'") try: self.oracle_version = int(self.connection.version.split('.')[0]) except ValueError: pass try: self.connection.stmtcachesize = 20 except: # Django docs specify cx_Oracle version 4.3.1 or higher, but # stmtcachesize is available only in 4.3.2 and up. pass if not cursor: cursor = FormatStylePlaceholderCursor(self.connection) # Default arraysize of 1 is highly sub-optimal. cursor.arraysize = 100 return cursor
def db_connect(envir): # # EXAMPLE # print("Connecting to AT prod database") # connection = db_connect('prod') # print(" found version "+ str(connection.version)) # querystring = "select * from dima_occasion where rownum = 1" # cursor = connection.cursor() # cursor.execute(querystring) # if len(cursor.fetchone()) >=1: # print(' data fetched') # else: # print(' NO data fetched') # connection.close() db_connectstrings = { 'dev' :'dmcg/[email protected]/ATTRCKD' ,'test':'dmcg/[email protected]/ATTRCKT' ,'acc' :'dmcg/[email protected]/ATTRCKA' ,'prod':'dmcg/[email protected]/ATTRCKP' } if envir == 'dev': dsnStr = cx_Oracle.makedsn("ATTRCKD.lsnr.persgroep.be", "1521", "ATTRCKD") con = cx_Oracle.connect(user="******", password="******", dsn=dsnStr) return con elif envir == 'test': dsnStr = cx_Oracle.makedsn("ATTRCKT.lsnr.persgroep.be", "1521", "ATTRCKT") con = cx_Oracle.connect(user="******", password="******", dsn=dsnStr) return con else: con = cx_Oracle.connect(db_connectstrings[envir]) return con
def connectORA(LOG): try: ip = '10.196.1.140' port = 1521 SID = 'vrl1' dsn_tns = cxo.makedsn(ip,port,SID) #con = cxo.connect('vlps/vlps@failover_db.virologic.com/vrl1') # get a connection or raise exception vlps = cxo.connect('vlps','vlps',dsn_tns) vlps_cursor = vlps.cursor() # connection returns a cursor object which is used to perform queries LOG.write(gt()+"\tSuccessful connection made to Oracle as vlps\n") except: exceptionType, exceptionValue, exceptionTraceback = sys.exc_info() LOG.write(gt()+"\tvlps Oracle Database connection failed!\n ->%s" % (exceptionValue)) #sys.exit(gt()+"\tOracle Database connection failed!\n ->%s" % (exceptionValue)) return 'failed','','' try: etag = cxo.connect('etag','etag',dsn_tns) etag_cursor = etag.cursor() LOG.write(gt()+"\tSuccessful connection made to Oracle as etag\n") except: exceptionType, exceptionValue, exceptionTraceback = sys.exc_info() LOG.write(gt()+"\tetag Oracle Database connection failed!\n ->%s" % (exceptionValue)) return 'failed','','' return 'ok', vlps_cursor, etag_cursor
def nodeconnect(connstr,preference=None): """connect to oracle, possibly looking for a particular node""" try: if preference is None: return cx_Oracle.connect(connstr) else: while True: conn=cx_Oracle.connect(connstr) myinstance=instance(conn)[0] print now(),'got instance...',myinstance if myinstance==preference: return conn else: conn.close() except cx_Oracle.DatabaseError,e: # ORA-01033: ORACLE initialization or shutdown in progress # ORA-12537: TNS:connection closed # ORA-12528: TNS:listener: all appropriate instances are blocking # new connections # ORA-12521: TNS:listener does not currently know of instance # requested in connect descriptor # ORA-12520: TNS:listener could not find available handler for # requested type of server # ORA-12514: TNS:listener does not currently know of service # requested in connect descriptor if e.message.code in [1033,12514,12537,12528,12521,12520]: print 'RETRYING:', e.message.message.strip() time.sleep(2) return nodeconnect(connstr,preference) else: raise(e)
def oracle_handling(ip, port, username, passwd, db_name, query, sid_type, db_type): try: cursor = None result = None dbconn = None is_socket_up = False is_socket_up = socket_monitor(ip, port) if is_socket_up: if sid_type == 2: dsn = cx_Oracle.makedsn(ip, port, db_name) dbconn = cx_Oracle.connect(username, passwd, dsn) else: dbconn = cx_Oracle.connect(username, passwd, '%s:%s/%s'%(ip, port, db_name)) if dbconn: cursor = dbconn.cursor() cursor.execute(query) result = cursor.fetchall() return True, "Success" else: return False, "Could not Get connection object" else: return False, "Socket Connection Fail" except Exception, ex: return False, "Exception while making connection"
def __init__(self, connectString, mode=""): # Instance attributs self.connection = None self.cursor = None # Read Conf self.conf = PysqlConf.getConfig() # Keep connection string to allow future connection self.connectString = connectString.encode(self.conf.getCodec(), "ignore") # Connect to Oracle try: if mode == "sysoper": try: self.connection = connect(self.connectString, mode=SYSOPER) except (DatabaseError), e: print CYAN + _("Connected to an idle instance") + RESET self.connection = connect(self.connectString, mode=SYSOPER | PRELIM_AUTH) elif mode == "sysdba": try: self.connection = connect(self.connectString, mode=SYSDBA) except (DatabaseError), e: print CYAN + _("Connected to an idle instance") + RESET self.connection = connect(self.connectString, mode=SYSDBA | PRELIM_AUTH)
def importdata_excel(dbstr,tablename,fileinfo): if dbstr=="tran1" : connect=cx_Oracle.connect('tran1','bfbusicrm','132.80.247.18:1521/ywqydb') elif dbstr=="ngcrm" : connect=cx_Oracle.connect('uop_crm1','ailkcubj','132.80.241.214:11521/ngcrm') data = xlrd.open_workbook(fileinfo) table = data.sheets()[0] nrows = table.nrows #行数 ncols = table.ncols #列数 lines =[] countpercent=1 countnow=0 column_str="" for rownum in range(1,nrows): row = table.row_values(rownum) lines.append(row) countpercent+=1 countcomp=countpercent*100/nrows if countcomp>countnow : tem='%d' %countcomp print "load %s%s...." %(tem,'%') countnow=countcomp for i in range(0,ncols): column_str+=":%d,"%(i+1) column_str = column_str[:-1] var = '''insert into %s values(%s)''' % (tablename,column_str) cursor = connect.cursor() cursor.executemany(var,lines); cursor.close(); connect.commit(); connect.close();
def main(): """main program""" p = optparse.OptionParser(usage="usage: %prog options pkg...") p.add_option("-C","--conn",action="store",type="string",dest="conn", help="Database connection string") p.add_option("-O","--output",action="store",type="string",dest="output", help="Output file") p.add_option("","--lang",action="store",type="string",dest="lang", help="Language binding") p.add_option("-P","--pass",action="store",type="string",dest="password", help="Database password") p.add_option("","--dump",action="store_true",dest="dump",default=False, help="dump datastructures for debugging") p.add_option("","--sys",action="store_true",dest="sys",default=False, help="connect as sysdba") p.add_option("","--helpfmt",action="store_true",dest="helpfmt", default=False, help="show help for formatting") (opts,args) = p.parse_args() if not (opts.conn and args): p.print_help() sys.exit(1) oraconnre = re.compile("^([^/@]*)(/([^@]*))?(@(.*))?$") user,password,host=oraconnre.match(opts.conn).groups()[0::2] if opts.password: password=opts.password if not password: password=getpass.getpass() connstr="%s/%s"%(user,password) if host: connstr+="@%s" % host if opts.sys: conn=cx_Oracle.connect(connstr,mode=cx_Oracle.SYSDBA) else: conn=cx_Oracle.connect(connstr) if opts.output: output=open(opts.output,"w") else: output=sys.stdout if opts.dump: trans=Trans(conn,output) trans.dump(args) sys.exit(0) if opts.lang is None or opts.lang=="python" or opts.lang=='py': trans=PyTrans(conn,output) elif opts.lang=='cxx': trans=None elif opt.lang=='rb': trans=None else: print >>sys.stderr,'unsupported language:',opt.lang sys.exit(1) trans.dofile(args) sys.exit(0)
def _connect(self,host=None,user=None,password=None,database=None): if user == None and password == None and database == None: self._conn = cx_Oracle.connect('','',host) else: if host == None: self._conn = cx_Oracle.connect(user,password) else: self._conn = cx_Oracle.connect(user,password,host) self._query("ALTER SESSION SET NLS_DATE_FORMAT='"+self.NLS_DATE_FORMAT+"'")
def __try_to_connect__(self,args): ''' ''' import cx_Oracle try: cx_Oracle.connect("{0}/{1}@{2}:{3}/{4}".format(self.args['user'],self.args['password'],self.args['server'],self.args['port'],self.args['sid'])) except Exception, e: pass
def demo(conn,curs): # normal user connect -- connstr looks like scott/tiger@orcl conn = cx_Oracle.connect(connstr) # normal user connect, three parms conn = cx_Oracle.connect('scott', 'tiger', 'orcl') # connect to the database as sysdba conn = cx_Oracle.connect('sys/secret@mydb', mode=cx_Oracle.SYSDBA)
def getCursor(): if DBUtil.con is None: if ('--local' in sys.argv): # SSH port forwarding is done, use localhost DBUtil.con = oracle.connect("aaron/ppppppp@localhost/orcl") else: # We are running on UF domain, connect to oracle.ci... DBUtil.con = oracle.connect("aaron/[email protected]/orcl") return DBUtil.con.cursor()
def get_db(self): """return cx_Oracle Connection() instance """ if self.oracle_db is None: if self.mode: self.oracle_db = cx_Oracle.connect(self.user, self.password, self.dsn, self.mode) else: self.oracle_db = cx_Oracle.connect(self.user, self.password, self.dsn) return self.oracle_db
def __init__(self) : # Connect to the Database hostname = socket.gethostname() if hostname.find('lxplus') > -1: self.dsn_ = 'cms_omds_adg' #offline else: self.dsn_ = 'cms_omds_lb' #online orcl = cx_Oracle.connect(user='******',password='******',dsn=self.dsn_) orcl = cx_Oracle.connect(user='******',password='******',dsn=self.dsn_) # Create a DB cursor self.curs = orcl.cursor()
def __try_to_connect__(self, user): ''' Establish a connection to the database ''' import cx_Oracle try: connectString = "{0}/{1}@{2}:{3}/{4}".format(user, 'aaaaaaa', self.args['server'], self.args['port'], self.args['sid']) logging.debug("Connecting with {0}".format(connectString)) cx_Oracle.connect(connectString) except Exception, e: pass
def cursor(self): from django.conf import settings if not self._valid_connection(): if len(settings.DATABASE_HOST.strip()) == 0: settings.DATABASE_HOST = 'localhost' if len(settings.DATABASE_PORT.strip()) != 0: dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME) self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options) else: conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME) self.connection = Database.connect(conn_string, **self.options) return FormatStylePlaceholderCursor(self.connection)
def __try_to_connect__(self, args): """ """ import cx_Oracle try: cx_Oracle.connect( "{0}/{1}@{2}:{3}/{4}".format( self.args["user"], self.args["password"], self.args["server"], self.args["port"], self.args["sid"] ) ) except Exception, e: pass
# @Time : 2018/11/16 14:41 # @Author : lg # @File : initConstructData.py import time import cx_Oracle import pandas as pd import pandas.io.sql as psql from AllSql_2014 import table_list_2014 from AllSql_2014_big import table_list_2014_big from AllSql_2015 import table_list_2015 from AllSql_all import table_list_all conn = cx_Oracle.connect('tax/[email protected]:1521/tax') print(conn.version) all_table_prefix = "2014_big_" # 数据获取 def get_feature(key, conn): lowKey = key.lower() # 全局变量table_list是dict, key是当前key sentence = table_list_2014_big[key] for fname in [all_table_prefix]: sqlline = sentence starttime = time.time() s1 = time.strftime("%Y-%m-%d-%H-%M-%S") print("current table is %s, time: %s" % (key, s1))
def setUp(self): global cx_Oracle, TestEnv self.connection = cx_Oracle.connect(TestEnv.USERNAME, TestEnv.PASSWORD, TestEnv.TNSENTRY) self.cursor = self.connection.cursor() self.cursor.arraysize = TestEnv.ARRAY_SIZE
import os import cx_Oracle import matplotlib.pyplot as plt os.chdir("C:\\instantclient_18_5") # Connection string connection = cx_Oracle.connect("hr", "inf5180", "144.217.163.57/XE") sql = "SELECT job_title, avg(SALARY) " \ "FROM EMPLOYEES E JOIN JOBS J " \ "ON E.JOB_ID = J.JOB_ID group by JOB_TITLE" curs = connection.cursor() curs.execute(sql) departments = [] name = [] for row in curs: departments.append(row[0]) name.append(row[1]) curs.close() connection.close() plt.bar(departments, name, color="red") plt.xticks(departments, rotation="vertical") plt.xlabel("Job Title") plt.ylabel("Average Salary") plt.title("Average Salary per Job Title")
# -*- encoding:utf8 -*- # coding:utf8 import os import cx_Oracle import pandas as pd import numpy as np import datetime from dateutil.relativedelta import relativedelta [userName, password, hostIP, dbName, tablePrefix] = ['reader', 'reader', '172.16.50.232:1521', 'dfcf', 'wind'] fund_db = cx_Oracle.connect(user=userName, password=password, dsn=hostIP + '/' + dbName) # 投研平台库 [userNamepif, passwordpif, hostIPpif, dbNamepif] = ['pra_info', 'pra_info', '172.16.126.23:1521', 'pra'] # [userNamepif, passwordpif, hostIPpif, dbNamepif] = ['pif', 'pif', '172.16.125.151', 'pif'] try: fund_dbpra = cx_Oracle.connect(user=userNamepif, password=passwordpif, dsn=hostIPpif + '/' + dbNamepif) cu_pra = fund_dbpra.cursor() except cx_Oracle.DatabaseError as e: print('数据库链接失败') path = os.path.dirname(os.path.realpath(__file__)) + '/'
def main(): con = cx_Oracle.connect('om', 'om', 'xe') cur = con.cursor() quit = False while quit == False: option = raw_input("== YELP ACADEMIC DATASET ANALYTICS TOOL ==\n\ Choose one of the following options below (e.g. '3'):\n\ >>OPTION 1: Categorical breakdown by city of stars by review count\n\ >>OPTION 2: Categorical breakdown by city of reviews by review count\n\ >>OPTION 3: Average rating by franchise\n\ >>OPTION 4: Average rating across all users\n\ >>OPTION 5: Categorical count distribution by average stars for all users\n\ >>OPTION 6: Average stars by year for a particular user\n\ >>QUIT: Quit application\n\n>> ") if option == "1": input_city = raw_input("Enter city name: ") input_city = input_city.rstrip('\n') star_distribution = cur.execute( "SELECT COUNT(*) AS STAR_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' GROUP BY STARS ORDER BY STARS") star_distribution = list(star_distribution) star_1 = star_distribution[0][0] star_15 = star_distribution[1][0] star_2 = star_distribution[2][0] star_25 = star_distribution[3][0] star_3 = star_distribution[4][0] star_35 = star_distribution[5][0] star_4 = star_distribution[6][0] star_45 = star_distribution[7][0] star_5 = star_distribution[8][0] star_list = [ '1 ', '1.5', '2 ', '2.5', '3 ', '3.5', '4 ', '4.5', '5 ' ] avg_stars = cur.execute( "SELECT COUNT(*) AS CITY_REVIEW_COUNT FROM BUSINESS WHERE CITY = \'" + input_city + "\'") avg_stars = list(avg_stars) avg_stars = avg_stars[0][0] avg_stars = float(avg_stars) print("Rating | Total Reviews in Range | Percentile of Total") for i in range(len(star_distribution)): star = star_distribution[i][0] print(" %s %d %.2f" % (star_list[i], star, (float(star) / avg_stars) * 100)) break_loop = raw_input("Press any key to continue.....") if option == "2": input_city = raw_input("Enter city name: ") input_city = input_city.rstrip('\n') rev_count_100 = cur.execute( "SELECT \'100\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT < 101 GROUP BY \'100\'") rev_count_100 = list(rev_count_100) rev_count_100 = rev_count_100[0][1] rev_count_200 = cur.execute( "SELECT \'200\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 101 AND 200 GROUP BY \'200\'") rev_count_200 = list(rev_count_200) rev_count_200 = rev_count_200[0][1] rev_count_300 = cur.execute( "SELECT \'300\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 201 AND 300 GROUP BY \'300\'") rev_count_300 = list(rev_count_300) rev_count_300 = rev_count_300[0][1] rev_count_400 = cur.execute( "SELECT \'400\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 301 AND 400 GROUP BY \'400\'") rev_count_400 = list(rev_count_400) rev_count_400 = rev_count_400[0][1] rev_count_500 = cur.execute( "SELECT \'500\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 401 AND 500 GROUP BY \'500\'") rev_count_500 = list(rev_count_500) rev_count_500 = rev_count_500[0][1] rev_count_600 = cur.execute( "SELECT \'600\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 501 AND 600 GROUP BY \'600\'") rev_count_600 = list(rev_count_600) rev_count_600 = rev_count_600[0][1] rev_count_700 = cur.execute( "SELECT \'700\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 601 AND 700 GROUP BY \'700\'") rev_count_700 = list(rev_count_700) rev_count_700 = rev_count_700[0][1] rev_count_800 = cur.execute( "SELECT \'800\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 701 AND 800 GROUP BY \'800\'") rev_count_800 = list(rev_count_800) rev_count_800 = rev_count_800[0][1] rev_count_900 = cur.execute( "SELECT \'900\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 801 AND 900 GROUP BY \'900\'") rev_count_900 = list(rev_count_900) rev_count_900 = rev_count_900[0][1] rev_count_1000 = cur.execute( "SELECT \'1000\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'" + input_city + "\' and REVIEW_COUNT BETWEEN 901 AND 1000 GROUP BY \'1000\'") rev_count_1000 = list(rev_count_1000) rev_count_1000 = rev_count_1000[0][1] review_print_list = [ "0-100", "100-200", "200-300", "300-400", "400-500", "500-600", "600-700", "800-900", "900-1000" ] review_count_list = [ rev_count_100, rev_count_200, rev_count_300, rev_count_400, rev_count_500, rev_count_600, rev_count_700, rev_count_800, rev_count_900, rev_count_1000 ] rev_count_avg = cur.execute( "SELECT COUNT(*) FROM BUSINESS WHERE CITY = \'" + input_city + "\'") rev_count_avg = list(rev_count_avg) rev_count_avg = rev_count_avg[0][0] rev_count_avg = float(rev_count_avg) #OUTPUT print("Range | Number of Businesses | Percent of Total") for i in range(len(review_count_list) - 1): print(" %s %s %.2f" % (review_print_list[i], review_count_list[i], (float(review_count_list[i]) / rev_count_avg) * 100)) break_loop = raw_input("Press any key to continue.....") if option == "3": franchise = raw_input("Enter franchise name: ") franchise = franchise.rstrip('\n') franch_avg_stars = cur.execute( "SELECT AVG(STARS), COUNT(*) FROM BUSINESS WHERE NAME_DESC Like \'%" + franchise + "%\' ") franch_avg_stars = list(franch_avg_stars) franch_stars, franch_count = franch_avg_stars[0] print("Average Rating for %s: %f" % (franchise, franch_stars)) print("Total Locations of %s Sampled: %d" % (franchise, franch_count)) break_loop = raw_input("Press any key to continue.....") if option == "4": avg_all_users = cur.execute("SELECT AVG(AVERAGE_STARS) FROM USERS") avg_all_users = list(avg_all_users) avg_all_users = avg_all_users[0][0] print("Average rating across all users: %s" % avg_all_users) break_loop = raw_input("Press any key to continue.....") if option == "5": avg_star_2 = cur.execute( "SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS < 2") avg_star_2 = list(avg_star_2) avg_star_2 = avg_star_2[0][0] avg_star_3 = cur.execute( "SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS BETWEEN 2 AND 3" ) avg_star_3 = list(avg_star_3) avg_star_3 = avg_star_3[0][0] avg_star_4 = cur.execute( "SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS BETWEEN 3 AND 4" ) avg_star_4 = list(avg_star_4) avg_star_4 = avg_star_4[0][0] avg_star_5 = cur.execute( "SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS BETWEEN 4 AND 5" ) avg_star_5 = list(avg_star_5) avg_star_5 = avg_star_5[0][0] #OUTPUT print("Rating Range | Average Review Count") print(" < 2 %f" % (avg_star_2)) print(" 2 - 3 %f" % (avg_star_3)) print(" 3 - 4 %f" % (avg_star_4)) print(" 4 - 5 %f" % (avg_star_5)) break_loop = raw_input("Press any key to continue.....") if option == "6": #user_id for demo: kGgAARL2UmvCcTRfiscjug user_id = raw_input("Enter user_id: ") user_id = user_id.rstrip('\n') user_by_year = cur.execute( "SELECT AVG(STARS), to_char(REVIEW_DATE, \'YYYY\') AS REVIEW_YEAR FROM REVIEWS WHERE USER_ID = \'" + user_id + "\' GROUP BY to_char(REVIEW_DATE, \'YYYY\') ORDER BY REVIEW_YEAR" ) user_by_year = list(user_by_year) print("") print("REVIEW YEAR | AVERAGE RATING") print("") for i in range(len(user_by_year)): user_avg_star, review_year = user_by_year[i] print(" %s %s" % (review_year, user_avg_star)) break_loop = raw_input("Press any key to continue.....") if option == "quit" or option == "QUIT" or option == "Quit": print("Quitting application") cur.close() quit = True
import tushare as ts import time import cx_Oracle conn = cx_Oracle.connect('stock/123456@localhost:1521/orcl') def query(conn): """ 查询数据库获取所有股票code :return: """ cursor = conn.cursor() sql = "select code from STOCK_BASICS" rs = cursor.execute(sql) result = rs.fetchall() tables = [i[0] for i in result] print(tables, type(tables)) return tables def get_data(code, start=None, end=None, index=False): """ 获取原始数据 :param code: :param start: :param end: :param index: :return: """ end = time.strftime('%Y-%m-%d', time.localtime(time.time()))
mailcontent = [] #这里打开oracle_list文件并读取其中内容 file1 = open(r"/home/oracle/script/oracle_list.txt") oracle_list = file1.readlines() file1.close() #循环读取每行的内容并使用split函数获取到相关数据库信息 for i in oracle_list: info = i.split() ipaddress = info[0].strip() username = info[1].strip() password = info[2].strip() port = info[3].strip() tnsname = info[4].strip() try: db = cx_Oracle.connect(username + '/' + password + '@' + ipaddress + ':' + port + '/' + tnsname, mode=cx_Oracle.SYSDBA) except Exception as e: content = (tnsname + ' is Unreachable,The reason is ' + str(e)).strip() mailcontent.append(content) else: cursor = db.cursor() data = oraclesql(cursor) cursor.close() db.close() for i in data: usage = int(i[4]) if usage >= 90: tablespace = i[0] mailcontent.append('Be Careful tablespace ' + tablespace +
def getoracleconntns(tns): conn = cx_Oracle.connect('baiqiao/ali88@%s'%tns ,mode=cx_Oracle.SYSDBA ) #conn = cx_Oracle.connect('/', mode=cx_Oracle.SYSDBA) return conn
def getoracleconntnspa(tns): conn = cx_Oracle.connect('baiqiao/ali88@%s'%tns ,mode = cx_Oracle.SYSDBA | cx_Oracle.PRELIM_AUTH) #conn = cx_Oracle.connect('/', mode=cx_Oracle.SYSDBA) return conn
def __init__(self): self.__db = cx_Oracle.connect(user_name, password, server) self.__cursor = self.__db.cursor()
''' Created on 2018. 5. 19. @author: Administrator ''' import cx_Oracle from pandas import Series import matplotlib.pyplot as plt id = "oraman/oracle@localhost:1521/xe" con = cx_Oracle.connect(id) cur = con.cursor() sql = "select * from BUNGITABLE" cur.execute(sql) Bungi = [] Count = [] total = 0 for pars in cur: print(pars) total += pars[1] Bungi.append(pars[0]) Count.append(pars[1]) RealSeries = [] for parsent in range(len(Count)): RealSeries.append(Bungi[parsent] + "\n" + str(round(100*(Count[parsent]/total),2))) print(RealSeries)
def analyze_runs(runs, partitions, end_delay, rerun=False): # First make connection to database conn_str = u'cms_trk_r/1A3C5E7G:FIN@cms_omds_lb' #os.system('getConfDb CONFDB_ONLINE') #conn_str = os.getenv('CONFDB') conn = cx_Oracle.connect(conn_str) c = conn.cursor() # Loop through list of runs for run in runs: run = int(run[3:]) hasEDM = False hasComm = False dbValid = False timeValid = False print '\n***************************************************' print 'Starting run', run # first get run information c.execute( u'select PARTITIONNAME,RUNMODE,STARTTIME,ENDTIME from RUN a join PARTITION b on a.PARTITIONID=b.PARTITIONID where RUNNUMBER=:run', {'run': run}) db_results = c.fetchall() print db_results if len(db_results): (partition, runmode, start, end) = db_results[0] else: # run number not in database print 'Run number', run, 'not in RUN database, aborting run' continue # For spy channel, make sure that you check that you retreive all partitions that were used in run if runmode == 15: partition = [result[0] for result in db_results] else: partition = [partition] # Make sure that there is an end time in the db if not end: print 'Run has not yet finished' continue # Now check that the run finished at least N hours ago # Then make sure all time info in DB makes sense if (datetime.datetime.now() - end) > end_delay: timeValid = validate_time(start, end) else: print 'Run recently finished, skipping and will check again later' continue # Check if run is of the type that we want to run modes_to_analyze = [2, 4, 5, 6, 7, 11, 14, 15, 16, 17, 21, 27] # If running certain partitions on different machines, then add CALIBRATION=3,CALIBRATION_DECO=33,CALIBRATION_SCAN=19,CALIBRATION_SCAN_DECO=20 modes #modes_to_analyze += [3, 19, 20, 33] if runmode not in modes_to_analyze: continue # Check if this partition run on machine (only matters if spltting jobs onto srv 29-01 and 30-01) # Exception is spy run which only gets run on srv-s2b17-XX-01 if any(part in partition[0] for part in partitions) and runmode != 15: pass elif runmode == 15 and partitions[0] == 'TI': pass else: continue # See if a run is currently being analyzed, by looking for the lock file created in analsis script # If analysis is running, skip run and verify integrity of outputs in later loop if any([ os.path.exists('/opt/cmssw/Data/lock_{part}_{run}'.format( part=part, run=run)) for part in partition ]): continue # For runs that are stopped and started multiple times, they will produce extra 0 event runs # For those runs, just ignore them and add the run number to list of good runs if len(glob.glob('/raid/fff/run{run}/*'.format(run=run))) == 4: # will have fu.lock, hlt, jsd, and run{run}_ls0000_EoR.jsn print 'This was an empty run' write_to_file(run, True) continue # first check if EDM file exists for run if os.path.exists('{rundir}/run{run}/run{run}.root'.format( rundir=rundir, run=run)): print 'run was analyzed' # If EDM file exists, check that analysis finished properly hasEDM = validate_events(run) (hasComm, dbValid) = validate_db(run, conn) hasRaw = len( glob.glob('{rundir}/run{run}/*.raw'.format(rundir=rundir, run=run))) > 0 if not hasEDM and hasRaw: print 'Problem in existing EDM file and raw files still exist.\nWill delete EDM file and re-run analysis' #os.system('ls {rundir}/run{run}/run{run}.root'.format(rundir=rundir, run=run)) os.system('rm {rundir}/run{run}/run{run}.root'.format( rundir=rundir, run=run)) elif not hasEDM and not hasRaw: print '***warning*** Problem validating EDM file but no raw files exist...Will continue with existing file, but problems may ensue' elif hasEDM and hasRaw: # EDM file is good and raw still exists, delete the no longer needed raw files to save space print 'Run has already been analyzed, but raw files still exist. Deleting raw files' delete_raw(run) # For spy runs, still need to check that analysis was run on each partition if not (hasEDM and dbValid) or runmode == 15: # Pack the raw files together and into EDM format and analyze based on run analysis print 'Will now perform analysis on run' if len(partition) == 1: os.system( 'sh /opt/cmssw/scripts/run_analysis_CC7.sh {run} False True {partition} False False True True' .format(run=run, partition=partition[0])) (hasComm, dbValid) = validate_db(run, conn) else: # for spy runs need to loop over all partitions dbValid_part = [] for part in partition: # Check if we have already succesfully analyzed this partition if all(validate_db(run, conn, part)): dbValid_part.append((True, True)) else: os.system( 'sh /opt/cmssw/scripts/run_analysis_CC7.sh {run} False True {partition} False False True True' .format(run=run, partition=part)) dbValid_part.append(validate_db(run, conn, part)) # Make sure check returns true for all partitions hasComm = all([valid[0] for valid in dbValid_part]) dbValid = all([valid[1] for valid in dbValid_part]) hasEDM = validate_events(run) if hasEDM: delete_raw(run) # Finally, make sure all validation checks return True print '**** Analysis Validation Checks ****' print 'Run start and end time are consistent:', timeValid print 'EDM File Good:', hasEDM print 'Commissioning and source files exist:', hasComm print 'Analysis added to DB:', dbValid print '**** Result ****' if timeValid and hasEDM and dbValid: print 'Run %d is good\n' % run write_to_file(run, True) elif not rerun: # If any check fails, try running it again print 'One or more check failed, will re-run over run\n', run analyze_runs(['run%d' % run], partitions, end_delay, rerun=True) else: # If run fails second time, add to list of failed runs and continue print 'Analysis failed a second time, adding run to fail list\n' write_to_file(run, False, [timeValid, hasEDM, hasComm, dbValid]) return return
def get_connection(): conn = cx_Oracle.connect('scott/cf6024584@localhost/orcl') # 用户名/密码@服务器地址/数据库名称 return conn
import tkinter as tk from tkinter import * import cx_Oracle import time T = time.strftime("%H:%M:%S%p") con = cx_Oracle.connect("system/vasumalik1") cur = con.cursor() win = tk.Tk() win.geometry("963x649+240+50") win.title("HOTEL MANGMENT") font14 = "-family {Courier New} -size 15 -weight normal -slant " \ "roman -underline 0 -overstrike 0" font10 = "-family {Courier New} -size 10 -weight normal -slant" \ " roman -underline 0 -overstrike 0" font11 = "-family {ENGRAVERS MT} -size 15 -weight bold -slant " \ "roman -underline 0 -overstrike 0" font9 = "-family {Segoe UI} -size 9 -weight normal -slant " \ "roman -underline 0 -overstrike 0" Frame1 = Frame(win) Frame1.place(relx=0.01, rely=0.05, relheight=0.12, relwidth=0.97) Frame1.configure(relief=GROOVE) Frame1.configure(borderwidth="2") Frame1.configure(relief=GROOVE) #self.Frame1.configure(background="#ffffff") Frame1.configure(highlightbackground="#ffffff") Frame1.configure(highlightcolor="black") Frame1.configure(width=995)
code2uri_sider = self.merge_dicts(code2uri, uri2uri) # print u'J05AB01', code2uri_sider[u'J05AB01'] code2ingredients = self.merge_dicts(code2uri_sider, uri2ingredient) # print u'J05AB01', code2uri_sider[u'J05AB01'] return code2ingredients if __name__ == '__main__': # in terminal: python integrate.py ../Desktop/indication.csv ../Desktop/effects.csv ../Desktop/ingredients.csv # ... or import from SQL (uncomment) import cx_Oracle rows = cx_Oracle.connect('datamart', 'datamart', '10.67.201.10:1521/XE').cursor() rows.execute('''SELECT med.atc_code FROM AA_KOP_patient_dates dts LEFT JOIN medicaties med ON dts.patientnummer = med.patientnummer''') idx = 0 # we only query atc codes # enrich indications, effects, ingredients = atc_enrichment(rows, idx) # export io.dict2csv(indications, sys.argv[1]) io.dict2csv(effects, sys.argv[2]) io.dict2csv(ingredients, sys.argv[3])
import cx_Oracle import csv import os os.environ["NLS_LANG"] = ".UTF8" os.environ[ "NLS_LANG"] = "Russian.AL32UTF8" #"American_America.AL32UTF8" ".UTF8" dsnStr = cx_Oracle.makedsn("s34.birt.localka", "1231", "bird") conn = cx_Oracle.connect(user="******", password="******", dsn=dsnStr, encoding='utf-8') curs = conn.cursor() printHeader = True outputFile = open('cohort_analytics.csv', 'w') # 'wb' output = csv.writer(outputFile, delimiter='|', lineterminator='\n') sql = '''with store as (SELECT distinct storeid,store FROM db.store_base), cohorts as (SELECT min(TO_CHAR(dato,'YYYY-MM')) as mindate,carnumb as users FROM db.sales WHERE dato >= TO_DATE('2011-01-01','YYYY-MM-DD') AND dato < TO_DATE('2019-04-01','YYYY-MM-DD') GROUP BY carnumb) SELECT TO_CHAR(dato,'YYYY-MM') as Period,store, mindate as Cohort, COUNT(distinct carnumb) as Quant_clnt FROM db.ret INNER JOIN store ON store.storeid=ret.storeid INNER JOIN cohorts ON cohorts.users=ret.carnumb WHERE dato >= TO_DATE('2011-01-01','YYYY-MM-DD') AND dato < TO_DATE('2019-04-01','YYYY-MM-DD') AND b2b_flag=0 GROUP BY TO_CHAR(dato,'YYYY-MM'),store, mindate '''
# Code Sample from the tutorial at https://learncodeshare.net/2015/06/02/select-crud-using-cx_oracle/ # section titled "Extra Fun 1" # Using the base template, the example code executes a simple query, uses fetchall to retrieve the data # and displays the results. import cx_Oracle import os connectString = os.getenv( 'DB_CONNECT' ) # The environment variable for the connect string: DB_CONNECT=user/password@database con = cx_Oracle.connect(connectString) cur = con.cursor() statement = 'select id, name, age, notes from lcs_people order by age' cur.execute(statement) res = cur.fetchall() print(res)
#------------------------------------------------------------------------------ # connect_drcp.py (Section 2.3 and 2.5) #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # Copyright 2017, 2018, Oracle and/or its affiliates. All rights reserved. #------------------------------------------------------------------------------ from __future__ import print_function import cx_Oracle import db_config con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn + ":pooled", cclass="PYTHONHOL", purity=cx_Oracle.ATTR_PURITY_SELF) print("Database version:", con.version)
import cgi import cx_Oracle as Oracle import json form = cgi.FieldStorage() #do form["key"] or form.getvalue("key","default value"). second is better #can also do form.getlist #connection, cursor = None,None cursor = None out = {"success": False} try: #connection = Oracle.connect("cbowley","secrets","oracle1.aero.und.edu:1521/cs513.aero.und.edu") with Oracle.connect( "cbowley", "secrets", "oracle1.aero.und.edu:1521/cs513.aero.und.edu") as connection: cursor = connection.cursor() param = {"name": form.getvalue("name", "")} if (param["name"] != ""): check_query = "SELECT id FROM customers WHERE name = :name" cursor.execute(check_query, param) result = cursor.fetchone() cid = int(result[0]) if result else -1 if (cid != -1): #already exists out["Id"] = cid out["success"] = True else: # need to add insert_query = "INSERT INTO customers(name,account) VALUES (:name,purchased_game_table())" cursor.execute(insert_query, param) cursor.execute(check_query, param)
import cx_Oracle con = cx_Oracle.connect('system','******','localhost:1522/XE') cur = con.cursor() cur.execute('drop table Account') cur.execute("""create table Account( CustomerId number(10) primary key, AccountNo varchar2(15) not null, AccountType varchar2(15) check(AccountType in ('Savings','Current','Recurring')), Balance number(12) )""") cur.executemany("insert into Account values(:1,:2,:3,:4)", [(101,'IBI1001','Savings',0),(102,'IBI1002','Current',1200), (103,'IBI1003','Savings',6543),(104,'IBI1004','Recurring',7500), (105,'IBI1005','Current',0)]) cur.execute('''select customerid, balance from Account where balance=(select max(balance) from account)''') print(cur.description) print(cur.fetchall()) cur.execute('select balance from Account where CustomerId=102') acct_bal = int(cur.fetchall()[0][0]) print(acct_bal) acct_bal+=2000 cur.execute('update Account set balance=:1 where CustomerId=102',[(acct_bal)]) cur.execute('select balance from Account where CustomerId=102')
def getX(start_date, end_date): dataX = [] column_list = [] # DBMS 활용 try: conn = mysql.connect("seungsu", "tmdtn12", "orcl") cur = conn.cursor() # #특정속성(upload_date등) 제외하고 워드 속성만 가져오기 # # Oracle1 - 속성명 받아와 UPLOAD_DATE 없애기 # sql_select_column= "select COLUMN_NAME from ALL_TAB_COLUMNS where table_name='INPUT1'" # #cur.execute(sql_select_column) # for column in cur.fetchall(): # column_list.append(column[0]) # column_list.remove("UPLOAD_DATE") # #print(len(column_list)) # # columns = "" # str_conn = ", " # for i in range(0, len(column_list)): # if i < len(column_list): # columns += column_list[i] + str_conn # else: # columns += column[i-1] # # print(columns) # # print(type(columns)) # # sql_select_data= "select " + str(column)[2:-3] + " from INPUT1 " \ # "where UPLOAD_DATE between to_date('" + start_date + "', 'YYYYMMDDHH24MISS') " \ # "and to_date('" + end_date + "', 'YYYYMMDDHH24MISS') " \ # "order by UPLOAD_DATE asc" # print(sql_select_data) # cur.execute(sql_select_data) # print(cur.fetchall()) sql_select_tables = "select * from input1 " \ "where UPLOAD_DATE between to_date('" + start_date + "', 'YYYYMMDDHH24MISS') " \ "and to_date('" + end_date + "', 'YYYYMMDDHH24MISS') " \ "order by UPLOAD_DATE asc" cur.execute(sql_select_tables) # Oracle2 전체 다 꺼내서 맨 앞 column만 제거(첫번째 column : upload_date) for _data in cur.fetchall(): data = _data[1:] print(data) dataX.append(data) # print(dataX) # for MySQL # sql_set = "set @qry = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), " \ # "'<upload_date>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " \ # "'<대상 테이블명>'AND TABLE_SCHEMA = '<seungu>'), ' FROM <input0> " \ # "where pcode ='" + company + "' " \ # "and st_date between to_date('" + start_date + "', 'YYYYMMDDHH24MISS') " + \ # "and to_date('" + end_date + "', 'YYYYMMDDHH24MISS') " \ # "order by st_date asc')" # print(sql_set) # cur.execute(sql_set) # print(cur.fetchall()) # sql_check = "SELECT @qry;" # cur.execute(sql_check) # print(cur.fetchone()) # # sql_prepare = "PREPARE result FROM @qry" # cur.execute(sql_prepare) # # sql_execute = "EXECUTE result" # cur.execute(sql_execute) # print(cur.fetchall()) # # # record_list = cur.fetchall() # # #words 속성들로 행렬만들기 # for i in range(0, len(record_list)/data_dim): # _x = record_list[i*data_dim : (i+1)*data_dim] # dataX.append(_x) except mysql.DatabaseError as e: print('getX Error : ', e) finally: cur.close() conn.close() return dataX
def get_conn(self) -> 'OracleHook': """ Returns a oracle connection object Optional parameters for using a custom DSN connection (instead of using a server alias from tnsnames.ora) The dsn (data source name) is the TNS entry (from the Oracle names server or tnsnames.ora file) or is a string like the one returned from makedsn(). :param dsn: the data source name for the Oracle server :param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS) :param sid: Oracle System ID that identifies a particular database on a system You can set these parameters in the extra fields of your connection as in .. code-block:: python { "dsn": ( "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" "(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=sid)))" ) } see more param detail in `cx_Oracle.connect <https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.connect>`_ """ conn = self.get_connection( self.oracle_conn_id) # type: ignore[attr-defined] conn_config = {'user': conn.login, 'password': conn.password} sid = conn.extra_dejson.get('sid') mod = conn.extra_dejson.get('module') schema = conn.schema service_name = conn.extra_dejson.get('service_name') port = conn.port if conn.port else 1521 if conn.host and sid and not service_name: conn_config['dsn'] = cx_Oracle.makedsn(conn.host, port, sid) elif conn.host and service_name and not sid: conn_config['dsn'] = cx_Oracle.makedsn(conn.host, port, service_name=service_name) else: dsn = conn.extra_dejson.get('dsn') if dsn is None: dsn = conn.host if conn.port is not None: dsn += ":" + str(conn.port) if service_name: dsn += "/" + service_name elif conn.schema: warnings.warn( """Using conn.schema to pass the Oracle Service Name is deprecated. Please use conn.extra.service_name instead.""", DeprecationWarning, stacklevel=2, ) dsn += "/" + conn.schema conn_config['dsn'] = dsn if 'encoding' in conn.extra_dejson: conn_config['encoding'] = conn.extra_dejson.get('encoding') # if `encoding` is specific but `nencoding` is not # `nencoding` should use same values as `encoding` to set encoding, inspired by # https://github.com/oracle/python-cx_Oracle/issues/157#issuecomment-371877993 if 'nencoding' not in conn.extra_dejson: conn_config['nencoding'] = conn.extra_dejson.get('encoding') if 'nencoding' in conn.extra_dejson: conn_config['nencoding'] = conn.extra_dejson.get('nencoding') if 'threaded' in conn.extra_dejson: conn_config['threaded'] = conn.extra_dejson.get('threaded') if 'events' in conn.extra_dejson: conn_config['events'] = conn.extra_dejson.get('events') mode = conn.extra_dejson.get('mode', '').lower() if mode == 'sysdba': conn_config['mode'] = cx_Oracle.SYSDBA elif mode == 'sysasm': conn_config['mode'] = cx_Oracle.SYSASM elif mode == 'sysoper': conn_config['mode'] = cx_Oracle.SYSOPER elif mode == 'sysbkp': conn_config['mode'] = cx_Oracle.SYSBKP elif mode == 'sysdgd': conn_config['mode'] = cx_Oracle.SYSDGD elif mode == 'syskmt': conn_config['mode'] = cx_Oracle.SYSKMT elif mode == 'sysrac': conn_config['mode'] = cx_Oracle.SYSRAC purity = conn.extra_dejson.get('purity', '').lower() if purity == 'new': conn_config['purity'] = cx_Oracle.ATTR_PURITY_NEW elif purity == 'self': conn_config['purity'] = cx_Oracle.ATTR_PURITY_SELF elif purity == 'default': conn_config['purity'] = cx_Oracle.ATTR_PURITY_DEFAULT conn = cx_Oracle.connect(**conn_config) if mod is not None: conn.module = mod # if Connection.schema is defined, set schema after connecting successfully # cannot be part of conn_config # https://cx-oracle.readthedocs.io/en/latest/api_manual/connection.html?highlight=schema#Connection.current_schema # Only set schema when not using conn.schema as Service Name if schema and service_name: conn.current_schema = schema return conn
import cx_Oracle con = cx_Oracle.connect('SYSTEM/dbms@localhost') print('ok') #1521
def leeEspecies(): connectionMaestros = cx_Oracle.connect("maestros", "maestros", "oracle.world", threaded=True) #sql = "select esp.descripcion, dcientifico,dingles,dfrances,ditaliano,dotros,fam.descripcion,grp.descripcion, par.descripcion from especies esp, familias fam, grupos grp, partidas_arancelarias par where familia_familia_id = familia_id and fam.grupo_grupo_id= grp.grupo_id and esp.p_arancela_p_arancela_id = par.p_arancela_id" #sql = "select familias.descripcion, grupos.descripcion from familias, grupos where grupo_grupo_id = grupo_id" #sql = "select descripcion from grupos" #sql = "select descripcion , codigo_estadistico from partidas_arancelarias" #sql = "select descripcion from tipos" #sql = "select descripcion from unidades_medidas " #sql = "select descripcion from canales" #sql = "select * from marcas" #sql = "select descripcion,f_master,peso_ud,peso_envase,agranel,volumen from envases" #sql = "select dim.descripcion,dim.maximo,dim.minimo,dim.piezas,uni.descripcion from dimensiones dim, unidades_medidas uni where u_medida_id= u_medida_u_medida_id" sql = "select dim.descripcion,esp.descripcion,tip.descripcion,codigo_barra,nvl(can.descripcion,'---') canal, env.descripcion, NVL(elaborado,'N'), nvl(mar.descripcion,'---') marcas, estado, fecha_baja, codarticulo , env.f_master from mercancias mer , dimensiones dim , especies esp , tipos tip , canales can, envases env ,marcas mar where mer.especie_especie_id = esp.especie_id and mer.tipo_tipo_id = tip.tipo_id and mer.marca_marca_id(+) = mar.marca_id and mer.envase_envase_id = env.envase_id and mer.dimension_dimension_id = dim.dimension_id" cursor = connectionMaestros.cursor() cursor.execute(sql) array = [] array = cursor.fetchall() # for partidas in array: # PartidasArancelarias.objects.create( descripcion= partidas[0],codigo_estadistico = partidas[1]) # for grupos in array: # Grupos.objects.create(descripcion=grupos[0]) # for descrip in array: # print "Descripcion %s " % descrip[0] # Familias.objects.create(descripcion=descrip[0],grupo_grupo=Grupos.objects.get(descripcion=descrip[1])) # # for especies in array: # uespecies = [] # i=0 # for x in especies: # if x is None: # uespecies.append(elimina_tildes(unicode(x))) # else: # print x # uespecies.append(elimina_tildes(unicode(x.decode('utf-8')))) # Especies.objects.create(descripcion=uespecies[0],dcientifico=uespecies[1],denglish=uespecies[2],dfrench=uespecies[3],ditalian=uespecies[4],dotros=uespecies[5],familia_familia=Familias.objects.get(descripcion=uespecies[6], grupo_grupo=Grupos.objects.get(descripcion=uespecies[7])), p_arancela_p_arancela = PartidasArancelarias.objects.filter(descripcion=uespecies[8])[0]) # for tipos in array: # Tipos.objects.create(descripcion=tipos[0]) # # for umedidas in array: # UnidadesMedidas.objects.create(descripcion=umedidas[0]) # for ucanales in array: # Canales.objects.create(descripcion = ucanales[0]) # # for umarcas in array: # Marcas.objects.create(descripcion = umarcas) # for tpenv in array: # if tpenv[0] is None: # cdesc="." # else: # cdesc=tpenv[0] # TiposEnvases.objects.create(descripcion=cdesc,f_master=tpenv[1],peso_ud=tpenv[2],peso_envase=tpenv[3],agranel=tpenv[4],volumen=tpenv[5]) # for dime in array: # Dimensiones.objects.create(descripcion=dime[0],maximo=dime[1],minimo=dime[2],piezas=dime[3],u_medida_u_medida = UnidadesMedidas.objects.get(descripcion=dime[4])) for prod in array: print prod try: espe = Especies.objects.get(descripcion=prod[1]) try: dime = Dimensiones.objects.get(descripcion=prod[0]) except ObjectDoesNotExist: pass tipos = Tipos.objects.get(descripcion=prod[2]) try: can = Canales.objects.get(descripcion=prod[4]) except ObjectDoesNotExist: pass try: enva = TiposEnvases.objects.get(descripcion=prod[5], f_master=prod[11]) except ObjectDoesNotExist: pass try: marc = Marcas.objects.get(descripcion=prod[7]) except ObjectDoesNotExist: pass Productos.objects.create(dimension=dime, especie=espe, tipo=tipos, codigo_barra=prod[3], canal=can, envase=enva, elaborado=prod[6], marca=marc, estado=prod[8], codarticulo=prod[10]) except ObjectDoesNotExist: pass
import csv import cx_Oracle import numpy as np import config user = config.ORACLE_DATABASE_CONFIG['user'] password = config.ORACLE_DATABASE_CONFIG['password'] host = config.ORACLE_DATABASE_CONFIG['host'] port = str(config.ORACLE_DATABASE_CONFIG['port']) dbname = config.ORACLE_DATABASE_CONFIG['dbname'] con = cx_Oracle.connect(user + '/' + password + '@' + host + ':' + port + '/' + dbname) cur = con.cursor() cur.execute('select * from HR.employees') result_qery = np.asarray(cur.fetchall()) print(result_qery) with open('foo.csv', "w") as output: writer = csv.writer(output, lineterminator='\n') writer.writerows(result_qery)
def get_conn(self): """ Returns a oracle connection object Optional parameters for using a custom DSN connection (instead of using a server alias from tnsnames.ora) The dsn (data source name) is the TNS entry (from the Oracle names server or tnsnames.ora file) or is a string like the one returned from makedsn(). :param dsn: the host address for the Oracle server :param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS) You can set these parameters in the extra fields of your connection as in ``{ "dsn":"some.host.address" , "service_name":"some.service.name" }`` see more param detail in `cx_Oracle.connect <https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.connect>`_ """ conn = self.get_connection(self.oracle_conn_id) conn_config = {'user': conn.login, 'password': conn.password} dsn = conn.extra_dejson.get('dsn', None) sid = conn.extra_dejson.get('sid', None) mod = conn.extra_dejson.get('module', None) service_name = conn.extra_dejson.get('service_name', None) port = conn.port if conn.port else 1521 if dsn and sid and not service_name: conn_config['dsn'] = cx_Oracle.makedsn(dsn, port, sid) elif dsn and service_name and not sid: conn_config['dsn'] = cx_Oracle.makedsn(dsn, port, service_name=service_name) else: conn_config['dsn'] = conn.host if 'encoding' in conn.extra_dejson: conn_config['encoding'] = conn.extra_dejson.get('encoding') # if `encoding` is specific but `nencoding` is not # `nencoding` should use same values as `encoding` to set encoding, inspired by # https://github.com/oracle/python-cx_Oracle/issues/157#issuecomment-371877993 if 'nencoding' not in conn.extra_dejson: conn_config['nencoding'] = conn.extra_dejson.get('encoding') if 'nencoding' in conn.extra_dejson: conn_config['nencoding'] = conn.extra_dejson.get('nencoding') if 'threaded' in conn.extra_dejson: conn_config['threaded'] = conn.extra_dejson.get('threaded') if 'events' in conn.extra_dejson: conn_config['events'] = conn.extra_dejson.get('events') mode = conn.extra_dejson.get('mode', '').lower() if mode == 'sysdba': conn_config['mode'] = cx_Oracle.SYSDBA elif mode == 'sysasm': conn_config['mode'] = cx_Oracle.SYSASM elif mode == 'sysoper': conn_config['mode'] = cx_Oracle.SYSOPER elif mode == 'sysbkp': conn_config['mode'] = cx_Oracle.SYSBKP elif mode == 'sysdgd': conn_config['mode'] = cx_Oracle.SYSDGD elif mode == 'syskmt': conn_config['mode'] = cx_Oracle.SYSKMT elif mode == 'sysrac': conn_config['mode'] = cx_Oracle.SYSRAC purity = conn.extra_dejson.get('purity', '').lower() if purity == 'new': conn_config['purity'] = cx_Oracle.ATTR_PURITY_NEW elif purity == 'self': conn_config['purity'] = cx_Oracle.ATTR_PURITY_SELF elif purity == 'default': conn_config['purity'] = cx_Oracle.ATTR_PURITY_DEFAULT conn = cx_Oracle.connect(**conn_config) if mod is not None: conn.module = mod return conn
# 0. 패키지(모듈) 임포트 import cx_Oracle as oci # 1. 연결객체 얻어오기 conn = oci.connect('scott/tiger@localhost:1521/orcl') # 2. 커서객체 얻어오기 # cursor = conn.cursor() # 3. sql 문장 # sql = "SELECT * FROM emp" # 4. sql 문장 실행 # cursor.execute(sql) # datas = cursor.fetchall() # print(datas) # 5. 커서객체 닫기 # cursor.close() # 6. 커넥션 닫기 with oci.connect('scott/tiger@localhost:1521/orcl') as conn: with conn.cursor() as cursor: sql = "SELECT * FROM emp" cursor.execute(sql) datas = cursor.fetchall() for data in datas: print(data)
for row in res: r = row["user_story"] query = ( "SELECT distinct file_name FROM vdeploy.vDeploy_build where user_story='" + r + "'") file_prod = "C:\\Users\\amedishetti\\Desktop\\sp\\" + r + "_prod.txt" file_env = "C:\\Users\\amedishetti\\Desktop\\sp\\" + r + "_" + z + ".txt" file_output = "C:\\Users\\amedishetti\\Desktop\\sp\\outputfile.txt" f = open(file_output, "w") cursor.execute(query) results = cursor.fetchall() for row in results: s = row["file_name"] if (s[0:2] == "wf"): con = cx_Oracle.connect( 'INFAREP_PROD_READ/[email protected]:1521/INFPRD' ) cur = con.cursor() query1 = ( "SELECT * FROM (SELECT WF.WORKFLOW_NAME, COUNT(TSK.INSTANCE_NAME) AS TSK_COUNT FROM INFAREP_PROD.REP_WORKFLOWS WF INNER JOIN INFAREP_PROD.REP_TASK_INST TSK ON WF.WORKFLOW_ID=TSK.WORKFLOW_ID GROUP BY WF.WORKFLOW_NAME) S WHERE S.WORKFLOW_NAME='" + s[0:-4] + "'") cur.execute(query1) for result in cur: print(result, file=open(file_prod, "a")) query2 = ( "SELECT * FROM (SELECT WF.WORKFLOW_NAME, COUNT(TSK.INSTANCE_NAME) AS TSK_COUNT FROM INFAREP_" + z + ".REP_WORKFLOWS WF INNER JOIN INFAREP_" + z + ".REP_TASK_INST TSK ON WF.WORKFLOW_ID=TSK.WORKFLOW_ID GROUP BY WF.WORKFLOW_NAME) S WHERE S.WORKFLOW_NAME='" + s[0:-4] + "'") con = cx_Oracle.connect( 'infarep_dev15/[email protected]/infdev15'
import cx_Oracle as oracle db = oracle.connect('l3main/[email protected]:1521/l3dev') cursor = db.cursor cursor.execute('select column_name,tale_name from user_tab_column_name like '*'') data = cursor.fetchone print(data) cursor.close() de.close()
import cx_Oracle con = cx_Oracle.connect('system/[email protected]/orcl') cur = con.cursor() cur.execute('select * from pyth order by id') #for result in cur: # print result row = cur.fetchone() print row row = cur.fetchmany(numRows=1) print row res = cur.fetchall() for r in res: print r cur.close() con.close()