Solved

Python with pyodbc and SQL Server

Posted on 2013-12-24
3
2,293 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
difference between String.subString() and String.subSequence() 6 200
withoutTen challenge 14 123
countX 22 113
Problem to refer to value 8 50
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 tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

831 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