ae.db_ora

database system core layer to access Oracle databases

the class OraDb of this namespace portion is a thin layer that is extending the DbBase of the module ae.db_core to connect to an Oracle database.

Hint

this namespace portion is using the python-oracledb package.

basic usage of the oracle database layer

to create an instance of the class OraDb you first have to create a SystemBase instance. this can be done either programmatically by providing an application instance (of the class ConsoleApp or an inherited subclass of it) plus any database parameters, like required credentials and any database configuration features/options:

app = ConsoleApp()
cred = {'user': 'username', 'password': 'password'}
system = SystemBase('system-id', app, cred)

alternatively provide all system-specific info within the ae config files and let UsedSystems load it:

system = used_systems['system-id']

finally pass the database parameters in system to create an instance of OraDb:

ora_db = OraDb(system)

then call the connect() method of this instance to connect to the Oracle database server:

error_message = ora_db.connect()
if error_message:
    print(error_message)

the connect() method is returning an error message string if the connection could not be established.

after a successful connection all the methods provided by DbBase, like e.g. update(), are available via the ora_db instance:

error_message = ora_db.update('my_table`, {'my_col': 'new value'})
if error_message:
    print(error_message)
    error_message = ora_db.rollback()

an explicit call of rollback() is only needed if you use transactions. in this case you should also use commit() at the end of each transaction to store any data updates:

error_message = ora_db.commit()

alternatively you can use the commit argument that is provided by the DbBase DML methods: by passing a True value to this argument, the method will automatically execute a commit() call for you if no error occurred in the DML method:

error_message = ora_db.update('table`, {'column': 369}, commit=True)

finally after all database actions are done you can close the connection to the databases server with the close() method:

error_message = ora_db.close()

Classes

OraDb(system)

Oracle database class, based on DbBase

class OraDb(system)

Bases: DbBase

Oracle database class, based on DbBase

__init__(system)

create instance of oracle database object.

Parameters:

system (SystemBase) – instance of a SystemBase class.

SystemBase (defined in the module ae.sys_core) is providing the credentials and features, which get retrieved from config files, then converted by connect_params() into connection parameters to connect to the Oracle database.

connect()

connect this instance to the database driver.

Return type:

str

prepare_ref_param(value)

prepare special Oracle reference parameter.

Parameters:

value (Union[datetime, int, float, str]) – the input value passed into the reference parameter of the called stored procedure.

Return type:

Any

Returns:

a handle to the reference variable.

the following code snippet shows how to use this method together with get_value() to retrieve the returned value of a reference parameter:

ora_db = OraDb(...)
*ref_var* = ora_db.prepare_ref_param("input_value")
err_msg = ora_db.call_proc('STORED_PROCEDURE', (*ref_var*, ...))
if not err_msg:
    output_value = ora_db.get_value(*ref_var*)

static get_value(var)

get output value from a reference variable passed into a stored procedure.

Parameters:

var – handle to a reference variable.

Return type:

Any

Returns:

output value of the reference variable.

static set_value(var, value)

set the input value of a reference variable to pass into a stored procedure.

Parameters:

• var (Any) – handle to the reference variable to set.

• value (Union[datetime, int, float, str]) – value to set as input value of the reference variable.