1

I've a simple table called Points with two columns QuestionID and UserID:

QuestionID  UserID
-------------------
5            2
6            1

I wrote a stored procedure to insert data into this table:

CREATE PROCEDURE [dbo].[SetPoints] 
     @QuestionID INT, 
     @UserID INT 
AS
     INSERT INTO dbo.Points (QuestionID, UserID)
     VALUES (@QuestionID, @UserID)

I executed this procedure and it seems to be working fine.

Then I wrote Python code to connect to this database and execute this procedure.

import pyodbc as po
    
# Connection variables
server = '################'
database = '#############'
username = '#############'
password = '################'
    
# Connection string
cnxn = po.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
            server+';DATABASE='+database+';UID='+username+';PWD=' + password)
cursor = cnxn.cursor()

# Prepare the stored procedure execution script and parameter values
storedProc = "EXEC SetPoints @QuestionID = ?, @UserID = ?"
params = (2, 3)
    
# Execute stored procedure with parameters
cursor.execute(storedProc, params)

# Close the cursor and delete it
cursor.close()
del cursor
    
# Close the database connection
cnxn.close()

When I run the Execute the Python code shows no errors, but if I check the Points table, nothing is inserted....

Can somebody tell me what's going on? The procedure works fine in SSMS but when I execute it from Python, nothing happens.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jurgen Windcaller
  • 41
  • 8

1 Answers1

0

SOLUTION: The solution was to add auto_commit=True to the pyodbc.connect() function. I found the answer from another post on this site.

Jurgen Windcaller
  • 41
  • 8
  • Interesting. `IMPLICIT_TRANSACTIONS ON` is the same as `auto_commit=off`. That's kinda wierd that instead of pyodbc using that official option it appears to take it's own transaction manually. – Charlieface Jan 17 '21 at 10:18