Hi, I just wanted to share a new tool I wrote that makes it very easy to
write table-valued functions with Python. Basically this is a wrapper
around a virtual table, allowing the Python developer to write two methods
(initialize and iterate) and get the full table-valued function benefits.
The way it works is your Python implementation defines a list of columns
(return values) and params (hidden columns). When the function is invoked
via SQLite, the param values are passed in to the Python initialize method.
Here is how the "generate series" implementation looks:
from vtfunc import TableFunction
class GenerateSeries(TableFunction):
params = ['start', 'stop', 'step']
columns = ['output']
name = 'series'
def initialize(self, start=0, stop=None, step=1):
self.start = start
self.stop = stop or float('inf')
self.step = step
self.curr = self.start
def iterate(self, idx):
if self.curr > self.stop:
raise StopIteration
ret = self.curr
self.curr += self.step
return (ret,)
If you're interested, the source code is up on github:
https://github.com/coleifer/sqlite-vtfunc
I've also written a blog post:
http://charlesleifer.com/blog/sqlite-table-valued-functions-with-python/
Lastly, I wonder if the SQLite development team would be amenable to the
idea of creating a standard API for creating table-valued functions,
something along the lines of the one used in this Python project? Basically
a wrapper around the current virtual table interface.
Thanks for reading! Any feedback would be most welcome,
Charlie
