python - SQL Update statement but using pyodbc

Question

I am using a pyodbc driver to connect to a microsoft access table using SQL. Does anyone know how I go about replacing fields within this table?? I have though about deleting the row and then putting the row back but that would change the primary key due to the autonumber in access.

I have this for inserting into the Progress table:

        cnxn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=C:\\Users\\...............(file location)')
        cursor = cnxn.cursor()
        cursor.execute("insert into Progress(CockpitDrill,Mirrors,MoveOff,TurnLeft) values (?,?,?,?)",cockpit,mirrors,moveOff,turnLeft,)
        cnxn.commit()

So how would I replace these fields. Let's say I wanted to change CockpitDrill from '2' to '3', (They are all strings).

Any help would be greatly appreciated.

SKoczian SKoczian · Accepted Answer · 2015-01-21T15:49:46

You can execute an UPDATE statement just as you now execute your INSERT:

    cnxn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=C:\\Users\\...............(file location)')
    cursor = cnxn.cursor()
    cursor.execute("UPDATE progress SET CockpitDrill = ? WHERE progress_primarykey = ?", newcockpitdrillvalue, oldprimarykeyvalue)
    cnxn.commit()

Does that help? "progress_primarykey" is the assumed name I've given to the primary key field in your database table. That's supposing you just want to change one record and you know its primary key.

python - SQL Update statement but using pyodbc

1 Answers