CODEWITHSUNDEEP
pythonsql-server-2008pyodbc
spuppett
spuppett

Reputation: 557

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

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.

Upvotes: 5

Views: 8005

Answers (3)

Evi Oj
Evi Oj

Reputation: 1

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")

Upvotes: 0

Abolfazl Rastgou
Abolfazl Rastgou

Reputation: 832

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()

Upvotes: 0

Yevgen Yampolskiy
Yevgen Yampolskiy

Reputation: 7198

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.

Upvotes: 17

Related Questions