12.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 12-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>