Handling exception when there are multiple sql queries to be fired

Question

I am fetching data from database using four select queries. The data is in such a way that there are cases when the input to select query may be empty. In that case it is okay for that particular select statement to not work. In short, what I want is that the four select statements should fire and whichever statements work should work irrespective of other query getting failed.

try:
    cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip_i)

except Exception as e:
    print("error while fetching details " + str(e))

result_i = cur.fetchall()

try:
    cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip_n)

except Exception as e:
    print("error while fetching details " + str(e))

result_n = cur.fetchall()

try:
    cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip_c)

except Exception as e:
    print("error while fetching details " + str(e))

result_c = cur.fetchall()

try:
    cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip_b)

except Exception as e:
    print("error while fetching details " + str(e))

result_b = cur.fetchall()

but if an exception occurs, you'll still perform a fetchall ? — Jean-François Fabre
– Jean-François Fabre ♦, Commented Oct 22, 2018 at 9:18
Put the ip_x values in a list and iterate through it. Side note: don't use string formatting to build queries. — roganjosh
– roganjosh, Commented Oct 22, 2018 at 9:18
@mash If someone's answer helped you then please upvote / accept the answer. — amitchone
– amitchone, Commented Oct 22, 2018 at 10:05

Alexis Drakopoulos · Accepted Answer · 2018-10-22 09:27:38Z

2

yes just put it in a for loop.

ip_list = [ip_i, ip_n, ip_c, ip_b]
result_list = []

for ip in ip_list:
    try:
        cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip)

    except Exception as e:
        print("error while fetching details " + str(e))        

    result_list.append(cur.fetchall())

I am guessing that cur.fetchall() does not generate an error, if it does or you don't want it to run then you can put it inside the try one.

So I would change it to this to keep track of which generated errors;

ip_list = [ip_i, ip_n, ip_c, ip_b]
result_list = []

for ip in ip_list:
    try:
        cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip)
        result_list.append(cur.fetchall())

    except Exception as e:
        print("error while fetching details " + str(e))  
        result_list.append("ERROR")

edited Oct 22, 2018 at 9:27

answered Oct 22, 2018 at 9:19

Alexis Drakopoulos

1,1457 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

roganjosh Over a year ago

result_list.append(cur.fetchall()) will fail on an exception, or may give duplicate values

Alexis Drakopoulos Over a year ago

ah I thought it didn't since that's what he's doing in his original code

roganjosh Over a year ago

Hmm, I guess we could assume the cursor is global. I still think the append should be done inside the try so that they fail together rather than trying to call fetchall() if the query fails.

Alexis Drakopoulos Over a year ago

I mean this can easily be fixed by just moving the append to after the cur.execute inside the try

roganjosh Over a year ago

Yep, which is what I'm suggesting you edit in your answer :)

|

amitchone · Accepted Answer · 2018-10-22 09:22:29Z

0

Add your IP address variables to a list and then iterate over the list. Here I'm using the traceback module to print an entire stack trace (not just the exception) and I'm also doing the fetchall within the try block, otherwise, if an exception does occur during the execute you'd be trying to fetch nothing.

import traceback

ip_list = [ ip_i, ip_n, ip_c, ip_b ]

for ip in ip_list:
    try:
        cur.execute("select IP_ADD,VENDOR,DVC_ROLE,CIRCLE,SSA,REGION from DVC_SUMMARY_DATA where IP_ADD in (%s);" % ip)

        result = cur.fetchall()

    except Exception as e:
        print(traceback.format_exc())

answered Oct 22, 2018 at 9:22

amitchone

1,6383 gold badges23 silver badges47 bronze badges

Collectives™ on Stack Overflow

Handling exception when there are multiple sql queries to be fired

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related