Python: Execute Stored Procedure with Parameters

Question

I'm working on a Python script that writes records from a stored procedure to a text file. I'm having issues executing the stored procedure with parameters.

I'm not sure what I could do differently to execute this stored procedure with both parameters. You can see the error I'm getting below.

Any insight would be appreciated.

Here's my code

# Import Python ODBC module
import pyodbc

# Create connection
cnxn = pyodbc.connect(driver="{SQL Server}",server="<server>",database="<database>",uid="<username>",pwd="<password>")
cursor = cnxn.cursor()

# Execute stored procedure
storedProc = "exec database..stored_procedure('param1', 'param2')"

# Loop through records
for irow in cursor.execute(storedProc):

    # Create a new text file for each ID
    myfile = open('c:/Path/file_' + str(irow[0]) + '_' + irow[1] + '.txt', 'w')

    # Write retrieved records to text file
    myfile.write(irow[2])

    # Close the file
    myfile.close()

Here's the error

Traceback (most recent call lst):
File "C:\Path\script.py", line 12, in <module>
  for irow in cursor.execute(storedProc):
      pyodbc.ProgrammingError: ('42000', "[4200] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'param1'. <102> <SQLExecDirectW>">

localhost · Accepted Answer · 2015-12-11 17:32:07Z

5

I was able to fix the syntax error by removing the parenthesis from the query string.

# Execute stored procedure
storedProc = "exec database..stored_procedure('param1', 'param2')"

should be

# Execute stored procedure
storedProc = "exec database..stored_procedure 'param1','param2'"

answered Dec 11, 2015 at 17:32

localhost

1,0824 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nusrath · Accepted Answer · 2019-03-06 06:09:12Z

2

This worked for me

query = "EXEC [store_proc_name] @param1='param1', @param2= 'param2'"
cursor.execute(query)

answered Mar 6, 2019 at 6:09

Nusrath

5271 gold badge4 silver badges16 bronze badges

Comments

Hugo Vares · Accepted Answer · 2020-01-12 14:27:12Z

1

For SQL Server:

cursor.execute('{call your_sp (?)}',var_name)

edited Jan 12, 2020 at 14:27

answered Jan 12, 2020 at 14:11

Hugo Vares

1,1678 silver badges7 bronze badges

Collectives™ on Stack Overflow

Python: Execute Stored Procedure with Parameters

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related