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)
