Python: Pyodbc execute stored procedure with parameters

Question

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.

score 5 · Answer 1 · edited Mar 28 '17 at 07:13

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)

score 3 · Accepted Answer · edited Sep 18 '17 at 17:33

3

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)

edited Sep 18 '17 at 17:33

wargre

4,575
1
19
35

answered Dec 16 '15 at 19:22

localhost

1,062
3
15
35

score 0 · Answer 3 · answered Dec 15 '15 at 18:36

0

Params are usually passed as a tuple, so

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

should work

answered Dec 15 '15 at 18:36

erkyky

107
6

Thank you for the response. That didn't work. I updated the question to include the parenthesis and a piece of my stored procedure. – localhost Dec 15 '15 at 18:41

Python: Pyodbc execute stored procedure with parameters

3 Answers3

Linked