从MSSQL运行Python脚本 [英] Run Python Script from MSSQL

问题描述

我想从MSSQL 2008运行或执行Python脚本.

我有一台带有python的服务器，我想要的是本地网络中的任何PC都可以使用某些输入执行该脚本，这些输入存储在MSSQL 2008 R2中. python脚本使用pandas(read_sql)读取这些变量，然后进行一些分析，并返回图像和表格，它们都保存在服务器的文件夹中，任何人都可以看到.

我遇到的问题是:我如何执行另一台装有python的计算机中的python脚本，但是我没有它，我只需要存储输出，但是这些输出会随时间变化，所以我必须随时执行该脚本?

我一直在尝试将MSSQL 20008与cmd Job(操作系统(CmdExec))一起使用，这是代码:

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'hola' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'hola',  
    @step_name = N'hola1',  
    @subsystem = N'CMDEXEC',  
    @command = N'python \..\script.py',   
    @retry_attempts = 1,  
    @retry_interval = 1;
GO  

我执行了它的每个部分，但没有收到任何错误，但是当我来看图像是否已创建时，我找不到它. (我将图像保存到一个已知的文件夹:fig.savefig('C:\ Images3 \\ Prueba3.png'))

我也尝试使用xp_cmdshell

EXEC xp_cmdshell 'python \\server\script.py';  
GO

但是它返回此错误:"python" no se reconoce como un comando interno o externo，但是python在系统路径中.当我使用cmd并输入python时，它将打开python，当我输入"python \ server \ script.py"时，它将执行脚本

同样使用xp_cmdshell，我尝试使用powershell，并制作并执行python脚本，当我在power shell中执行该脚本时，它可以工作，但是当我输入以下代码时:

EXEC xp_cmdshell 'powershell.exe -ExecutionPolicy Unrestricted -file  C:\Images3\script.ps1';  
GO 

它给了我与上一个"EXEC xp_cmdshell'python \ server \ script.py'"相同的错误.

我正在使用anaconda python，当我在CMD中输入路径时，它就在其中:请参阅带有PATH的图像 在此处输入图片描述

但是我不知道如何执行它.

请有人帮忙，或者知道另一种方法.

非常感谢您的宝贵时间和支持

今天过得很愉快！

解决方案

使用

EXEC xp_cmdshell 'C:/.../python.exe C:\...\script.py';  
GO

一个问题是，您需要在xp_cmdshell中使用.exe，因此仅通过python命令它不起作用，您必须使用python.exe. 另一个问题是python脚本必须位于执行该脚本的计算机中，以前该脚本位于公共服务器中，但是它拒绝了该权限，并且当将该脚本的目录更改为该服务器时，它可以正常工作./p>

I want to Run or Execute a Python Script from MSSQL 2008.

I have a server with python, and what i want is that any PC in the local network can execute that scripts with some inputs, those inputs are stored in a MSSQL 2008 R2. The python script read those variables with pandas (read_sql) and then do some analysis and it return an image and a table, both of them are saved in a folder of a server and anybody can see it.

The problem I have is: How can I execute a python script that is in another computer that has python, but I dont have it, I just need the Outputs that are stored but those outputs changes with time so i have to execute that script anytime?

I've been trying using MSSQL 20008 with the cmd Job (Operating System (CmdExec)), here is the code:

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'hola' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'hola',  
    @step_name = N'hola1',  
    @subsystem = N'CMDEXEC',  
    @command = N'python \..\script.py',   
    @retry_attempts = 1,  
    @retry_interval = 1;
GO  

I execute each part of it but i don't get any error, but when I come to see if the image was created, i cant find it. (I save the image to a known folder: fig.savefig('C:\Images3\\Prueba3.png') )

I also try using xp_cmdshell

EXEC xp_cmdshell 'python \\server\script.py';  
GO

But it return this error: ""python" no se reconoce como un comando interno o externo", but python is in the path of the system. When I use the cmd and i entre python, it opens python, and when i enter "python \server\script.py" it executes the script

Also with xp_cmdshell i try to use powershell, and make and script executing python, when i execute that script in the power shell it works, but when i enter this code:

EXEC xp_cmdshell 'powershell.exe -ExecutionPolicy Unrestricted -file  C:\Images3\script.ps1';  
GO 

It gives me the same error as in the previous "EXEC xp_cmdshell 'python \server\script.py'".

I'm using anaconda python, and when i enter path in the CMD it is there: See image with the PATH enter image description here

But i don't know how to execute it.

Please can someone help, or know another way to do this.

I appreciate really much your time and your support

Have a really nice day!

解决方案

It worked using

EXEC xp_cmdshell 'C:/.../python.exe C:\...\script.py';  
GO

One problem is that you need to use a .exe in the xp_cmdshell, so just with the python command it doesn't work, you must use the python.exe. Another problem was that the python script must be in the computer that is executing the script, previously the script was in a common server, but it denied the permission, and when the script was changed of directory to this server, it worked!.

这篇关于从MSSQL运行Python脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！