0

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?

Bryan Oakley
  • 370,779
  • 53
  • 539
  • 685
Jim Mehus
  • 1
  • 1
  • Typo: `cursor.commit` should be `cursor.commit()`. As the error said, it should be `cursor.execute('''delete ...''', (boxRow,))` instead. – acw1668 Aug 02 '23 at 16:30
  • i would avoid using pop inside a for loop. Use ```Python3 for i in range(len(items)): item = items[i] # do something if ``` – TheCableGUI Aug 02 '23 at 18:43

1 Answers1

0

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()
Smordy
  • 146
  • 7