title: Python Tkinter Save Listbox Item Ordering to Sqlite Database
body: <p>I have a drag-and-drop listbox (<a href="https://www.safaribooksonline.com/library/view/python-cookbook-2nd/0596007973/ch11s05.html" rel="nofollow noreferrer">found here</a>) that lets the user drag/move items up or down to re-order them.</p>
  
  <p>I have a <code>db.sqlite</code> file that looks like this initially:</p>
  
  <pre><code>&gt;&gt;&gt; make_df()
      ordering          fruit
  0          0         apples
  1          1        oranges
  2          2    blueberries
  3          3     watermelon
  4          4     cantaloupe
  5          5          pears
  6          6    pomegranate
  7          7    raspberries
  8          8   blackberries
  9          9  boysenberries
  10        10     nectarines
  </code></pre>
  
  <p>What I am trying to do is reorder items around in the listbox and have that order saved to the database so when I re-launch the program, the items are in the same order as the last time I used it.</p>
  
  <p>The problem:
  Every time I relaunch the program, the items from the listbox are always in the same order. However, when I reorder the items, then run <code>make_df</code> which looks into the database to see the ordering and returns a dataframe, it shows the database during  that session did update.  <strong>It's just not saving it permanently and that's what I can't figure out.</strong></p>
  
  <p><a href="https://i.stack.imgur.com/NNUP4.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NNUP4.gif" alt="Example"></a></p>
  
  <pre><code>import tkinter as tk
  import pandas as pd
  import sqlite3
  root = tk.Tk()
  
  def make_sqlite_db_for_stackoverflow():
    connection = sqlite3.connect('db.sqlite')
    db_file_name = 'db.sqlite'
    df = pd.DataFrame(
      [(0, 'apples'), (1, 'oranges'), (2, 'blueberries'), (3, 'watermelon'),
       (4, 'cantaloupe'), (5, 'pears'), (6, 'pomegranate'), (7, 'raspberries'),
       (8, 'blackberries'), (9, 'boysenberries'), (10, 'nectarines')],
      columns=['ordering', 'fruit']
      )
    df.to_sql('columns', connection, index=False, if_exists='replace')
    connection.close()
  
  make_sqlite_db_for_stackoverflow()
  
  def make_df():
    print(pd.DataFrame(sqlite3.connect('db.sqlite').cursor().execute(
      'SELECT * FROM columns ORDER BY "ordering";').fetchall(), columns=['ordering', 'fruit']))
  
  class Drag_and_Drop_Listbox(tk.Listbox):
    """ A tk listbox with drag'n'drop reordering of entries. """
    def __init__(self, master, **kw):
      kw['selectmode'] = tk.EXTENDED
      tk.Listbox.__init__(self, master, kw)
      self.bind('&lt;Button-1&gt;', self.setCurrent)
      self.bind('&lt;B1-Motion&gt;', self.shiftSelection)
      self.curIndex = None
    def setCurrent(self, event):
      self.curIndex = self.nearest(event.y)
    def shiftSelection(self, event):
      i = self.nearest(event.y)
      if i &lt; self.curIndex:
        x = self.get(i)
        self.delete(i)
        self.insert(i+1, x)
        self.curIndex = i
      elif i &gt; self.curIndex:
        x = self.get(i)
        self.delete(i)
        self.insert(i-1, x)
        self.curIndex = i
  
  def update_ordering(*args):
    connect = sqlite3.connect('db.sqlite')
    cursor = connect.cursor()
    field_ordering = [(order,fruit) for order,fruit in enumerate(ddlistbox.get(0, 'end'))]
    print(field_ordering)
    for field in field_ordering:
      cursor.execute("UPDATE columns SET 'ordering'="+str(field[0])+" WHERE fruit='"+field[1]+"';")
    connect.commit()
    connect.close()
    print(ddlistbox.curselection())
  
  scrollbar = tk.Scrollbar(root, orient="vertical")
  ddlistbox = Drag_and_Drop_Listbox(root, yscrollcommand=scrollbar.set)
  scrollbar.grid(row=0, column=1, sticky='ns')
  scrollbar.config(command=ddlistbox.yview)
  
  def get_sqlite_report_fields():
    conn = sqlite3.connect('db.sqlite')
    conn.row_factory = lambda cursor, row: row[0]
    cursor = conn.cursor()
    fetch = cursor.execute("SELECT fruit FROM columns ORDER BY 'ordering';").fetchall()
    conn.close()
    return fetch
  
  for fruit in get_sqlite_report_fields():
    ddlistbox.insert(0, fruit)
  ddlistbox.config(width=30)
  ddlistbox.bind('&lt;Double-Button-1&gt;' , func=update_ordering)
  ddlistbox.grid(row=0, column=0)
  
  button = tk.Button(root, text='Check', command=update_ordering)
  button.grid(row=1, column=0)
  
  root.mainloop()
  </code></pre>