def get_conn(cls, tag=''):
tag = tag.strip().upper() if tag else cls.tag
if cls.conn.get(tag):
return cls.conn[tag]
try:
cx_Oracle.init_oracle_client(
lib_dir=current_app.config.get('OCI_LIB_PATH'))
except Exception:
pass
try:
cls.conn[tag] = create_engine(
current_app.config.get(tag),
connect_args={
'encoding': 'UTF-8',
'nencoding': 'UTF-8',
'events': True
},
max_identifier_length=128,
).connect()
return cls.conn[tag]
except Exception as e:
current_app.logger.error('oci_charge err: {0} {1!r}'.format(
tag, e))
def main():
parser = create_parser()
args = parser.parse_args()
propertyfile = args.propertyfile
try:
with open(propertyfile, "rb") as fin:
config_bytes = fin.read()
config = Dict(yaml.safe_load(config_bytes))
file_format = config.s3.file_format
logger.info('Initialize oracle client')
cx_Oracle.init_oracle_client(lib_dir=config.oracle.instant_client)
for table in config.database.tables:
logger.info(f'Start to transfer data from table {table}')
retriever = oracle.oracle_retriever(config, table)
df = retriever.retrieve_data()
client = s3_client.s3_client(config)
if file_format == 'csv':
client.write_pandas_csv_to_s3(df, table)
else: # default: parquet
client.write_pandas_parquet_to_s3(df, table)
logger.info(f'End to transfer data from table {table}')
except IOError as e1:
logger.error(f'Cannot not open property file {propertyfile}')
logger.error(f'{e1.args}')
except Exception as e:
logger.error(f'{e.args}')
def __init__(self, dsn='rdrprod1', encoding='UTF-8'):
self.parameters = Parameters()
self.connection = None
self.client_lib = f"C:\\Users\\{self.parameters.username}\\Downloads\\instantclient-basic-nt-19.8.0.0.0dbru\\instantclient_19_8"
self.dsn = dsn
self.encoding = encoding
try:
cx_Oracle.init_oracle_client(lib_dir=self.client_lib)
except Exception as err:
'''
'''
if err.__str__(
) == 'Oracle Client library has already been initialized':
pass
else:
print("Whoops!")
print(err)
try:
self.connection = cx_Oracle.connect(self.parameters.username,
self.parameters.orapass,
dsn,
encoding=self.encoding)
except cx_Oracle.Error as error:
print(error)
def create_connection(self):
self.set_connection_string()
try:
if self.server in [settings.EDW_SERVER, settings.EDW_SERVER_PROD]:
dll_dir = os.path.join(os.path.dirname(__file__),
'oracle_dlls')
if platform.system() == 'Windows':
try:
cx_Oracle.init_oracle_client(lib_dir=dll_dir)
except cx_Oracle.ProgrammingError as err:
error, = err.args
if 'already been initialized' in error.message:
pass
else:
raise err
engine = sa.create_engine(self.connection_string)
connection = engine.connect()
else:
connection = pyodbc.connect(self.connection_string)
except pyodbc.Error as err:
print(f'Could not connect!: {err}')
except Exception as err:
print(f'Could not connect!: {err}')
return connection
def connect_to_db(connecting_params, option):
return_object = []
cursor = None
cx_Oracle.init_oracle_client(
lib_dir="/oracle-instantclient/instantclient_12_1")
try:
dsn_tns = cx_Oracle.makedsn(
connecting_params['dbHost'],
connecting_params['dbPort'],
service_name=connecting_params['dbName']
) # if needed, place an 'r' before any parameter in order to address special characters such as '\'.
conn = cx_Oracle.connect(
user=connecting_params['dbUser'],
password=connecting_params['dbPassword'],
dsn=dsn_tns
) # if needed, place an 'r' before any parameter in order to address special characters such as '\'. For example, if your user name contains '\', you'll need to place 'r' before the user name: user=r'User Name'
cursor = conn.cursor()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
cursor.execute(
tables_and_columns(connecting_params['dbName'])
) # use triple quotes if you want to spread your query across multiple lines
for row in cursor:
return_object.append(row)
print(
row[0], '-', row[1]
) # this only shows the first two columns. To add an additional column you'll need to add , '-', row[2], etc.
conn.close()
cursor.close()
return return_object
def init_oracle_client(path_to_client):
print(f'Initializing Oracle client to {path_to_client}')
try:
cx_Oracle.init_oracle_client(path_to_client)
except cx_Oracle.DatabaseError as e:
print(e)
print(f"Variable CLIENT_ORACLE for the Oracle Client software not/badly configured in config.py.\n"
f"Value: {path_to_client}.")
def dbconnect(dbuser, dbpw, connstr):
if not (os.path.isfile(os.path.join(wallet_folder, 'tnsnames.ora'))):
get_wallet(db_client, db_id, wallet_password)
cx_Oracle.init_oracle_client(config_dir=wallet_folder)
connection = cx_Oracle.connect(dbuser, dbpw, connstr, encoding="UTF-8")
return connection
def initOracleClient(self):
try:
cx_Oracle.init_oracle_client(self.dirOraClient)
except cx_Oracle.Error as erro:
if str(erro) == "Oracle Client library has already been initialized":
pass
else:
raise erro
def create(cls, database_type):
if database_type == 'mysql':
engine = create_engine(cls.mysql_uri)
if database_type == 'oracle':
cxoc.init_oracle_client(lib_dir=cls.oracle_lib_dir)
engine = create_engine(cls.oracle_uri)
if database_type == 'sqlite':
engine = create_engine(cls.sqlite_path)
return engine
def __init__(self, env: str, db_name: str):
cx_Oracle.init_oracle_client(
lib_dir=r"C:\\oraclexe\\instantclient_19_11")
self.db_conf = OracleDBConfiguration(env, db_name)
self.engine = create_engine(
self.db_conf.db_url(),
encoding='utf8',
pool_size=self.db_conf.pool_size,
pool_timeout=self.db_conf.connection_timeout)
def odatafetch(clients):
username = environ.get('DB_USER')
password = environ.get('DB_PWD')
tns_name = environ.get('TNS_NAME')
cx_Oracle.init_oracle_client(config_dir="/instantclient_21_1/network/admin")
try:
with cx_Oracle.connect(
username,
password,
tns_name,
encoding="UTF-8",
events=True) as connection:
logger.info("DB connection OK")
def callback(cqn_message):
for table in cqn_message.tables:
if table.name == 'DEMODATA.MESSAGES':
for row in table.rows:
# we got a row added. Grab the rowid and send the data to our clients
cursor = connection.cursor()
cursor.execute("""
SELECT ROWID, m.msg.ts ts, m.msg.value value, m.msg.hostname host
FROM demodata.messages m
WHERE ROWID = :rid""", rid=row.rowid)
rows = cursor.fetchall()
for d in rows:
msg_obj = {'date': float(d[1]), 'value': float(d[2]), 'host': d[3]}
msg = format_sse(data=json.dumps(msg_obj))
# post to all client queues
for k, v in clients.items():
try:
v.put(msg)
except Exception as e:
logger.error(str(e))
# Subscribe to Change Query Notifications, to get data updates
logger.info("subscribing")
subscription = connection.subscribe(
namespace=cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE,
operations=cx_Oracle.OPCODE_INSERT,
qos=cx_Oracle.SUBSCR_QOS_BEST_EFFORT | cx_Oracle.SUBSCR_QOS_ROWIDS,
callback=callback,
clientInitiated=True
)
# Register query to
logger.info("registering query")
subscription.registerquery("""SELECT rcvd_at_ts FROM demodata.messages""")
while True:
sleep(5)
except Exception as e:
logger.error(str(e))
def call(self):
try:
cx_Oracle.init_oracle_client(lib_dir=client)
except Exception as err:
print("Whoops!")
print(err);
sys.exit(1);
connection = cx_Oracle.connect(user = self.user,
password = self.password,
dsn = self.url)
return connection
def __init__(self):
load_dotenv('ccb.env')
cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_9")
self.connection = self.connect_db(getenv("ccb_host"),
getenv("ccb_port"),
getenv("ccb_service_name"))
basicConfig(
handlers=[FileHandler(encoding='utf-8', filename='ccb.log')],
level=DEBUG,
format=u'%(levelname)s - %(name)s - %(asctime)s: %(message)s')
if not self.check_connection():
raise Exception("Couldn't connect to CCB DB.")
def makeQuery(data):
cx_Oracle.init_oracle_client(
lib_dir="/Users/joshuawelsh/instantclient_19_8")
connection = cx_Oracle.connect(cred.user, cred.psswd,
"lawtech.law.miami.edu/CSC_423")
cursor = connection.cursor()
cursor.execute(data)
columns = [c[0] for c in cursor.description]
data = cursor.fetchall()
df = pd.DataFrame(data, columns=columns)
print(df) # examine
print(df.shape)
print(df.columns)
def __init__(self):
self.session = Request()
sys_str = platform.system()
# 驱动路径
if sys_str == "Windows":
self.or_client_path = Config().ORACLE_CLIENT_WIN_PATH
elif sys_str == "Linux":
self.or_client_path = Config().ORACLE_CLIENT_WIN_PATH
else:
self.or_client_path = Config().ORACLE_CLIENT_WIN_PATH
# notice python 64位 instantclient-basic 64位 cx_oracle 64位
cx_Oracle.init_oracle_client(lib_dir=self.or_client_path)
def ora_client_init(path=None):
"""用指定的路径对OCI客户端进行初始化;返回值:msg错误消息,空串正常."""
if path is None:
if platform.system() == 'Windows':
path = 'd:/soft/instantclient_11_2/' # 告知默认oci立即客户端的位置
else:
path = '/usr/lib/oracle/11.2/client64/lib/'
try:
co.init_oracle_client(path)
return ''
except Exception as e:
print('oracle instant client init fail.\n\t' + str(e))
return str(e)
def init_instant_client():
from . import gls
with verrou:
if gls.client_is_init is False:
u.log("Initialising Oracle client...")
gls.client_is_init = True
if not exists(cfg.ORACLE_CLIENT):
s = ("Error: The Oracle instant client directory specified in"
f" {cfg.__file__} (ORACLE_CLIENT = {cfg.ORACLE_CLIENT})"
" doesn't exist. Please enter a valid directory for the"
" Oracle instant client.")
u.log(s)
raise Exception(s)
cx.init_oracle_client(cfg.ORACLE_CLIENT)
u.log("Client Oracle initialised")
def __init__(self):
host = "192.168.20.28"
user = "******"
passw = "1234"
tsname = "xe"
cx_Oracle.init_oracle_client(
lib_dir=r"C:\oraclexe\app\oracle\instantclient_19_6")
try:
self.conexion = cx_Oracle.connect(
user, passw, host + "/" + tsname + "?connect_timeout=30")
print("Conexion Establecida!!")
except Exception as error:
print("No se pudo conectar a la base de datos. Error : " + error)
def main():
parser = argparse.ArgumentParser()
# parameter from system
parser.add_argument('-f', '--file', default='./init.yml')
args = parser.parse_args()
with open(args.file, 'r') as conf:
c = yaml.load(conf, Loader=yaml.FullLoader)
# print(configSet)
init_oracle_client(
lib_dir=c['server']['instantclient'])
start_http_server(int(c['server']['port']), c['server']['host'])
for config in c['config']:
# print(config)
o = threading.Thread(target=oracleSQL(config).do_execute)
o.start()
def __init__(self):
connection = None
cx_Oracle.init_oracle_client(lib_dir=config.db_client_dir)
try:
connection = cx_Oracle.connect(config.username,
config.password,
config.dsn,
encoding=config.encoding)
# show the version of the Oracle Database
print(connection.version)
except cx_Oracle.Error as error:
print(error)
finally:
# release the connection
if connection:
connection.close()
def __init__(self):
db.init_oracle_client(lib_dir=config.lib_dir)
dsnStr = db.makedsn(config.path, config.port, config.SID)
connection = None
try:
print(f'Łączenie się z serwerem : {config.path}, proszę czekać...')
self.connection = db.connect(config.username,
config.password,
dsn=dsnStr,
encoding=config.encoding)
except db.Error as error:
print(f'Błąd: {error}, program zostanie zakończony!')
sys.exit()
else:
if connection:
print("Udało się!")
print(f'Wersja bazy danych: {self.connection.version}')
def _connect_to_sql(self):
# This is the path to the ORACLE client files
lib_dir = r"/Users/rajeevratan/Downloads/instantclient_19_8"
try:
cx_Oracle.init_oracle_client(lib_dir=lib_dir)
except Exception as err:
print("Error connecting: cx_Oracle.init_oracle_client()")
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = '' #enter your username
PASSWORD = '' #enter your password
HOST = 'IP' #enter the oracle db host url
PORT = 1522 # enter the oracle port number
SERVICE = '' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD + '@' + HOST + ':' + str(
PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH, max_identifier_length=128)
return engine
def __cx_oracle_connector(connection_profile):
base_keys_validation_output = RdbmsDAO._validate_connection_profile(
connection_profile,
['user', 'password', 'host', 'port', 'sid', 'client_library_dir'])
if not base_keys_validation_output[0]:
raise KeyError(
f'One or more of {base_keys_validation_output[1]} keys not configured in profile'
)
cx_Oracle.init_oracle_client(
lib_dir=connection_profile['client_library_dir'])
dsn = cx_Oracle.makedsn(connection_profile['host'],
connection_profile['port'],
connection_profile['sid'])
return cx_Oracle.connect(connection_profile['user'],
connection_profile['password'],
dsn=dsn)
def setup_connection():
oracle_usr, oracle_pwd = get_oracle_usr_pwd()
client_lib_dir = os.getenv('ORACLE_CLIENT_LIB')
if database == 'sequences':
if not client_lib_dir or not os.path.isdir(client_lib_dir):
sys.stderr.write(
"ERROR: Environment variable $ORACLE_CLIENT_LIB must point at a valid directory\n"
)
exit(1)
cx_Oracle.init_oracle_client(lib_dir=client_lib_dir)
connection = None
try:
dsn = cx_Oracle.makedsn("ora-vm5-008.ebi.ac.uk",
1531,
service_name="ENAPRO")
connection = cx_Oracle.connect(oracle_usr,
oracle_pwd,
dsn,
encoding="UTF-8")
return connection
except cx_Oracle.Error as error:
print(error)
else:
if not client_lib_dir or not os.path.isdir(client_lib_dir):
sys.stderr.write(
"ERROR: Environment variable $ORACLE_CLIENT_LIB must point at a valid directory\n"
)
exit(1)
cx_Oracle.init_oracle_client(lib_dir=client_lib_dir)
connection = None
try:
dsn = cx_Oracle.makedsn("ora-vm-009.ebi.ac.uk",
1541,
service_name="ERAPRO")
connection = cx_Oracle.connect(oracle_usr,
oracle_pwd,
dsn,
encoding="UTF-8")
return connection
except cx_Oracle.Error as error:
print(error)
def getdata():
try:
cx_Oracle.init_oracle_client(r"D:\instantclient_19_9")
dsn = cx_Oracle.makedsn('10.224.81.31', '1521', 'VNAP')
conn = cx_Oracle.connect("AP2", "NSDAP2LOGPD0522", dsn)
if isinstance(conn, cx_Oracle.Connection):
with conn.cursor() as cursor:
cursor.execute("select * from MES4.R_SYSTEM_LOG where emp_no ='APP_AUTO' and PRG_NAME ='ALERT WECHAT'")
# res = cursor.fetchall()
res = cursor.fetchall()
for row in res:
message = row[1] + '-' + row[3]
print(message)
writelog(message)
# send_msg(message)
# cursor.execute("update MES4.R_SYSTEM_LOG set ACTION_TYPE ='1' where ACTION_DESC='" + row[3] + "'")
conn.commit()
conn.close()
except Exception as err:
writelog(err)
print(err)
def validar_tabla(nombre):
import cx_Oracle
import pandas as pd
if isinstance(nombre, list):
print('Nombre aceptado')
else:
raise Exception('El objeto ingresado no es una lista')
try:
cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\luigg\OneDrive\Documentos\instantclient_12_2")
except:
print('oracle iniciado')
con = cx_Oracle.connect("USUARIO", "password", "IP:1521/dgpp")
consulta= "SELECT DISTINCT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'ESQUEMA01' UNION ALL SELECT DISTINCT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'ESQUEMA02' UNION ALL SELECT DISTINCT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'ESQUEMA03'"
bd = pd.read_sql_query(consulta, con)
if len(bd['TABLE_NAME'][bd['TABLE_NAME'].isin(nombre)])>0:
print('tabla existe')
con.close()
return True
else:
print('tabla no existe')
con.close()
return False
def main():
path = '.\\credentials.json'
if not os.path.isfile(".\\credentials.json"):
print(
"Отсутствует файл конфигурации логина/пароля. Поместите файл credentials.json в папку со скриптом"
)
return
csv = r"C:\\oracle\\python_homework\\data.csv"
credentials = json.load(open(path))
cx_Oracle.init_oracle_client(credentials["client"])
clear(credentials)
c = categorizer.categorizer()
csv_table = table.csv_table(csv)
table_name = "petrushin_test"
oracle_table = table.oracle_table(credentials["login"],
credentials["password"],
credentials["host"], table_name)
oracle_table.categories = c.get_category(oracle_table.get_data())
csv_table.categories = c.get_category(csv_table.get_data())
print(csv_table.get_data())
print("=======")
print("Данные petrushin_test до обогащения")
print(oracle_table.get_data())
print("=======")
print("Данные petrushin_test после обогащения")
oracle_table.update_from_table(csv_table)
print(oracle_table.get_data())
class ConfigLoader:
# LOAD JSON DATA
with open(str(Path(__file__).parent.absolute()) + "/data/config.json",
encoding="utf-8") as file:
data_: dict[str, str] = json.load(file)
# INIT ORACLE LIB
cx_Oracle.init_oracle_client(data_["oracle_dir"])
connection_pool_ = cx_Oracle.SessionPool(data_["db_user"],
data_["db_pwd"],
data_["db_url"],
encoding=data_["db_encoding"])
def __new__(cls, *args, **kwargs) -> None:
if cls is ConfigLoader:
raise TypeError("ConfigLoader cannot be instantiated!")
@staticmethod
def get_oracle_dir() -> str:
return ConfigLoader.data_.get('oracle_dir')
@staticmethod
def get_db_user() -> str:
return ConfigLoader.data_.get('db_user')
@staticmethod
def get_db_pwd() -> str:
return ConfigLoader.data_.get('db_pwd')
@staticmethod
def get_db_url() -> str:
return ConfigLoader.data_.get('db_url')
@staticmethod
def get_db_encoding() -> str:
return ConfigLoader.data_.get('db_encoding')
@staticmethod
def get_connection_pool():
return ConfigLoader.connection_pool_
@staticmethod
def get(key: str) -> Union[str, None]:
if key in ConfigLoader.data_:
return ConfigLoader.data_[key]
return None
except (cx_Oracle.IntegrityError, cx_Oracle.DatabaseError):
print(f"\tSALTATO elemento {index}")
def populate_search(oracle_connection):
"""
Per ogni utente presente nel db inseriamo 10 ricerche, per semplicità di sessioni diverse,
prendendo un termine e il riferimento ad esso da inserire nel campo keyword e nel campo term;
come user_associated avremo il ref a quell'utente
:param oracle_connection:
:return:
"""
if __name__ == '__main__':
cx_Oracle.init_oracle_client(
lib_dir=r"instantclient-basic-nt-19.8.0.0.0dbru/instantclient_19_8")
# Set folder in which Instant Client is installed in system path
os.environ[
'PATH'] = 'C:/Users/Pierpaolo/Desktop/django_cinemaapp_piernicola/instantclient-basic-nt-19.8.0.0.0dbru/instantclient_19_8'
con = cx_Oracle.connect("pierpaolo_search_engine_1", "pierpaolo",
"localhost:1521/orcl")
print("Connected!")
# populate_users(con)
try:
#populate_pages(con)
populate_users(con)
#poi i links
#infine le search
#from lib.action_oracle import *
from ldap.controls import SimplePagedResultsControl
import ldap
import cx_Oracle
import datetime
import time
if __name__ =="__main__":
cx_Oracle.init_oracle_client(lib_dir="/Users/billxzy1215/Downloads/instantclient_19_8")
datax_conn=cx_Oracle.connect("datax", "datax", "dw")
datax_cursor=datax_conn.cursor()
datax_cursor.execute("select nvl(to_char(inc_start_time - 1/3, 'yyyymmddHHmiss'), '19000101000000') || '.0Z' as last_time from datax.job_info where job_desc = 'ODS_AD_Student'")
last_dt_0Z = datax_cursor.fetchone()[0]
current_time = datetime.datetime.now()
current_dt = current_time.strftime('%Y%m%d%H%M%S')
current_dt_0Z = (current_time + datetime.timedelta(hours=-8)).strftime('%Y%m%d%H%M%S') + '.0Z'
base_dn_student = 'OU=Students,OU=Users,OU=University,DC=nottingham,DC=edu,DC=cn'
base_dn_alumni = 'OU=Graduates,OU=Alumni,OU=University,DC=nottingham,DC=edu,DC=cn'
search_filter_student = '(&(objectCategory=person)(objectClass=user)(|(&(whenChanged>=' + last_dt_0Z + ')(whenChanged<=' + current_dt_0Z + '))(&(whenCreated>=' + last_dt_0Z + ')(whenCreated<=' + current_dt_0Z + '))))'
search_filter_alumni = '(|(&(whenChanged>=last_dt_0Z)(whenChanged<=current_dt_0Z))(&(whenCreated>=last_dt_0Z)(whenCreated<=current_dt_0Z)))'
attr_list_student=['employeeID','cn','description','givenName','sn','instanceType','distinguishedName','displayName','mail','whenCreated','whenChanged','memberOf','uSNCreated','uSNChanged','department','extensionAttribute1','extensionAttribute14','userAccountControl','codePage','countryCode','lastLogon','pwdLastSet','primaryGroupID','accountExpires','logonCount','eduPersonEntitlement','eduPersonAffiliation','msExchRemoteRecipientType','msExchRecipientDisplayType', 'lastLogonTimestamp']
attr_list_alumni = ''
ldapconn = ldap.initialize('ldap://ldap.nottingham.edu.cn:389')
bind = ldapconn.simple_bind('CN=LDAP_dw,OU=LDAP,OU=Services,OU=University,DC=nottingham,DC=edu,DC=cn', '')
time.sleep(10)
page_size = 1000
pages = 0
req_ctrl = SimplePagedResultsControl(criticality=True, size=page_size, cookie='')
