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.

Ossicle

Just tried this SP with out the insert, only the select, and it's returning just fine. – Ossicle

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.

Lippe

The same applies to pymssql - I tried calling a stored procedure but without commit it won't make any changes. Thank you for also explaining this behaviour. – Eusebiaeusebio

Answer 2

add cursors.commit() after cursors.execute(qry, params)

        cursors = sql_con.cursor()
        qry = "exec SP_NAME @Parm1 = ?, @Parm2 = ?"""
        params = (val1, val2)
        cursors.execute(qry, params)
        cursors.commit()
        cursors.close()

Mcvey

Answer 3

While using SQLAlchemy with python, this line must be added to setup connection string with engine:

conn=engine.connect()
conn.execution_options(isolation_level="AUTOCOMMIT")

Lethargy