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

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.

CodePudding user response：

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

CodePudding user response：

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