CODEWITHSUNDEEP
pythonsqlitemultiple-return-values
Shannon
Shannon

Reputation: 1205

Add a function with multiple return values to SQLite in Python

I want to add a function to SQLite in Python (like explained here)

My function returns multiple values. In python, I can access to different return values by indexing (using []).

However, it seems indexing does not work in SQLite. In other words, the following SELECT statement will have an error:

SELECT my_function(table1.column1)[0] FROM table1;

sqlite3.OperationalError: user-defined function raised exception

Is there any way to access to different return values in SQLite?

Upvotes: 3

Views: 1387

Answers (2)

salva
salva

Reputation: 10234

One way to do that is to have the function return a string with the multiple values encoded as a JSON array and then use SQLite JSON extraction functions to access the individual values:


select json_extract(fout, $[0]) as v0,
       json_extract(fout, $[1]) as v1
from (select my_func(cols...) as fout
      from ...)

I have used that solution myself and performance is ok. The JSON encoding/decoding doesn't seem to introduce a noticeable performance penalty.

Upvotes: 2

CL.
CL.

Reputation: 180030

The only way to return multiple values from a function is with a table-valued function, which requires creating a virtual table module, which is not possible with only the default Python SQLite driver.

There are additional Python modules to allow this, for example, sqlite-vtfunc.

Upvotes: 3

Related Questions