Using python defined functions in SQL statements?

[Luke Whitehorn]

Hi all,

  I've just spent a few hours searching and came up with nothing so I suspect that the answer to this question is a flat out 'no', but I'd like to be able to confirm it.

So, is it possible to use a python defined function in an SQL query? 

At the moment, I have to get data via a query, iterate though it and build a python list of values calculated from my python function. I'd much rather just have the function called during the query as it'd save me all these steps.

So, is that possible? 

[Chris Lambacher]

Hi,

If you are using SQL Server, that is theoretically possible (though I have never tried it) by using the .NET extensibility of SQL Server. Iron Python is a .NET native version of Python that you could theoretically embed in SQL Server and with a little bit of C# glue code have it run your Python function on every row in the query.

Without details about what it is your function does and why you think it would be better to run it in SQL Server, I question the usefulness of trying to do this. Is it to limit the amount of data that gets sent (because you create some kind of aggregate) or is this just a "hey neat I can do that" kind of a question?

For what it is worth, PostgreSQL has the ability to run python functions though embedding of a CPython interpreter, though I can't remember what it is called.

-Chris

[luke.wh...@gmail.com]

Thanks for the reply.
It's just a 'how neatly can I do that' kind of thing :) Furthermore
I'm using pyodbc to connect to an MS Access database, so I don't think
that it's actually possible in my situation. It's not too big of a
deal, but it's just a bit of a pain to do a secondary step of
processing that could easily be done in SQL if it had access to a
function (all the function would have done is to take a value, and
return something of the same type, nothing crazy).
I'm relatively new to this way of working with databases so I wasn't
sure if it was a common thing to let SQL do some of the processing for
you, or to just use SQL to get the information that you then process
in Python itself.