10.6.2.3 pyqTableEval Function (Autonomous Database)

The function pyqTableEval function when used in Oracle Autonomous Database, runs a user-defined Python function on data from an Oracle Database table.

Pass data to the user-defined Python function from the table name specified in the INP_NAM parameter. Pass arguments to the user-defined Python function with the PAR_LST parameter.

The user-defined Python function can return a boolean, a dict, a float, an int, a list, a str, a tuple or a pandas.DataFrame object. You define the form of the returned value with the OUT_FMT parameter.

Syntax

FUNCTION PYQSYS.pyqTableEval(
   INP_NAM    VARCHAR2,
   PAR_LST    VARCHAR2,
   OUT_FMT    VARCHAR2,
   SCR_NAME   VARCHAR2,
   SCR_OWNER  VARCHAR2 DEFAULT NULL,
   ENV_NAME   VARCHAR2 DEFAULT NULL
   )
   RETURN SYS.AnyDataSet

Parameters

Parameter	Description
INP_NAM	The name of a table or view that specifies the data to pass to the Python function specified by the SCR_NAME parameter. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view.
PAR_LST	A JSON string that contains additional parameters to pass to the user-defined Python function specified by the SCR_NAME parameter. Special control arguments, which start with oml_, are not passed to the function specified by SCR_NAME, but instead control what happens before or after the invocation of the function. For example, to specify the input data type as pandas.DataFrame, use: '{"oml_input_type":"pandas.DataFrame"}' See also: Special Control Arguments (Autonomous Database).
OUT_FMT	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The Python function must return a pandas.DataFrame, a numpy.ndarray, a tuple, or a list of tuples. The string 'JSON', which specifies that the table returned contains a CLOB that is a JSON string. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured Python objects first, followed by the image or images generated by the Python function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the Python function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats (Autonomous Database).
SCR_NAME	The name of a user-defined Python function in the OML4Py script repository.
SCR_OWNER	The owner of the registered Python script. The default value is NULL. If NULL, will search for the Python script in the user’s script repository.
ENV_NAME	The name of the conda environment that should be used when running the named user-defined Python function.

Example

Define the Python function fit_model and store it with the name myLinearRegressionModel as a private function in the script repository, overwriting any existing user-defined Python function stored with that name.

The fit_model function fits a regression model to the input data dat and then saves the fitted model as an object specified by the modelName argument to the datastore specified by the datastoreName argument. The fit_model function returns the fitted model in a string format.

By default, Python objects are saved to a new datastore with the specified datastoreName. To save an object to an existing datastore, either set the overwrite or append argument to True in the oml.ds.save invocation.

BEGIN
    sys.pyqScriptCreate('myLinearRegressionModel',
        'def fit_model(dat, modelName, datastoreName):
            import oml
            from sklearn import linear_model
            regr = linear_model.LinearRegression()
            regr.fit(dat.loc[:, ["Sepal_Length", "Sepal_Width", \
                                "Petal_Length"]],
                    dat.loc[:,["Petal_Width"]])
            oml.ds.save(objs={modelName:regr}, name=datastoreName,
                         overwrite=True)
            return str(regr)',
        FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
END;
/

This example uses the IRIS table created in the example shown in pyqEval Function (Autonomous Database). Run a SELECT statement that invokes the pyqTableEval function. The INP_NAM parameter of the pyqTableEval function specifies the IRIS table as the data to pass to the Python function. The PAR_LST parameter specifies the names of the model and datastore to pass to the Python function. The OUT_FMT parameter specifies returning the value in XML format and the SCR_NAME parameter specifies the myLinearRegressionModel function in the script repository as the Python function to invoke. The XML output is a CLOB; you can call set long [length] to get more output.

SELECT * 
FROM table(pyqTableEval( 
    inp_nam => 'IRIS', 
    par_lst => '{"modelName":"linregr", 
                 "datastoreName":"pymodel"}', 
out_fmt => 'XML', 
scr_name => 'myLinearRegressionModel'));

The output is the following:

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
<root><str>LinearRegression()</str></root>
1 row selected.