예제 #1
0
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
예제 #2
0
 def connect_to_database(self, user_name, password, host, port, sid_service, encoding="American_America.AL32UTF8"):
     environ["NLS_LANG"] = encoding
     if sid_service.split("=")[0] == "sid":
         dsn = cx_Oracle.makedsn(host,  port, sid_service.split("=")[1])
     else:
         dsn = cx_Oracle.makedsn(host,  port, service_name=sid_service.split("=")[1])
     self.connection = cx_Oracle.connect(user_name, password, dsn)
예제 #3
0
    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
예제 #4
0
파일: pyOracle.py 프로젝트: okcorral/python
def get_dsn_tns(database):
    if len(config.get(database,'sid'))==0:
        dsn_tns = cx_Oracle.makedsn(config.get(database,'host'), config.get(database,'port'), service_name=config.get(database,'service_name'))
    else:
        dsn_tns = cx_Oracle.makedsn(config.get(database,'host'), config.get(database,'port'), config.get(database,'sid'))

    return dsn_tns
예제 #5
0
파일: oradb.py 프로젝트: allenforrest/wxbiz
 def _makedsn(self, host, port, sid, service_name = 'ORCL'):
     """
     Method: _makedsn
     Description: 生成连接字符串DSN
     Parameter: 
         host: 数据库服务器名
         port: 数据库服务器端口
         sid: 数据库名
         service_name: 数据库服务名
     Return: 
         非None: Oracle数据库连接字符串
     Others: 
     """
     if host.find(',') == -1:
         versions = cx_Oracle.version.split('.')
         if tuple(int(x) for x in versions) >= (5, 1, 1):
             if service_name is not None:
                 return cx_Oracle.makedsn(host, port, service_name = service_name)
             return cx_Oracle.makedsn(host, port, sid)
         dsn = cx_Oracle.makedsn(host, port, sid)
         if service_name is not None:
             dsn.replace('SID', 'SERVICE_NAME')
     else:
         hosts = host.split(',')
         index = 0
         address = ''
         for host in hosts:
             address += '(ADDRESS=(PROTOCOL=TCP)(HOST=%s)(PORT=%s))' % (host, port)
         rac = '(LOAD_BALANCE=ON)(FAILOVER=ON)'
         if service_name is not None:
             dsn = '(DESCRIPTION=%s(ADDRESS_LIST=%s)(CONNECT_DATA=(SERVICE_NAME=%s)))' % (rac, address, service_name)
         else:
             dsn = '(DESCRIPTION=%s(ADDRESS_LIST=%s)(CONNECT_DATA=(SID=%s)))' % (rac, address, sid)
     return dsn
예제 #6
0
def db_connect(envir):
	
	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 connect_to_database(self, username, password, host, port, sid_service, encoding="American_America.AL32UTF8"):
     """
     Example usage:
     | Connect To Database | user1 | qwerty | localhost | 1521 | service=db_serv1 | Russian.AL32UTF8 |
     For set database sid: sid=db_sid_name.\n
     Encoding is optional parameter. By default is American_America.AL32UTF8.
     """
     os.environ['NLS_LANG'] = encoding
     if sid_service.split("=")[0] == "sid":
         dsn = cx_Oracle.makedsn(host, port, sid_service.split("=")[1])
     else:
         dsn = cx_Oracle.makedsn(host, port, service_name=sid_service.split("=")[1])
     self.connection = cx_Oracle.connect(username, password, dsn)
예제 #8
0
 def __enter__(self):
     if self.service:
         ora_dsn = cx_Oracle.makedsn('localhost',
                                     CONF.get(MANAGER).listener_port,
                                     service_name=self.sid)
     else:
         ora_dsn = cx_Oracle.makedsn('localhost',
                                     CONF.get(MANAGER).listener_port,
                                     self.sid)
     self.conn = cx_Oracle.connect(user=self.user_id,
                                   password=self.password,
                                   dsn=ora_dsn, mode=self.mode)
     return self.conn
def create_connection(module, user, password, host, port, sid=None, service=None, mode=None):
    if sid:
        dsn = cx_Oracle.makedsn(host=host, port=port, sid=sid)
    else:
        dsn = cx_Oracle.makedsn(host=host, port=port, service_name=service)

    try:
        if mode:
            conn = cx_Oracle.connect(user=user, password=password, dsn=dsn, mode=map_mode(mode))
        else:
            conn = cx_Oracle.connect(user=user, password=password, dsn=dsn)
        return conn
    except cx_Oracle.DatabaseError as e:
        module.fail_json(msg='{dsn}: {err}'.format(dsn=dsn, err=str(e)))
예제 #10
0
    def __getcon(dbtype="test"):
        _conf = DBConf.getInst()
        if Oracle._oracle_con_pool is None:
            if dbtype == "hbdt79":
                dsn_tns = cx_Oracle.makedsn(_conf.get_oracle('iphbdt79'), _conf.get_oracle('porthbdt79'), _conf.get_oracle("sidhbdt79"))
                Oracle._oracle_con_pool = cx_Oracle.connect(_conf.get_oracle('usernamehbdt79'), _conf.get_oracle('passwordhbdt79'), dsn_tns)
            elif dbtype == "hbgj90":
                dsn_tns = cx_Oracle.makedsn(_conf.get_oracle('iphbgj90'), _conf.get_oracle('porthbgj90'), _conf.get_oracle("sidhbgj90"))
                Oracle._oracle_con_pool = cx_Oracle.connect(_conf.get_oracle('usernamehbgj90'), _conf.get_oracle('passwordhbgj90'), dsn_tns)
            else:
                dsn_tns = cx_Oracle.makedsn(_conf.get_oracle('iptest'), _conf.get_oracle('porttest'), _conf.get_oracle("sidtest"))
                Oracle._oracle_con_pool = cx_Oracle.connect(_conf.get_oracle('usernametest'), _conf.get_oracle('passwordtest'), dsn_tns)

        return Oracle._oracle_con_pool
def oracle_connect(module,host,port,user,password,
                   sid=None,service=None,as_sysdba=False):
    if sid is not None:
        dsn = cx_Oracle.makedsn(host,port,sid)
    elif service is not None:
        dsn = cx_Oracle.makedsn(host,port,service_name=service)

    try:
        if as_sysdba:
            conn = cx_Oracle.connect(user,password,dsn,
                                     mode=cx_Oracle.SYSDBA)
        else:
            conn = cx_Oracle.connect(user,password,dsn)
    except Exception, e:
        module.fail_json(msg='Cannot connect to database (err: %s)' % (str(e)))
예제 #12
0
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
예제 #13
0
def oraclelogin(target,user,password,database,port):
    print "[+] Trying Default User and Password (%s----->%s)" % (user,password)
    try:
        conn = cx_Oracle.connect(user,password,cx_Oracle.makedsn(target,port,database))
        conn.close()
	return (True,user,password)
    except DatabaseError:
        try:
            conn = cx_Oracle.connect(user,password,cx_Oracle.makedsn(target,port,database),cx_Oracle.SYSDBA)
            conn.close()
            return (True,user,password)
        except:
            return (False,user,password)
    except:
        return (False,user,password)
예제 #14
0
 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
예제 #15
0
파일: base.py 프로젝트: GeyseR/django
 def _dsn(self):
     settings_dict = self.settings_dict
     if not settings_dict['HOST'].strip():
         settings_dict['HOST'] = 'localhost'
     if settings_dict['PORT']:
         return Database.makedsn(settings_dict['HOST'], int(settings_dict['PORT']), settings_dict['NAME'])
     return settings_dict['NAME']
 def __init__(self, config, schema):
     os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
     config["dsn"] = cx_Oracle.makedsn(host=config["host"], port=config["port"], sid=config["sid"])
     self.db = cx_Oracle.connect(user=config["user"], password=config["password"], dsn=config["dsn"])   #用自己的实际数据库用户名、密码、主机ip地址 替换即可
     self.db.current_schema = schema
     self.db.autocommit = 1
     self.cursor = self.db.cursor()
예제 #17
0
	def checkDatabaseConnection(self,expectedDatabases,database_username,database_password,listener_port):
		"""
		Check if the connection to databases are working.
		"""
		try:
			error = False
			# Process expected databases
			expected_databases = expectedDatabases.split(',')
			
			for vmname in self.vminfo.keys():
				self.logger.info("Checking " + vmname)
				host = self.vminfo[vmname]
				port = int(listener_port)
				for database in expected_databases:
					self.logger.info("Checking " + database + " connection")
					connectstring = cx_Oracle.makedsn(host, port, service_name=database.upper())
					try:
						# try connect to the database
						connection_obj = cx_Oracle.connect(database_username,database_password,connectstring)
					except:
						error = True
						self.logger.error("Vm "+vmname+" Cannot connect to database"+database)
			if error:
					raise ECUTestFailureError("Database connection test failed")
		except:
			self.logger.error(traceback.format_exc());
			raise ECUTestFailureError("Database test Fail")
예제 #18
0
 def connect_oracle(self):
     dsn = cx_Oracle.makedsn(db_host, 1521, "xe")
     dg(1, "connect_oracle | DSN:" + str(dsn))
     self.con = cx_Oracle.connect(db_user, db_pass, dsn)
     dg(1, "connect_oracle | CON:" + str(self.con.version))
     self.cursor = self.con.cursor()
     return self.cursor
예제 #19
0
def connect_ora(g_dbid):
  global oracledb,oraclecur,oraclecur2
  g_completed='N'
  g_gmt_create=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
  g_gmt_modify=g_gmt_create
  table_name='b_oracle_quota_collect_day'
  get_ora_pass(g_dbid)  
  if h_type=='standby':
       get_ora_standby(g_dbid)  
  else:
       tns_name = cx_Oracle.makedsn(ip_addr,port,sid)
       oracledb = cx_Oracle.connect(username,password, tns_name)
       oraclecur=oracledb.cursor()
       if h_type=='awr':
               get_awr()
       if h_type=='sqltext':
               get_sqltext()
       if h_type=='stat':
               get_ora_stat(g_dbid)
       if h_type=='longsql':
               get_ora_long_sql(g_dbid)
       if h_type=='trans':
               get_trans_info(g_dbid)
       if h_type=='level':
               get_index_level(g_dbid)
  mysqlcur.close()
  mysqldb.close()
