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()
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_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()
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 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 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 _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()
