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, but the language permission has to be added to the user role.

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.

The admin-role does not include the necessary permissions to use Python by default. To grant Python access for users with the admin-role, you need to add the L permission for the "python" resource via a custom role and assign this custom role to the intended users.

L permission for Python removed from the admin-role since v4.9

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

1. 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);;

2. 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);;	

3. 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);;

4. This script passes user-defined variables to the script using the PASSING keyword, where '2' is the value to be passed and AS 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);;

5. This more complex script with the PASSING keyword uses multiple user-defined variables. Results are returned for each row mentioned in the COLUMNS 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);;

6. 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);;