Re: Returning empty results from an embedded python table function.

Hey Joseph, It’s quite a naive hack intended only for easier prototyping. The function indeed gets loaded every time it is called, so if you intend to call the function often it’s not a good idea to store it in a file. Also, another word of warning: keep the permissions of the files in mind. Anyone that can modify the files can change the code that gets run when the functions get called. If other people have access to your server make sure the file permissions are set so they cannot modify the files. Thanks for your suggestion, that would indeed be a nicer way of implementing it. The nice thing about the current implementation from my perspective is that it’s a trivial implementation (I just read the file and use it as function body), but if I have time I might implement your nicer solution. Mark

On 18 Dec 2016, at 17:16, Joseph D'silva wrote:

Thank you Mark, that worked.

And thanks for the tip. Does this approach have an overhead compared to my original code, especially if the function definition is not going to change (of course once things are fully developed) ? What I mean to ask is does the database keep track of the timestamp of the file to skip reading it repeatedly ?

Also, I foresee having a lot of related python functions, so ideally, I would prefer to keep the related functions together in a single file (like a module) and be able to refer to them by their name in the create function explicitly.

i.e., something like this. CREATE FUNCTION test() RETURNS TABLE(i INTEGER) LANGUAGE PYTHON ‘/home/user/file.py:test_function’;

As, the way it is now, the file.py should contain a return statement outside of the body of a function. i.e something like this.

return 101

This makes it impossible to test some of these things outside of the database using Python as

python3 file.py

will throw an error.

But on the other hand, if the above mentioned syntax is supported, we can write.

# file.py

def test_function(): return 101

if __name__ == '__main__': test_function()

And this would work in both the database as well as outside of it. (not to mention we can have multiple functions in file.py (like a module )

But thanks for the work on integrating python into the database, the potential is quite immense given the emerging trends.

Joseph

On Sat, Dec 17, 2016 at 6:03 AM, Mark Raasveldt mailto:M.Raasveldt@cwi.nl> wrote: Hey Joseph,

You can return an empty result set by returning a list of empty lists, e.g.:

CREATE FUNCTION empty_table() RETURNS TABLE(i INTEGER, j INTEGER) LANGUAGE PYTHON { return [[],[]] };

It seems the dictionary result doesn’t work correctly when returning an empty array. I will create a testcase and fix it.

Another tip for prototyping functions, you can enter a file path instead of a function body. This way you don’t need to constantly recreate the function, e.g.:

CREATE FUNCTION test() RETURNS TABLE(i INTEGER) LANGUAGE PYTHON ‘/home/user/file.py’;

Everytime the function is executed the function body will be loaded from the file.

Hope that helps,

Mark

...

On 16 Dec 2016, at 18:31, Joseph D'silva mailto:joedsilva@gmail.com> wrote:

Hi All,

I am trying to return an "empty" result set from an embedded python function (as in some situations I will not have valid rows to produce in the output). However the very many attempts of trying to return an "empty" result from the function have failed (as is documented in the example below).

How do you get it done ?

Thanks.

DROP FUNCTION p_results_exp01; CREATE FUNCTION p_results_exp01() RETURNS TABLE(val1 STRING, val2 INTEGER) LANGUAGE PYTHON { #Unsupported result object. Expected either a list, dictionary, a numpy array, a numpy masked array or a pandas data frame, but received an object of type "" #return None; #Errors

#Expected a return value with name "val1", but this key was not present in the dictionary. #return {}; #Errors

#Error converting dict return value "val1": MALException:pyapi.eval:Unsupported result object. Expected either a list, dictionary, a numpy array, a numpy masked array or a pandas data frame, but received an object of type "". #return {'val1':None, 'val2':None}; #Errors

#Error converting dict return value "val1": MALException:pyapi.eval:An array of size 0 was returned, yet we expect a list of 1 columns. The result is invalid.. #return {'val1':[], 'val2':[]};

#return {'val1':['value_1'], 'val2':[1]}; #Works, as it has value. }; SELECT * FROM p_results_exp01();

Joseph _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list