SQL Server – Does SSMS v17 Allow User Input in Python Scripts?

pythonsql serversql-server-2017ssms

With the introduction of Python feature shipped with SQL Server 2017, I would like to know if anyone has been able to use SSMS v17.x and run a python script that requires a user input. Here is a sample basic script:

execute sp_execute_external_script  @language = N'Python', 
@script =N' print ("This is a PYTHON test")
userinput = int(input ("Enter LBs to convert to KG")) 
converted = userinput * 0.453592     
print (converted) '

The script runs endlessly in SSMS while if it was just a print statement; that completes successfully and produces the print result.

I know I can use a Python IDE console like Pycharm but I am wondering if SSMS has such capabilities yet. My research on the internet has not been that fruitful. Most of the use of Python is related to modelling and analysis.

Best Answer

I cannot see any possible way for this to work given that the script is being submitted through a back-end service where there is no user-context (i.e. display, etc). SSMS is not executing anything. It is merely submitting those statements to SQL Server which handles the execution (even if SQL Server then passes it off to something else).

The only potential possibility of getting around this, and it is would have been a long-shot and not likely to be a practical work-around even if it does did work, is to run this statement against SQL Server Express LocalDB. Because LocalDB runs in user mode (not an NT service), it does have access to the console / display. I know this because I have been able to pop-up a Windows dialog / confirm box via SQLCLR when using LocalDB :-D.

HOWEVER, I just tried and couldn't even get the print to work. So, I checked the supported features list ( Editions and supported features of SQL Server 2017 ) and neither R nor Python are available in Express Edition, and hence not in LocalDB. :-(

Related Question