예제 #20
0
def createuser():
    opts={}
    user = '******'
    password = '******'
    host = 'localhost'
    port = '1521'
    SID = 'orcl'
    dsn_tns = cx_Oracle.makedsn(host,port,SID)
    try:
        conn = cx_Oracle.connect(user,password,dsn_tns)

        if request.method == 'POST':
            try:
                username = request.form["username"]
                userpass = request.form["userpass"]
                cursor = conn.cursor()
                cursor.execute("""CREATE USER %s IDENTIFIED BY %s DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS""" % (username, userpass))
                cursor.execute("grant CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW to %s" % (username))
                
                cursor.execute("grant resource to %s" %(username))
                cursor.execute("grant connect to %s" % (username))
                conn.commit()
            except Exception as e:
                print e
                opts["errors"] = u"Error en la conexión de la base de datos. Verifique el host o el usuario"
    except cx_Oracle.DatabaseError, exc:
        return redirect(url_for('errordb'))
예제 #21
0
def return_cursor(creds_path):
    '''
    Method to return an Oracle DB cursor which is connected to database
    instance using credentials found in a local file.

    Parameters
    ----------
    creds_path : string (filepath)
        path to the csv file with 'DB_USER' as the header and the
        corresponding ASCII text for the user name underneath; same
        'DB_PASSWD', 'DB_HOST', 'DB_PORT', and 'DB_SID'headers and text

    Returns
    -------
    cursor : OracleCursor
        a cx_Oracle cursor object which is used to query and modify an
        Oracle database
    '''

    # Import packages
    import cx_Oracle

    # Get DB connection variables
    user, passwd, host, port, sid = return_rds_vars(creds_path)
    # Setup connection and cursor
    dsn = cx_Oracle.makedsn(host, port, sid)
    conn = cx_Oracle.connect(user, passwd, dsn)
    cursor = conn.cursor()

    # Return cursor
    return cursor
예제 #22
0
   def loop(self):

      ## use DBMS_PIPE based notification
      self.usePipe = True

      ## DBMS_PIPE.receive_message timeout in secs
      self.pipeTimeout = int(1)

      ## Iff true, recheck event table on every pipe timeout
      self.recheckOnPipeTimeout = False

      import time, json

      import os
      os.environ["NLS_LANG"] = "AMERICAN_AMERICA.UTF8"

      import cx_Oracle

      try:
         dsn = cx_Oracle.makedsn(self.connect.host,
                                 self.connect.port,
                                 self.connect.sid)
         self.conn = cx_Oracle.connect(self.connect.user,
                                       self.connect.password,
                                       dsn,
                                       threaded = False)
         self.conn.autocommit = True
         log.msg("pusher Oracle connection established")
      except Exception, e:
         #log.msg(str(e))
         raise e
예제 #23
0
 def getParent(self, childBatchUid):
     sql = """
     select * from bblearn.course_main
     where 
       pk1 in (
         select crsmain_pk1 from bblearn.course_course 
           where crsmain_parent_pk1=(
             select pk1 from bblearn.course_main 
               where batch_uid='%s'
             )
           )
     """%childBatchUid
     ezConStr = cx_Oracle.makedsn("devlmshost1.ucc.nau.edu",1521,"learn")
     connection = cx_Oracle.Connection(
                                       dsn=ezConStr,
                                       user="******",
                                       password="******")
     cursor=cx_Oracle.Cursor(connection);
     cursor.execute(sql)
     i=0
     for row in cursor.fetchall():
         i=i+1
     cursor.close()
     connection.close()
     return i
예제 #24
0
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"
예제 #25
0
	def _executeQuery(self, query):
		self.getLogger().debug("Executing query:\n%s" % repr(query))
		try:
			dsn = dbapi.makedsn(str(self['host']), self['port'], str(self['sid']))
			conn = dbapi.connect(str(self['user']), str(self['password']), dsn)
			cursor = conn.cursor()
			self.logSentPayload(query.split(' ')[0].upper(), query)
			try:
				cursor.execute(str(query))
				conn.commit()
			except Exception, e:
				self.getLogger().warning("Exception while executing query: %s" % getBacktrace())
				conn.rollback()
				cursor.close()
				conn.close()
				raise e

			res = []
			if cursor.description: # equivalent to "the previous execution() provided a set ?"
				columnNames = map(lambda x: x[0], cursor.description)
				for row in cursor.fetchall():
					res.append(dict(zip(columnNames, row)))

			self.triEnqueueMsg(('result', res))
			cursor.close()
			conn.close()
예제 #26
0
def get_cursor(username="", password="", host="datadb.admsec.wwu.edu",
               port=1521, db="DATAW"):
    """Prompt for username and password if they are not present, and return a
    cursor to the database connection."""

    # Get the username to use for the connection.
    if not username:

        if version_info[0] >= 3:
            get_input = input
        else:
            get_input = raw_input

        username = get_input("Enter Database Username: "******"Enter Password: "******"DRIVER={{Microsoft ODBC for Oracle}};"
                   "SERVER={};UID={};PWD={}".format(host, username, password))
        cnxn = pyodbc.connect(cnxnstr)
    else:
        import cx_Oracle

        dsn = cx_Oracle.makedsn(host, port, db)
        cnxn = cx_Oracle.connect(username, password, dsn)

    # Clear out the password.
    password = ""

    return cnxn.cursor()
예제 #27
0
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
예제 #28
0
 def update_sql(values, column):
     for i in values:
         conn = None
         try:
             dsn = cx_Oracle.makedsn('localhost', 1521, 'urbandeve')
             conn = cx_Oracle.connect(user='******', password='******', dsn=dsn)
             try:
                 cursor = conn.cursor()
                 for i in values:
                     sql = '''update TBL_DPSHOPS set %s = '%s' where SHOPNO = '%s' ''' 
                     t = (column,) + i
                     sql = sql % t 
                     cursor.prepare(sql)
                     cursor.execute(None)
                     print(cursor.rowcount)
                     if cursor.rowcount == 0:
                         values = (values[1], values[0])
                         cursor.prepare('''insert into TBL_DPSHOPS 
                         (SHOPNO, %s) 
                         values(:1, :2)''' % column)
                         cursor.execute(None, values)
                     conn.commit()
             finally:
                 cursor.close()
         finally:
             if conn is not None:
                 conn.close() 
예제 #29
0
    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()))
예제 #30
0
파일: connections.py 프로젝트: exclm/pysql
 def set_corrections(self):
     if self.mode:
         self.mode = getattr(cx_Oracle, self.mode.upper())
     if self.hostname:
         self.dsn = cx_Oracle.makedsn(self.hostname, self.port, self.database)
     else:
         self.dsn = self.database
예제 #31
0
 def __init__(self):
     self.tns = cx.makedsn('npc.che.volvocars.net', 49957, sid='DPQ')
     self.db = cx.connect('cip_sys', 'cip_sys1', self.tns, encoding="UTF-8", nencoding="UTF-8")
     self.cur = self.db.cursor()
예제 #32
0
 def __init__(self,ip,port,sid,user, password):
     self.constring = cx_Oracle.makedsn(ip, port, sid)
     self.password = password
     self.user = user
예제 #33
0
def handle(req):

    if (re.search('ATOM_ERROR:', req)):
        return req

    try:
        with open('function/config.json') as con:
            jsn = json.load(con)
    except:
        return ("ATOM_ERROR: get-db-to-json: Invalid Configuration file\n")

    fetch_field = ''
    txt_output = json.loads(req)

    for j in jsn['field_details']:
        fetch_field = fetch_field + j['db_col_name'] + ', '

    fetch_field = fetch_field[:-2]

    ip = jsn['database_details'][0]['ip']
    port = jsn['database_details'][0]['port']
    SID = jsn['database_details'][0]['sid']
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)

    #Preparing database connection
    try:
        db = cx_Oracle.connect(jsn['database_details'][0]['db_name'],
                               jsn['database_details'][0]['password'], dsn_tns)
    except:
        return (
            "ATOM_ERROR: get-db-to-json: Unable to connect to Billing database\n"
        )
    cur = db.cursor()

    #Prepare the query

    #TODO: For the pelatom we have taken a scenario of single table, with a single primary key.
    #This can be enhanced to be more generic in the future

    query = 'select ' + fetch_field + ' from ' + jsn['database_details'][0][
        'table_name'] + ' where ' + jsn['database_details'][0][
            'table_key'] + ' = :tab_key'
    cur.prepare(query)
    cur.execute(None, tab_key=txt_output[jsn['field_details'][0]['entity']])
    db_result = cur.fetchall()
    db.close()
    i = 0
    db_values = {}

    if not db_result:
        return (
            "ATOM_ERROR: get-db-to-json:No record returned from Billing database\n"
        )

    #Converting extracted data to string for JSON comparison
    #if  db_result:
    for j in jsn['field_details']:
        db_values[j['entity']] = str(db_result[0][i])
        i += 1

    #TODO: Below code can be moved to another atom once parallel/persistence issue around JESI is resolved
    final_result = ''
    diffkeys = [k for k in db_values if db_values[k] != txt_output[k]]

    #If there is any mismatch in any attribute, diffkeys will be non-empty
    if diffkeys:
        final_result = 'PDF Validation Fail for \n'
    else:
        final_result = 'PDF Validation Success \n'

    for k in diffkeys:
        #print(k, ':', db_values[k], '->', txt_output[k])
        final_result = final_result + k + ' : ' + db_values[
            k] + ' (DB Value) -> ' + txt_output[k] + ' (PDF Value)\n'

    return final_result
import pandas as pd
import numpy as np
from my_functions import marc_parser_1_field
import pandasql
from my_functions import cSplit
from my_functions import gsheet_to_df
import cx_Oracle

dsn_tns = cx_Oracle.makedsn('pbl.ibl.poznan.pl', '1521', service_name='xe')
connection = cx_Oracle.connect(user='******',
                               password='******',
                               dsn=dsn_tns,
                               encoding='windows-1250')

pbl_bn_magazines = gsheet_to_df('1V6BreA4_cEb3FRvv53E5ri2Yl1u3Z2x-yQxxBbAzCeo',
                                'Sheet1')
pbl_bn_magazines = pbl_bn_magazines[pbl_bn_magazines['decyzja'] == 'tak']

bn_magazines = pd.read_excel('bn_magazines_to_statistics.xlsx')[[
    'id', '008', '773', 'decision'
]]
bn_magazine_title = marc_parser_1_field(bn_magazines, 'id', '773',
                                        '\$')[['id', '$t']]
