pyqIndexEval Function (Autonomous Database)

Syntax

FUNCTION PYQSYS.pyqIndexEval(
    PAR_LST VARCHAR2,
    OUT_FMT VARCHAR2,
    TIMES_NUM NUMBER,
    SCR_NAME VARCHAR2,
    SCR_OWNER VARCHAR2 DEFAULT NULL,
    ENV_NAME VARCHAR2 DEFAULT NULL
)
RETURN SYS.AnyDataSet

Parameters

Parameter	Description
`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).
`TIMES_NUM`	The number of times to execute the Python script.
`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

This example defines a Python function to use with Conda environment.

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

begin
  sys.pyqScriptCreate('test_seaborn_idx',
  'def fun_tab(idx):
  import seaborn as sns
  import matplotlib.pyplot as plt
  import numpy as np
  import pandas as pd
  data = np.random.multivariate_normal([0, 0], [[5, 2], [2, 2]], size=2000)
  data = pd.DataFrame(data, columns=["x", "y"])
  sns.displot(data["x"])
  plt.title("Title {}".format(idx))
  plt.show()
  return idx   
  ',FALSE,TRUE); -- V_GLOBAL, V_OVERWRITE
end;
/

This example calls the pyqIndexEval function, which runs the specified Python function multiple times.

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 TIMES_NUM argument specifies to run the specified script 2 times.

The SCR_NAME parameter specifies the 'test_seaborn_idx' script as the Python function to invoke.

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

select *
  from table(pyqIndexEval(
        par_lst => '{"oml_graphics_flag":true}',
        out_fmt => 'PNG',
        times_num => 2,
        scr_name => 'test_seaborn_idx',
        scr_owner => NULL,
        env_name => 'seaborn'
));

The output is the following.

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
TIME_1
         1
1

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
Title 1
89504E470D0A1A0A0000000D4948445200000280000001E0080600000035D1DCE400000039744558
74536F667477617265004D6174706C6F746C69622076657273696F6E332E332E332C206874747073

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
3A2F2F6D6174706C6F746C69622E6F72672FC897B79C000000097048597300000F6100000F6101A8
3FA7690000666749444154789CEDDD797C5355FE3FFE579236E9BEB7495BBA52A0AC2D1428C50594
7E2DA0A3082AA0332083B80C30424747F1A720CE5254441C65649C11706340E68338A28342655128

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

TIME_2
         1

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
2
Title 2
89504E470D0A1A0A0000000D4948445200000280000001E0080600000035D1DCE400000039744558

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
74536F667477617265004D6174706C6F746C69622076657273696F6E332E332E332C206874747073
3A2F2F6D6174706C6F746C69622E6F72672FC897B79C000000097048597300000F6100000F6101A8
3FA7690000687649444154789CEDDD79785355FE3FF0F74DDAA47BBA6FD0D2859DB216A84514957E

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
2DC2A8082A220E8A88CA80A3561DADBF19709919500171D491D1914505451CF7A55A2A204BD95ACA

Example

Define the Python function fit_lm and store it with the name myFitMultiple in the script repository. The function returns a pandas.DataFrame containing the index and prediction score of the fitted model on the data sampled from scikit-learn’s IRIS dataset.

begin
    sys.pyqScriptCreate('myFitMultiple',
        'def fit_lm(i, sample_size):
            from sklearn import linear_model
            from sklearn.datasets import load_iris
            import pandas as pd
            
            import random
            random.seed(10)
            
            iris = load_iris()
            x = pd.DataFrame(iris.data, columns = ["Sepal_Length",\
                        "Sepal_Width","Petal_Length","Petal_Width"])
            y = pd.DataFrame(list(map(lambda x: {0:"setosa", 1: "versicolor",\
                                  2: "virginica"}[x], iris.target)),\
                         columns = ["Species"])
            dat = pd.concat([y, x], axis=1).sample(sample_size)
            regr = linear_model.LinearRegression()
            regr.fit(x.loc[:, ["Sepal_Length", "Sepal_Width", \
                              "Petal_Length"]],
                     x.loc[:,["Petal_Width"]])
            sc = regr.score(dat.loc[:, ["Sepal_Length", "Sepal_Width", \
                              "Petal_Length"]],
                            dat.loc[:,["Petal_Width"]])
            return pd.DataFrame([[i,sc]],columns=["id","score"])
        ',FALSE,TRUE); -- V_GLOBAL, V_OVERWRITE
end;
/

Issue a query that invokes the pyqIndexEval function. In the function, the PAR_LST argument specifies the function argument sample_size. The OUT_FMT argument specifies a JSON string that contains the column names and data types of the table returned by pyqIndexEval. The TIMES_NUM parameter specifies the number of times to execute the script. The SCR_NAME parameter specifies the user-defined Python function stored with the name myFitMultiple in the script repository.

select *
    from table(pyqIndexEval(
        par_lst => '{"sample_size":80,
                        "oml_parallel_flag":true", "oml_service_level":"MEDIUM"}',
        out_fmt => '{"id":"number","score":"number"}',
        times_num => 3,
        scr_name => 'myFitMultiple'));

The output is the following:

        id score
---------- ----------
         1 .943550631
         2 .927836941
         3 .937196049
3 rows selected.

11.7.2.6 pyqIndexEval Function (Autonomous Database)