0

I am new to Python and have been given a task to download data from different Database ( MS SQl and Teradata ). The logic behind my code is as follow : 1: Code picks up data for Vendor from an excel file. 2: From that list it loops through all the vendors and gives out a list of documents. 3: Then I use the list downloaded in step 2 to download data from teradata and append in a final dataset.

My question is, if the data in second step is blank the while loop goes in infinite. Any way to exit that an still execute the rest of the iteration?

import pyodbc 
import pandas as pd


VendNum = pd.ExcelFile(r"C:\desktop\VendorNumber.xlsx").parse('Sheet3', 
          dtype=str)
VendNum['Vend_Num'] = VendNum['Vend_Num'].astype(str).str.pad(10, 
         side='left', fillchar='0')

fDataSet = pd.DataFrame()
MSSQLconn=pyodbc.connect(r'Driver={SQL Server Native Client 
     11.0};Server=Servername;Database=DBName;Trusted_Connection=yes;')
 TDconn =  pyodbc.connect
 (r"DSN=Teradata;DBCNAME=DBname;UID=User;PWD=password;",autocommit =True)    


for index, row in VendNum.iterrows():
    DocNum = pd.DataFrame()
    if index > len(VendNum["Vend_Num"]):
        break
    while DocNum.size == 0:
            print("Read SQL " + row["Vend_Num"])
            DocNum = pd.read_sql_query("select  Col1 from Table11 where 
   Col2 = " + "'" + row["Vend_Num"] + "'" + " and Col3 = 
      'ABC'",MSSQLconn)
            print("Execute SQL " + row["Vend_Num"])
  if DocNum.size > 0:    
        print(row["Vend_Num"])
        dataList = ""
        dfToList = DocNum['Col1'].tolist()
        for i in dfToList:
            dataList += "'"+i+ "'" + ","
        dataList=dataList[0:-1]

        DataSet= pd.read_sql("
         Some SQl statement which works fine "),TDconn)
        fDataSet = fDataSet.append(DataSet)               


MSSQLconn.close()
TDconn.close() 

The expected output is to append fDataset with each iteration of the code but when a blank Dataframe ( named DataSet ) is there the while loop doesn't exit.

  • A while loop is supposed to be infinite, until a condition is met. Perhaps create a for loop instead, which makes a few attempts then passes. – Chris Apr 8 at 17:18
0

When you are using system resources you should use

with open(...):
0

As mentioned by Chris,

A while loop is supposed to be infinite, until a condition is met. Perhaps create a for loop instead, which makes a few attempts then passes.

I changed the WHILE to IF and it's working fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.