Python with pyodbc and SQL Server

I am building a query batch of multiple queries to be executed from python in sql server like:

update tbl1 set... where...
insert tbl2 values...
update tbl3 set... where...

If the insert causes a PK violation the execution of the batch does not report anything. It is like the execution is successful. However the same query executed directly in SQL server will fail.

How can I catch the error?
LVL 27
Who is Participating?
mankowitzConnect With a Mentor Commented:
You will have to catch errors individually, using exceptions. For example

conn = pyodbc.connect( 'dsn=MyDSN')
cur = conn.cursor()
     cur.execute ( 'SELECT * FROM table')
except pyodbc.Error:
     print 'caught an error... continue?'

Open in new window

ZberteocAuthor Commented:
I need to rectify my question a bit. My problem isn't that the error is not caught but the error message which is not explicit, doesn't really say what happened:

      ErrNo:      HY007
      ErrMsg:      [HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared (0) (SQLNumResultCols)

I also tied to wrap the SQL query batch in TRY ... CATCH and if there is error (and there is PK violation) I use RAISERROR SQL statement to bring it back in Python.

However in this case the RAISERROR is completely ignored by Python and it won't report error unless the first command in the query batch produces it!

I want to catch the real error message, even if produced in the middle of the batch and bring it back to python to log it.
ZberteocAuthor Commented:
I am not sure what happened but now I get the error after a combination of try..catch in python with try..catch with raiserror in SQL that I execute. Anyway I consider the question answered.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.