Solved

Python with pyodbc and SQL Server

Posted on 2013-12-24
3
2,203 Views
Last Modified: 2013-12-29
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?
0
Comment
Question by:Zberteoc
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39739967
You will have to catch errors individually, using exceptions. For example

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

Open in new window

0
 
LVL 26

Author Comment

by:Zberteoc
ID: 39739991
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.
0
 
LVL 26

Author Closing Comment

by:Zberteoc
ID: 39744581
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now