def connectToOracle(url, username, password, mode=None):
if mode is not None:
connection = cx.Connection(user=username,
password=password,
dsn=url,
mode=mode)
else:
connection = cx.Connection(user=username, password=password, dsn=url)
return connection
def connectToOracle(url, username, password, mode=None):
try:
if mode is not None:
connection = cx_Oracle.Connection(user=username,
password=password,
dsn=url,
mode=mode)
else:
connection = cx_Oracle.Connection(user=username,
password=password,
dsn=url)
except cx_Oracle.DatabaseError as ex:
raise
return connection
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
def fetch_from_db(start_time: datetime.datetime, end_time: datetime.datetime,
dmdm: str, table):
connection = cx_Oracle.Connection("CYZX", "CYZX", "10.118.84.24/PDCP")
connection.ping()
logging.debug("connect db ok")
cursor = connection.cursor()
cursor.arraysize = 100
# TODO: add limit?
cursor.execute("""
SELECT JGSJ,DMDM,HPHM,HPYS,CLSD,CDBH,TPID,CSYS FROM {} T WHERE T.HPYS = '01' AND T.JGSJ >= :start_time AND T.JGSJ < :end_time AND (T.DMDM LIKE :dmdm ) """
.format(table),
start_time=start_time,
end_time=end_time,
dmdm=dmdm)
r = []
for row in cursor:
row_json = {
"JGSJ": row[0].timestamp(),
"JGSJ_STR": row[0].isoformat(),
"DMDM": row[1],
"HPHM": row[2],
"HPYS": row[3],
"CLSD": row[4],
"CDBH": row[5],
"TPID": row[6],
"CSYS": row[7]
}
# logging.debug("fetch row %s", json.dumps(row_json, ensure_ascii=False))
r.append(row_json)
logging.info("fetch over, row len %s", len(r))
connection.close()
return r
def __init__(self, dsn):
'''Constructor, it opens the connection with the database'''
self._dsn = dsn
self._user = self._get_dbuser()
self._connection = cx_Oracle.Connection(self._dsn)
self._cursor = self._connection.cursor()
def __oracleConnection(self,username, password, database): import cx_Oracle self.text_t = self.__text_oracle self.int_t = self.__integer_oracle self.float_t = self.__float_oracle self.double_t = self.__double_oracle return cx_Oracle.Connection(username, password, database)
def _oracle_action(*args, condition, credentials_file=ora_conf):
"""
Fonction generique permettant de se connecter a Oracle sur Platon en utilisant les parametres
ora_conf.
ATTENTION les attributs de confs sont notes en specifique, si vous changez les parametres, assurez vous de
changer les attributs de conf si necessaire.
:param args:
:param condition:
:return:
"""
conf = _file_config(credentials_file)
# log en premier car sinon on n'a pas ces infos dans rf
print('Oracle connection -> user:{} - pwd:{} - {}:{}/{}'.format(
conf.P_USRTRT, conf.P_PWDTRT, conf.P_HOSTTRT, conf.P_PORTTRT,
conf.P_SIDTRT))
# les attributs dynamiques sont notés en brut, l'ide n'aime pas ça, mais creer un intermediaire
# prendrait des heures et obligerai à ajouter un fichier de parametres, je concidere donc la methode en specifique
connection = cx_Oracle.Connection(
conf.P_USRTRT, conf.P_PWDTRT,
"{}:{}/{}".format(conf.P_HOSTTRT, conf.P_PORTTRT, conf.P_SIDTRT))
print('Oracle connection -> CONNECTED')
# generation du cursor
cursor = connection.cursor()
# utilisation conditionnelle du cursor, cette methode 1e classe est necessaire a l'utilistaion de cette fonction
result = condition(cursor, *args)
connection.commit()
print('Oracle connection -> COMMITED')
connection.close()
print("Oracle connection -> CLOSED")
# Le retour est generique, si pas besoin de retour, renvoie None
return result
def call_proc(db,proc,*para):
found=False
curs=None
connection=None
print('Processing procedure '+proc)
if para!=None :
connection = cx_Oracle.Connection(db)
try:
curs = connection.cursor()
curs.callproc("dbms_output.enable", (None,)) # enable output, if procedure has dbms_output.put_line, the message will output as well
#return_val=curs.callfunc( para[0],cx_Oracle.STRING,para[1:]) #This for call oracle function.
curs.callproc(proc,para)
statusVar = curs.var(cx_Oracle.NUMBER)
lineVar = curs.var(cx_Oracle.STRING)
while True:
curs.callproc("dbms_output.get_line", (lineVar, statusVar))
if statusVar.getvalue() != 0:
break
outmsg=(lineVar.getvalue())
print('MSG From %s => %s'%(proc,outmsg))
except Exception as e:
connection.rollback()
print('call_proc:'+str(e), file=sys.stderr)
finally:
if curs!=None:
curs.close()
if connection!=None:
connection.commit()
connection.close()
def get_selected_pel_term_desc(term, ptrm):
"""get the selected pel term
displayed on course_search_pel.html / localsite views / course_search_pel def
:param term:
:param ptrm:
:rtype: tuple
:return: result - a tuple containing the name of the pel term
"""
logger = logging.getLogger('django')
try:
con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL)
cursor = con.cursor()
cursor.prepare(
"SELECT PTRM_DESC FROM SWVPTRM_UP_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm"
)
cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm})
result = cursor.fetchone()
cursor.close()
con.close()
logger.info(
"get_selected_pel_term_desc - term %s - ptrm %s - against Oracle"
% (term, ptrm))
except cx_Oracle.DatabaseError as e:
print(e)
result = None
return result
def get_campus(term='', ptrm=''):
"""get the campus locations
displayed on course_search_pel.html / localsite views / course_search_pel def
:param term:
:param ptrm:
:rtype: list
:return: results - a list of tuples containing the campus locations
"""
logger = logging.getLogger('django')
try:
con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL)
cursor = con.cursor()
cursor.prepare(
"SELECT TERM, PTRM, CAMP, CAMP_DESC FROM SWVCAMP_UP_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm"
)
cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm})
results = cursor.fetchall()
cursor.close()
con.close()
logger.debug("get_campus - term %s - ptrm %s - against Oracle" %
(term, ptrm))
except cx_Oracle.DatabaseError as e:
print(e)
results = None
return results
def get_all_instructors(term='', ptrm=''):
"""get all the instructors
displayed on course_search_res.html / localsite views / course_search_res def
displayed on course_search_pel.html / localsite views / course_search_pel def
:param term:
:param ptrm:
:rtype: list
:return: results - a list of tuples containing all of the instructors
"""
logger = logging.getLogger('django')
try:
con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL)
cursor = con.cursor()
cursor.prepare(
"SELECT TERM, PTRM, PREF_NAME, CA_EMAIL, PREF_FIRST_NAME, PIDM FROM SWVINST_ASGN_PTRM_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm"
)
cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm})
results = cursor.fetchall()
cursor.close()
con.close()
logger.debug(
"get_all_instructors - term %s - ptrm %s - against Oracle" %
(term, ptrm))
except cx_Oracle.DatabaseError as e:
print(e)
results = None
return results
def ssh_oracle():
sql = str(
"select location_type from tlb_rent_request t where t.rent_request_code='S2009180003'"
)
conn = cx.Connection('NFRZZL/[email protected]/YWDFNFRZZL')
cur = conn.cursor()
print('我是oracle数据库')
def select_db(sql):
connection = cx_Oracle.Connection(DBLINE)
cursor = connection.cursor()
cursor.execute(sql)
data = cursor.fetchall()
return data
connection.close()
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()))
def pgDetails(self, tableName, pgId, pgName, timeStamp, status):
try:
dbConnection = cx_Oracle.Connection(self.connString)
cur = dbConnection.cursor()
#2020-02-02 08:39:39.211
cur.prepare(
" insert into " + tableName +
" values ( :pgId ,:pgName,TO_TIMESTAMP(:timeStamp,'YYYY-MM-DD HH24:MI:SS.FF'),:status) "
)
cur.execute(
None, {
'pgId': pgId,
'pgName': pgName,
'timeStamp': timeStamp,
'status': status
})
dbConnection.commit()
cur.close()
dbConnection.close()
except (IOError, OSError) as e:
print("While reading data from database process got failed " + e)
exit()
def _connect(self):
dbh = cx_Oracle.Connection(self.username, self.password, self.database)
if hasattr(sys, "argv"):
dbh.cursor().execute(
"BEGIN DBMS_APPLICATION_INFO.SET_MODULE('%s',NULL); END;" %
sys.argv[0])
return dbh
def __init__(self, configfile):
config = configparse()
config.read(configfile)
self.sourceDb = cx_Oracle.Connection(
"%s/%s@%s:%s/%s" %
(config.get("sourcedb", "user"), config.get(
"sourcedb", "password"), config.get(
"sourcedb", "host"), config.get("sourcedb", "port"),
config.get("sourcedb", "instance")
or config.get("sourcedb", "database")))
self.targetDb = pymysql.connect(
host=config.get("targetdb", "host"),
port=int(config.get("targetdb", "port")),
user=config.get("targetdb", "user"),
password=config.get("targetdb", "password"),
database=config.get("targetdb", "database"),
charset=config.get(
"targetdb",
"charset")) #, cursorclass = pymysql.cursors.SSCursor)
self.sourceCursor = self.sourceDb.cursor()
self.targetCursor = self.targetDb.cursor()
if "oracle" in str(type(self.sourceCursor)).lower():
self.sourceSchema = config.get("sourcedb", "schema") or config.get(
"sourcedb", "user")
if "mysql" in str(type(self.sourceCursor)).lower():
self.sourceSchema = config.get("sourcedb", "database")
if "mysql" in str(type(self.targetCursor)).lower():
self.targetCursor.execute("SET @@SQL_MODE='ANSI_QUOTES'")
self.targetCursor.execute("set autocommit=0")
self.targetSchema = config.get("targetdb", "database")
self.tablelist = config.get("targetdb", "tablelist").split("\n")
self.sourceTimestampField = config.get("sourcedb", "timestamp")
self.targetTimestampField = config.get("targetdb", "timestamp")
class Consumer():
def __init__(self,v_str,v_type,v_sid):
self.host=v_str.strip().split(':')[0]
self.port=v_str.strip().split(':')[1]
self.user=v_str.strip().split(':')[2]
self.passwd=v_str.strip().split(':')[3]
self.db=v_str.strip().split(':')[4]
self.table=v_str.strip().split(':')[5]
self.type=v_type.strip()
self.sid=v_sid.strip()
def get_type(self):
type=self.type
return type
def get_cursor(self):
db_type = self.get_type()
if db_type == 'o2m' or db_type == 'm2m':
try:
conn = pymysql.connect(host=self.host, port=int(self.port), user=self.user, passwd=self.passwd, db=self.db, charset='UTF8')
except Exception , e:
print e
return conn
elif db_type == 'o2o' or db_type == 'm2o':
try:
v_dsn = cx_Oracle.makedsn(self.host, self.port, self.sid)
conn = cx_Oracle.Connection(user=self.user, password=self.passwd, dsn=v_dsn)
except Exception , e:
print e
def save_report(user, periodo, report):
ora = cx_Oracle.Connection(settings.DATABASES['default']['USER'] + '/' +
settings.DATABASES['default']['PASSWORD'] +
'@' + settings.DATABASES['default']['HOST'] +
':' + settings.DATABASES['default']['PORT'] +
'/' + settings.DATABASES['default']['NAME'])
cursor = ora.cursor()
model = HistorialCreacionReporte()
model.usuario = user
model.periodo_id = periodo
model.actualizacion = datetime.now()
model.reporte_id = report
model.save()
if report == 1:
cursor.callproc('MARGEN_MENSUAL')
elif report == 2:
cursor.callproc('PESO_TIPO_CLIENTE')
elif report == 3:
cursor.callproc('PRECIO_PROMEDIO')
elif report == 4:
cursor.callproc('DESCUENTOS')
elif report == 5:
cursor.callproc('COSTOS_UNITARIOS')
elif report == 6:
cursor.callproc('CARGA_FORMULA_INGRESO')
cursor.callproc('FORMULA_INGRESOS')
cursor.callproc('TIMELINE')
cursor.close()
def grava_alarme(target, alarme, descricao):
"""Grava um novo alarme"""
#conectando
config_dbmon = Settings()
dbmon_string = config_dbmon.conn_dbmon
conn_dbmon = cx_Oracle.Connection(dbmon_string)
cur_dbmon = cx_Oracle.Cursor(conn_dbmon)
#check se nao existe
check = check_alarme_ativo(target, alarme)
if check == 0:
#cadastro novo alarme
strinsert = """
INSERT INTO TARGET_ALARME (TARGET, ALARME, DATA_ALARME, DESCRICAO, ISRECONHECIDO)
VALUES (:binTarget,
:binAlarme,
sysdate,
:binDescricao,
0)
"""
cur_dbmon.execute(strinsert,
binTarget=target,
binAlarme=alarme,
binDescricao=descricao)
conn_dbmon.commit()
if check == 1:
#atualizo o alarme informando a data do alarme atualizada
strupdate = """ UPDATE TARGET_ALARME SET
DATA_ULTIMO_ALARME=SYSDATE
WHERE TARGET=:binTarget
AND ALARME=:binAlarme
AND ISRECONHECIDO=0 """
cur_dbmon.execute(strupdate, binTarget=target, binAlarme=alarme)
conn_dbmon.commit()
cur_dbmon.close()
conn_dbmon.close()
def __enter__(self):
#self.__db = cx_Oracle.Connection( "{USER}usercine/[email protected]:1521/orcl.example.com")
db_conf = getattr(settings, "DATABASES", None)['default']
self.__db = cx_Oracle.Connection(
"usuaroi/contrase@servidor:puerto/nombreorcl".format(**db_conf))
self.__cursor = self.__db.cursor()
return self
def get_all_subjects(term='', ptrm=''):
"""get all the subjects for a term and ptrm
displayed on course_search_res.html / localsite views / course_search_res def
displayed on course_search_pel.html / localsite views / course_search_pel def
:param term:
:param ptrm:
:rtype: list
:return: results - a list of tuples containing all of the subjects
"""
logger = logging.getLogger('django')
try:
con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL)
cursor = con.cursor()
cursor.prepare(
"SELECT TERM, SUBJ, NVL(SUBJ_DESC, 'n/a'), PTRM FROM SWVSUBJ_WEB WHERE TERM = :the_term AND PTRM = :the_ptrm"
)
cursor.execute(None, {'the_term': term, 'the_ptrm': ptrm})
results = cursor.fetchall()
cursor.close()
con.close()
logger.debug(
"get_all_subjects - term %s - ptrm %s - against Oracle" %
(term, ptrm))
except cx_Oracle.DatabaseError as e:
print(e)
results = None
return results
def setUp(self):
import cx_Oracle
import TestEnv
self.connection = cx_Oracle.Connection(TestEnv.MAIN_USER,
TestEnv.MAIN_PASSWORD, TestEnv.CONNECT_STRING,
encoding = TestEnv.ENCODING, nencoding = TestEnv.NENCODING)
self.cursor = self.connection.cursor()
self.cursor.arraysize = TestEnv.ARRAY_SIZE
def __enter__(self):
if self.__tns_connection:
self.__db = self.__connection_string
else:
self.__db = cx_Oracle.Connection(self.__connection_string)
self.__cursor = self.__db.cursor()
return self
def __newConnection( self ):
"""
Create a New connection and put it in the Queue
"""
self.logger.debug( '__newConnection:' )
connection = cx_Oracle.Connection( self.__userName, self.__passwd, self.__tnsName, threaded = True )
self.__putConnection( connection )
def get_ora_db():
import cx_Oracle
#conn = cx_Oracle.Connection('lims_read/[email protected]:1521/naut90.mpimp-golm.mpg.de')
#conn = cx_Oracle.Connection('TROST_USER/[email protected]:1521/naut81.mpimp-golm.mpg.de')
conn = cx_Oracle.Connection('lims_read/jsbach@limsdb2/naut90.mpimp-golm.mpg.de')
conn.current_schema = 'LIMS';
return conn
def getConnection(self, **kwargs):
import cx_Oracle
import TestEnv
return cx_Oracle.Connection(TestEnv.MAIN_USER,
TestEnv.MAIN_PASSWORD,
TestEnv.CONNECT_STRING,
encoding=TestEnv.ENCODING,
nencoding=TestEnv.NENCODING,
**kwargs)
def __getDBCfg(self):
self.dbname=os.environ['YKT_DBNAME']
self.dbuser=os.environ['YKT_USER']
self.dbpwd=os.environ['YKT_PWD']
try:
self.connect = cx_Oracle.Connection(self.dbuser + "/" + self.dbpwd + "@" + self.dbname)
except cx_Oracle.DatabaseError, exc:
print "连接数据库失败 :" , self.__parseOraExpt(exc)
raise exc
def get_cursor(self): type = self.get_type() if type == 'o2o' or type == 'o2m': try: v_dsn = cx_Oracle.makedsn(self.host, self.port, self.sid) conn = cx_Oracle.Connection(user=self.user, password=self.passwd, dsn=v_dsn) except Exception , e: print e return conn
def LogMessages(connection, pipeName):
"""Log messages using the cx_Logging module."""
cx_Logging.Debug("logging messages from pipe %s", pipeName)
debugConnection = cx_Oracle.Connection(connection.username,
connection.password,
connection.tnsentry,
threaded=True)
for message in MessageGenerator(debugConnection, pipeName):
cx_Logging.Trace("%s", message)
