To call a stored procedure from a Python application, use pyodbc package. The procedure that you call can include input parameters (IN), output parameters (OUT), and input and output parameters (INOUT).
Execute a stored procedure
import pyodbc server = 'tcp:your_server_name' database = 'your_database_name' username = 'your_username' password = 'your_password' conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) # Create a cursor object cursor = conn.cursor() # Define the stored procedure call with parameters procedure_name = "{CALL your_procedure_name (?, ?, ?)}" # Input parameters input_param1 = 'some_value' input_param2 = 123 # Output parameter (using a placeholder) output_param = 0 # Execute the stored procedure with input and output parameters cursor.execute(procedure_name, (input_param1, input_param2, output_param)) # Call commit() method to save changes to the database conn.commit() # Fetch the output parameter value output_param = cursor.fetchone()[0] # Print the output parameter value print(f"Output Parameter: {output_param}") # Close the cursor and connection cursor.close() conn.close()
Reading Data
# After executing the above stored procedure for row in cursor: # print('row = %r' % (row,)) print(row[0])
-
pyodbc
An open-source Python module, to access SQL Server databases.