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

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;
/

Use the following code to create the 'test_seaborn_inp' script:

begin  sys.pyqScriptCreate('test_seaborn_inp',  
         'def fun_tab(dat):
          import seaborn as sns
          import matplotlib.pyplot as plt
          sns.lineplot(x="Sepal_Length", y="Sepal_Width", data=dat)
          plt.title("Iris plot")
          plt.show()
          return "hello world"
          ',FALSE,TRUE); -- V_GLOBAL, V_OVERWRITE
end;
/

This example calls the pyqTableEval function, which runs the specified Python function on the specified data set.

The INP_NAM argument specifies the data in the IRIS table to pass to the Python function.

The PAR_LST argument specifies capturing images rendered in the script with the special control argument oml_graphics_flag.

The OUT_FMT arguments specifies returning a table with BLOB containing the images generated by the Python function.

The SCR_NAME parameter specifies the 'test_seaborn_inp' script, which is the name in the script repository of the user-defined Python function to invoke.

The ENV_NAME parameter specifies 'seaborn', which is a Conda environment created in pyqEval Function (Autonomous Database) .

select *
  from table(pyqTableEval(
        inp_nam =>  'IRIS',
        par_lst => '{"oml_graphics_flag":true}',
        out_fmt => 'PNG',
        scr_name => 'test_seaborn_inp',
        scr_owner => NULL,
        env_name => 'seaborn'
));

The output is the following.

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------

         1
"hello world"

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
Iris plot
89504E470D0A1A0A0000000D4948445200000280000001E0080600000035D1DCE400000039744558
74536F667477617265004D6174706C6F746C69622076657273696F6E332E332E332C206874747073

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
3A2F2F6D6174706C6F746C69622E6F72672FC897B79C000000097048597300000F6100000F6101A8
3FA7690000B9BC49444154789CECDD797CDC759D3FF0D7F79A2B9399DC499BA44DEF527A41B9CA61
3945C042576559500AABAC2BE2AE8ABA520459442CBA80E0FA0304517057B60A0B28972C22E5A6F4

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------

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.