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]
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 _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 _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 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 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 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 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 _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 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 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 _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(), )
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():
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()