def insert_multiple(table_name: str, df: pd.DataFrame, conn_db: cx_Oracle.Connection, batch_size=10000):
"""multiple insert
parameters:
*) table_name: table_name you're inserting into
*) df: dataframe being inserted into table
*) conn_db: a connection object from cx_oracle or open_connection
*) batch_size: batch size of commit (number of rows)
"""
cur = conn_db.cursor()
sql = "INSERT INTO {0} ({1}) VALUES (:{2})".format(table_name,
', '.join(df.columns),
', :'.join(list(map(str,range(1, len(df.columns)+1)))))
# Get column types so they can be specified before the insert statement.
# This avoids an error when inserting dates
# See http://cx-oracle.readthedocs.io/en/latest/cursor.html#Cursor.execute
cur.execute('select * from {} where 1=0'.format(table_name))
db_types = (d[1] for d in cur.description)
cur.setinputsizes(*db_types)
i = 0
while ((i * batch_size) < len(df)):
rows = []
min = i*batch_size
max = ((i+1)*batch_size)-1
for x in df.loc[min:max,:].values:
rows.append([None if pd.isnull(y) else y for y in x])
cur.executemany(sql, rows)
conn_db.commit()
i = i + 1
cur.close()
class ConnectionManager:
"""Context Management Protocol for Database access.
Implementation for accessing Oracle Databases."""
def __init__(self, config: tuple) -> None:
"""config should contain the following Infromation:
(username, password, connection_info)"""
self.config = config
pass
def __enter__(self) -> Cursor:
"""Returns a cursor for the interaction with the db"""
try:
self.connection = Connection(*self.config)
self.cursor = self.connection.cursor()
return self.cursor
except ProgrammingError as e:
raise CMProgrammingError(e)
except InterfaceError as e:
raise CMInterfaceError(e)
except Error as e:
raise CMError(e)
except Exception as e:
raise CMError(e)
pass
def __exit__(self, exc_type, exc_val, exc_tb) -> None:
"""Commits the buffered interactions and closes the cursor and the connection"""
self.connection.commit()
self.cursor.close()
self.connection.close()
if exc_type:
raise exc_type(exc_val)
pass
def _insert_matches(con: cx_Oracle.Connection):
"""
Update the MATCH table with data from the staging table
:param con: Oracle connection object
"""
cur = con.cursor()
logger.info("updating MATCH")
cur.execute("""
DELETE FROM INTERPRO.MATCH
WHERE PROTEIN_AC IN (
SELECT PROTEIN_AC
FROM INTERPRO.PROTEIN_TO_SCAN
)
""")
logger.info(f"{cur.rowcount} rows deleted")
cur.execute("""
INSERT INTO INTERPRO.MATCH
SELECT * FROM INTERPRO.MATCH_NEW
""")
logger.info(f"{cur.rowcount} rows inserted")
con.commit()
oracle.drop_table(cur, "INTERPRO.MATCH_NEW", purge=True)
cur.close()
def call_function(connection: cx_Oracle.Connection,
name: str,
return_type: Type[T],
*,
args: List[Any] = None,
kwargs: Dict[str, Any] = None) -> T:
if kwargs is None:
kwargs = {}
if args is None:
args = []
with connection.cursor() as cursor:
if issubclass(return_type, objects.Base):
r_type = connection.gettype(return_type.__type_name__)
else:
r_type = return_type
args_ = [
v.to_oracle_object(connection)
if isinstance(v, objects.Base) else v for v in args
]
kwargs_ = {
k: (v.to_oracle_object(connection)
if isinstance(v, objects.Base) else v)
for k, v in kwargs.items()
}
obj = cursor.callfunc(name, r_type, args_, kwargs_)
if issubclass(return_type, objects.Base):
return return_type.from_oracle_object(obj)
else:
return obj
def __init__(self, connection_string):
self.connection_string = connection_string
self.username = str(connection_string[:connection_string.find('/')])
self.password = str(connection_string[connection_string.find('/') +
1:connection_string.find('@')])
self.dsn = str(connection_string[connection_string.find('@') + 1:])
self.cnn = Connection(user=self.username,
password=self.password,
dsn=self.dsn)
class Db:
def __init__(self, connection_string):
self.connection_string = connection_string
self.username = str(connection_string[:connection_string.find('/')])
self.password = str(connection_string[connection_string.find('/') +
1:connection_string.find('@')])
self.dsn = str(connection_string[connection_string.find('@') + 1:])
self.cnn = Connection(user=self.username,
password=self.password,
dsn=self.dsn)
def __str__(self):
return "%s.%s[%s]" % (self.__module__, self.__class__.__name__,
self.connection_string)
def select(self, sql_text, **kwargs):
cursor = self.cnn.cursor()
try:
cursor.execute(sql_text, **kwargs)
desc = [d[0] for d in cursor.description]
df = pd.DataFrame(cursor.fetchall(), columns=desc)
# df = df.fillna(value=nan)
df = df.fillna('')
return df
finally:
cursor.close()
def execute_plsql(self, pl_sql_text, **kwargs):
cursor_vars = {}
cursor = self.cnn.cursor()
try:
for k, v in kwargs.items():
if v in (cx_Oracle.CLOB, cx_Oracle.BLOB, cx_Oracle.NCHAR,
cx_Oracle.NUMBER):
cursor_vars[k] = cursor.var(v)
else:
cursor_vars[k] = v
cursor.execute(pl_sql_text, **cursor_vars)
for k, v in kwargs.items():
if v in (cx_Oracle.CLOB, cx_Oracle.BLOB, cx_Oracle.NCHAR,
cx_Oracle.NUMBER):
value = cursor_vars[k].getvalue()
if value and v in (cx_Oracle.CLOB, cx_Oracle.BLOB):
value = value.read()
cursor_vars[k] = value
return cursor_vars
finally:
cursor.close()
def select_sid(self):
return self.select(
"select sys_context('userenv','instance_name') sid from dual"
)["SID"][0]
def execute(statement: str, conn_db: cx_Oracle.Connection):
"""execute a statement
parameters:
*) statement: str with a statetement
*) conn_db : a connection object from cx_oracle or open_connection
"""
cur = conn_db.cursor()
cur.execute(statement)
conn_db.commit()
cur.close()
def cancel(self, connection):
connection_name = connection.name
oracle_connection = connection.handle
logger.info("Cancelling query '{}' ".format(connection_name))
try:
Connection.close(oracle_connection)
except Exception as e:
logger.error('Error closing connection for cancel request')
raise Exception(str(e))
logger.info("Canceled query '{}'".format(connection_name))
def __enter__(self) -> Cursor:
"""Returns a cursor for the interaction with the db"""
try:
self.connection = Connection(*self.config)
self.cursor = self.connection.cursor()
return self.cursor
except ProgrammingError as e:
raise CMProgrammingError(e)
except InterfaceError as e:
raise CMInterfaceError(e)
except Error as e:
raise CMError(e)
except Exception as e:
raise CMError(e)
pass
def _dbms_utility_name_resolve(
connection: oracle.Connection, name: str,
context: int) -> Tuple[str, str, str, str, int, int]:
"""Wrapper for Oracle DBMS_UTILITY.NAME_RESOLVE procedure"""
with connection.cursor() as cursor:
schema = cursor.var(str)
part1 = cursor.var(str)
part2 = cursor.var(str)
database_link = cursor.var(str)
part1_type = cursor.var(int)
object_id = cursor.var(int)
cursor.callproc(
"dbms_utility.name_resolve",
[
name,
context,
schema,
part1,
part2,
database_link,
part1_type,
object_id,
],
)
return (
schema.getvalue(),
part1.getvalue(),
part2.getvalue(),
database_link.getvalue(),
part1_type.getvalue(),
object_id.getvalue(),
)
def set_call_timeout(self, conn: cx_Oracle.Connection):
try:
# Connection.callTimeout is in milliseconds
conn.callTimeout = 20000
print(f"Query timeout configured to: 20000 milisecs.")
except Exception as err:
print(f"Trying to configure query timeout thrown an unknown error: {err}.")
def insert_multiple(table_name: str,
df: pd.DataFrame,
conn_db: cx_Oracle.Connection,
batch_size=5000):
"""multiple insert
parameters:
*) table_name : table_name to load
*) df : dataframe to load
*) conn_db : a connection object from cx_oracle or open_connection
*) batch_size : batch size of commit
"""
cur = conn_db.cursor()
sql = "INSERT INTO {0} ({1}) VALUES (:{2})".format(
table_name, ', '.join(df.columns),
', :'.join(list(map(str, range(1,
len(df.columns) + 1)))))
i = 0
while ((i * batch_size) < len(df)):
rows = []
min = i * batch_size
max = ((i + 1) * batch_size) - 1
for x in df.ix[min:max, :].values:
rows.append([None if pd.isnull(y) else y for y in x])
cur.executemany(sql, rows)
con.commit()
i = i + 1
cur.close()
def _check_matches(con: cx_Oracle.Connection):
"""
Check there are not errors in imported matches
:param con: Oracle connection object
"""
cur = con.cursor()
# Matches outside of the protein
logger.info("checking out-of-bound matches")
cur.execute("""
SELECT M.PROTEIN_AC, M.METHOD_AC, M.POS_TO, P.LEN
FROM INTERPRO.MATCH_NEW M
INNER JOIN INTERPRO.PROTEIN P
ON M.PROTEIN_AC = P.PROTEIN_AC
WHERE M.POS_TO > P.LEN
""")
cnt = 0
for row in cur:
logger.critical("out-of-bound: {}\t{}\t{}\t{}".format(*row))
cnt += 1
if cnt:
cur.close()
con.close()
raise RuntimeError(f"{cnt} out-of-bound matches")
# Matches with invalid start/end positions
logger.info("checking invalid matches")
cur.execute("""
SELECT PROTEIN_AC, METHOD_AC, POS_FROM, POS_TO
FROM INTERPRO.MATCH_NEW
WHERE POS_FROM < 1 OR POS_FROM > POS_TO
""")
cnt = 0
for row in cur:
logger.critical("invalid: {}\t{}\t{}\t{}".format(*row))
cnt += 1
if cnt:
cur.close()
con.close()
raise RuntimeError(f"{cnt} invalid matches")
cur.close()
async def cash_transactions_sp(
request: models.TransactionRequest,
my_conn: cx_Oracle.Connection = Depends(get_raw_connection)):
print(request)
with my_conn.cursor() as cursor:
collection_type = my_conn.gettype("ott_cashTransactionsList")
request4 = {
'P_FORMAT': request.format,
'P_COMPANY': request.company,
'P_SOURCE': request.source,
'P_COUNTRY': request.country,
'P_ACCOUNT_ID': request.accountId,
'P_DATE_FROM': request.dateFrom,
'P_DATE_TO': request.dateTo,
'P_TRACE_ID': "eyanez",
'P_ITEMS_PER_PAGE': request.itemsPerPage,
'P_PAGE_NUMBER': request.pageNumber,
'P_TOTAL_PAGES': cursor.var(int),
'P_TOTAL_RECORDS': cursor.var(int),
'P_RESPONSE_CODE': cursor.var(int),
'P_MESSAGE': cursor.var(str),
'PTT_CASHTRANSACTIONS': cursor.var(collection_type)
}
pl_sql4 = f"begin TRAINING_PACKAGE.PRO_GET_CASH_TRANSACTIONS({declare_parameters(request4)}); end;"
cursor.execute(pl_sql4, request4)
my_collection = request4['PTT_CASHTRANSACTIONS'].getvalue().aslist()
print(f"PTT_CASHTRANSACTIONS length: {len(my_collection)}")
# Fetch data using reflection (using the metadata from the DB):
# No impact for changes in the contract
response_collection = []
for elem in my_collection:
my_dict = dict()
for the_attrib in elem.type.attributes:
my_dict[the_attrib.name] = getattr(elem, the_attrib.name)
response_collection.append(my_dict)
response = {
'P_TOTAL_PAGES': request4['P_TOTAL_PAGES'].getvalue(),
'P_TOTAL_RECORDS': request4['P_TOTAL_RECORDS'].getvalue(),
'P_RESPONSE_CODE': request4['P_RESPONSE_CODE'].getvalue(),
'P_MESSAGE': request4['P_MESSAGE'].getvalue(),
'PTT_CASHTRANSACTIONS': response_collection
}
return response
def getTicket_7_Data(request):
if request.method == 'POST':
if request.POST.get('getTicket_7_Data') =='Yes':
cur=con.cursor();
cur.execute(data_layer.ticketicket_data_7)
ticketicket_data_7 = cur.fetchall()
#con.close()
return JsonResponse({ "ticketicket_data_7": ticketicket_data_7})
def set_call_timeout(self, conn: cx_Oracle.Connection):
try:
# Connection.callTimeout is in milliseconds
conn.callTimeout = self.db_conf.query_timeout
print(
f"Query timeout configured to: {self.db_conf.query_timeout}.")
except Exception as err:
traceback.print_exc()
print(
f"Trying to configure query timeout thrown an unknown error: {err}."
)
def insert_or_update(
self,
table_name: str,
data: dict,
conn: cx_Oracle.Connection = None,
key: str = None,
main_key: [str] = None,
):
if conn:
cursor = conn.cursor()
else:
cursor = self.__cursor
field = ','.join(data.keys())
param = [':' + str(e) for e in data.keys()]
sql = ("insert into {} ({}) "
"values ({}) ").format(table_name, field, ','.join(param))
value = [e for e in data.values()]
try:
cursor.execute(sql, value)
except cx_Oracle.IntegrityError:
upd_sql = ("UPDATE {} ").format(table_name)
set_statement = "set " + ", ".join(
["{} = :{}".format(k, k) for k in data.keys()])
if main_key is None:
where_statement = " where " + " and ".join(
["{} = :{}".format(k, k) for k in data.keys() if k != key])
else:
where_statement = " where " + " and ".join(
["{} = :{}".format(k, k) for k in main_key])
upd_sql += set_statement + where_statement
cursor.execute(upd_sql, data)
except cx_Oracle.DatabaseError:
self.logger.error(traceback.format_exc(), "sql: {}".format(sql))
print(traceback.format_exc())
print("sql: {}".format(sql))
if conn:
conn.commit()
else:
self.__local_db.commit()
def query_to_df(query: str, conn_db: cx_Oracle.Connection, arraysize: int):
"""Do the query and transform the result to a dataframe
parameters:
*) query: str with a query statetement
*) conn_db : a connection object from cx_oracle or open_connection
*) arraysize : arrayfetch size
"""
cur = conn_db.cursor()
##setting arraysize
if arraysize:
cur.arraysize = arraysize
##execute query
cur.execute(query)
##fetch all row
r = cur.fetchall()
cols = [n[0] for n in cur.description]
cur.close()
data = pd.DataFrame.from_records(r, columns=cols)
return (data)
def _dbms_describe_describe_procedure(connection: oracle.Connection,
name: str) -> Iterator[Tuple[Any, ...]]:
"""Wrapper for Oracle DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure"""
number_table, varchar2_table = (
connection.gettype("SYS.DBMS_DESCRIBE.NUMBER_TABLE"),
connection.gettype("SYS.DBMS_DESCRIBE.VARCHAR2_TABLE"),
)
with connection.cursor() as cursor:
overload = number_table.newobject()
position = number_table.newobject()
level = number_table.newobject()
argument_name = varchar2_table.newobject()
datatype = number_table.newobject()
default_value = number_table.newobject()
in_out = number_table.newobject()
length = number_table.newobject()
precision = number_table.newobject()
scale = number_table.newobject()
radix = number_table.newobject()
spare = number_table.newobject()
try:
cursor.callproc(
"dbms_describe.describe_procedure",
[
name,
None,
None,
overload,
position,
level,
argument_name,
datatype,
default_value,
in_out,
length,
precision,
scale,
radix,
spare,
],
)
except oracle.DatabaseError as err:
if err.args[0].code == 20001:
raise NotFound
raise
return zip(
overload.aslist(),
position.aslist(),
level.aslist(),
argument_name.aslist(),
datatype.aslist(),
map(bool, default_value.aslist()),
map(Direction, in_out.aslist()),
length.aslist(),
precision.aslist(),
scale.aslist(),
radix.aslist(),
)
def close_connection(conn_db: cx_Oracle.Connection):
"""Close the connection
parameters:
*) conn_db : connection object
"""
conn_db.close()
def __new__(cls, user, pwd, dsn):
if '_conn' not in vars(cls):
cls._conn = Connection.__new__(cls, user, pwd, dsn = dsn)
return cls._conn
def pullLatestData(request):
sync_job_status=''
current_date="''"
try:
url='https://oihap.oraclecorp.com/osbcommon/TicketingService/TicketingRest/reports?id=103784&system=oal%20osvc'
response = requests.get(url)
response.encoding = 'utf-8'
jsonResponse= response.json()
jsonData = jsonResponse["report"]
column=jsonData[0].get('columns')
data=jsonData[0].get('rows')
dat=data
print(len(dat))
cur.execute(data_layer.truncate_table)
for rec in dat:
'''name = item.get("rows")
campaignID = item.get("lookupName")
print(name)
print(campaignID)'''
item=rec.split(',')
#print(item)
if len(item[11])==0:
item[11]="''"
cur.execute("INSERT INTO REPORT ( TICKET, QUEUE, PRODUCT_ID, SOURCE_SYSTEM, CATEGORY, DISPOSITION, CONTROL_NUMBER, BUG_NUMBER, SEVERITY, STATUS,CREATED, CLOSED, ASSIGNED, UPDATED, PRODUCT_HIERARCHY, CREATED_BY ) VALUES ('"
+ str(item[0]) + '\',\'' + str(item[1])+ '\',\''
+ str(item[2]) + '\',\'' + str(item[3])+ '\',\''
+ str(item[4]) + '\',\'' + str(item[5])+ '\',\''
+ str(item[6]) + '\',\'' + str(item[7])+ '\',\''
+ str(item[8]) + '\',\'' + str(item[9])+ '\','
#+ item[10] + '\',\'' + item[11]+ '\',\''
+ "to_date(" + str(item[10]) +",'yyyy-mm-dd hh24:mi:ss')" +','
+ "to_date(" + str(item[11]) +",'yyyy-mm-dd hh24:mi:ss')"+ ',\''
+ str(item[12]) + '\','
+ "to_date(" + str(item[13]) +",'yyyy-mm-dd hh24:mi:ss')"+ ',\''
+ str(item[14]) + '\',\''
# + "q\"[" +item[15] + "]\""+ '\',\'' + str(item[16])+ "')")
+ str(item[15])+ "')")
con.commit()
print("executed")
except Exception as e :
print(e)
sync_job_status='Error'
current_date=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S");
else:
sync_job_status='Success'
current_date=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S");
finally :
cur.execute("INSERT INTO sync_job_status (last_updated_status, last_updated) VALUES ('" + sync_job_status +"'," + "to_date('" + str(current_date) +"','yyyy-mm-dd hh24:mi:ss')"+ ")")
con.commit()
return JsonResponse({"sync_job_status":sync_job_status})
def open_connection(connstr, desc=''):
global conndict
conn = Connection(connstr, encoding="UTF-8")
token = get_token()
conndict[token] = Conn(conn, desc)
return token
def _prepare_matches(con: cx_Oracle.Connection):
"""
Import protein matches in a staging table
:param con: Oracle connection object
"""
cur = con.cursor()
logger.info("populating MATCH_NEW")
oracle.drop_table(cur, "INTERPRO.MATCH_NEW", purge=True)
cur.execute("""
CREATE TABLE INTERPRO.MATCH_NEW NOLOGGING
AS
SELECT *
FROM INTERPRO.MATCH WHERE 1 = 0
""")
cur.execute("""
INSERT /*+ APPEND */ INTO INTERPRO.MATCH_NEW
SELECT
P.PROTEIN_AC, M.METHOD_AC, M.SEQ_START, M.SEQ_END, 'T',
D.DBCODE, D.EVIDENCE,
SYSDATE, SYSDATE, SYSDATE, 'INTERPRO',
M.EVALUE, M.MODEL_AC, M.FRAGMENTS
FROM INTERPRO.PROTEIN_TO_SCAN P
INNER JOIN IPRSCAN.MV_IPRSCAN M
ON P.UPI = M.UPI
INNER JOIN INTERPRO.IPRSCAN2DBCODE D
ON M.ANALYSIS_ID = D.IPRSCAN_SIG_LIB_REL_ID
-- Exclude MobiDB-Lite, Phobius, SignalP (Euk, Gram+, Gram-), TMHMM, COILS
WHERE D.DBCODE NOT IN ('g', 'j', 'n', 's', 'v', 'q', 'x')
AND M.SEQ_START != M.SEQ_END
""")
con.commit()
logger.info("indexing")
for col in ("DBCODE", "PROTEIN_AC"):
cur.execute(f"""
CREATE INDEX I_MATCH_NEW${col}
ON INTERPRO.MATCH_NEW ({col})
TABLESPACE INTERPRO_IND
NOLOGGING
""")
# logger.info("gathering statistics")
# oracle.gather_stats(cur, "INTERPRO", "MATCH_NEW")
logger.info("deleting SUPERFAMILY duplicated matches")
cur.execute("""
DELETE FROM INTERPRO.MATCH_NEW M1
WHERE EXISTS(
SELECT 1
FROM INTERPRO.MATCH_NEW M2
WHERE M2.DBCODE = 'Y'
AND M1.PROTEIN_AC = M2.PROTEIN_AC
AND M1.METHOD_AC = M2.METHOD_AC
AND M1.POS_FROM = M2.POS_FROM
AND M1.POS_TO = M2.POS_TO
AND M1.SCORE > M2.SCORE
)
""")
logger.info(f"{cur.rowcount} SUPERFAMILY matches deleted")
con.commit()
cur.close()
from django.template.loader import get_template
import pandas as pd
from django.views.generic import TemplateView
from .forms import srint_select_form
from .reports import InteractiveGraph
from django.http.response import HttpResponse, JsonResponse
from PED import data_layer
from cx_Oracle import Connection as con
from PED.data_layer import incident_sr_count
# Create your views here.
con = data_layer.getConnectionCursor()
cur=con.cursor()
class PED_summary(TemplateView):
def get(self, request, **kwargs):
form = srint_select_form(initial={'18.2': '18.2'})
return render(request, 'index.html', {'form':form})
def Fetch_Sprint(request):
# if this is a POST request we need to process the form data
if request.method == 'POST':
# create a form instance and populate it with data from the request:
form = srint_select_form(request.POST)
# check whether it's valid:
if form.is_valid():
from cx_Oracle import Connection
import time
import sys
while True:
time.sleep(0.2)
try:
c = Connection("cx_Oracle/dev@localhost")
c.close()
sys.stdout.write(".")
sys.stdout.flush()
except Exception, e:
print e
sys.stdout.write("x")
sys.stdout.flush()
