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.