Python Tkinter Save Listbox Item Ordering to Sqlite Database
<p>I have a drag-and-drop listbox (<a href="https://www.safaribooksonline.com/library/view/python-cookbook-2nd/0596007973/ch11s05.html" rel="nofollow">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="http://i.stack.imgur.com/NNUP4.gif" rel="nofollow"><img src="http://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>

<p>Highly doubt anyone will answer my question so I'm posting my solution. It uses a drag-and-drop listbox to allow the user to arrange items in the listbox and saves their item ordering to a database for next time.</p>

<pre><code>import tkinter as tk
import pandas as pd
import sqlite3
root = tk.Tk()

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, activestyle='none')
scrollbar.grid(row=0, column=1, sticky='ns')
scrollbar.config(command=ddlistbox.yview)

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 ASC").fetchall()
for field in fetch:
  ddlistbox.insert(tk.END, field)
ddlistbox.config(width=30)
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>

python tkinter sqlite3 drag-and-drop listbox