Need to update contents of access database using a dictionary using python 3

Question

Need to update contents of a column labelled "usage" in a table in access database labelled "INFO". I am doing this by cross referencing variables in another column labelled "ID" to the corresponding value of these variables stored a python dictionary, and updating the values in the database using the values that match the variables in the dictionary.

The code I show below throws out an error that basically says dictionary keys and values take no arguments. Any suggestions on what i can do to get this code to work such that I can update contents of the access database table using a dictionary.

import pyodbc as pyo
import pandas as pd
import numpy as np

conn_str= (r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"r"DBQ=C:\database.mdb")
conn=pyo.connect(conn_str)
crsr=conn.cursor()

#code to get list of all IDs from table in database

Custinfo_ = np.array([row for row in crsr.execute('select ID from INFO')]).flatten()

#dictionary of ID's to be updated.
dict={"cus1":3, "cus2:4}

for i in range (0, len(Custinfo_)):
    sql= f"UPDATE INFO SET Usage = '{dict.values(i)}' WHERE ID ={dict.keys(i)}"
    crsr.execute(sql)
    
crsr.commit()
crsr.close()
conn.close()

Answer 1

You don't need to retrieve all of the ID values in the table. You only need the ID values of the rows you want to update, and you have them in your dict. So, for an existing table named INFO

ID    Usage
----  -----
cus1      0
cus2      0
cus3      0

you can just do

# dictionary of ID's to be updated.
my_dict = {"cus1": 3, "cus2": 4}

sql = "UPDATE INFO SET Usage = ? WHERE ID = ?"
params = [(v, k) for k, v in my_dict.items()]
print(params) 
# [(3, 'cus1'), (4, 'cus2')]

crsr.executemany(sql, params)
crsr.commit()

print(crsr.execute("SELECT * FROM INFO").fetchall())
# [('cus1', 3), ('cus2', 4), ('cus3', 0)]