pyqTableEval Function (On-Premises Database)

This topic describes the pyqTableEval function when used in an on-premises Oracle Database. The pyqTableEval function runs a user-defined Python function on data from an Oracle Database table.

You pass data to the Python function with the INP_NAM parameter. You can pass arguments to the Python function with the PAR_QRY parameter.

The 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_QRY parameter.

Syntax

pyqTableEval (
    INP_NAM     VARCHAR2       IN
    PAR_QRY     VARCHAR2       IN
    OUT_QRY     VARCHAR2       IN
    EXP_NAM     VARCHAR2       IN)

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 EXP_NAM 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_QRY

A JSON string that contains additional parameters to pass to the user-defined Python function specified by the EXP_NAM parameter. Special control arguments, which start with oml_, are not passed to the function specified by EXP_NAM, 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"}'

OUT_QRY

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 name of a table or view to use as a prototype. 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.
  • 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.

EXP_NAM

The name of a user-defined Python function in the OML4Py script repository.

Returns

Function pyqTableEval returns a table that has the structure specified by the OUT_QRY parameter value.

Example 9-16 Using the pyqTableEval Function

This example stores a user-defined Python function in the OML4Py script repository with the name create_iris_table. It uses the function to create a database table as the result of a pyqEval function invocation. It creates another user-defined Python function that fits a linear regression model to the input data and saves the model in the OML4Py datastore. The example runs a SQL SELECT statement that invokes the pyqTableEval function, which invokes the function stored in the script repository with the name myLinearRegressionModel.

In a PL/SQL block, define the Python function create_iris_table and store in the script repository with the name create_iris_table, overwriting any existing user-defined Python function stored in the script repository with the same name.

The create_iris_table function imports and loads the iris data set, creates two pandas.DataFrame objects, and then returns the concatenation of those objects.

BEGIN
  sys.pyqScriptCreate('create_iris_table',
    'def create_iris_table():
       from sklearn.datasets import load_iris
       import pandas as pd
       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"])
       return pd.concat([y, x], axis=1)',
    FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
END;
/
CREATE TABLE IRIS AS
(SELECT * FROM pyqEval(
      NULL,
      '{"Species":"VARCHAR2(10)","Sepal_Length":"number",
        "Sepal_Width":"number","Petal_Length":"number",
        "Petal_Width":"number"}',
      'create_iris_table'
));

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

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_QRY parameter specifies the names of the model and datastore to pass to the Python function, and specifies the oml_connect control argument to establish an OML4Py connection to the database during the invocation of the user-defined Python function. The OUT_QRY parameter specifies returning the value in XML format and the EXP_NAM 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(
  'IRIS',
  '{"modelName":"linregr",
    "datastoreName":"pymodel",
    "oml_connect":1}',
  'XML',
  'myLinearRegressionModel'));

The output is the following:

NAME  VALUE
----- ------------------------------------------------------------
      <root><str>LinearRegression()</str></root>