bn_magazine_title.columns = ['id', 'bn_magazine']
bn_magazine_title = pd.merge(bn_magazine_title,
                             pbl_bn_magazines[['pbl_magazine', 'bn_magazine']],
                             'left', 'bn_magazine')
pbl_query = 'select zr.zr_zrodlo_id, zr.zr_tytul from pbl_zrodla zr'
pbl_magazines = pd.read_sql(pbl_query,
                            connection).rename(columns={
                                'ZR_TYTUL': 'pbl_magazine',
예제 #35
0
    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]  # pylint: disable=no-member
                                   )
        conn_config = {'user': conn.login, 'password': conn.password}
        sid = conn.extra_dejson.get('sid')
        mod = conn.extra_dejson.get('module')

        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 or conn.schema:
                    dsn += "/" + (service_name or 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

        return conn
예제 #36
0
import cx_Oracle
import re

dsn_tns = cx_Oracle.makedsn('LAPTOP-LBV8A2GC', '1521', service_name='XE')
conn = cx_Oracle.connect(user=r'kandarp', password='******', dsn=dsn_tns)


def queryFirst():
    c = conn.cursor()
    print("________________________________________________________________")
    print("100|D-Mart    |Surat    |Utran")
    print("110|D-Mart    |Surat    |Vesu")
    print("120|Big Bazaar|Surat    |Utran")
    print("130|Big Bazaar|Surat    |Vesu")
    print("200|D-Mart    |Ahmedabad|Chandkheda")
    print("210|D-Mart    |Ahmedabad|Lal Darwaja")
    print("220|Big Bazaar|Ahmedabad|Chandkheda")
    print("230|Big Bazaar|Ahmedabad|Lal Darwaja")
    print("________________________________________________________________")
    sid = input("Enter supermarketid to find out deapartmentwise total sell: ")
    print("supermarketid\t\ttotalsell")
    print("_____________\t\t__________")
    c.execute(
        "select departmentid,sum(totalcost) from (select * from purchasedetails natural join productproj where supermarketid="
        + sid + ") group by departmentid order by departmentid asc")
    for row in c:
        print(row[0], "\t\t", row[1])


def querySecond():
    c = conn.cursor()
예제 #37
0
def select_all_from(table, cursor):
    sql = f'select * from {table.upper()}'
    # from 구문에서 테이블 이름은 ''로 감싸면 안되기 때문에
    # data binding 방식을 사용할 수 없다.
    cursor.execute(sql)
    # data = cursor.fetchall()  # [row for row in cursor]
    data_frame = pd.DataFrame(cursor)  # pd.DataFrame(data)
    # 데이터 프레임에 컬럼 이름을 설정
    data_frame.columns = get_column_names_of(table, cursor)
    return data_frame


if __name__ == '__main__':
    # 오라클 DB 서버에 접속
    dsn = cx_Oracle.makedsn('localhost', 1521, 'orcl')
    with cx_Oracle.connect('scott', 'tiger', dsn) as connection:
        # Cursor 객체 생성
        with connection.cursor() as cursor:
            emp_columns = get_column_names_of('emp', cursor)
            print(emp_columns)  # ['empno', 'enam', 'job', ...]

            emp_df = select_all_from('emp', cursor)  # pandas.DataFrame
            # DataFrame은 테이블의 컬럼 이름(인덱스)를 포함하고 있어야 함.
            print(emp_df)

            dept_df = select_all_from('DEPT', cursor)
            print(dept_df)

            salgrade_df = select_all_from('salgrade', cursor)
            print(salgrade_df)
예제 #38
0
@author: SH-NoteBook
"""
import xlsxwriter
import openpyxl
import pandas as pd
import numpy as np
import cx_Oracle
import itertools
from calculate_bm_updown import calculate_bm_updown
#from one_factor_v4 import QVGSM_VALUE
from one_factor_v5 import QVGSM_VALUE
from Performance_Evaluation_v2 import Performance_Evaluation

#이거 두개 반드시 선언!
cx0 = cx_Oracle.makedsn("localhost", 1521, "xe")
connection = cx_Oracle.connect("lshlsh135", "2tkdgns2", cx0)  #이게 실행이 안될때가 있는데
#그때는 services에 들어가서 oracle listner를 실행시켜줘야함

kospi_day = pd.read_sql("""select * from kospi_day_prc_20190628""",
                        con=connection)  # 코스피 일별 종가
kospi_day.set_index('TRD_DATE', inplace=True)
kospi200_day = pd.read_sql("""select * from kospi200_day_prc_20190628""",
                           con=connection)  # 코스피 일별 종가
kospi200_day.set_index('TRD_DATE', inplace=True)
#a=calculate_bm_updown(kospi200_day,net_wealth)
#a.make_kospi200_plot()
#kospi_quarter = pd.read_sql("""select * from kospi_quarter_return_20180928""",con=connection)
#kospi_month = pd.read_sql("""select * from kospi_month_return_20180928""",con=connection)
#DATA를 가져온다!!
kospi = pd.read_sql(
예제 #39
0
import cx_Oracle

#TNSnames.ora

#ORCL =
#  (DESCRIPTION =
#    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
#    (CONNECT_DATA =
#      (SERVICE_NAME = orcl)
#    )
#  )
# ("Ip_servidor" ,"Porta", "Serviço")

TNS = cx_Oracle.makedsn("187.44.254.2", "1521", "WINT")

con = cx_Oracle.connect(user="******", password="******", dsn=TNS)


# print(con.version)
# abre o cursor
class Pesquisa():
    codcli = input(('escolha um codigo'))
    cur = con.cursor()
    cur.prepare('select enderent,numeroent,bairroent,municent,estent,cepent from pcclient where codcli = :codcli')
    cur.execute(None, {'codcli': codcli})
    row = cur.fetchall()[0]
    endereco = row
    print(endereco)
    numero = row[1]
    bairro = row[2]
    cidade = row[3]
예제 #40
0
import cx_Oracle

# info about the oracle connection
dsn_tns = cx_Oracle.makedsn('grad.icmc.usp.br', 15215, 'orcl')
user = '******'
password = '******'


# open the oracle server connection
def openConnection():
    global con
    con = cx_Oracle.connect(user, password, dsn_tns)
    global cur
    cur = con.cursor()


# close the oracle server connection
def closeConnection():
    cur.close()
    con.close()
예제 #41
0
#

from subprocess import Popen, PIPE
import cx_Oracle


def disp_rec(cur):
    while 1:
        one = cur.fetchone()
        if one != None:
            print one
        else:
            break


tns_name = cx_Oracle.makedsn('10.169.8.59', '1521', 'orcl')
db = cx_Oracle.connect('v3xuser', 'Www123456', tns_name)

print db.dsn

cur_src = db.cursor()

sql = 'SELECT * FROM logon_log'

info = cur_src.execute(sql)

disp_rec(info)

cur_src.close()
db.close()
예제 #42
0
# Simplified program for calculating ROP from annual usage and lead time data

import time
import cx_Oracle
import numpy as np
import pandas as pd
from scipy.stats import norm
from sqlalchemy import create_engine

import onccfg as cfg

user = cfg.username
password = cfg.password
sid = cx_Oracle.makedsn(cfg.host, cfg.port, sid=cfg.sid)  # host, port, sid

cstr = f"oracle://{user}:{password}@{sid}"

engine = create_engine(cstr,
                       convert_unicode=False,
                       pool_recycle=20,
                       pool_size=100,
                       echo=False)


def read_sql(filename):
    sql_file = open(filename, "r")
    query = sql_file.read()
    sql_file.close()

    df_from_sql = pd.DataFrame(pd.read_sql_query(query, engine))
    df_from_sql.columns = map(str.upper, df_from_sql.columns)
예제 #43
0
import cx_Oracle

dsn_tns = cx_Oracle.makedsn('64.101.193.207', '1541', service_name='abcnd.cisco.com')

this is my first program
예제 #44
0
def run(args):

    # The write log handle
    sout = args.sout
    desthumbs.tilefinder.SOUT = args.sout
    desthumbs.thumbslib.SOUT = args.sout

    # Read in CSV file with pandas
    df = pandas.read_csv(args.inputList)

    # Decide if we do search by RA,DEC or by COADD_ID
    if 'COADD_OBJECTS_ID' in df.columns:
        searchbyID = True
        if not args.coaddtable:
            raise Exception(
                "ERROR: Need to provide --coaddtable <tablename> when using COADD_OBJECTS_ID"
            )
        coadd_id = dec = df.COADD_OBJECTS_ID.values
        nobj = len(coadd_id)
        req_cols = ['COADD_OBJECTS_ID']
    else:
        searchbyID = False
        ra = df.RA.values  #if you only want the values otherwise use df.RA
        dec = df.DEC.values
        nobj = len(ra)
        req_cols = ['RA', 'DEC']

    # Check columns for consistency
    check_columns(df.columns, req_cols)

    # Check the xsize and ysizes
    xsize, ysize = check_xysize(df, args, nobj)

    # Get DB handle
    try:
        dbh = desdbi.DesDbi(section=args.db_section)
    except:
        if args.db_section == 'desoper' or args.db_section == 'db-desoper':
            host = 'desdb.ncsa.illinois.edu'
            port = '1521'
            name = 'desoper'
        elif args.db_section == 'oldsci' or args.db_section == 'db-oldsci':
            host = 'desdb-dr.ncsa.illinois.edu'
            port = '1521'
            name = 'desdr'

        kwargs = {'host': host, 'port': port, 'service_name': name}
        dsn = cx_Oracle.makedsn(**kwargs)
        dbh = cx_Oracle.connect(args.user, args.password, dsn=dsn)

    # Define the schema
    if args.tag[0:4] == 'SVA1' or args.tag[0:4] == 'Y1A1':
        schema = 'des_admin'
    elif args.tag[0:3] == 'DR1':
        schema = 'dr1'
    elif args.tag[0:3] == 'DR2':
        schema = 'dr2'
    else:
        schema = 'prod'

    print "SCHEMA is", schema
    # Get archive_root
    archive_root = desthumbs.get_archive_root(dbh, schema=schema, verb=True)

    # Make sure that outdir exists
    if not os.path.exists(args.outdir):
        if args.verb: sout.write("# Creating: %s\n" % args.outdir)
        os.makedirs(args.outdir)

    # Find all of the tilenames, indices grouped per tile
    if args.verb: sout.write("# Finding tilename for each input position\n")
    if searchbyID:
        tilenames, ra, dec, indices, tilenames_matched = desthumbs.find_tilenames_id(
            coadd_id, args.coaddtable, dbh, schema=schema)
    else:
        tilenames, indices, tilenames_matched = desthumbs.find_tilenames_radec(
            ra, dec, dbh, schema=schema)

    # Add them back to pandas dataframe and write a file
    df['TILENAME'] = tilenames_matched
    # Get the thumbname base names and the them the pandas dataframe too
    df['THUMBNAME'] = get_base_names(tilenames_matched,
                                     ra,
                                     dec,
                                     prefix=args.prefix)
    matched_list = os.path.join(args.outdir,
                                'matched_' + os.path.basename(args.inputList))
    df.to_csv(matched_list, index=False)
    sout.write("# Wrote matched tilenames list to: %s\n" % matched_list)

    # Make sure that all found tilenames *are* in the tag (aka data exists for them)
    #tilenames_intag = desthumbs.get_tilenames_in_tag(dbh,args.tag)

    # Loop over all of the tilenames
    t0 = time.time()
    Ntile = 0
    for tilename in tilenames:

        t1 = time.time()
        Ntile = Ntile + 1
        sout.write("# ----------------------------------------------------\n")
        sout.write("# Doing: %s [%s/%s]\n" % (tilename, Ntile, len(tilenames)))
        sout.write("# ----------------------------------------------------\n")

        # 1. Get all of the filenames for a given tilename
        filenames = desthumbs.get_coaddfiles_tilename_bytag(tilename,
                                                            dbh,
                                                            args.tag,
                                                            bands=args.bands,
                                                            schema=schema)

        # ------------------
        # IMPORTANT NOTE
        # For SV1/Y2A1/Y3A1 we get one entry per band in the array,
        # but for DR1, we get all entries independently, so the shape of the record arrays are different
        # ------------------

        if filenames is False:
            sout.write("# Skipping: %s -- not in TAG:%s \n" %
                       (tilename, args.tag))
            continue
        # Fix compression for SV1/Y2A1/Y3A1 releases
        else:
            filenames = desthumbs.fix_compression(filenames)

        indx = indices[tilename]
        if schema == 'dr1':
            avail_bands = desthumbs.get_avail_bands_dr1(filenames)
        else:
            avail_bands = filenames.BAND

        # 2. Loop over all of the filename -- We could use multi-processing
        p = {}
        n_filenames = len(avail_bands)
        for k in range(n_filenames):

            # Rebuild the full filename with COMPRESSION if present
            if 'COMPRESSION' in filenames.dtype.names:
                filename = os.path.join(
                    archive_root, filenames.PATH[k],
                    filenames.FILENAME[k]) + filenames.COMPRESSION[k]
            elif schema == 'dr1':
                #filename = filenames[0][k].replace('/easyweb/files/dr1/','/des004/despublic/dr1_tiles/')
                filename = filenames[0][k].replace(
                    'http://desdr-server.ncsa.illinois.edu', '/des004')
            else:
                filename = os.path.join(archive_root, filenames.PATH[k])

            print filename
            ar = (filename, ra[indx], dec[indx])
            kw = {
                'xsize': xsize[indx],
                'ysize': ysize[indx],
                'units': 'arcmin',
                'prefix': args.prefix,
                'outdir': args.outdir,
                'tilename': tilename,
                'verb': args.verb
            }
            if args.verb: sout.write("# Cutting: %s\n" % filename)
            if args.MP:
                NP = len(avail_bands)
                p[filename] = mp.Process(target=desthumbs.fitscutter,
                                         args=ar,
                                         kwargs=kw)
                p[filename].start()
            else:
                NP = 1
                desthumbs.fitscutter(*ar, **kw)

        # Make sure all process are closed before proceeding
        if args.MP:
            for filename in p.keys():
                p[filename].join()

        # 3. Create color images using stiff for each ra,dec and loop over (ra,dec)
        for k in range(len(ra[indx])):
            desthumbs.color_radec(ra[indx][k],
                                  dec[indx][k],
                                  avail_bands,
                                  prefix=args.prefix,
                                  colorset=args.colorset,
                                  outdir=args.outdir,
                                  verb=args.verb,
                                  stiff_parameters={'NTHREADS': NP})

        if args.verb:
            sout.write("# Time %s: %s\n" %
                       (tilename, desthumbs.elapsed_time(t1)))

    sout.write("\n*** Grand Total time:%s ***\n" % desthumbs.elapsed_time(t0))
    return
예제 #45
0
파일: query.py 프로젝트: sxuaner/python
# query.py

import cx_Oracle

cx_Oracle.init_oracle_client(lib_dir="/Users/xuansong/.bin/instantclient_19_8")

print("Client version: " + str(cx_Oracle.clientversion()))

# Establish the database connection
# dsn is short for data source name
dsn_tns = cx_Oracle.makedsn('localhost',
                            '1521',
                            service_name='ORCLCDB.localdomain')
conn = cx_Oracle.connect(user=r'hr', password='******', dsn=dsn_tns)

# Obtain a cursor
cursor = conn.cursor()

# Data for binding
managerId = 145
firstName = "Peter"

# Execute the query
# The data values in managerId and firstName are ‘bound’ to the statement placeholder ‘bind variables’: mid and : fn when the statement
# is executed. This separates the statement text from the data, which helps avoid SQL Injection security risks. Binding is also important
# for performance and scalability.
sql = """SELECT first_name, last_name
         FROM employees
         WHERE manager_id = :mid AND first_name = :fn"""
cursor.execute(sql, mid=managerId, fn=firstName)
예제 #46
0
# WITH SID

import cx_Oracle
dsn_tns = cx_Oracle.makedsn('localhost', '1521', 'xe')
conn = cx_Oracle.connect(user='******', password='******', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from customers')
for row in c:
    print(row)
conn.close()

# WITH SERVICE

import cx_Oracle
dsn_tns = cx_Oracle.makedsn('server', 'port', service_name='service_name')
conn = cx_Oracle.connect(user='******', password='******', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from TABLE_NAME')
for row in c:
    print(row)
conn.close()
예제 #47
0
def set_db_config(db_type, db_name=None, env="dev"):
    if env == "dev":
        if db_type == "mysql":
            if not db_name:
                db_name = "test"
                host = "127.0.0.1"
                user = "******"
                passwd = "122901"
                db_config = {
                    "host": host,
                    "port": 3306,
                    "user": user,
                    "passwd": passwd,
                    "db": db_name,
                    "charset": 'utf8',
                    "cursorclass": pymysql.cursors.
                    DictCursor  # 加上cursorclass之后就可以直接把字段名捞出来,和字段值组成键值对的形式
                }
            else:
                host = "rm-bp1t54ibu89062yg2lo.mysql.rds.aliyuncs.com"
                user = "******"
                passwd = "o7JFQIqhaEo8FGu^"
                db_config = {
                    "host": host,
                    "port": 3306,
                    "user": user,
                    "passwd": passwd,
                    "db": db_name,
                    "charset": 'utf8',
                    "cursorclass": pymysql.cursors.
                    DictCursor  # 加上cursorclass之后就可以直接把字段名捞出来,和字段值组成键值对的形式
                }
        elif db_type == "sqlite":
            if not db_name:
                db_name = "identify_record.db"
            db_config = {'database': r'../case/{}'.format(db_name)}
        elif db_type == "redis":
            db_config = {
                "host": "172.16.3.210",
                "port": 6379,
                "password": "******",
                "max_connections": 1024
            }
        else:
            if not db_name:
                db_name = "testing"
            tns = cx_Oracle.makedsn("127.0.0.1", 1521, db_name)
            db_config = {
                'user': "******",
                'password': "******",
                'dsn': tns,
                'nencoding': "utf8"
            }
    else:
        if db_type == "mysql":
            host = "rm-bp1t54ibu89062yg2lo.mysql.rds.aliyuncs.com"
            user = "******"
            passwd = "o7JFQIqhaEo8FGu^"
            db_config = {
                "host": host,
                "port": 3306,
                "user": user,
                "passwd": passwd,
                "db": db_name,
                "charset": 'utf8',
                "cursorclass": pymysql.cursors.
                DictCursor  # 加上cursorclass之后就可以直接把字段名捞出来,和字段值组成键值对的形式
            }
    return db_config
예제 #48
0
from __future__ import print_function

import cx_Oracle

# Connect as user "hr" with password "welcome" to the "orclpdb1" service running on this computer.
dsn = cx_Oracle.makedsn("localhost", 32799, sid="ORCLCDB")
connection = cx_Oracle.connect("rdms", "rdms", dsn, encoding="UTF-8")

cursor = connection.cursor()
cursor.execute("""
    SELECT *
    FROM Airline
    """)
for id, name, _ in cursor:
    print("Values:", id, name)

# connection.autocommit = True
dataToInsert = []

for i in range(1, 21):
    dataToInsert.append((i, "name" + str(i), "type" + str(i)))

cursor = connection.cursor()
# sql = ' ({}, \'{}\', \'{}\')'.format(i, 'name'+str(i), 'type'+str(i))
cursor.executemany("insert into AIRLINE values (:i, :j, :k)", dataToInsert)
cursor.close()

connection.commit()

cursor = connection.cursor()
cursor.execute("""
예제 #49
0
# dsn_tns = cx_Oracle.makedsn(r'ORAODSTB578B.azul.corp', '1521', service_name='TRAX_ODS')
# conn = cx_Oracle.connect(user=r'userselect', password=r'userselect', dsn=dsn_tns)
# fh = conn.cursor()
# fh.execute("""SELECT
# '01'||'/'||TO_CHAR(FLIGHT_DATE, 'MM')||'/'||TO_CHAR(FLIGHT_DATE, 'YYYY') MES,
# CASE
# WHEN AM.AC_TYPE = 'PILATUS-12' THEN 'PILATUS'
# WHEN AM.AC_TYPE = 'ATR72' THEN 'ATR'  ELSE AM.AC_TYPE END AC_TYPE,
# ((SUM(AF.FLIGHT_HOURS)*60)+SUM(AF.FLIGHT_MINUTES))/60 FH
# FROM ODB.AC_ACTUAL_FLIGHTS AF INNER JOIN ODB.AC_MASTER AM ON AF.AC = AM.AC
# WHERE AF.FLIGHT_DATE>='01-JAN-2020' AND AF.FLIGHT_DATE<='31-JAN-2020'
# GROUP BY '01'||'/'||TO_CHAR(FLIGHT_DATE, 'MM')||'/'||TO_CHAR(FLIGHT_DATE, 'YYYY'), AM.AC_TYPE """)
# dffh = pd.DataFrame(fh, columns=['MES', 'FLEET', 'FH'])
# print(dffh)

import pandas as pd
import os
os.chdir(r'C:\Users\paulo.roberto\Documents\Movto Estoque Consolidado')
import cx_Oracle
dsn_tns = cx_Oracle.makedsn(r'ORAODSTB578B.azul.corp',
                            '1521',
                            service_name='TRAX_ODS')
conn = cx_Oracle.connect(user=r'userselect',
                         password=r'userselect',
                         dsn=dsn_tns)
fh = conn.cursor()
fh.execute(
    """SELECT WO.WO, WO.WO_DESCRIPTION, WO.WO_CATEGORY, WO.LOCATION, WO.SITE, WO.EXPENDITURE FROM ODB.WO"""
)
dffh = pd.DataFrame(fh, columns=['MES', 'FLEET', 'FH'])
print(dffh)
예제 #50
0
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
import requests
import time

import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir=r"C:\instantclient_19_10")

