wxPython and SQLAlchemy: Loading Random SQLite Databases for Viewing

I was recently contacted about whether or not there were any wxPython applications out here that could present a SQLite database. As I understood it, they wanted to be able to introspect the database and view the tables, probably using the wx.grid.Grid widget. I find the Grid widget to be very powerful and also rather difficult to use. So I spent some time writing a super simple application that uses the ObjectListView widget instead.

Getting Started

First off, we need a database to test with. So I ended up coding up a simple database creation script with SQLAlchemy that populates a couple tables with a couple rows. Here’s the script I’m using:

from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, String

engine = create_engine('sqlite:///example.db', echo=True)
Base = declarative_base()

########################################################################
class Book(Base):
    """"""
    __tablename__ = "books"
    
    id = Column(Integer, primary_key = True)
    title = Column(String)
    author = Column(String)
        
    #----------------------------------------------------------------------
    def __init__(self, title, author):
        """Constructor"""
        self.title = title
        self.author = author
        

########################################################################
class Character(Base):
    """"""
    __tablename__ = "characters"
    
    id = Column(Integer, primary_key = True)
    first_name = Column(String)
    last_name = Column(String)
    book_id = Column(ForeignKey("books.id"))
    book = relationship("Book", backref=backref("characters", order_by=id))

    #----------------------------------------------------------------------
    def __init__(self, first_name, last_name):
        """Constructor"""
        self.first_name = first_name
        self.last_name = last_name
        
        
    #----------------------------------------------------------------------
    @property
    def fullname(self):
        """"""
        return "%s %s" % (self.first_name, self.last_name)
    
    #----------------------------------------------------------------------
    def __repr__(self):
        """"""
        return "" % self.fullname
    
Base.metadata.create_all(engine) 
Session = sessionmaker(bind=engine)
session = Session()
print
new_char = Character("Hermione", "Granger")
new_char.book = Book("Harry Potter", "JK Rowling")
session.add(new_char)
new_char = Character("Sherlock", "Holmes")
new_char.book = Book("The Adventure of the Creeping Man", "Arthur Conan Doyle")
session.add(new_char)
session.commit()

I’m going to assume you understand SQLAlchemy well enough to follow this. If not, they have some of the best documentation of any Python project I’ve ever used.

Creating the Viewer

Now we just need to create the database table viewer. This took me a little tinkering, but I eventually figured it out. Please note that it’s basically alpha quality and doesn’t have any error checking in it.

import os
import wx
from ObjectListView import ObjectListView, ColumnDefn
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker, clear_mappers

########################################################################
class GenericDBClass(object):
    """"""
    pass
    
