Loop to run 4 times to try run a SQL procedure, after 4 attempts then stop

Question

I have attempted to write some code in python to do a loop 4 times. It will fail as spTest doesn't exist. So I want to try loop again (repeated 4 times total) if it still can't find it, I want to break out and raise an error.

import traceback
import urllib
from datetime import datetime
import numpy as np
import pandas as pd
import sqlalchemy as db
from sqlalchemy import event
import logging
from tqdm import tqdm
import smtplib
import ssl
from time import sleep

def test():
    for x in range(0, 4):  # try 4 times
        try:
            df = pd.read_sql(sql='EXEC [prod].[spTest]', con=engine, )
            str_error = None
        except Exception as str_error:
            print(str_error)
            pass

        if str_error:
            sleep(2)  # wait for 2 seconds before trying to fetch the data again
        else:
            break

    print(df)

Error I get:

if str_error: 
  UnboundLocalError: local variable 'str_error' referenced before assignment

Updated code:

def test():
    str_error = None
    for x in range(0, 4):  # try 4 times
        try:
            df = pd.read_sql(sql='EXEC [prod].[spTest]', con=engine, )
        except Exception as str_error:
            print(str_error)

        if str_error:
            sleep(2)
        else:
            break

        print(df)

Issue: No longer loops 4 times.

I actually found it quite interesting why there was a `NameError` even after doing `except Exception as str_error` and asked a [follow-up question](https://stackoverflow.com/questions/69465045/what-are-the-scoping-rules-for-exception-handling) about this — Tomerikoo, Oct 06 '21 at 11:43

score 2 · Answer 1 · edited Oct 06 '21 at 11:28

2

The error told you what the issue is - str_error doesn't have any value assigned.

If you really need the str_error outside your try except, you should edit it to:

str_error = None

try:
    df = pd.read_sql(sql='EXEC [prod].[spTest]', con=engine, )
except Exception as error:
    str_error = error
    print(error)

if str_error != None:
    sleep(2)
else:
    break

edited Oct 06 '21 at 11:28

Tomerikoo

18,379
16
47
61

answered Oct 06 '21 at 11:07

Mahrkeenerh

1,104
1
9
25

I have tried this which no longer complains about the str_error, however it no longer does a loop of 4 times. See above updated question – RA19 Oct 06 '21 at 11:11
1

I've edited the answer, and now it should resolve both the issues – Mahrkeenerh Oct 06 '21 at 11:16

score 2 · Accepted Answer · answered Oct 06 '21 at 11:17

Why do you need a separate condition to check if the error occurred? That's exactly what the try/except is for:

for x in range(4):  # try 4 times
    try:
        df = pd.read_sql(sql='EXEC [prod].[spTest]', con=engine, )
        break
    except Exception as str_error:
        print(str_error)
        sleep(2)  # wait for 2 seconds before trying to fetch the data again

Loop to run 4 times to try run a SQL procedure, after 4 attempts then stop

2 Answers2

Linked