Ask a Question

Python - Using ADO and RecordSet in testcomplete scripts

SOLVED
dwong_smartbear
Occasional Contributor

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-sou...

doesn't really cover this in detail.

 

thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
cunderw
Community Hero

Re: Python - Using ADO and RecordSet in testcomplete scripts

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;
  }
}

Thanks,
Carson

Click the Accept as Solution button if my answer has helped

View solution in original post

12 REPLIES 12
tristaanogre
Community Hero

Re: Python - Using ADO and RecordSet in testcomplete scripts

You don't extract a row... you loop through the records in the record set using a while loop while rec_set.EOF is false.  Basic Python code for going through a record set resulting from an SQL query can be found at https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html

 


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
NisHera
Valued Contributor

Re: Python - Using ADO and RecordSet in testcomplete scripts

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
Occasional Contributor

Re: Python - Using ADO and RecordSet in testcomplete scripts

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?

dwong_smartbear
Occasional Contributor

Re: Python - Using ADO and RecordSet in testcomplete scripts

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.

tristaanogre
Community Hero

Re: Python - Using ADO and RecordSet in testcomplete scripts

To access all the columns, you need to include all the columns in your loop.  You will either need to designate each one individually or you will need to embed a for loop within the while loop to write out the values of the columns based upon index (0 to ColumnCount).  There is no way to say "Write out the row" as far as I know... you need to access the data in a record set via column.

 

As for your select query problems.... might be helpful to see your EXACT code rather than the pseudo-code.  It could be some way that you're formatting stuff in the SQL query string that is not translating nicely.  


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
cunderw
Community Hero

Re: Python - Using ADO and RecordSet in testcomplete scripts

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;
  }
}

Thanks,
Carson

Click the Accept as Solution button if my answer has helped

View solution in original post

dwong_smartbear
Occasional Contributor

Re: Python - Using ADO and RecordSet in testcomplete scripts

@tristaanogre thanks again for responding.  Here're my select statements, showing one that worked, and another one that didn't:

 

This one works:

rec_set = conn.Execute_("SELECT ACTIONTYPE, BID, TYPE, ETD FROM ADMD.TT_ADJUST WHERE ETD in ('AZ1014')")

 

This one doesn't:

rec_set = conn.Execute_("SELECT * FROM ADMD.TT_ADJUST WHERE ETD in ('AZ1014')")

 

I get this exception:

 

RuntimeError
Unspecified error
Error location:
Unit: "pyADMD\pyADMD\Script\sandbox"
Line: 33 Column: 1.
tristaanogre
Community Hero

Re: Python - Using ADO and RecordSet in testcomplete scripts


@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.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
dwong_smartbear
Occasional Contributor

Re: Python - Using ADO and RecordSet in testcomplete scripts

@cunderw thanks! that was the solution.  Just for completeness this is translation in Python:

 

  l_recset = []
  rec_set.MoveFirst()
  while not rec_set.EOF:
    d_row = {}
    for i in range(rec_set.Fields.Count):
      d_row[rec_set.Fields.Item[i].Name] = rec_set.Fields.Item[i].Value
    l_recset.append(d_row)
    rec_set.MoveNext()
cancel
Showing results for 
Search instead for 
Did you mean: