Not sure why my Stored Procedure isn't working between pyodbc and SQL Server

Question

I'm pretty new to Python (2.7) and am just not a lot of help with pyodbc. I have a script that calls a stored procedure:

sql = "exec gen_all.dbo.rpt_trinity_total '" + startDate + "', '"  + endDate + "'"

print sql
dbCursor.execute(sql)   
rows = dbCursor.fetchall()

for row in rows:
    print row[0], row[1]

At the end of the stored procedure I returned the dates passed in, just to make sure the SP is getting called at that the parameters are getting set. Everything looks like it should be working, but when I look at the table that is supposed to be populated by the SP, nothing is in there. If I run the SP in Management Console, it works just fine.

I tried what was found to be the solution here, namely:

dbCursor.execute("{call gen_all.dbo.rpt_trinity_total(?,?)}", (startDate),(endDate))

but gave me the same results, nothing. The SP itself is very simple, a TRUNCATE and INSERT, using a SELECT, based on the dates passed.

Just wonder if anyone could give some insight on this. Thanks a bunch.

Answer 1

I suspect the issue is that you did not commit on connection. pyodbc disables auto-commit by default to meet Python DB API specs. Try setting conn.autoCommit = True or call conn.commit() on your connection.