Forum

[MikeAtkinson]

Do you have any example using external_scripts with python?

[MikeAtkinson]

The following script shows some python external scripts that you could try out, with techniques to deal with potential problems (e.g. ensuring the LIMERICK script only runs one instance, and so the output is only generated once, rather than once for every ram store process in the system; ensuring the ALTERNATE script can deal with multiple input streams from the database)

Code: Select all

-- Set up Python environment
drop script environment PYTHON cascade;
create script environment PYTHON command '/usr/bin/python';

LIMERICK: Static output

Code: Select all

create external script LIMERICK environment PYTHON
    sends (SEQ int, TEXT varchar character set utf8)
    limit 1 threads --<-- so we don't get repeated output
    script S'EOF(
print "1,There was a young curate from Kew";
print "2,Who kept a tom cat in a pew";
print "3,And taught it to speak";
print "4,Alphabetical Greek";
print "5,But it never got further than 'µ'.";
    )EOF'; --<-- S'EOF(...)EOF' to prevent quoting problems
external script LIMERICK order by 1;

ENV: Print the environment (once for each script instance)

Code: Select all

create external script ENV environment PYTHON
    sends (VAR varchar, VALUE varchar)
    script '
import os
import csv # Use csv to avoid quoting problems with commas in variables
import sys
csvwriter = csv.writer(sys.stdout)
for (k, v) in os.environ.iteritems():
    csvwriter.writerow([k, v])
';
external script ENV;

DUPLICATE: Return each row twice

Code: Select all

create external script DUPLICATE environment PYTHON
    receives (...)
    sends (...)
    script '
import sys
for line in sys.stdin:
    print line[:-1] # remove trailing newline otherwise you get ET010F error
    print line[:-1]
';

external script DUPLICATE from (select 'Double vision');
external script DUPLICATE from (select 'Double vision',
'with',
4,
'arguments');
select * 
from (external script DUPLICATE from (select ID, NAME from IPE_TABLE)) DT
where ID < 5
order by ID;

REVERSE: Reverse text input

Code: Select all

create external script REVERSE environment PYTHON
receives (TEXT varchar)
sends (TEXTOUT varchar)
script '
import sys
for line in sys.stdin:
    print line[0:-1][::-1]
';
external script REVERSE from (select 'abcdefg');

ALTERNATE: between lines in two input streams

Code: Select all

create external script ALTERNATE environment PYTHON
    receives (TEXT1 varchar) in 3 --<-- Use file handles 3 and 4 because there's
    receives (TEXT2 varchar) in 4 --<-- only one standard input
    sends (TEXTOUT varchar)
    limit 1 threads --<-- want to serialise the input
script '
import os

f3 = os.fdopen(3, "r")
f4 = os.fdopen(4, "r")

finished_3 = False
finished_4 = False

while not (finished_3 and finished_4):
    l = f3.readline()
    if len(l) > 0:
        print l[:-1] # remove trailing newline otherwise you get ET010F error
    else:
        finished_3 = True
    l = f4.readline()
    if len(l) > 0:
        print l[:-1]
    else:
        finished_4 = True
';

external script ALTERNATE
from (select * from values ('A'), ('B'), ('C'), ('D'), ('E')),
(select * from values ('a'), ('b'), ('c'), ('d'), ('e'));