Post date: Nov 09, 2014 6:1:29 PM
Here's another way to solve a couple recent programs. It uses the PythonService, which I developed just for this stupid SQL trick. To use the PythonService to solve a Project Euler question, build a string containing the python to execute and then call the dbo.RunPython UDF to get the result. RunPython is a CLR user-defined function that builds the proper JSON body, makes a web service call to the service, and returns the JSON response back to you. It doesn't parse out the JSON for you. I haven't yet come up with a good idea for how to handle dynamic objects in a non-dynamic, non-object-oriented scripting environment. I could have returned XML and let you use xquery, but xquery is a crime against humanity and should not be enabled to do any more harm than it already does.
RunPython uses JsonFx to build the JSON. JsonFx is released under an MIT-style license and is Copyright 2006-2010 Stephen M. McKamey. I included my Visual Studio solution for RunPython, using .Net Framework 3.5, which includes the relevant version of JsonFx. If you want to use a different version of JsonFx.dll, download the right version from https://github.com/jsonfx/jsonfx/downloads and use it. You cannot simply replace the DLL, because Visual Studio will choke. Remove the reference to the old DLL, replace the file, and then add a reference to the replaced DLL.
The database will need to be in UNSAFE mode to work (you will also probably need to set it as TRUSTWORTHY). Reflection is heavily restricted in the Microsoft SQL Server world. If CLR integration is not enabled, you must enable it.
You could use the MakeJSONWebRequest method from PythonRunner to make any web post to a HTTP or RESTful service. It's longer than the Python version (using requests) but it's still very simple.
dbo.RunPython has the following four inputs:
url - The URL on which PythonService is listening.
command - The text of the Python command to run (infinite length).
arguments - Any input arguments for the command, listed as a comma-separated string in the format variable1, value1, variable2, value2, etc. If you need a value that contains a comma, enclose the item using double-quote marks as the quote delimiter (see the documentation for Kipper for more examples). These arguments are treated as variables to be populated before the command in command is run. You could always concatenate these values into the command, but remember the story of little Johnny Tables. A service that runs any code you reqeust it to is dangerous enough without concatenating in dirty user data.
variables - Any output arguments whose values are to be returned as results of the command, listed as a comma-separated string. Variables in python must be named as simple alphanumeric strings, so complex parsing rules are not needed or allowed here.
Now that that's out of the way, here's a new solution to Project Euler problem 52:
DECLARE @command nvarchar(max)
SET @command = '
def testPermute(n):
sort = "".join(sorted(str(n)))
match = lambda n, i: sort == "".join(sorted(str(i*n)))
for i in range(2, 6):
if not match(n, i):
return False
return True
matched = False
result = 0
while not matched:
result += 1
matched = testPermute(result)
'
SELECT dbo.RunPython('http://localhost:8080/', @command, NULL, 'result')
And here's a new solution to Problem 48. I told you it was simple:
DECLARE @command nvarchar(max)
SET @command = ' result = str(sum([x ** x for x in range(1,1000)]))[-10:]'
SELECT dbo.RunPython('http://localhost:8080/', @command, NULL, 'result')