0
350

Written by

Guillaume Rongier
Sales Engineer at InterSystems
STAFF
Follow
Question Guillaume Rongier · Sep 23, 2022

Store Procedure Returning a ResultSet with Embedded Python

#InterSystems Natural Language Processing (NLP, iKnow) #InterSystems IRIS #Embedded Python #SQL

Hello,

I'm looking for a way to write a stored procedure or something to return a ResultSet with Embedded Python.

My goal is the following:

I have a Goal table with a Text field that is free text.

CREATETable Goal (
    Idint,
    TextVARCHAR(5000)
);


I would like to create a procedure that returns all the entities (in the iKnow sense) in a new Entity column.

Python code, i would like to use :

import iknowpy

engine = iknowpy.iKnowEngine()

# Row to parse
text = 'This is a test of the Python interface to the iKnow engine. another sentence to test this program with.'
engine.index(text, 'en')

for s in engine.m_index['sentences']:
    for e in s['entities']:
        if e['type'] == 'Concept':
            print(e['index']) # Should return one new row in the result set

Expected result :

Call My_Python_Procedure(Table);

Entity
-----
test
python interface
iknow engine
sentence
program

How do I go about it ?

Product version: IRIS 2022.1
Discussion (5)0
Log in or sign up to continue

Comments

Eduard Lebedyuk · Sep 23, 2022

Define a custom class query with Exec/Fetch methods in python, after that call this query from a method with ReturnResultsets enabled.

Or just call custom class query from SQL.

0
Guillaume Rongier  Sep 23, 2022 to Eduard Lebedyuk

Is it possible to do the same thing only in SQL and Python?

CREATEPROCEDURE procname(parameter_list)
    [ characteristics ]
    LANGUAGE PYTHON
   { code_body }
0
Eduard Lebedyuk  Sep 23, 2022 to Guillaume Rongier

You can generate methods using Native API for Python, so technically yes.

0
Guillaume Rongier · Sep 26, 2022

I have partially found a solution:

First I create an SQL function with python code :

CREATEFUNCTION sqliknowparser(tText VARCHAR(50000))
    RETURNSVARCHAR(50000)
    LANGUAGE PYTHON
{
    import iknowpy

    engine = iknowpy.iKnowEngine()

    # indexsometexttext = tText
    engine.index(text, 'en')

    t_output = ""

    # or make it a little nicer
    for s in engine.m_index['sentences']:
        for e in s['entities']:
            if e['type'] == 'Concept':
                t_output = t_output  + e['index']+ "|"return t_output[:-1]
}

Then I use this function in my query :

SELECTID, sqliknowparser(Text) as entities
FROM AA.Goal

Then I "piece" it an use a union query :

SELECTID, $piece(sqliknowparser(Text),'|',1) as entities
FROM AA.Goal 
unionSELECTID, $piece(sqliknowparser(Text),'|',2) as entities
FROM AA.Goal

Any improvement are welcome :)

0