Executing stored procedure through Python NativeAPI
I'm looking into finding ways to fetch data from cache efficiently and work with it with python - specially pandas.
i tried the following but i'm wondering if this is the most efficient way?
to run the stored procedure as %ResultSet inside cache in a classmethod and serialize the data to JSON
and then call that class method from python
Class User.RegObj Extends %RegisteredObject { ClassMethod runAlpha() As %Library.DynamicAbstractObject { set QHi=##class(%DynamicAbstractObject).%FromJSON("{}") set rs=##class(%ResultSet).%New("User.Person:alpha") set pr=rs.Execute("","") set idx=0 while rs.Next() { set idx=$I(idx) Set miniList=##class(%DynamicAbstractObject).%FromJSON("{}") do miniList.%Set(1,rs.Data("name")) do miniList.%Set(2,rs.Data("DB")) do QHi.%Set(idx,miniList) } return QHi.%ToJSON() } }
call from Python
>>> import irisnative as iris
>>> conn=iris.createConnection("127.0.0.1", 51773, "USER", "_SYSTEM","SYS")
>>> wrk=iris.createIris(conn)
>>> print(wrk.classMethodValue("User.RegObj","runAlpha"))
{"1":{"1":"adel","2":"62116"},"2":{"1":"adam,2019-01-01","2":"62116"},"3":{"1":"nadam^2019-01-01","2":"62116"},"4":{"1":"nadam:2019-01-01","2":"62116"},"5":{"1":"madam","2":"62116"},"6":{"1":"jadam","2":"62116"},"7":{"1":"qadam","2":"62116"}}
the JSON can be further manipulated to a pandas dataframe.
Hi Adel!
Maybe it's not the answer to how to call the procedure from Python. But as soon as you have some ObjectScript code already you may want to try the way to call Python (pandas) from IRIS using Objectscript and transfer the data from your query to the python libraries and run it.
Python Gateway does the thing, here is the set of articles and also tagging @Eduard Lebedyuk and @Sergey Lukyanchikov to provide more details if any.
With Python Gateway you "drive" your Python context by sending to it the portions of your script and retrieving to your IRIS context the Python objects you need:
When the Python object is retrieved into IRIS context, you are free to decide how to harvest it, for example:
@Jenny Ames @Raj Singh could you please help?
As other commenters have stated you can use Python Gateway to drive Python process, in that case check this article, the Data Transfer section specifically. Also join our MLToolkit@intersystems.com usergroup - it's focused on AI/ML scenarios.
I'm a user of Python Native API so I can give the following advice. There are two cases here:
In addition to the other suggestions, if you're interested in working in a Jupyter notebook this is a good article: https://community.intersystems.com/post/python-jdbc-connection-iris-data...