Running Python from the Code Editor
The Code Editor supports using Python3 as a scripting language inside the OBJECTTABLE function. No additional packages need to be installed.
The Python Script Engine implemented in the Code Editor is based on GraalVM's Polyglot API which currently supports only *nix systems - which means that Python scripts are supported for CData Virtuality SaaS and *nix-based on-premise CData Virtuality Server.
Scripts can run out of the box without the usage of special characters such as new line (\n
) or tabulation (\t
) and can use if/else statements. Please see below for more examples of how to use Python scripts within ObjectTable.
To execute a Python script, simply specify 'python' as a language in your SQL statement as in the examples below.
Examples
This script evaluates an expression and returns the result stored in the
result
variable, where "result" in double quotes corresponds to the column name shown in the CData Virtuality Server and 'result', to the variable name in the script:
SELECT *
FROM (
ObjectTable (
language 'python'
'result = 1 + 1'
COLUMNS "result" integer 'result')
AS x);;
This script evaluates an expression and obtains the results by using Python's functions:
SELECT *
FROM (
ObjectTable (
language 'python'
'import math
a = math.exp(3)'
COLUMNS "a" integer 'a')
AS x);;
This script evaluates multiple columns with multiple return:
SELECT *
FROM (
ObjectTable (
language 'python'
'a = 1 + 1
b = a + 1
c = b * 2'
COLUMNS "a" integer 'a',
"b" integer 'b',
"c" integer 'c')
AS x);;
This script passes user-defined variables to the script using the
PASSING
keyword, where '2' is the value to be passed andAS two
references the variable name in the script:
SELECT *
FROM (
ObjectTable (
language 'python'
'a = 1 + two'
PASSING '2' AS two
COLUMNS "a" integer 'a')
AS x);;
This more complex script with the
PASSING
keyword uses multiple user-defined variables. Results are returned for each row mentioned in theCOLUMNS
field:
SELECT *
FROM (
ObjectTable (
language 'python'
'a = 1 + two
b = 2 + three
c = 3 + four
d = teststring
e = f
g = charhere'
PASSING '2' AS two,
'3' AS three,
'4' AS four,
'''hello''' AS teststring,
'25.0' AS f,
'''s''' AS charhere
COLUMNS "a" integer 'a',
"b" integer 'b',
"c" integer 'c',
"d" string 'd',
"e" double 'e',
"g" char 'g')
AS x);;
This script shows how to use the if/else statement:
SELECT *
FROM (
ObjectTable (
language 'python'
'result = 2
if (result % 2 == 0):
result = 1
else:
result = 0'
COLUMNS "result" integer 'result')
AS x);;