Can't edit or delete rows from SQL database with Python 3.11 and pypyodbc

Question

I am using python 3.11, tkinter, and Pypyodbc 1.3.6 to interact with sql. I can enter new rows >and read existing rows, but I can't delete or edit rows within the database. I don't believe it >is pypyodbc because I was able to delete rows in another project with the same Python code. >Now, everything I try gives me an error related to the variable I'm using to set the where >clause in the sql statement.

After trying to directly edit a selected table row, with no luck, I decided to delete the >existing row and enter the whole row again with a different quantity value. This worked on my >first script. So, I used this code to pull data from the correct table:

    conn = pypyodbc.connect(connection_string)
    cursor = conn.cursor()
    cursor.execute('''select boxID, boxLength, boxWidth, boxHeight, boxQuantity, boxRow 
    from boxInventory where boxQuantity > 0 order by boxLength, boxWidth, boxHeight''')
    boxSelectionRow = cursor.fetchall()

I then run a where loop to find the smallest set of dimensions which meet the requirements. At >the end of the loop, I put: boxSelectionRow.pop(0) Finally, I run the block of code to delete the selected row in the table:

   
    boxRow = boxSelectionRow[0][5]
    conn = pypyodbc.connect(connection_string)
    cursor = conn.cursor()
    cursor.execute('''delete from boxinventory where boxSelectionRow = ?''', boxRow)
    cursor.commit 

In the first script I did this, it worked. That was a simple call from one database, interpret >the values, and place in a second database, with no direct input from user. This time, I am >using tKinter as a GUI. I can add and read rows, but I can't edit or delete. I keep running >into: TypeError: Params must be in a list, tuple, or row. Is this an issue between tKinter and >pypyodbc?

Answer 1

this should work :

boxRow = boxSelectionRow[0][5]
conn = pypyodbc.connect(connection_string)
cursor = conn.cursor()
cursor.execute('''delete from boxinventory where boxRow = ?''', [boxRow])  # Note the square brackets around boxRow
cursor.commit()