pyodbc is not updating table

Question

Basically I'm trying to update Column1_mbgl field data in Table1, all based in MS Access database. The script gets executed without any errors, but when the table is checked no update occurred. I have tried two options as shown in the code without any success. The second option is the SQL code generated directly from MS Access query. Can anybody suggest what I'm missing in the code?

#import pypyodbc
import pyodbc

# MS ACCESS DB CONNECTION
pyodbc.lowercase = False
conn = pyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\temp\DB_access.accdb;")

# OPEN CURSOR AND EXECUTE SQL
cur = conn.cursor()

# Option 1 - no error and no update
cur.execute("UPDATE Table1 SET Column1_mbGL = Column2_mbGL-0.3 WHERE ((Column3_name='PZ01') AND (DateTime Between #6/14/2016 14:0:0# AND #6/16/2016 12:0:0#) AND (TYPE='LOG'))");

# Option 2 - no error and no update
#cur.execute("UPDATE Table1 SET Table1.Column1_mbGL = [Table1]![Column2_mbGL]-0.3 WHERE (((Table1.Column3_name)='PZ01') AND ((Table1.DateTime) Between #6/14/2016 14:0:0# And #6/16/2016 12:0:0#) AND ((Table1.TYPE)='LOG'))");

cur.close()
conn.close()

Depository

Did you check whether you have records in db satisfying the where condition? Also, it is worth to check whether the query updates as expected outside python. – Uncaredfor

yes the same query ran inside MS Access with successful updates, so there is data for this condition. – Depository

You forgot to conn.commit() after executing your UPDATE query. – Omnivore

@GordThompson, you are a life saver, thanks very much! – Depository

Answer 1

You forgot to conn.commit() after executing your UPDATE query. The Python database API specifies that connections open with "autocommit" off by default, so an explicit commit is needed.

Omnivore