########################################################################
class MainPanel(wx.Panel):
    #----------------------------------------------------------------------
    def __init__(self, parent):
        wx.Panel.__init__(self, parent=parent, id=wx.ID_ANY)
        self.db_data = []
        self.current_directory = os.getcwd()
 
        self.dataOlv = ObjectListView(self, wx.ID_ANY, style=wx.LC_REPORT|wx.SUNKEN_BORDER)
        self.dataOlv.Hide()
         
        # Allow the cell values to be edited when double-clicked
        self.dataOlv.cellEditMode = ObjectListView.CELLEDIT_SINGLECLICK
        
        # load DB
        loadDBBtn = wx.Button(self, label="Load DB")
        loadDBBtn.Bind(wx.EVT_BUTTON, self.loadDatabase)
        self.table_names = []
        self.tableCbo = wx.ComboBox(self, value="", choices=self.table_names)
        self.tableCbo.Bind(wx.EVT_COMBOBOX, self.loadTable)
 
        # Create some sizers
        mainSizer = wx.BoxSizer(wx.VERTICAL)
 
        mainSizer.Add(loadDBBtn, 0, wx.ALL|wx.CENTER, 5)
        mainSizer.Add(self.tableCbo, 0, wx.ALL|wx.CENTER, 5)
        mainSizer.Add(self.dataOlv, 1, wx.ALL|wx.EXPAND, 5)
        
        self.SetSizer(mainSizer)
        
    #----------------------------------------------------------------------
    def loadTable(self, event):
        """"""
        print
        current_table = self.tableCbo.GetValue()
        metadata = MetaData(self.engine)
        table = Table(current_table, metadata, autoload=True, autoload_with=self.engine)
        self.columns = table.columns.keys()
        
        clear_mappers() #http://docs.sqlalchemy.org/en/rel_0_6/orm/mapper_config.html#sqlalchemy.orm.clear_mappers
        mapper(GenericDBClass, table)
        
        Session = sessionmaker(bind=self.engine)
        session = Session()
        self.db_data = session.query(GenericDBClass).all()
        
        self.setData()
        self.dataOlv.Show()
        self.Layout()
        
    #----------------------------------------------------------------------
    def loadDatabase(self, event):
        """"""
        wildcard = "All files (*.*)|*.*"
        dlg = wx.FileDialog(
            self, message="Choose a file",
            defaultDir=self.current_directory,
            defaultFile="",
            wildcard=wildcard,
            style=wx.OPEN | wx.CHANGE_DIR
            )
        if dlg.ShowModal() == wx.ID_OK:
            db_path = dlg.GetPath()
            dlg.Destroy()
        else:
            dlg.Destroy()
            return
                
        self.engine = create_engine('sqlite:///%s' % db_path, echo=True)
                
        self.table_names = self.engine.table_names()
        self.tableCbo.SetItems(self.table_names)
        self.tableCbo.SetValue(self.table_names[0])
        self.loadTable("")
        
    #----------------------------------------------------------------------
    def setData(self, data=None):
        olv_columns = []
        for column in self.columns:
            olv_columns.append(ColumnDefn(column.title(), "left", 120, column.lower()))
        self.dataOlv.SetColumns(olv_columns)
 
        self.dataOlv.SetObjects(self.db_data)
 
########################################################################
class MainFrame(wx.Frame):
    #----------------------------------------------------------------------
    def __init__(self):
        wx.Frame.__init__(self, parent=None, id=wx.ID_ANY,
                          title="Database Viewer", size=(800,600))
        panel = MainPanel(self)
 
########################################################################
class GenApp(wx.App):
 
    #----------------------------------------------------------------------
    def __init__(self, redirect=False, filename=None):
        wx.App.__init__(self, redirect, filename)
 
    #----------------------------------------------------------------------
    def OnInit(self):
        # create frame here
        frame = MainFrame()
        frame.Show()
        return True
 
#----------------------------------------------------------------------
def main():
    """
    Run the demo
    """
    app = GenApp()
    app.MainLoop()
 
if __name__ == "__main__":
    main()

Let’s spend a little time breaking down how this works. You’ll note that after we create out ObjectListView widget, we hide it as we don’t yet know what’s going into it. That won’t happen until the user clicks the Load DB button. In the loadDatabase handler, we pop-up a file dialog to allow the user to select the SQLite database file they want to load. For this test, I would recommend using the database we created earlier. After that’s selected, we create an SQLALchemy engine, pull the table names from it, set the combobox drop-down to the list of tablenames and then load the first table in the list by calling our loadTable method.

In loadTable, we use SQLAlchemy’s handy autoload feature to “reflect” the data from the database into a Table object. We call clear_mappers
because we need to make sure that nothing is currently mapped to our dummy class and then we map our new table to the class. Finally we create a SQLAlchemy session and do a simple SELECT * query to pull all the records from the database and pass them to the ObjectListView widget, which we then show.

Wrapping Up

I tried running this script against Mozilla’s places database, but my little application didn’t like it. Feel free to try to break it with your databases. Currently this is only a proof-of-concept. I had thought of something similar to this myself a year or so ago and I may try to improve this a bit in the future. In the mean time, I wanted to post my first draft and see what kind of feedback I can get. I hope you enjoy it!

Note: Tested on Windows 7, wxPython 2.8.12.1 with Python 2.6.6

Further Reading

• SQLAlchemy, reflection, different backends and table/column case insensitivity

Source Code

• wxDbViewer.zip