[python-win32] Dict, List and Com Object...oh my!

[Waldemar Osuch]

On Thu, Jul 30, 2009 at 11:20 AM, Person, Roderick<personrp at upmc.edu> wrote:
> I'm connecting to a MS SQL 2005 database using ADO com objects and getting a dataset from a stored procedure -- Works great.
>
> I'm placing that dataset into a dictionary -- works.
> I'm placing that dictionary into a list -- problem!! When I move to the next record in the dataset and place the dictionary created from that into the list all the members that are in the list change to that record.

Not really a pywin32 related by I will bite :)
For every iteration of the while loop you have to create a new
dictionary,  fill it up with data and append to the list.

>
> here is that portion of the code:
>
>    prv640_records = []
>    while not rtn.EOF:
        prv640_dic = {}
>      prv640_dict['promise_id'] = rtn.Fields.Item('promise_id').Value
>      prv640_dict['service_location'] = rtn.Fields.Item('service_location').Value
>      prv640_dict['mco_code'] = rtn.Fields.Item('subnetwork_id').Value
>      prv640_dict['begin_date'] = rtn.Fields.Item('begin_date').Value
>      prv640_dict['end_date'] = rtn.Fields.Item('end_date').Value
>      prv640_dict['mco_prov_number'] = rtn.Fields.Item('provider_id').Value
>      prv640_dict['action'] = rtn.Fields.Item('STATUS').Value
>
>      rtn.MoveNext()
>      prv640_records.append(prv640_dict)
>
> So the first time through the while loop, prv640_records will contain one dictionary that is the first record in rtn. The second time through the loop prv640_records will contain 2 dictionaries that are both the 2nd record in rtn. The first dictionary in prv640_records is somehow replaced.
>
> So in testing this with a record set of 402 records, at the end of the while loop, the list prv640_records will contain 402 dictionaries that are all the last record of rtn.
>

It really is the same dictionary referenced 402 times.
In a debugger  type
>>> id(prv640_records[0]), d(prv640_records[1])

It will display two identical identifiers.

Also I would recommend using adodbapi or pyodbc module for talking to
SQL Server.
adodbapi is already included in pywin32 distribution.
The API of both those extensions follow DBAPI 2
http://www.python.org/dev/peps/pep-0249/
Much nicer IMHO.

Waldemar