def prepare(self): """ 迁移数据的准备工作,更新连接参数后进行数据库连接;读取进度记录 :return: """ # 加载配置数据,并创建连接 with open('config.yaml') as file: self.config = yaml.safe_load(file) # db_url=f'{self.config["user"]}/{self.config["psw"]}@{self.config["host"]}:{self.config["port"]}/{self.config["db"]}' self.session_pool = cx_Oracle.SessionPool( user=self.config["user"], password=self.config["psw"], dsn=f'{self.config["host"]}/{self.config["db"]}', min=1, max=10, increment=1) # self.db = cx_Oracle.connect(db_url) # self.cursor = self.db.cursor() # 读取记录 if os.path.exists('./record.yaml'): with open('./record.yaml') as file: record = yaml.safe_load(file) if record is not None: self.record = record else: self.record = {} # 读取最早时间记录 if os.path.exists('ancient.yaml'): with open('ancient.yaml') as f: self.ancient = yaml.safe_load(f)
def connection_pool(self): """ this function try to connect to oracle instance and create a pool of connections based on config.cfg file :params :returns returning a pool of connections """ try: dsn_tns = cx_Oracle.makedsn(config.oracle_host, config.oracle_port, config.oracle_db) pool = cx_Oracle.SessionPool(config.oracle_username, config.oracle_password, dsn_tns, min=int(config.oracle_pool_size), max=int(config.oracle_pool_size), increment=0, encoding="UTF-8", threaded=True) except cx_Oracle as error: connection_logger.exception(error) return None return pool
def __init__(self, user=None, password=None, dsn=None, *args, **kwargs): self.pool = cx_Oracle.SessionPool(user, password, dsn, min=1, max=10, increment=1)
def start_pool(): # Generally a fixed-size pool is recommended, i.e. pool_min=pool_max. # Here the pool contains 4 connections, which is fine for 4 concurrent # users. # # The "get mode" is chosen so that if all connections are already in use, any # subsequent acquire() will wait for one to become available. pool_min = 4 pool_max = 4 pool_inc = 0 pool_gmd = cx_Oracle.SPOOL_ATTRVAL_WAIT print("Connecting to", db_info.db_endpoint) dsn_str = cx_Oracle.makedsn(db_info.db_endpoint, db_info.db_port, "ORCL") print(dsn_str) pool = cx_Oracle.SessionPool(user=db_info.db_username, password=db_info.db_pw, dsn=dsn_str, min=pool_min, max=pool_max, increment=pool_inc, threaded=True, getmode=pool_gmd, sessionCallback=DB.init_session) return pool
def start_pool(): # Generally a fixed-size pool is recommended, i.e. pool_min=pool_max. # Here the pool contains 4 connections, which is fine for 4 conncurrent # users. # # The "get mode" is chosen so that if all connections are already in use, any # subsequent acquire() will wait for one to become available. pool_min = 4 pool_max = 4 pool_inc = 0 pool_gmd = cx_Oracle.SPOOL_ATTRVAL_WAIT print("Connecting to", os.environ.get("PYTHON_CONNECTSTRING")) # pool = cx_Oracle.SessionPool(user=os.environ.get("PYTHON_USERNAME"), # password=os.environ.get("PYTHON_PASSWORD"), # dsn=os.environ.get("PYTHON_CONNECTSTRING"), pool = cx_Oracle.SessionPool(user="******", password="******", dsn="172.31.49.54/xepdb1", min=pool_min, max=pool_max, increment=pool_inc, threaded=True, getmode=pool_gmd, sessionCallback=init_session) return pool
def start_pool(): # Generally a fixed-size pool is recommended, i.e. pool_min=pool_max. # Here the pool contains 4 connections, which is fine for 4 conncurrent # users. # # The "get mode" is chosen so that if all connections are already in use, any # subsequent acquire() will wait for one to become available. # # Note there is no explicit 'close cursor' or 'close connection'. At the # end-of-scope when init_session() finishes, the cursor and connection will be # closed automatically. In real apps with a bigger code base, you will want to # close each connection as early as possible so another web request can use it. pool_min = 4 pool_max = 4 pool_inc = 0 pool_gmd = cx_Oracle.SPOOL_ATTRVAL_WAIT username = db_config.user password = db_config.pw connect_string = db_config.connect_string print("Connecting to", connect_string) global pool pool = cx_Oracle.SessionPool(user=username, password=password, dsn=connect_string, min=pool_min, max=pool_max, increment=pool_inc, threaded=True, getmode=pool_gmd, sessionCallback=init_session)
def get_session_pool(): sql_connection = config_get('database', 'default') sql_connection = sql_connection.replace("oracle://", "") user_pass, tns = sql_connection.split('@') user, passwd = user_pass.split(':') db_pool = cx_Oracle.SessionPool(user, passwd, tns, min=12, max=20, increment=1) return db_pool
def _create_pool(self): """create the cx_oracle connection pool from the Flask Config Environment :return: an instance of the OCI Session Pool """ # this uses the builtin session / connection pooling provided by # the Oracle OCI driver # setting threaded =True wraps the underlying calls in a Mutex # so we don't have to that here def InitSession(conn, requestedTag): cursor = conn.cursor() cursor.execute("alter session set TIME_ZONE = 'America/Vancouver'") return cx_Oracle.SessionPool( user=current_app.config.get('NRO_USER'), password=current_app.config.get('NRO_PASSWORD'), dsn='{0}:{1}/{2}'.format(current_app.config.get('NRO_HOST'), current_app.config.get('NRO_PORT'), current_app.config.get('NRO_DB_NAME')), min=1, max=10, increment=1, connectiontype=cx_Oracle.Connection, threaded=True, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, waitTimeout=1500, timeout=3600, sessionCallback=InitSession)
class BaseTasksProvider(object): # Retreive DB settings ORACLE_USERNAME = settings.local.dbaccess['default']['USER'] ORACLE_PWD = settings.local.dbaccess['default']['PASSWORD'] ORACLE_SNAME = settings.local.dbaccess['default']['NAME'] ORACLE_CONNECTION_URL = "(DESCRIPTION=(ADDRESS= (PROTOCOL=TCP) (HOST=adcr-s.cern.ch) (PORT=10121) ) (LOAD_BALANCE=on)" \ "(ENABLE=BROKEN)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME="+ORACLE_SNAME+".cern.ch)))" pool = cx_Oracle.SessionPool(ORACLE_USERNAME, ORACLE_PWD, ORACLE_CONNECTION_URL, 2, 3, 1, threaded=True) lock = threading.Semaphore(1) # We allow to pileup not more than 2 consecutive calls def getNumberOfActiveDBSessions(self): totalSessionCount = -1 try: db = self.pool.acquire() cursor = db.cursor() cursor.execute("SELECT SUM(NUM_ACTIVE_SESS), SUM(NUM_SESS) FROM ATLAS_DBA.COUNT_PANDAMON_SESSIONS") for row in cursor: totalActiveSessionCount = row[0] totalSessionCount = row[1] break cursor.close() except: pass return totalSessionCount def logActivity(self): pass def processPayload(self): pass def execute(self): if self.lock.acquire(blocking=False): self.processPayload() self.lock.release()
def _create_pool(): """Create the cx_oracle connection pool from the Flask Config Environment. :return: an instance of the OCI Session Pool """ # this uses the builtin session / connection pooling provided by # the Oracle OCI driver # setting threaded =True wraps the underlying calls in a Mutex # so we don't have to that here def init_session(conn, *args): # pylint: disable=unused-argument; Extra var being passed with call cursor = conn.cursor() cursor.execute("alter session set TIME_ZONE = 'America/Vancouver'") return cx_Oracle.SessionPool( user=current_app.config.get('ORACLE_USER'), # pylint:disable=c-extension-no-member password=current_app.config.get('ORACLE_PASSWORD'), dsn='{0}:{1}/{2}'.format(current_app.config.get('ORACLE_HOST'), current_app.config.get('ORACLE_PORT'), current_app.config.get('ORACLE_DB_NAME')), min=1, max=10, increment=1, connectiontype=cx_Oracle.Connection, # pylint:disable=c-extension-no-member threaded=True, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, # pylint:disable=c-extension-no-member waitTimeout=1500, timeout=3600, sessionCallback=init_session, encoding='UTF-8', nencoding='UTF-8')
def __init__(self, config): try: self.DataName = config['datatype'] del config['datatype'] except: self.DataName = 'MYSQL' if self.DataName == 'MYSQL': try: self.pool = mysql.connector.pooling.MySQLConnectionPool( **config) self.cnx = self.cur = None except mysql.connector.Error as err: # 这里需要记录操作日志 logging.debug(err.msg) self.cnx = None raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'POSTGRESQL': try: self.pool = SimpleConnectionPool(**config) except: raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'ORACLE': try: if config['NLS_LANG']: os.environ['NLS_LANG'] = config['NLS_LANG'] del config['NLS_LANG'] except: pass try: self.pool = cx_Oracle.SessionPool(**config) except: raise BaseError(701) # 与数据库连接异常
def __connect_db(self): if self.text_db_value.get() != '': try: self.__get_db_info(self.db_info) dsn = cxo.makedsn(self.db_info.get("host"), self.db_info.get("port"), self.db_info.get("server_name")) self.pool = cxo.SessionPool(self.db_info.get("user_name"), self.db_info.get("user_password"), dsn, min=1, max=500, increment=1, threaded=True) msg.showinfo(title='connect succedd', message='connect succeed', parent=self) self.destroy() a = ct.Content(self.pool, self.file_name) except: msg.showerror( title='connection error', message='error when try to connect the pool :\n' + traceback.format_exc(), parent=self) else: msg.showerror(title='please choose one database', message='please choose one database', parent=self)
def get_pool(user=None, password=None, **kwargs): if user is None: user = get_main_user() if password is None: password = get_main_password() return oracledb.SessionPool(user, password, get_connect_string(), **kwargs)
def sessionPool(self, username, password, connectString, min=5, max=50, increment=5): """ `BETA` This will return a pool of connections. `username:` The user name that will be used to make the connections to the database `password:` The password of the user for connections `connectString:` The connection string. Valid formats are <host>:<port>/<service_name> and <host>:<port>:<sid> `min:` The number of connections to start and always keep in the pool `max:` The maximum number of connections that can be created in the session pool `increment:` How many connections should be created when all connections in the pool are busy. `Returns:` SessionPool object that can be used to get connections """ connectDsn = self.__buildConnectString(connectString=connectString) pool = cx_Oracle.SessionPool(user=username, password=password, database=connectDsn, min=min, max=max, increment=increment) return pool
def connect(): config = ConfigParser() config.read("db.config") db_cfg = config['oracle.sql'] pool = cx_Oracle.SessionPool(db_cfg['usr'], db_cfg['pwrd'], db_cfg['conn'], min = 2, max = 5, increment = 1, threaded = True) return pool
def get_conn_pool(self): if self.__pool is None: with self.__thread_lock: if self.__pool is None: self.__pool = cx_Oracle.SessionPool( *self.args, **self.kwargs) self.__conn = self.__pool.acquire() return self.__conn
def __init__(self, mode=None): self._mode = mode if self._mode == 'pool': self.db_pool = cx_Oracle.SessionPool(Config.get_secret('ORA_USERNAME'), Config.get_secret('ORA_PASSWORD'), Config.get_secret('CONNECTION_STRING'), encoding="UTF-8", nencoding="UTF-8", # Pour que les accents soient bien gérés min=0, max=20, increment=1)
def _get_pool(self, *args, **kwargs): """ Get the connection pool or create it if it doesnt exist Add thread lock to prevent server initial heavy load creating multiple pools Let's create session pool with five initial sessions (min=5), limit maximum session to 10, increment # of sessions by 1, connectiontype = [not documented?] threaded = False (by default, search cx_Oracle docs for OCI_THREADED) getmode = [cx_Oracle.SPOOL_ATTRVAL_NOWAIT | cx_Oracle.SPOOL_ATTRVAL_WAIT | cx_Oracle.SPOOL_ATTRVAL_FORCEGET] homogeneous = True (according to cx_Oracle docs, if pool is not homogeneous then different authentication can be used for each connection "pulled" from the pool) WARNING The threaded argument is expected to be a boolean expression which indicates whether or not Oracle should use the mode OCI_THREADED to wrap accesses to connections with a mutex. Doing so in single threaded applications imposes a performance penalty of about 10-15% which is why the default is False. """ pool_name = '_pool_%s' % getattr(self, 'alias', 'common') if not hasattr(self.__class__, pool_name): lock = thread.allocate_lock() lock.acquire() try: pool = cx_Oracle.SessionPool( user=self.user, password=self.password, dsn=self.tns, min=CX_POOL_SESSION_MIN, max=CX_POOL_SESSION_MAX, increment=CX_POOL_SESSION_INCREMENT, connectiontype=cx_Oracle.Connection, threaded=CX_POOL_THREADED, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, homogeneous=True) except Exception as err: pool = None if pool: pool.timeout = CX_POOL_CONNECT_TIMEOUT setattr(self.__class__, pool_name, pool) else: msg = """ ### Database login failed or database not found ### """ raise self.Database_Error, ('%s') % (msg) lock.release() return getattr(self.__class__, pool_name)
def db_conn_pool(self): return cx_Oracle.SessionPool(user=self.user_name, password=self.passwd, dsn=self.db_tns(self.env), min=1, max=500, increment=1, encoding='UTF-8', nencoding='UTF-8')
def populate(): uri = get_connection_uri() db_pool = cx_Oracle.SessionPool(*uri, min=2, max=5, increment=1) conn = db_pool.acquire() cursor = conn.cursor() # data generation # -------------------------------- # countries datagen = countries_gen() for cd, nm in datagen: cursor.execute(q_insert['countries'], [cd, nm]) conn.commit() q = f"SELECT * FROM PUJLAB.COUNTRY" print(search_in_db(conn, q)) # cities dg = cities_gen() for c in dg: cursor.execute(q_insert['cities'], c) conn.commit() q = f"SELECT * FROM PUJLAB.CITY" print(search_in_db(conn, q)) # q = f"SELECT * FROM PUJLAB.COUNTRY # user dg = users_gen() for data in dg: print(data) cursor.execute(q_insert['user'], data) conn.commit() q = f'SELECT * FROM PUJLAB."USER"' users = search_in_db(conn, q) print(users) users = choices(users, k=1000) # network net_g = gen_network(users, conn) for msg in net_g: cursor.execute(q_insert['network'], msg) conn.commit() q = f'SELECT * FROM PUJLAB." SOCIAL_NETWORK"' print(search_in_db(conn, q)) # messages msg_g = msg_gen(users, conn) count = 0 for msg, hsh in msg_g: cursor.execute(q_insert['messages'], msg) if hsh: cursor.execute(q_insert['topic_hashtag'], hsh) conn.commit() count += 1 q = f"SELECT * FROM PUJLAB.MESSAGE" print(search_in_db(conn, q)) q = f"SELECT * FROM PUJLAB.TOPIC_HASHTAG" print(search_in_db(conn, q)) # topic_hashtag # -------------------------------- # connection delete db_pool.release(conn) db_pool.close()
def run(): global pool while True: if pool: time.sleep(10) continue logger.debug("Create Connection Pool Started") try: db_password = "" if vault_secret_ocid != "": reportDown(error.code) signer = oci.auth.signers.InstancePrincipalsSecurityTokenSigner( ) secrets_client = oci.secrets.SecretsClient( config={'region': region_id}, signer=signer) secret_bundle = secrets_client.get_secret_bundle( secret_id=vault_secret_ocid) logger.debug(secret_bundle) base64_bytes = secret_bundle.data.secret_bundle_content.content.encode( 'ascii') message_bytes = base64.b64decode(base64_bytes) db_password = message_bytes.decode('ascii') else: db_password = k8s_secret_dbpassword pool = cx_Oracle.SessionPool( db_user, db_password, db_connect_string, externalauth=False if db_password else True, encoding="UTF-8", min=db_connection_count, max=db_connection_count, increment=0, threaded=True, events=True, getmode=cx_Oracle.SPOOL_ATTRVAL_TIMEDWAIT, waitTimeout=10000) except cx_Oracle.DatabaseError as e: error, = e.args # ORA-12514: TNS:listener does not currently know of service requested in connect descriptor # ORA-12757: instance does not currently know of requested service # ORA-12541: TNS:no listener if error.code in [12514, 12757, 12541]: reportDown(error.code) else: raise else: logger.debug("Create Connection Pool Ended") reportUp() finally: time.sleep(10)
def db_connect(self): dsn = cx_Oracle.makedsn(self.args.address, 1521, self.args.database) self.pool = cx_Oracle.SessionPool(user=self.args.username, password=self.args.password, dsn=dsn, min=1, max=3, increment=1) self.db = self.pool.acquire() self.cur = self.db.cursor()
def connect(pool): pid = os.getpid() if pool.pid != pid: pool.forked_pools.append((pool.cx_pool, pool.pid)) pool.cx_pool = cx_Oracle.SessionPool(**pool.kwargs) pool.pid = os.getpid() if core.local.debug: log_orm('GET CONNECTION') con = pool.cx_pool.acquire() con.outputtypehandler = output_type_handler return con, True
def _getConn(dbInfo): if oraclePool._pool is None: oraclePool._pool = cx_Oracle.SessionPool( user=dbInfo['user'], password=dbInfo['pwd'], dsn="%s:%s/%s" % (dbInfo['host'], dbInfo['port'], dbInfo['sid']), min=3, max=8, increment=1) return oraclePool._pool.acquire()
def moveTableToTextOS(self): pool = cx_Oracle.SessionPool(self.username, self.password, self.service_name, 2, 5, 1, threaded=True) conn = pool.acquire() cursor = conn.cursor() print("TheLongQuery(): beginning execute...") print("Starting to dump data to object storage!\n") start_time = time.time() now = datetime.datetime.now() print("Start time : ") print(now) print(self.dump_file, self.cred, self.object_storage, self.dump_file) textfile = str(self.table + ".txt") bind_vars = { "dump_file": str(self.dump_file), "cred": str(self.cred), "object_storage": str(self.object_storage), "textfile": textfile, "table": self.table } sql = """ declare p_file utl_file.file_type; begin p_file := utl_file.fopen( 'DATA_PUMP_DIR', :textfile , 'w' ); for c in (select * from :table WHERE ROWNUM <= 10) loop utl_file.put_line(p_file, c.cust_id ); end loop; utl_file.fclose(p_file); dbms_cloud.put_object( :cred, :object_storage, 'DATA_PUMP_DIR', :dump_file ); end; """ cursor.execute(sql, bind_vars) print("TheLongQuery(): done execute...") elapsed_time = time.time() - start_time print("All done!\n") now = datetime.datetime.now() print("End time : ") print(now) print("Total time in seconds to finish : ") print(elapsed_time) print("\n")
def __connect(self, user, password, host, port, sid, _min, _max): dsn = cx_Oracle.makedsn(host, port, sid) self.logger.info( "start connect oracle database [ user=%s, host=%s, port=%s ]", user, host, port) self.pool = cx_Oracle.SessionPool(user=user, password=password, dsn=dsn, min=_min, max=_max, increment=1)
def getPool(): dic = read_db_info() print 'start connect to %s' % dic['tns'] os.environ["NLS_LANG"] = "AMERICAN_AMERICA.UTF8" Oracle.pool = cx_Oracle.SessionPool(user=dic['user'], password=dic['password'], dsn=dic['tns'], min=5, max=50, increment=1, threaded=True)
def fetch_data_oracle(): Start_Date=dt.datetime.now() print("Start Fetching Data: ",Start_Date) # Create the session pool pool = cx_Oracle.SessionPool(ORACLE_USER, ORACLE_PASSWORD, ORACLE_STR_HOST_DB,min=2, max=5, increment=1,sessionCallback=initSession, encoding="UTF-8") # Acquire a connection from the pool connection = pool.acquire() # Create query for each item in table list lowercase_tables = [lowercase_table.lower() for lowercase_table in table_list] cursor = connection.cursor() cursor.outputtypehandler = OutputTypeHandler for lowercase_table in lowercase_tables: ora_query = """ select * from """ + lowercase_table.upper() count_stmt = """ select count(*) from """ + lowercase_table.upper() numRows = 400000 df_res = pd.DataFrame() try: rowCounts = cursor.execute(count_stmt) for rowCnt in rowCounts: print("the count for table {} {}".format(lowercase_table,rowCnt)) cursor.execute(ora_query) headers = [i[0] for i in cursor.description] while True: rows = cursor.fetchmany(numRows) if not rows: break for i, row in enumerate(rows): printProgressBar(i, len(rows), prefix='Progress', suffix='Completed', length=50) list_rows = list( row ) df = pd.DataFrame([list_rows]) df_res = df_res.append(df) print('********************') try: df_res.to_csv( lowercase_table + ".csv" , index=False, header=headers , sep="~", quotechar='#', quoting=csv.QUOTE_NONNUMERIC, escapechar="\\" ,line_terminator='\n') except Exception as e: print(lowercase_table,e) pass except cx_Oracle.DatabaseError as exc: error, = exc.args if error.code == 31603 or error.code == 942: print("table " + lowercase_table + " doesn't exists in the schema") pass # Release the connection to the pool pool.release(connection) # Close the pool pool.close() End_Date=dt.datetime.now() print("Finished Fetching Data: ",End_Date)
def GetPool(user=None, password=None, **kwargs): if user is None: user = GetMainUser() if password is None: password = GetMainPassword() return cx_Oracle.SessionPool(user, password, GetConnectString(), encoding="UTF-8", nencoding="UTF-8", **kwargs)
def __init__(self, db_user, db_pass, db_tns, target_types): self.db_user = db_user self.db_pass = db_pass self.db_tns = db_tns self.target_types = target_types self.pool = cx_Oracle.SessionPool(user=self.db_user, password=self.db_pass, dsn=self.db_tns, min=1, max=10, increment=1, threaded=True)