another newboe mxodbc quest. -- update set

[David Bolen]

"Nicole Cook" <cook at maya.com> writes:

> I'm running python 1.5.2 on windows98 and using mxODBC to talk to an Access
> database.   I'm having no trouble reading information from the database, but
> now I want to write something to a column.
> 
> So I do :
> db = ODBC.Windoes.connect("accessdb")
> cs = db.cursor()
> 
> cs.execute("UPDATE Project SET Item='"+item+"' WHERE Name='"+name+"'")
> 
> When I do this from  the interpreter it returns 1, when I use it as a line
> in a function Access freezes until I quit python.  Either way, nothing
> happens to the column in the db that I'm trying to write.  Is there
> something else I have to do to get mxODBC to execute an update?

As far as an actual command goes, it looks ok (barring bad values in
either item or name that might mess up the quoting - you might want to
check that for debugging purposes).

Could you provide additional samples of the code - particularly how it
is used within the function?

As for seeing the change - mxODBC (conforming to the DB-API 2.0) sets
any database that supports transactions to use them by default.  I'm
not familiar enough with Access (which I'm guessing your using with
the default Jet engine?) to know if it supports them, but if it does,
then you won't see your change until you db.commit().  So if you just
exit your script (or drop the database connection object), your
changes will be rolled back.

mxODBC has an mxODBC specific named parameter on the connect() call
"clear_auto_commit" which can be used to defeat this default behavior,
as in:

    db = ODBC.Windows.connect(<database>,clear_auto_commit=0)

which will let the database's default transaction behavior take over.
That may or may not still require you to commit, depending on the
database and your ODBC definition for that database.

--
-- David
-- 
/-----------------------------------------------------------------------\
 \               David Bolen            \   E-mail: db3l at fitlinxx.com  /
  |             FitLinxx, Inc.            \  Phone: (203) 708-5192    |
 /  860 Canal Street, Stamford, CT  06902   \  Fax: (203) 316-5150     \
\-----------------------------------------------------------------------/