newbie database question

[Bill Tate]

"james kowalka" <jkowalka at nycap.rr.com> wrote in message news:<UnX_7.37065$qv.6254802 at typhoon.nyroc.rr.com>...
> Bill,
>     I was able to load the DBF and read it into the tuple, but isn't there
> an easier way to navigate through the DBF and read/write from one record at
> a time.  How would you write changes back into the table from a tuple,
> especially if there were thousands of records?
> 
> Thanks,
> 
> Jim
> 

James,
My apologies, I didn't see your questions to me earlier.  Let me try
to answer each of them:

Performing DB transactions.  Welcome to the world of objects and SQL. 
In SQL you have to execute a separate statement to the database for
each record that you want to do an update on (whether it be update,
insert, delete) - so yes you could be iterating over a list of a 1000
records quite easily.  Before I get into this, you may wish to add
Webware for Python's MiddleKit implementation to your list of things
to consider.

The most basic ABC's of this (in ODBC anyway) are as follows (again a
very simple approach):
 
    1. open connection get connection object
    2. use connection object to get a cursor object 
    3. use cursor.execute(<sql string>) - you can use the cursor's
description attribute to get a list of fields (note this is a list of
tuples - check this out to get familiar with what gets returned)
    4. Fetch the records which, when in the case of using odbc,
returns a python list.  Other implementations might return something
equivalent to the functionality of a python list specifically designed
to work with collection of database records.  There are numerous
options but in any event, you are working with an in-memory structure
that holds data that may or MAY NOT be in a dbf table.
    5. An application typically works with the list - making whatever
necessary changes to members stored list based on various business
rules. This is where things usually start to get hairy, i.e., keeping
track of new members in the list, members that should be deleted,
members that have/have not been modified, etc... Since you are going
to want to know this when you determine whether you should be sending
a update/insert/delete sql statement to the database - even this
approach is no guarantee, i.e., send an update statement when a record
has since been deleted - again welcome to the world of SQL
    6. Using helper functions or helper classes, iterate over the
contents of the list, build a sql statement (INSERT, DELETE, UPDATE)
dynamically using field names in the list (see cursor.description) and
current data values in EACH member in the list.
    7. Execute the SQL statement using a cursor.execute(<my update sql
statement>) and trap any exceptions that might occur.
    8. Close connection when finished.

There are no shortage of different ways to do all this stuff from
simple to very complex.  In any event, some aspect of either an
implementation you create or which is provided by another 3rd party
tool has to deal with all the database integrity issues.  You have to
deal with all the gymnastics of ensuring that you are not trying to
update a record that has since been deleted by someone else, or
inserting a record with a duplicate primary key value, etc. 
Eventually you probably want to check out papers written on
persistence services layer - there was a python conference paper some
years ago on this that may be useful to you (the author's name was
Shrentz(sp?) I believe) and is available via the python.org web site. 
Again check out middlekit - it could make things a lot easier for you.

ADO - there are numerous tutorials on the web using ADO with Python
and Mark Hammonds book Python Programming on Win32 covers this as
well.  There is a fair amount of discussion on this subject that's
worth getting to know.  In any event, using ADO in python is pretty
straight-forward thanks to win32com but you usually do take a
performance hit but it can make your life a lot easier.  You will also
want to check out the MSDN library on ADO to get more familiar with
its functionality (if you are not already).  In most cases, the
difference between using ado in Python versus say VB or VBScript has
more to do with the instantiation of com objects.  After that it
usually becomes fairly straightforward from there to use ADO objects.

good luck,
Bill