Forum Discussion

dwong_smartbear's avatar
dwong_smartbear
Occasional Contributor
7 years ago

Python - Using ADO and RecordSet in testcomplete scripts

Two questions I have regarding the ADO interface with Python:

 

1. When I do:

rec_set = conn.Execute_("SELECT A, B FROM C WHERE D='E")

... there's no problem, but if I do:

 

rec_set = conn.Execute_("SELECT * FROM C WHERE D='E'")

... I get a RuntimeError - Unspecified error exception window?

 

 

2. I'm not sure how to iterate through and get rows from a RecordSet.  Assuming rec_set above is populated from a successful query, how do you extract each row and put them in a list of dicts (where the dict keys are the column names, each row in the list is a row in the RecordSet).  The examples one example I've seen in:

https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/databases/using-ado-and-bde-objects/working-with-ado-recordset.html

doesn't really cover this in detail.

 

thanks,

  • cunderw's avatar
    cunderw
    7 years ago

    Here is an approach I used for something similar. It basically will take a record set and build an array of objects with their properties being the column names / values. It's javascript, but shouldn't be hard to do a similar approach in python. 

     

    function recordToObjectArray(rec) {
  let retVal = [];
  try {
   while(!rec.EOF) {
      let currObj = {};
      for(var i = 0; i < rec.Fields.Count; i++) {
        currObj[rec.Fields.Item(i).Name] = rec.Fields.Item(i).Value;        
      }
      retVal.push(currObj);
      rec.MoveNext();
    }
  } catch(err) {
    Log.Warning("There was an error building object. See additional information", err.message + "\n" + err.stack);
  } finally {
    return retVal;
  }
}
    • dwong_smartbear's avatar
      dwong_smartbear
      Occasional Contributor
      7 years ago

      Hi tristaanogre thanks for your reply.

      Yes I noticed all the examples I see are of this flavor (using my example):

       

      rec_set = conn.Execute_("SELECT * FROM C WHERE D='E'")
rec_set.MoveFirst()
while not rec_set.EOF:
  Log.Message(rec_set.Fields.Item["A"].Value, rec_set.Fields.Item["B"].Value)
  rec_set.MoveNext()

       

      But what if I don't want just the values for columns "A" and "B", but for all the columns.  Also, I'm still having trouble with the "SELECT *...." statement, which is strange.

      • cunderw's avatar
        cunderw
        Community Hero
        7 years ago

        Here is an approach I used for something similar. It basically will take a record set and build an array of objects with their properties being the column names / values. It's javascript, but shouldn't be hard to do a similar approach in python. 

         

        function recordToObjectArray(rec) {
  let retVal = [];
  try {
   while(!rec.EOF) {
      let currObj = {};
      for(var i = 0; i < rec.Fields.Count; i++) {
        currObj[rec.Fields.Item(i).Name] = rec.Fields.Item(i).Value;        
      }
      retVal.push(currObj);
      rec.MoveNext();
    }
  } catch(err) {
    Log.Warning("There was an error building object. See additional information", err.message + "\n" + err.stack);
  } finally {
    return retVal;
  }
}
  • NisHera's avatar
    NisHera
    Valued Contributor
    7 years ago

    1) I'm not sure why you get this .......I think if works in first should work for second

    Are you sure the connection is Open? or no other problems in your SQL statement ? 

    I can see first statement has 'E  and second has 'E' ...............  note single quote open in first and close in second

     

    2) Haven tried dealing with record set in python but in j Script go like this 

    RecSet.MoveFirst();
    while (! RecSet.EOF )
    {
      Log.Message(RecSet.Fields("Emp ID").Value, RecSet.Fields("Name").Value);
      RecSet.MoveNext();
    }

    Emp ID and Name are SQL fields.......

    MoveFIRST (),  MoveNext () and EOF are recset operations so you only have to convert while loop to python. 

    remember.............!  keep open connection and close after you finish.

    • dwong_smartbear's avatar
      dwong_smartbear
      Occasional Contributor
      7 years ago

      Thanks NisHera I saw this example as well.  However what I was looking for was an iterative way of getting the values.  In this example, "Emp ID" and "Name" are hardcoded, but what if I have a record that has about 100 columns and I want to print all the values of each column?

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        7 years ago
        dwong_smartbear wrote:

        Thanks NisHera I saw this example as well.  However what I was looking for was an iterative way of getting the values.  In this example, "Emp ID" and "Name" are hardcoded, but what if I have a record that has about 100 columns and I want to print all the values of each column?

        Reference cunderw's example... note the for loop... that's how you do it.