Search code examples
pythonsqlfirebird

Update database based on user input

I'm trying to make a database management program in Python and Tkinter that relies on user input for updating it. The script goes like this:

  1. User searches for records.
  2. Selects an entry in treeview widget.
  3. Selection populates all of the Entry fields.
  4. User changes one or more values in them.
  5. Program updates the database with the new values

My problem is this: How do I compare the old value (that came from selecting in threeview) and the new value (that user changed)? I have an idea for a loop that 'scans' the new and old values and based on that executes UPDATE query on the changed column, but I can't get the value that is in the Entry widget.

Solution

  • Your widget should do it, not the database.

    What would be triggering the database writing anyway? would it be some "save" button in the window? Or should it be immediately done after the node of the tree was renamed (probably, user stroke ENTER key after keying in new name, but different tree wdgets can have different user interactions methods).

    So, the latter option would be like this:

    When user starts renaming the node, when it enters the tree node editing mode, you tree widget notifies you about it, and then you read the value (before user changed it) and save it into variable. Then you wait for the user to stop editing the node.

    When the tree widget informs you that user completed the editing, you check what kind of completion it was. If it was CANCEL-like completion, then you do nothing.

    If it was OK-like completion, then read the now changed name of the node to another variable.

    You compare the two variables and if the values are different, you command database to UPDATE the row corresponding to the edited node and then to COMMIT the transaciton.

    As @MehdiKhlifi said earlier, you would have to have ID column in your table (read about SQL sequence, in Firebird/Interbase this was created as generator before SQL standardized it, it is the same thing). You would have to somehow store those IDs into the tree nodes (read your widget documentation how to do it), so you would know which table row corresponds to just edited node.

    Alternatively, you may consider the whole window as data frame, not one node.

    Then you would have to make two functions:

    • reading the tree from some buffer (array, hashmap/dictionary, object or something)
    • creating new empty buffer and writing the tree to it

    When form is opened you create one buffer and read it from the database, then toy read the tree from the buffer.

    When user preses SAVE button, you write the tree into the new buffer, then you compare those two buffers, and then for every changed item you do the SQL update like described above, then you do the SQL commit for all he updated rows.

    Notice that usually user can do more than renaming specific nodes: often user can add new tree nodes, delete nodes, or move nodes to different branch (prune-and-paste).