CODEWITHSUNDEEP
pythonsql-serverstored-procedurespyodbc
localhost
localhost

Reputation: 1082

Python: Pyodbc execute stored procedure with parameters

I'm having trouble executing a SQL Server stored procedure with Python 3.4. I'm importing Pyodbc to create the connection and have a few lines that are supposed to sent data to a stored procedure.

My Python:

sql = "exec <stored_procedure_name> @int1=?, @int2=?, @string1=?, @int3=?"
params = (irow[0], irow[15], irow[2], irow[6])
cursor.execute(sql, params)

Stored Procedure:

  @int1 int,
  @int2 int,
  @string1 varchar(10),
  @int3 int
AS
BEGIN
  --Do stuff
END

I'm not getting an errors in Command Prompt when I execute the script. When I print

print(sql, params)

I get a weird looking result. Nothing is inserted into the table that is being targeted in the stored procedure.

exec <stored_procedure_name> @int1=?, @int2=?, @string1=?, @int3=? (0, 0, 'string', 0)

I'm new to Python so any insight would be greatly appreciated. Nothing I've seen on SO has helped.

Upvotes: 5

Views: 20553

Answers (3)

localhost
localhost

Reputation: 1082

I found that the problem was caused by not having autocommit turned on. Pyodbc has autocommit set to False by default.

To enable autocommit, you can declare the connection like: 

cnxn = pyodbc.connect(driver="<driver>", server="<server>", database="<database>", uid="<username>", pwd="<password>", autocommit=True)

Upvotes: 3

the_deb
the_deb

Reputation: 61

I think what's missing is the commit. Either run the commit method against the cursor:

cursor.execute(sql, params)
cursor.commit()

or set autocommit=True when the connection is created as in the other answers offered. That connection is used to create the cursor, so the autocommit behavior is inherent to it when the execute() is called:

cnxn = pyodbc.connect(driver="<driver>", server="<server>", 
                      database="<database>", uid="<username>", pwd="<password>", 
                      autocommit=True)

Upvotes: 5

erkyky
erkyky

Reputation: 107

Params are usually passed as a tuple, so

params = (irow[0], irow[15], irow[2], irow[6])

should work

Upvotes: 0

Related Questions