Hi,

I have a python script which saves some web data to a csv file.

Let's say my script is located at c:\Python\data.py

I'd like to run the Python script from SQL Server ideally from the job agent or a stored procedure.

How do I do this?

Thanks,

Bassmann

To run it from Agent, set up a job step of Type CmdExec. (That's the second dropdown in the Step dialogue.)

To run it from a stored procedure, you would write a CLR stored procedure that runs the script. In this case, the assembly must be installed the EXTERNAL ACCESS permission set. This requires that the assembly is signed with a key (a.k.a "strong name"), and this key is then imported into the master database, whereupon a login is created from the key and this login is granted EXTERNAL ACCESS permission.

There is also xp_cmdshell, but that is definitely not recommendable.

I would recommend that you start it from Agent, and don't involve the Database Engine at all.

Hi Erland,

Ive tried a few ways with a job step of Type CmdExec.

1 and 2 below complete successfully, but my python script has not run (because it hasnt done what its meant to).

3. Out of curiosity I did try cmdshell and get the error The system cannot find the file specified

I can run my script by double clicking or at the command prompt so there's nothing worng with my script. I'm not sure where Im going wrong.

Is there something wrong with my syntax?

Any help appreciated

Examples tried as job step, type CmdExec

1

CD C:\Python

data.py

2

Powershell "C:\Python\data.py"

3

EXEC master..xp_cmdshell N 'C:\Python34\python.exe C:\Python\data.py';

I also experimented in the powershell

cd c:\Python

PS C:\Python> powershell "C:\Python\data.py"

This works....

PS C:\Python> cd..

PS C:\> powershell "C:\Python\data.py"

Traceback (most recent call last):

  File "C:\Python\data.py", line 3, in <module>

    urllib.request.urlretrieve(url, "intraday.csv")

  File "C:\Python34\lib\urllib\request.py", line 196, in urlretrieve

    tfp = open(filename, 'wb')

PermissionError: [Errno 13] Permission denied: 'intraday.csv'

PS C:\>

This doesn't

On that, I tried this in the agent step

CD C:\Python
Powershell "C:\Python\data.py"

It says it completed successfully, but didn't run my py script.

Hi bassmann2,

It think you have to Install python on the database server to make it work.I do remember one of my friend having a similar issue like your's and it got resolved only after installation of python  on the database server.

Please check the below link

http://databaseadministration.aspcode.net/ppst/63547616526662281691826/in-sql-server-how-to-launch-a-python-script-from-an-insert-trigger

Hi Milan,

Can confirm Python is already installed on the server. It lets me run .py script from the servers command line. Just having issues running from SQL Job Agent.

Regards,

Bassmann2