url = 'https://www.youtube.com/gaming/games'
driver = webdriver.Chrome('chromedriver')
driver.get(url)
SCROLL_PAUSE_SEC = 1
body = driver.find_element_by_css_selector('body')

dsn = cx_Oracle.makedsn("222.232.15.205", 1521, "orcl")
db = cx_Oracle.connect("rootweb2020", "1234", dsn)

for i in range(10):
    # 끝까지 스크롤 다운
    body.send_keys(Keys.END)
    # 1초 대기
    time.sleep(SCROLL_PAUSE_SEC)

html = driver.page_source
soup = BeautifulSoup(html, 'html.parser')
i = 1
for tag in soup.select('div[id = "game"]'):
    cursor = db.cursor()
    print(
        i,
def selects(request):
    # Cargar archivo de configuración principal
    config = configparser.ConfigParser()
    config.read('settings.ini')
    # Conexión
    ip = config.get('database', 'DATABASE_HOST')
    port = config.get('database', 'DATABASE_PORT')
    SID = config.get('database',
                     'DATABASE_NAME')  # Oracle System ID - Instancia
    dsn_tns = cx_Oracle.makedsn(
        ip, port, SID)  # Realizar Data Source Name (Nombre de origen de datos)
    cnxn = cx_Oracle.connect(config.get('database', 'DATABASE_USER'),
                             config.get('database', 'DATABASE_PASSWORD'),
                             dsn_tns)

    # Aplicar filtros
    socio_seleccionado = request.POST.getlist('socios')
    socio_seleccionado = [i for i in socio_seleccionado if i != '']
    socio_seleccionado = [
        sub_item for item in socio_seleccionado for sub_item in item.split(",")
    ]

    linea_fina_seleccionado = request.POST.getlist('linea_negocio')
    linea_fina_seleccionado = [i for i in linea_fina_seleccionado if i != '']
    linea_fina_seleccionado = [
        sub_item for item in linea_fina_seleccionado
        for sub_item in item.split(",")
    ]

    periodo_seleccionado = request.POST.getlist('periodo')
    periodo_seleccionado = [i for i in periodo_seleccionado if i != '']
    periodo_seleccionado = [
        sub_item for item in periodo_seleccionado
        for sub_item in item.split(",")
    ]

    canal_seleccionado = request.POST.getlist('canal')
    canal_seleccionado = [i for i in canal_seleccionado if i != '']
    canal_seleccionado = [
        sub_item for item in canal_seleccionado for sub_item in item.split(",")
    ]

    producto_seleccionado = request.POST.getlist('productos')
    producto_seleccionado = [i for i in producto_seleccionado if i != '']
    producto_seleccionado = [
        sub_item for item in producto_seleccionado
        for sub_item in item.split(",")
    ]

    where2 = ' WHERE 1=1 '

    list_2 = []

    if len(socio_seleccionado) > 0:
        a = tuple(socio_seleccionado)
        b = '1=1'
        l = list(a)
        l.append(b)
        socio_seleccionado = tuple(l)
        where2 = where2 + " AND NOM_SOCIO IN " + str(socio_seleccionado)

    if len(linea_fina_seleccionado) > 0:
        a = tuple(linea_fina_seleccionado)
        b = '1=1'
        l = list(a)
        l.append(b)
        linea_fina_seleccionado = tuple(l)
        where2 = where2 + " AND LINEA_NEGOCIO IN " + str(
            linea_fina_seleccionado)

    if len(periodo_seleccionado) > 0:
        a = tuple(periodo_seleccionado)
        b = '1=1'
        l = list(a)
        l.append(b)
        periodo_seleccionado = tuple(l)
        where2 = where2 + " AND PERIODO IN " + str(periodo_seleccionado)

    if len(canal_seleccionado) > 0:
        a = tuple(canal_seleccionado)
        b = '1=1'
        l = list(a)
        l.append(b)
        canal_seleccionado = tuple(l)
        where2 = where2 + " AND CANAL IN " + str(canal_seleccionado)

    if len(producto_seleccionado) > 0:
        a = tuple(producto_seleccionado)
        b = 0
        l = list(a)
        l.append(b)
        producto_seleccionado = tuple(l)
        where2 = where2 + " AND COD_PROD IN " + str(producto_seleccionado)

    #############################################################################################################

    # Consultar Base de Datos

    # Query Unicos Tabla Productos
    sql1 = """  SELECT DISTINCT COD_PROD, PERIODO, LINEA_NEGOCIO, NOM_SOCIO, CANAL
                FROM ACTUARIA.TABLA_PRODUCTOS 
                """ + where2 + """ AND ESTADO != 'NO-DATA'    """

    # Dataframe Unicos
    df_unicos = pd.read_sql(sql1, cnxn)

    lista = list(df_unicos['COD_PROD'].unique())
    for i in range(len(lista)):
        lista[i] = int(lista[i])
    lista.insert(0, 0)

    # Query CART_VIGENTES_FACTOR
    sql2 = """  SELECT FECHA_INICIO_EMITE AS FECHA_EMISION, FECHA_INICIO AS FECHA_CORTE, SUM(TOTAL_VIGENTES) AS TOTAL_VIGENTES
        FROM ACTUARIA.CART_VIGENTES_TRIANG 
        WHERE PRODUCTO IN {0} 
        AND FECHA_INICIO_EMITE >= 201501       
        GROUP BY FECHA_INICIO_EMITE,FECHA_INICIO""".format(tuple(lista))

    # Dataframe VIGENTES_TRIANG
    frame_ = pd.read_sql(sql2, cnxn)
    print('01-OK', len(df_unicos))

    # sort values
    frame_.sort_values(['FECHA_EMISION', 'FECHA_CORTE'], inplace=True)

    # selected.fillna(0, inplace=True)
    frame_['FECHA_EMISION'] = frame_['FECHA_EMISION'].astype(int)
    frame_['FECHA_CORTE'] = frame_['FECHA_CORTE'].astype(int)
    frame_['TOTAL_VIGENTES'] = frame_['TOTAL_VIGENTES'].astype(int)

    if len(frame_) != 0:

        unique_month = pd.DataFrame(frame_.FECHA_CORTE.unique())
        unique_month.columns = ['MONTH']

        month_join = pd.DataFrame(frame_.FECHA_CORTE.unique())
        month_join.columns = ['FECHA_CORTE']
        month_join['FECHA_EMISION'] = month_join['FECHA_CORTE']

        list_end = []
        frame_2 = pd.DataFrame()

        n = 0
        for i in range(0, len(month_join)):
            list_mer = list(np.arange(n, len(month_join), 1))
            zmonth_join2 = month_join.iloc[list_mer]
            maximo = zmonth_join2['FECHA_CORTE'].min()
            zmonth_join2['FECHA_EMISION'] = maximo
            result = zmonth_join2
            list_end.append(result)
            n += 1
        frame_2 = pd.concat(list_end)

        # Merge Simetria
        frame = pd.merge(frame_2,
                         frame_,
                         how='left',
                         on=['FECHA_EMISION', 'FECHA_CORTE'])
        frame = frame.replace(np.inf, np.nan).fillna(0)
        #frame.to_excel(
        #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/frame-simetria-merge.xlsx',
        #    sheet_name='frame-simetria')

        unique_sime = pd.DataFrame(frame.FECHA_CORTE.unique())
        unique_sime.columns = ['FECHA_CORTE']
        unique_sime = unique_sime[-3:]
        # unique_sime['FECHA_CORTE'] = unique_sime['FECHA_CORTE'].astype(int)

        unique_sime = list(unique_sime['FECHA_CORTE'])

        frame = frame[(frame['FECHA_CORTE'].isin(unique_sime) == False)]

        #frame.to_excel(
        #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/2-frame-simetria-merge.xlsx',
        #    sheet_name='frame-simetria')

        # delete temp dataframe
        del [[frame_, frame_2, month_join]]

        frame['FECHA_CORTE'] = frame['FECHA_CORTE'].astype(str)
        frame['QUARTER_YEAR'] = frame['FECHA_CORTE'].str[:4]
        frame['QUARTER_MONTH'] = frame['FECHA_CORTE'].str[4:]

        frame['FECHA_EMISION'] = frame['FECHA_EMISION'].astype(str)
        frame['Q_OCURR_YEAR'] = frame['FECHA_EMISION'].str[:4]
        frame['Q_OCURR_MONTH'] = frame['FECHA_EMISION'].str[4:6]

        # Calculo LAG = ((dt_two.year - dt_one.year)*12 + (dt_two.month - dt_one.month))/3.0
        frame['LAG'] = ((frame['QUARTER_YEAR'].astype(int) -
                         frame['Q_OCURR_YEAR'].astype(int)) * 12 +
                        (frame['QUARTER_MONTH'].astype(int) -
                         frame['Q_OCURR_MONTH'].astype(int)))  # / 3

        # PIVOTE DATA FRAMES
        tria_frame = pd.pivot_table(data=frame,
                                    values='TOTAL_VIGENTES',
                                    index=['FECHA_EMISION'],
                                    columns=['LAG'],
                                    aggfunc=np.sum)  # ;print(tria_count)
        #tria_frame.to_excel(
        #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/1-tria_frame.xlsx',
        #    sheet_name='tria_frame')

        #######################################################################################################################################################################################
        ################################################################################## Triangulo Polizas ##################################################################################
        #######################################################################################################################################################################################

        copy_frame = tria_frame.copy()

        def triangulo_factors(tria_a):
            def isnumpy(item):
                return 'numpy' in str(type(item))

            def Lapply(_list, fun, **kwargs):
                iSize = _list.__len__()
                out = list()
                for index in range(0, iSize):
                    out.append(fun(_list[index], **kwargs))
                if isnumpy(_list):
                    out = np.array(out)
                return out

            def GetFactor(index, mTri):
                colSum = mTri[:np.negative(index + 1),
                              index:(index + 2)].sum(0)
                colSum2 = colSum[1] / colSum[0]
                col_mean = np.nan_to_num(colSum2)
                if (col_mean == 0):
                    col_mean = col_mean + 1
                else:
                    col_mean
                return col_mean

            def GetChainSquare(mClaimTri):
                iSize = mClaimTri.shape[1]
                dFactors = Lapply(np.arange(iSize - 1),
                                  GetFactor,
                                  mTri=mClaimTri)
                print('dFactors:', dFactors)
                list_2.append(dFactors)
                dAntiDiag = mClaimTri[:, ::-1].diagonal()[1:]
                for index in range(dAntiDiag.size):
                    mClaimTri[index + 1, np.negative(index + 1):] = dAntiDiag[
                        index] * dFactors[np.negative(index + 1):].cumprod()
                return mClaimTri

            dff_2 = tria_a.values
            factor_ = GetChainSquare(dff_2)
            factor2_a = pd.DataFrame(factor_)
            factor2 = factor2_a.replace(np.inf, np.nan).fillna(0)

            return (factor2)

        # call function triangle
        ultim_polizas = triangulo_factors(copy_frame)
        ultim_polizas = pd.DataFrame(ultim_polizas)
        ultim_polizas = pd.concat([unique_month, ultim_polizas], axis=1)

        # concatenar lista & get last 3 factors
        frame_2 = pd.DataFrame(np.concatenate(list_2))
        frame_2.columns = ['FACTORS']
        frame_2 = frame_2[-3:].reset_index()
        frame_2 = frame_2.drop(['index'], axis=1)

        # Minimo 3 factores para Calcular

        if len(frame_2) == 3:

            # Calcular 60 factores
            factors_60 = frame_2.copy()
            for i in range(3, 63):
                factors_60.loc[i, 'FACTORS'] = np.minimum(
                    (((factors_60.loc[i - 1, 'FACTORS'] +
                       factors_60.loc[i - 2, 'FACTORS'] +
                       factors_60.loc[i - 3, 'FACTORS']) / 3) +
                     ((factors_60.loc[i - 3:i - 1, 'FACTORS']).std(axis=0)) *
                     0.5), 1)
            # print('frame_3::', frame_3)
            #factors_60.to_excel(
            #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/factors_60.xlsx',
            #    sheet_name='factors_60')

            # separate last 60 factors
            factors_60 = factors_60[-60:].reset_index()
            factors_60 = factors_60.drop(['index'], axis=1)

            ############################# Proyectar a 60 meses ############################################################################

            months = range(1, 61)
            # name last column
            ultim_polizas['ep0'] = ultim_polizas.iloc[:, -1]
            j = 0
            for month in months:
                colname = 'ep%d' % month
                prev_colname = 'ep%d' % (month - 1)
                ultim_polizas[colname] = ultim_polizas[
                    prev_colname] * factors_60.loc[j, 'FACTORS']
                j += 1

            ultim_polizas.drop('ep0', axis=1, inplace=True)
            #ultim_polizas.to_excel(
            #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/2-ultim_polizas.xlsx',
            #    sheet_name='ultim_polizas')

            # delete temp dataframe
            del [[
                frame, frame_2, tria_frame, unique_month, factors_60,
                copy_frame
            ]]

            ################################################################################## End Triangulo Polizas ##############################################################################
            # %%

            #######################################################################################################################################################################################
            ################################################################################## Triangulo Porcentajes ##############################################################################
            #######################################################################################################################################################################################

            porcen_all = ultim_polizas.copy()

            # Rename Columns
            cols = porcen_all.columns.tolist()
            porcen_all.columns = cols[:1] + [
                'ep%i' % i for i in range(0,
                                          len(cols[2:]) + 1)
            ]
            ultim_porc = porcen_all[['MONTH', 'ep0']]

            # limit of columns range
            number_cols = ultim_polizas.shape[1] - 1

            months = range(1, number_cols)
            k = 1
            for month in months:
                colname = 'ep%d' % month  # ;print('colname::',colname)
                prev_colname = 'ep%d' % (
                    month - k)  # ;print('prev_colname::',prev_colname)
                ultim_porc[
                    colname] = porcen_all[colname] / porcen_all[prev_colname]
                k += 1

            ultim_porc = ultim_porc.replace(np.inf, np.nan).fillna(0)
            #ultim_porc.to_excel(
            #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/3-ultim_porc.xlsx',
            #    sheet_name='ultim_porc')

            # delete temp dataframe
            del [[porcen_all]]

            #######################################################################################################################################################################################
            ################################################################################# % Persistencia mes a mes por año de venta############################################################
            #######################################################################################################################################################################################

            ultim_v_pre = pd.DataFrame(
                (ultim_porc['MONTH'].astype(str).str[:4]).unique())
            ultim_v_pre.columns = ['MONTH']

            # limit of columns range
            number_cols2 = ultim_porc.shape[1] - 1

            months = range(1, number_cols2)
            k = 1

            for month in months:
                colname = 'ep%d' % month  # ;print('colname::',colname)
                prev_colname = 'ep%d' % (
                    month - k)  # ;print('prev_colname::',prev_colname)
                colname2 = 'ep%d' % month  # ;print('colname2::',colname2)

                def my_func2(row2):
                    # Suma-Producto / Suma
                    d = ((row2[prev_colname] *
                          row2[colname]).sum()) / np.maximum(
                              ((row2[prev_colname]).sum()),
                              0.00000000001)  # ;print('d:', d)
                    return pd.Series({colname2: d})

                result = ultim_porc.groupby(
                    (ultim_porc['MONTH'].astype(str).str[:4]
                     )).apply(my_func2).reset_index()
                ultim_persis = pd.merge(ultim_v_pre,
                                        result,
                                        how='left',
                                        on=['MONTH'])
                ultim_v_pre = ultim_persis
                k += 1

            ####################################################################################################
            # Calculate Column ep0
            def my_func0(row_0):
                colname = 'ep0'
                d = (row_0[colname].sum()) / np.maximum(
                    (row_0[colname].sum()), 0.00000000001)
                return pd.Series({colname: d})

            result_0 = ultim_porc.groupby(
                (ultim_porc['MONTH'].astype(str).str[:4]
                 )).apply(my_func0).reset_index()
            ultim_persis = pd.merge(ultim_persis,
                                    result_0,
                                    how='left',
                                    on=['MONTH'])
            # Move position ep0
            ep0 = ultim_persis['ep0']
            ultim_persis.drop(labels=['ep0'], axis=1, inplace=True)
            ultim_persis.insert(1, 'ep0', ep0)
            ####################################################################################################

            ultim_persis.rename(columns={'MONTH': 'ANUAL'}, inplace=True)

            # mean column ep0
            mean_ep0 = ultim_persis['ep0'].mean()

            # Calcular Totals Persistencia mes a mes por año de venta
            ultim_persis.loc[len(ultim_persis)] = [
                ((ultim_porc['ep0'] * ultim_porc[col]).sum()) /
                ((ultim_porc['ep0']).sum()) for col in ultim_porc.columns
            ]

            # Change values Total and ep0
            ultim_persis.at[ultim_persis.index[-1],
                            ['ANUAL', 'ep0']] = ['Total', mean_ep0]

            #ultim_persis.to_excel(
            #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/4-ultim_persis.xlsx',
            #    sheet_name='ultim_persis')

            del [[ultim_polizas, ultim_v_pre, result]]

            #######################################################################################################################################################################################
            ################################################################################# Tasa de Caida Mensual############################################################
            #######################################################################################################################################################################################

            ultim_tasa = pd.DataFrame(ultim_persis['ANUAL'])

            # limit of columns range
            number_cols3 = ultim_persis.shape[1] - 1

            months = range(0, number_cols3)

            k = 1

            for month in months:
                colname = 'ep%d' % month  # ;print('columns:',colname)
                prev_colname = k - 1
                ultim_tasa[colname] = (-np.log(ultim_persis[colname]) /
                                       prev_colname)
                k += 1

            ultim_tasa = ultim_tasa.replace(np.inf, np.nan).fillna(0)
            #ultim_tasa.to_excel(
            #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/5-ultim_tasa.xlsx',
            #    sheet_name='ultim_tasa')

            del [[ultim_porc]]

            #######################################################################################################################################################################################
            ################################################################################# % Persistencia meses trimestrales por año de venta ############################################################
            #######################################################################################################################################################################################

            list_C = list(np.arange(1, len(ultim_persis), 1))
            list_C.insert(0, 0)

            list_Q = list(np.arange(1, 46, 3))
            list_Q.insert(0, 0)

            # Quarters
            ultim_persis_Q = ultim_persis.iloc[list_C, list_Q]

            ultim_persis_Q['Duration'] = ultim_persis.iloc[:, 1:62].sum(axis=1)

            # ultim_persis_Q['Duration']   = ultim_persis_Q['Duration'] / 100

            ultim_persis_Q['Lapse_rate'] = ultim_tasa.iloc[:,
                                                           2:62].mean(axis=1)

            #ultim_persis_Q.to_excel(
            #    'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/6-ultim_persis_Q.xlsx',
            #    sheet_name='ultim_persis_Q')

            resultado = ultim_persis_Q

            del [[ultim_tasa, ultim_persis]]

        else:
            resultado = pd.DataFrame(
                [[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]],
                columns=[
                    'ANUAL', 'ep0', 'ep3', 'ep6', 'ep9', 'ep12', 'ep15',
                    'ep18', 'ep21', 'ep24', 'ep27', 'ep30', 'ep33', 'ep36',
                    'ep39', 'ep42', 'Duration', 'Lapse_rate'
                ])

    else:
        resultado = pd.DataFrame(
            [[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]],
            columns=[
                'ANUAL', 'ep0', 'ep3', 'ep6', 'ep9', 'ep12', 'ep15', 'ep18',
                'ep21', 'ep24', 'ep27', 'ep30', 'ep33', 'ep36', 'ep39', 'ep42',
                'Duration', 'Lapse_rate'
            ])
    print('02-OK')
    #resultado.to_excel('C:/Users/b89591/Desktop/test01.xlsx', sheet_name='test01')

    #############################################################################################################################################################

    # Data Tables
    data_table_1 = resultado.copy()

    decimals = 4
    data_table_1['ep0'] = data_table_1['ep0'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep3'] = data_table_1['ep3'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep6'] = data_table_1['ep6'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep9'] = data_table_1['ep9'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep12'] = data_table_1['ep12'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep15'] = data_table_1['ep15'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep18'] = data_table_1['ep18'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep21'] = data_table_1['ep21'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep24'] = data_table_1['ep24'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep27'] = data_table_1['ep27'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep30'] = data_table_1['ep30'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep33'] = data_table_1['ep33'].apply(
        lambda x: round(x, decimals))
    data_table_1['ep36'] = data_table_1['ep36'].apply(
        lambda x: round(x, decimals))
    data_table_1['Duration'] = data_table_1['Duration'].apply(
        lambda x: round(x, decimals))
    data_table_1['Lapse_rate'] = data_table_1['Lapse_rate'].apply(
        lambda x: round(x, decimals))

    data_table_01 = []
    for i, row in data_table_1.iterrows():
        ep0 = '{:.2%}'.format(row['ep0'])
        ep3 = '{:.2%}'.format(row['ep3'])
        ep6 = '{:.2%}'.format(row['ep6'])
        ep9 = '{:.2%}'.format(row['ep9'])
        ep12 = '{:.2%}'.format(row['ep12'])
        ep15 = '{:.2%}'.format(row['ep15'])
        ep18 = '{:.2%}'.format(row['ep18'])
        ep21 = '{:.2%}'.format(row['ep21'])
        ep24 = '{:.2%}'.format(row['ep24'])
        ep27 = '{:.2%}'.format(row['ep27'])
        ep30 = '{:.2%}'.format(row['ep30'])
        ep33 = '{:.2%}'.format(row['ep33'])
        ep36 = '{:.2%}'.format(row['ep36'])
        Lapse_rate = '{:.2%}'.format(row['Lapse_rate'])
        Duration = '{0:,.2f}'.format(row['Duration'])

        data_table_01.append({
            "ANUAL": str(row['ANUAL']),
            "ep0": ep0,
            "ep3": ep3,
            "ep6": ep6,
            "ep9": ep9,
            "ep12": ep12,
            "ep15": ep15,
            "ep18": ep18,
            "ep21": ep21,
            "ep24": ep24,
            "ep27": ep27,
            "ep30": ep30,
            "ep33": ep33,
            "ep36": ep36,
            "Duration": Duration,
            "Lapse_rate": Lapse_rate
        })

    # ***************************************************************************************************#
    # Data Graph 1
    graph_1 = resultado.copy()

    # traspuesta
    graph_1 = graph_1.set_index('ANUAL').T.reset_index()
    graph_1 = graph_1.drop(['index'], axis=1)

    if '2015' not in graph_1:
        graph_1['2015'] = 0

    if '2016' not in graph_1:
        graph_1['2016'] = 0

    if '2017' not in graph_1:
        graph_1['2017'] = 0

    if '2018' not in graph_1:
        graph_1['2018'] = 0

    graph_1['MONTH'] = graph_1.index
    graph_1['MONTH'] = graph_1['MONTH'] * 3
    # reorder
    graph_1 = graph_1[['MONTH', '2015', '2016', '2017', '2018', 'Total']]
    #print('data_trans01:', data_trans)
    graph_1.columns = graph_1.columns.astype(str).str.replace(r"[2]", "_2")
    # data_trans.rename(columns={'2015':'_2015', '2016':'_2016', '2017':'_2017', '2018':'_2018' }, inplace=True)
    list_C = list(np.arange(0, (len(graph_1) - 2), 1))
    list_Q = list(np.arange(0, (len(graph_1.columns))))
    # Quarters
    graph_1 = graph_1.iloc[list_C, list_Q]
    #graph_1.to_excel('C:/Users/b89591/Desktop/data_trans.xlsx', sheet_name='data_trans', index = None)

    # Format Graph AmCharts (Multiplica *100)
    graph_1['_2015'] = graph_1['_2015'] * 100
    graph_1['_2016'] = graph_1['_2016'] * 100
    graph_1['_2017'] = graph_1['_2017'] * 100
    graph_1['_2018'] = graph_1['_2018'] * 100
    graph_1['Total'] = graph_1['Total'] * 100

    #decimals = 3
    #graph_3['q_x'] = graph_3['q_x'].apply(lambda x: round(x, decimals))

    graphs_1 = []
    for i, row in graph_1.iterrows():
        MONTH = '{0:,.0f}'.format(row['MONTH'])
        graphs_1.append({
            "MONTH": MONTH,
            "2015": str(row['_2015']),
            "2016": str(row['_2016']),
            "2017": str(row['_2017']),
            "2018": str(row['_2018']),
            "Total": str(row['Total'])
        })

    ################################################################################################
    # Data Graph 2
    graph_2 = resultado.copy()
    graph_2 = graph_2[['ANUAL', 'Duration', 'Lapse_rate']]
    # data_rate = data_rate[1:(len(data_rate)-1)]
    graph_2 = graph_2[:-2]

    #Remove last column
    #graph_2 = graph_2.iloc[:, :-1]
    #graph_2['ANUAL'] = graph_2['ANUAL'].astype(int)
    #graph_2['ANUAL_STR'] = graph_2['ANUAL'].astype(str)

    # Format Graph AmCharts (Multiplica *100)
    graph_2['Lapse_rate'] = graph_2['Lapse_rate'] * 100

    # decimals = 3
    # graph_3['q_x'] = graph_3['q_x'].apply(lambda x: round(x, decimals))

    graphs_2 = []
    for i, row in graph_2.iterrows():
        Duration = '{0:,.1f}'.format(row['Duration'])
        graphs_2.append({
            "ANUAL": str(row['ANUAL']),
            "Duration": Duration,
            "Lapse_rate": str(row['Lapse_rate']),
        })

    #print(graphs_2)

    # UNICOS SELECT
    socios = list(df_unicos['NOM_SOCIO'].unique())
    socios.sort()
    # print('socios', socios)

    # linea = list(df_unicos['LINEA'].unique());
    # linea.sort();

    linea_negocio = list(df_unicos['LINEA_NEGOCIO'].unique())
    # linea_negocio.sort();
    # print('linea_negocio', linea_negocio)

    periodo = list(df_unicos['PERIODO'].unique())
    periodo.sort()
    # print('periodo', periodo)

    canal = list(df_unicos['CANAL'].unique())
    canal.sort()
    # print('canal', canal)

    df_unicos['COD_PROD'] = df_unicos['COD_PROD'].astype(str)
    productos = list(df_unicos['COD_PROD'].unique())
    productos.sort(key=int)

    # tipo = list(df_unicos['TIPO'].unique());
    # tipo.sort();

    cnxn.close()

    return JsonResponse([
        socios, linea_negocio, periodo, canal, productos, data_table_01,
        graphs_1, graphs_2
    ],
                        safe=False)
예제 #52
0
    logging.warning('warning message')
    logging.error('error message')
    logging.critical('critical message')
    logging.basicConfig(
        level=logging.DEBUG,  # 控制台打印的日志级别
        filename='new.log',
        filemode='a',  ##模式,有w和a,w就是写模式,每次都会重新写日志,覆盖之前的日志,a是追加模式,默认如果不写的话,就是追加模式
        format=
        '%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
    )

    # 数据库参数设置
    options_barra_database = {
        'user': '******',
        'password': '******',
        'dsn': cx_Oracle.makedsn('172.16.100.188', '1522', 'markdb')
    }
    connect_barra = ConnectOracle(options_barra_database['user'],
                                  options_barra_database['password'],
                                  options_barra_database['dsn'])
    options_xrisk_database = {
        'user': '******',
        'password': '******',
        'dsn': cx_Oracle.makedsn('172.16.100.230', '1521', 'xrisk')
    }
    connect_xrisk = ConnectOracle(options_xrisk_database['user'],
                                  options_xrisk_database['password'],
                                  options_xrisk_database['dsn'])
    options_winddb_datebase = {
        'server': '172.16.100.7',
        'user': '******',
예제 #53
0
def __get_project(region: str, data_source: str):
    """ the main function, include three part: 1st is getting objects from oracle; 2nd is getting flag and coordinate
            by the api of amap, 3rd is update data and compute the distances of name and address' coordinates

    :param:
        region {str} - the name of region
        data_source {str} - the code of region
    :return:
        any
    """
    __connect = {
        "host": "172.29.251.71",
        "port": "1521",
        "sid": "orcl",
        "account": "assessprice",
        "psw": "assessprice",
    }
    __region_name = region
    __region_code = data_source
    try:
        __dsn = oracle.makedsn(__connect["host"], __connect["port"],
                               __connect["sid"])
        __conn = oracle.connect(__connect["account"], __connect["psw"], __dsn)
        __cursor = __conn.cursor()
        __cursor.prepare(
            "SELECT ID, PROJECT_NAME, GD_NAME, LJ_NAME, DISTRICT_ADD, "
            "REGION, ADD_LNG, ADD_LAT, NAME_LNG, NAME_LAT "
            "FROM ASSESSPRICE.TMP_LJ_DISTRICT_REL "
            "WHERE REGION =:region")
        __cursor.execute(None, {"region": __region_name})
        __data = __rows_as_dicts(__cursor)
        __m = len(__data)
        __n = 0

        for __d in __data:
            __r = amp_class.or_proj(__d)
            __addr = "%s%s" % (__region_name, __r.ds_addr)
            __flag, __p_lng, __p_lat = __addr_2_coor(__addr, __region_code)
            if __flag == '1':
                __update_date = datetime.datetime.now().strftime(
                    '%Y-%m-%d %H:%M:%S')
                __fid = __r.r_id
                __distance = coor_dis(__r.name_lng, __r.name_lat, __r.addr_lng,
                                      __r.addr_lat)
                print(__r.r_id, __r.pj_name, __r.ds_addr, __r.addr_lng,
                      __r.addr_lat, __p_lng, __p_lat, __distance)
                print("\n")
                __cursor.prepare(
                    "UPDATE ASSESSPRICE.TMP_LJ_DISTRICT_REL "
                    "SET ADD_LNG = :addr_lng, ADD_LAT = :addr_lat, DISTANCE = :distance, "
                    "SYS_UPDATE_TIME = to_date(:update_time,'yyyy-mm-dd hh24:mi:ss')"
                    "WHERE ID = :fid")
                __cursor.execute(
                    None, {
                        "addr_lng": __p_lng,
                        "addr_lat": __p_lat,
                        "distance": __distance,
                        "update_time": __update_date,
                        "fid": __fid
                    })
                __conn.commit()
                __n = __n + 1
                if __n == 1000:
                    time.sleep(20)
                    print("等待20s!")
                else:
                    time.sleep(0.2)

        __cursor.close()
        __conn.close()
        print("执行完成!%s 共 %s 条数据, 更新 %s 条!" % (__region_name, __m, __n))
    except Exception as e:
        raise e
    finally:
        pass
예제 #54
0
파일: oracle.py 프로젝트: jack161641/credit
# -*- coding:utf-8 -*-

import cx_Oracle
import os
import sys
from influxdb import InfluxDBClient

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
#os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
#os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
reload(sys)
sys.setdefaultencoding('utf8')

#client = InfluxDBClient('localhost',8086,'root',",")
cardno_entry = '$45CA30662C7F368F11D6DCA7BDF2F1901A48A0E6792BE92F'
dsn_tns = cx_Oracle.makedsn('10.214.2.71', 1530, 'efsdb')
db = cx_Oracle.connect('devro', 'SLI8fE05', dsn_tns)
cursor = db.cursor()
cursor.execute("""select 
t.cardno ,t.name ,t.pair_similarity ,h.acct_id ,
h.req_time ,
y.file_name||'.jpg' ,
replace(y.file_path,'/opt/netdata/Creditdata','/u01/data/sata2/pics_cd') 
from cpdb_ds.t_ds_yitu_auth_result@kqcd_cpdb_ds t ,cpdb_app.t_sys_req_header@kqcd_cpdb_app h,
cpdb_app.t_sys_file_factory@kqcd_cpdb_app y
where t.cardno = '%s'
and t.photo_id is not null
and t.trade_id = h.trade_id and y.id = t.photo_id
and h.acct_id in ('coc_act_app_user','coc_kyh_user','coc_zyt_user','coc_kyh2_user')
and t.pair_similarity >= 66
union all 
SEND_MESSAGE_URL = 'http://58.87.84.167:5000/send-message'

FIND_ALL_URL = 'https://op.stariboss.com/message-center-service/send-message/all'

GROUP_NAME = '新BOSS告警'

HOST = 'starboss.csxbifqn7nwb.eu-west-1.rds.amazonaws.com'
PORT = '1521'
SID = 'STARBOSS'
USER = '******'
PASSWORD = '******'

SEND_NOT_RECEIVE_SQL = 'SELECT company_id,count(id) FROM BOSS_PMS_PARTITION.EQUIPMENT_INSTRUCTION ei where ei.STATUS = 1 and ei.DEAL_STATUS in (4, 6) and ei.LAST_SEND_INSTANT < sysdate - 1/24 GROUP BY company_id'

dsn = cx_Oracle.makedsn(HOST, PORT, SID)
dsn_tns = dsn.replace('SID', 'SERVICE_NAME')


def monitor():
    try:
        conn = cx_Oracle.connect(USER, PASSWORD, dsn_tns)
        handle_send_not_receive_message(conn)
        conn.close()
    except Exception as e:
        logging.exception(e)


def handle_send_not_receive_message(conn):
    not_receive_cursor = conn.cursor()
    not_receive_cursor.execute(SEND_NOT_RECEIVE_SQL)
예제 #56
0
파일: common.py 프로젝트: tosmart01/fastsql
 def make_dsn(self, dsn):
     if "DESCRIPTION" in dsn.upper():
         return dsn
     else:
         _dsn = re.search(r"(.+):(\d+)/(\w+)", dsn)
         return cx_Oracle.makedsn(*_dsn.groups())
예제 #57
0
def process_data(iterator):
    sql_insert = generateInsertSQL("ITR23.RLT_ESCLT_DVC_D",
                                   "./RLT_ESCLT_DVC_D.conf")
    dsn = cx_Oracle.makedsn(info["host"], info["port"], info["sid"])
    con = cx_Oracle.connect(info["username"], info["passward"], dsn)
    cursor = con.cursor()

    for oneArray in iterator:
        oneArray = list(eval(oneArray))
        for i in range(len(oneArray)):
            afterProcessArray = []
            oneTuple = oneArray[i]
            delElement = [13, 12, 11, 9]
            for i in delElement:
                del oneTuple[i]

            checkDataMd5 = ''
            dvc_id = str(oneTuple[0])
            # operating a tuple of a list
            for j in range(len(oneTuple)):
                oneElement = oneTuple[j]
                if oneElement != None:

                    if type(oneElement) is str:
                        proData = oneElement.strip()
                        if proData == "t" and j == 8 or j == 14:
                            proData = "y"
                        if proData == "f" and j == 8 or j == 14:
                            proData = "n"
                        afterProcessArray.append(proData)
                        checkDataMd5 = checkDataMd5 + proData
                    else:
                        afterProcessArray.append(oneElement)
                        checkDataMd5 = checkDataMd5 + str(oneElement)

                else:
                    afterProcessArray.append('NULL')
            #check if the md5 are the same or existence
            md5_str = hashlib.md5(checkDataMd5).hexdigest()
            operation = []
            operation.append("get")
            operation.append(dvc_id)
            rs = RedisOp(operation)
            dvc_id = 'RLT_ESCLT_DVC_D' + dvc_id
            if rs[0]:
                r_s = list(eval(rs[1]))
                if r_s[1] != md5_str:
                    r_s[1] = md5_str
                    afterProcessArray.insert(0, r_s[0])
                    r = tuple(afterProcessArray)
                    #update the data int the database
                    update_sql = generateUpdateSql("ITR23.RLT_ESCLT_DVC_D",
                                                   "RLT_ESCLT_DVC_D.conf",
                                                   ["ESCLT_DVC_D_KY", r_s[0]])
                    res = cursor.execute(update_sql % r)
                    con.commit()
                    RedisOp(["set", dvc_id, r_s])
            else:
                res = RedisOp(["get", "ESCLT_DVC_D_count"])
                r_s = []
                if res[0]:
                    count = int(res[1]) + 1
                    r_s.append(count)
                    #inster data to the database
                    afterProcessArray.insert(0, count)
                    r = tuple(afterProcessArray)
                    sql_v = sql_insert % r
                    res = cursor.execute((sql_insert % r))
                    con.commit()
                    RedisOp(["set", "ESCLT_DVC_D_count", count])
                else:
                    r_s.append(1)
                    #inster data to the database
                    afterProcessArray.insert(0, 1)
                    r = tuple(afterProcessArray)
                    sql_v = sql_insert % r
                    res = cursor.execute(sql_v)
                    con.commit()
                    RedisOp(["set", "ESCLT_DVC_D_count", 1])
                r_s.append(md5_str)
                RedisOp(["set", dvc_id, r_s])
예제 #58
0
 def initOracleConnect(self):
     oracle_tns = cx_Oracle.makedsn('XXX.XXX.XXX.XXX', 1521, 'XX')
     if self.connCnt == 0:
         self.connectObj = cx_Oracle.connect('oracleUserName', 'password',
                                             oracle_tns)
         self.connCnt += 1
예제 #59
0
"""
Create Time: 2020/3/27 9:56
Author: FengkaiXiao
"""

import cx_Oracle
import random

NUM = 0
'''
方法三:配置监听并连接
'''
tns = cx_Oracle.makedsn('192.168.125.135', 1521, 'helowin')
db = cx_Oracle.connect('system', 'system', tns)
cursor = db.cursor()

for i in range(100):
    Ageint = 20 + random.randint(0, 40)
    IMCONME = 10000 + random.randint(0, 1000)
    NUM += 1
    NAME = 'name' + str(NUM)
    Nation = 'CHINA', 'JAPAN', 'UK', 'USA', 'GERMAN', 'FRANCE', 'KOREA', 'BRAZIL'
    Lastname = 'SMTH', 'KOBE', 'MICHEAEL', 'JASON', 'F**K', 'STUPID'
    Hobby = 'FOOTBALL', 'BASKETBALL', 'VOLLEBALL'
    COME = random.choice(Nation)
    LASTNAME = random.choice(Lastname)
    HOBBY = random.choice(Hobby)
    sql = "INSERT INTO XFK_ORACLE_10.XFK10(id, first_name, middle_name, last_name, come_from, age, income, hobby) VALUES ('%d', '%s','%s','%s', '%s', '%d', '%d' ,'%s')" % (
        NUM, NAME, COME, LASTNAME, COME, Ageint, IMCONME, HOBBY)
    cursor.execute(sql)
    db.commit()
예제 #60
0
파일: ooooo.py 프로젝트: 60092389/crawler
from selenium import webdriver
import re
from time import sleep
import pymongo
from selenium.common.exceptions import NoSuchElementException
import os
import cx_Oracle  # @UnresolvedImport
from konlpy.tag import Twitter

#몽고디비 접속
conn = pymongo.MongoClient('192.168.1.28', 27017)
db = conn.gaduda
collection = db.rep_test

#오라클 접속
ora_dsn = cx_Oracle.makedsn('192.168.1.20', 1521, 'orcl')
ora_conn = cx_Oracle.connect('ora_user', '1234', ora_dsn)
ora_cursor = ora_conn.cursor()

#driver = webdriver.Chrome('D:\chromedriver_win32\chromedriver')
driver = webdriver.PhantomJS(
    executable_path=r'C:\phantomjs-2.1.1-windows\bin\phantomjs')

#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1322253347&trTypeCd=20&trCtgrNo=585021&lCtgrNo=1001364&mCtgrNo=1002273'
#site= 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=96531135&trTypeCd=20&trCtgrNo=1002273'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1183869688&trTypeCd=21&trCtgrNo=1009053'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1367968916&trTypeCd=20&trCtgrNo=1002276'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1335741053&trTypeCd=21&trCtgrNo=1002276'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1319927303&trTypeCd=20&trCtgrNo=1002276'
"""
site_arr = ['http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1319927303&trTypeCd=20&trCtgrNo=1002276',