Python: using pyodbc and replacing row field values

I'm trying to figure out if it's possible to replace record values in a Microsoft Access (either .accdb or .mdb) database using pyodbc. I've poured over the documentation and noted where it says that "Row Values Can B开发者_开发百科e Replaced" but I have not been able to make it work.

More specifically, I'm attempting to replace a row value from a python variable. I've tried:

• setting the connection autocommit to "True"

• made sure that it's not a data type issue

Here is a snippet of the code where I'm executing a SQL query, using fetchone() to grab just one record (I know with this script the query is only returning one record), then I am grabbing the existing value for a field (the field position integer is stored in the z variable), and then am getting the new value I want to write to the field by accessing it from an existing python dictionary created in the script.

pSQL = "SELECT * FROM %s WHERE %s = '%s'" % (reviewTBL, newID, basinID)

cursor.execute(pSQL)
record = cursor.fetchone()
if record:
    oldVal = record[z]
    val = codeCrosswalk[oldVal]
    record[z] = val

I've tried everything I can think bit cannot get it to work. Am I just misunderstanding the help documentation?

The script runs successfully but the newly assigned value never seems to commit. I even tried putting "print str(record[z])this after the record[z] = val line to see if the field in the table has the new value and the new value would print like it worked...but then if I check in the table after the script has finished the old values are still in the table field.

Much appreciate any insight into this...I was hoping this would work like how using VBA in MS Access databases you can use an ADO Recordset to loop through records in a table and assign values to a field from a variable.

thanks, Tom

---

The "Row values can be replaced" from the pyodbc documentation refers to the fact that you can modify the values on the returned row objects, for example to perform some cleanup or conversion before you start using them. It does not mean that these changes will automatically be persisted in the database. You will have to use sql UPDATE statements for that.