Forum Discussion

JackSparrow's avatar
JackSparrow
Frequent Contributor
9 years ago

adding a (python) list to project suite variable from database

Hi All,

 

Am trying to fetch the values from database which am able to do now after fetching, I want to add those values as a list in to single project suite variable but here its storing only the last loop variable.

 

  1. And if when there is space like in between South East ,from DB it is unable to fetch the particular value I tried concatenation every thing but its not working.
  2. area of i in the below code contains = North, South, East , South East , West.

 

 

def dbarea():
  for i in range(0,5):
   area = ProjectSuite.Variables.Location
    #DBquery =ProjectSuite.Variables.Deal_DBQuery
   area = area.split(",")
   Log.Message(str(area[i]))
   AConnection = ADO.CreateADOConnection()
    # Specify the connection string
   AConnection.ConnectionString = "Provider=MSDASQL.1;" + \
    "Data Source=LMk2 Penelope";
     #AConnection.ConnectionString = "Provider=MSDASQL.1;" + \
     #"Data Source=itvuat";
   AConnection.LoginPrompt = False
   AConnection.Open()
   #Log.Message((area[i]))
    # Execute a simple query
   RecSet = AConnection.Execute_('select sare_no from sare where short_name='+str(area[i]))
   RecSet.MoveFirst();
   data_container = []
   r= []
   while not RecSet.EOF:  
     # rows1 = ""
      rows = RecSet.Fields.Item["Sare_no"].Value
      data_container.append(rows)
      # rows = rows.append(",")
      #rows.split(",")
      #Log.Message(rows)
      RecSet.MoveNext()  
   AConnection.Close()
   Log.Message(str(data_container))
   dbcount = len(data_container)
   Log.Message("The Database Count is :"+str(dbcount))
   ProjectSuite.Variables.AddVariable("Var5","String")
   ProjectSuite.Variables.Var5 = str(data_container)
   Log.Message(ProjectSuite.Variables.Var5)

Kindly let me know any suggestions

ProjecSuite Variables
Python
  • baxatob's avatar
    baxatob
    9 years ago

         ...but here its storing only the last loop variable.

     

    This is because you have used a wrong indentation as well as have declared variables in the wrong places. Try this:

     

     

    def dbarea():
	area_string = ProjectSuite.Variables.Location    
	all_areas = area_string.split(",")
	data_container = []
	
	for area in all_areas:   
		AConnection = ADO.CreateADOConnection()    
		AConnection.ConnectionString = "Provider=MSDASQL.1;" + \
					       "Data Source=LMk2 Penelope"     
		AConnection.LoginPrompt = False
		AConnection.Open()  
		RecSet = AConnection.Execute_('select sare_no from sare where short_name={}'.format(area))
		RecSet.MoveFirst()
   
		while not RecSet.EOF:       
			rows = RecSet.Fields.Item["Sare_no"].Value
			data_container.append(rows)      
			RecSet.MoveNext()  
	  
		AConnection.Close()
   
       dbcount = len(data_container)
       Log.Message("The Database Count is : {}".format(dbcount))
       ProjectSuite.Variables.AddVariable("Var5","String")
       ProjectSuite.Variables.Var5 = str(data_container)

     

    Also note, that splitting of your string: "North, South, East , South East , West" will return:

     

    ["North",
" South",
" East ",
" South East ",
" West"]

     

    With all spaces. So be careful.

     

     

  • baxatob's avatar
    baxatob
    Community Hero
    9 years ago

         ...but here its storing only the last loop variable.

     

    This is because you have used a wrong indentation as well as have declared variables in the wrong places. Try this:

     

     

    def dbarea():
	area_string = ProjectSuite.Variables.Location    
	all_areas = area_string.split(",")
	data_container = []
	
	for area in all_areas:   
		AConnection = ADO.CreateADOConnection()    
		AConnection.ConnectionString = "Provider=MSDASQL.1;" + \
					       "Data Source=LMk2 Penelope"     
		AConnection.LoginPrompt = False
		AConnection.Open()  
		RecSet = AConnection.Execute_('select sare_no from sare where short_name={}'.format(area))
		RecSet.MoveFirst()
   
		while not RecSet.EOF:       
			rows = RecSet.Fields.Item["Sare_no"].Value
			data_container.append(rows)      
			RecSet.MoveNext()  
	  
		AConnection.Close()
   
       dbcount = len(data_container)
       Log.Message("The Database Count is : {}".format(dbcount))
       ProjectSuite.Variables.AddVariable("Var5","String")
       ProjectSuite.Variables.Var5 = str(data_container)

     

    Also note, that splitting of your string: "North, South, East , South East , West" will return:

     

    ["North",
" South",
" East ",
" South East ",
" West"]

     

    With all spaces. So be careful.

     

     

    • JackSparrow's avatar
      JackSparrow
      Frequent Contributor
      9 years ago

      Hi baxatob,

       

      Thanks for that guidance, now its working but the output the area code from The DB is not storing in a single variable it storing in different variables.

       

       

    • JackSparrow's avatar
      JackSparrow
      Frequent Contributor
      9 years ago

      Hi baxatob

       

      def dbarea():
  area = ProjectSuite.Variables.Deal_Location
  Log.Message("The Area Codes which are give are : " + area)
  for i in range(0,5):
   area = ProjectSuite.Variables.Deal_Location
   area = area.split(",")
   Log.Message("The First value selected is  : "+str(area[i]))
    
   AConnection = ADO.CreateADOConnection()
    # Specify the connection string
   AConnection.ConnectionString = "Provider=MSDASQL.1;" + \
    "Data Source=teachers_DB";
   AConnection.LoginPrompt = False
   AConnection.Open()
   
   RecSet = AConnection.Execute_('select area_no from sare where short_name='+str(area[i]))
   RecSet.MoveFirst();
   data_container = []
   val= []
   while not RecSet.EOF:  
     # rows1 = ""
      r = RecSet.Fields.Item["area_no"].Value
      val = str(r)
      data_container = val.append(",")
      data_container.split(",")
     
      RecSet.MoveNext()
      
   AConnection.Close()
   
   Log.Message("The Area Code number for "+ str(area[i])+ " is " + str(r))
  Log.SaveResultsAs("C:\\Work\\Log3\\",lsHTML)
  Log.Message(str(data_container))  
  ProjectSuite.Variables.AddVariable("Var6","String")
  ProjectSuite.Variables.Var7 = str(data_container)
  Log.Message(ProjectSuite.Variables.Var7)

      in the while loop am trying to append  all retrieved single values into  a list but its not working , its storing only the last loop value. Where did  I went wrong in it.

      • baxatob's avatar
        baxatob
        Community Hero
        9 years ago

        OMG... :)

         

        1. Your WHILE loop is the part of the FOR loop.

        2. You have declared your variable (val) before the WHILE loop, but already within the FOR loop.

         

        As the result you are about to "reset" your val variable on each iteration of the FOR loop.

         

        Please check again my code above. I declare the list variable before all of the loops are defined.