I am trying to update SQL table entries (using pypyodbc) with values from a pandas dataframe. Currently, I am creating a numpy array from the pandas dataframe, then iterating on each row of the array, using a unique ItemID to do an UPDATE WHERE ItemID = ? statement.
Is there a way to accomplish the below in a single UPDATE statement instead of looping on each record?
import pypyodbc
import pandas as pd
data = [['tom', 10, 11], ['nick', 12, 13], ['juli', 14, 15]]
df = pd.DataFrame(data)
np_array = df.to_numpy()
query = """
UPDATE table_A
SET Item1 = ?, Item2 = ?
WHERE ItemID = ?
"""
connection = pypyodbc.connect('<connection_string>')
cursor = connection.cursor()
for i in range(0,len(np_array)):
itemID = np_array[i][0]
item1 = np_array[i][1]
item2 = np_array[i][2]
params = (
item1,
item2,
itemID,
)
cursor.execute(query, params)
cursor.close
cursor.commit()
connection.close