11.6.3 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_LST 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_FMT parameter.
Syntax
pyqTableEval(
inp_nam VARCHAR2,
par_lst VARCHAR2,
out_fmt VARCHAR2,
scr_name VARCHAR2,
scr_owner VARCHAR2 DEFAULT NULL)Parameters
| Parameter | Description |
|---|---|
|
|
The name of a table or view that specifies the data to pass to the
Python function specified by the |
|
|
A JSON string that contains additional parameters to pass to the
user-defined Python function specified by the For example, to specify the input data type as
|
|
|
The format of the output returned by the function. It can be one of the following:
|
|
|
The name of a user-defined Python function in the OML4Py script repository. |
|
|
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. |
Returns
Function pyqTableEval returns a table that has the structure
specified by the OUT_FMT parameter value.
Example 11-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_LST 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_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(
'IRIS',
'{"modelName":"linregr",
"datastoreName":"pymodel",
"oml_connect":1}',
'XML',
'myLinearRegressionModel'));The output is the following:
NAME VALUE
----- ------------------------------------------------------------
<root><str>LinearRegression()</str></root>