New post
Question
· Sep 23, 2022

Store Procedure Returning a ResultSet with Embedded Python

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

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.

CREATE Table Goal (
    Id int,
    Text VARCHAR(5000)
);
SQL
SQL


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
Python
Python

Expected result :

Call My_Python_Procedure(Table);

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

How do I go about it ?

Product version: IRIS 2022.1
Discussion (5)1
Log in or sign up to continue
Guillaume Rongier · Sep 26, 2022

I have partially found a solution:

First I create an SQL function with python code :

CREATE FUNCTION sqliknowparser(tText VARCHAR(50000))
    RETURNS VARCHAR(50000)
    LANGUAGE PYTHON
{
    import iknowpy

    engine = iknowpy.iKnowEngine()

    # index some text
    text = 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]
}
SQL
SQL

Then I use this function in my query :

SELECT 
ID, sqliknowparser(Text) as entities
FROM AA.Goal 
SQL
SQL

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

SELECT 
ID, $piece(sqliknowparser(Text),'|',1) as entities
FROM AA.Goal 
union
SELECT 
ID, $piece(sqliknowparser(Text),'|',2) as entities
FROM AA.Goal 
SQL
SQL

Any improvement are welcome :)

0 0