wxPython and SqlAlchemy: An Intro to MVC and CRUD

In this article, we will be creating a program to store our personal books or perhaps just the books you’ve read. It will demonstrate how to combine wxPython and SqlAlchemy, a SQL Object Relational Mapper (ORM). This program will also give you an introduction to the model-view-controller (MVC) and “create, read, update and destroy” (CRUD) methodologies. The aim is to show you how to create an application that can do the following:

• Create a database (create)
• Add records to that database (sort of create)
• Display the records (read and view)
• Modify records (update)
• Delete records (destroy)

The order of this tutorial will follow MVC, so we will start with the model. The model is the basis for the GUI (the View) anyway, so it’s a good place to start.

Prerequisites

Before we jump into the model section, you may need to download some extras to follow along. Here’s what you’ll need:

• Python 2.5 or better, but not 3.x
• wxPython >= 2.8.10.1
• SqlAlchemy >= 0.5.0
• ObjectListView

Once you have all those installed, you’re ready to move on to the next section!

The Model

Our model contains our SqlAlchemy database classes and a model for our ObjectListView widget, which we’ll be using to display our data later. The SqlAlchemy classes are using the Declarative system, which combines the table creation and the class creation. It’s a little easier to understand if you just take a look at the code.

# model.py

from sqlalchemy import Table, Column, create_engine
from sqlalchemy import Integer, ForeignKey, String, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relation

engine = create_engine("sqlite:///devdata.db", echo=True)
DeclarativeBase = declarative_base(engine)
metadata = DeclarativeBase.metadata

########################################################################
class OlvBook(object):
    """
    Book model for ObjectListView
    """

    #----------------------------------------------------------------------
    def __init__(self, id, title, author, isbn, publisher, last_name, first_name):
        self.id = id  # unique row id from database
        self.title = title
        self.author = author
        self.isbn = isbn
        self.publisher = publisher
        self.last_name = last_name
        self.first_name = first_name

########################################################################
class Person(DeclarativeBase):
    """"""
    __tablename__ = "people"
    
    id = Column(Integer, primary_key=True)
    first_name = Column("first_name", String(50))
    last_name = Column("last_name", String(50))
        
    #----------------------------------------------------------------------
    def __repr__(self):
        """"""
        return "" % (self.first_name, self.last_name)
    

########################################################################
class Book(DeclarativeBase):
    """"""
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey("people.id"))
    title = Column("title", Unicode)
    isbn = Column("isbn", Unicode)
    publisher = Column("publisher", Unicode)
    person = relation("Person", backref="books", cascade_backrefs=False)
        
metadata.create_all()

We’re not going to spend a lot of time parsing this code as there are several SqlAlchemy tutorials out there and the SqlAlchemy documentation is really good. Instead, we’ll just give a quick overview. The beginning of the code imports all the SqlAlchemy stuff we need. One of the most important bits is where create the engine:

engine = create_engine("sqlite:///devdata.db", echo=True)

This means that we’ll be creating a SQLite database in the same directory as the model is in. You can pass a path in too, like this: create_engine(“sqlite:///c:/path/to/devdata.db”) and you can name the database whatever you want, including the extension. The echo parameter is turned on for debugging purposes. It will output all the SQL that SqlAlchemy issues so we can use it to figure out our queries or issues that may crop up when we create the database.

The first class we encounter is OlvBook, which defines a book class. We’ll be using that later to help us populate the previously mentioned ObjectListView widget that we will use to display our records. The next two classes are called Person and Book, respectively. The tablename is defined by the magic method: __tablename__. Person is a ForeignKey in Book, which we use for our Author. Originally, I thought I could use Person to add multiple authors or other people related to the book, like the artist. Or maybe for the Songwriter for one of my CDs, but I decided to just keep it simple instead. You can enhance this model a little to make it more polymorphic so it can behave in that way though.

Anyway, if you run this script, it will execute the last line: metadata.create_all() This causes the database file to be created with those two tables defined. Now we’re ready to add data to it. But first, we need to take a look at the View!

The View

The View is our wxPython interface that we will use to display the records as well as filter, add, modify and delete them. The code for our GUI is just over 150 lines long. Let’s take a look!

import addModRecord
import commonDlgs
import controller
import wx
from ObjectListView import ObjectListView, ColumnDefn

########################################################################
class BookPanel(wx.Panel):
    """"""

    #----------------------------------------------------------------------
    def __init__(self, parent):
        """Constructor"""
        wx.Panel.__init__(self, parent)
        try:
            self.bookResults = controller.getAllRecords()
        except:
            self.bookResults = []
        
        mainSizer = wx.BoxSizer(wx.VERTICAL)
        searchSizer = wx.BoxSizer(wx.HORIZONTAL)
        btnSizer = wx.BoxSizer(wx.HORIZONTAL)
        font = wx.Font(10, wx.SWISS, wx.NORMAL, wx.BOLD) 
        
        # create the search related widgets
        cat = ["Author", "Title", "ISBN", "Publisher"]
        searchByLbl = wx.StaticText(self, label="Search By:")
        searchByLbl.SetFont(font)
        searchSizer.Add(searchByLbl, 0, wx.ALL, 5)
        
        self.categories = wx.ComboBox(self, value="Author", choices=cat)
        searchSizer.Add(self.categories, 0, wx.ALL, 5)
        
        self.search = wx.SearchCtrl(self, style=wx.TE_PROCESS_ENTER)
        self.search.Bind(wx.EVT_TEXT_ENTER, self.onSearch)
        searchSizer.Add(self.search, 0, wx.ALL, 5)
        
        self.bookResultsOlv = ObjectListView(self, style=wx.LC_REPORT
                                                        |wx.SUNKEN_BORDER)
        self.bookResultsOlv.SetEmptyListMsg("No Records Found")
        self.setBooks()
        
        # create the button row
        addRecordBtn = wx.Button(self, label="Add")
        addRecordBtn.Bind(wx.EVT_BUTTON, self.onAddRecord)
        btnSizer.Add(addRecordBtn, 0, wx.ALL, 5)
        
        editRecordBtn = wx.Button(self, label="Edit")
        editRecordBtn.Bind(wx.EVT_BUTTON, self.onEditRecord)
        btnSizer.Add(editRecordBtn, 0, wx.ALL, 5)
        
        deleteRecordBtn = wx.Button(self, label="Delete")
        deleteRecordBtn.Bind(wx.EVT_BUTTON, self.onDelete)
        btnSizer.Add(deleteRecordBtn, 0, wx.ALL, 5)
        
        showAllBtn = wx.Button(self, label="Show All")
        showAllBtn.Bind(wx.EVT_BUTTON, self.onShowAllRecord)
        btnSizer.Add(showAllBtn, 0, wx.ALL, 5)
        
        mainSizer.Add(searchSizer)
        mainSizer.Add(self.bookResultsOlv, 1, wx.ALL|wx.EXPAND, 5)
        mainSizer.Add(btnSizer, 0, wx.CENTER)
        self.SetSizer(mainSizer)
        
    #----------------------------------------------------------------------
    def onAddRecord(self, event):
        """
        Add a record to the database
        """
        dlg = addModRecord.AddModRecDialog()
        dlg.ShowModal()
        dlg.Destroy()
        self.showAllRecords()
        
    #----------------------------------------------------------------------
    def onEditRecord(self, event):
        """
        Edit a record
        """
        selectedRow = self.bookResultsOlv.GetSelectedObject()
        if selectedRow == None:
            commonDlgs.showMessageDlg("No row selected!", "Error")
            return
        dlg = addModRecord.AddModRecDialog(selectedRow, title="Modify",
                                           addRecord=False)
        dlg.ShowModal()
        dlg.Destroy()
        self.showAllRecords()
        
    #----------------------------------------------------------------------
    def onDelete(self, event):
        """
        Delete a record
        """
        selectedRow = self.bookResultsOlv.GetSelectedObject()
        if selectedRow == None:
            commonDlgs.showMessageDlg("No row selected!", "Error")
            return
        controller.deleteRecord(selectedRow.id)
        self.showAllRecords()
        
    #----------------------------------------------------------------------
    def onSearch(self, event):
        """
        Searches database based on the user's filter choice and keyword
        """
        filterChoice = self.categories.GetValue()
        keyword = self.search.GetValue()
        print "%s %s" % (filterChoice, keyword)
        self.bookResults = controller.searchRecords(filterChoice, keyword)
        self.setBooks()
        
    #----------------------------------------------------------------------
    def onShowAllRecord(self, event):
        """
        Updates the record list to show all of them
        """
        self.showAllRecords()
        
    #----------------------------------------------------------------------
    def setBooks(self):
        self.bookResultsOlv.SetColumns([
            ColumnDefn("Title", "left", 350, "title"),
            ColumnDefn("Author", "left", 150, "author"),
            ColumnDefn("ISBN", "right", 150, "isbn"),
            ColumnDefn("Publisher", "left", 150, "publisher")
        ])
        self.bookResultsOlv.SetObjects(self.bookResults)
        
    #----------------------------------------------------------------------
    def showAllRecords(self):
        """
        Show all records in the object list view control
        """
        self.bookResults = controller.getAllRecords()
        self.setBooks()
        
########################################################################
class BookFrame(wx.Frame):
    """"""

    #----------------------------------------------------------------------
    def __init__(self):
        """Constructor"""
        wx.Frame.__init__(self, None, title="MvP Media Organizer",
                          size=(800, 600))
        panel = BookPanel(self)
        
        self.Show()
        
#----------------------------------------------------------------------
if __name__ == "__main__":
    app = wx.App(False)
    frame = BookFrame()
    app.MainLoop()

You’ll notice that we import a few custom items, like addModRecord, commonDlgs and controller. The addModRecord is a dialog that can be used both to add a record and modify one. The commonDlgs module is used just to simplify creating message dialogs. If we add some new features to this program, we would add other small dialog code in that module. The controller module is where all the SqlAlchemy code is executed. Most programs have some configuration options, so this would be a good place to store that code.

Anyway, let’s go over the highlights of the GUI. The search bar is set to search when the user presses enter. That’s what the style flag wx.TE_PROCESS_ENTER means. That and the ObjectListView’s SetEmptyListMsg method are probably the only really unfamiliar things here. That method just shows a message to the user when there’s no data in the control.

The onAddRecord and onEditRecord both call our addModRecord module to display the Add / Modify dialog. It should look something like this:

The onEditRecord method sets a couple parameters that affect the look of the widget and passes in some information to populate the fields. The onDelete method just takes the selected item and calls the controller to delete it, then it updates the display.

Our onSearch method does what you’d expect. It searches for records in our database based on what filter the user chooses and their keyword. They can choose “Author”, “Title”, “ISBN” or “Publisher” as their filter.

The onShowAllRecord method just shows all the records on the display. A future enhancement would be to limit the number of currently showed records so we don’t cause major slow down of the application. The setBooks method just updates the displayed records.

Now we’ve reached the last piece of our puzzle: the Controller.

The Controller and CRUD

The controller is the glue which holds the model and the view together. At least, that’s what it seems like to me. It uses the model for queries and for adding, updating and deleting records. It also reports the updates to the database to the view (our GUI) so it can update appropriately. Let’s take a look at our controller code:

# controller.py
from model import Book, Person, OlvBook
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

#----------------------------------------------------------------------
def addRecord(data):
    """
    Data should be a tuple of two dictionaries in the following format:
    
    ("author":{"first_name":"John", "last_name":"Doe"},
     "book":{"title":"Some book", "isbn":"1234567890", 
             "publisher":"Packt"}
    )
    """
    book = Book()
    book.title = data["book"]["title"]
    book.isbn = data["book"]["isbn"]
    book.publisher = data["book"]["publisher"]
    author = Person()
    author.first_name = data["author"]["first_name"]
    author.last_name = data["author"]["last_name"]
    book.person = author
    
    # connect to session and commit data to database
    session = connectToDatabase()
    session.add(book)
    session.commit()
    session.close()
    
#----------------------------------------------------------------------
def connectToDatabase():
    """
    Connect to our SQLite database and return a Session object
    """
    engine = create_engine("sqlite:///devdata.db", echo=True)
    Session = sessionmaker(bind=engine)
    session = Session()
    return session

#----------------------------------------------------------------------
def convertResults(results):
    """
    Convert results to OlvBook objects
    """
    print
    books = []
    for record in results:
        author = "%s %s" % (record.person.first_name, 
                            record.person.last_name)
        book = OlvBook(record.id, record.title, author,
                       record.isbn, record.publisher,
                       record.person.last_name,
                       record.person.first_name
                       )    
        books.append(book)
    return books

#----------------------------------------------------------------------
def deleteRecord(idNum):
    """
    Delete a record from the database
    """
    session = connectToDatabase()
    record = session.query(Book).filter_by(id=idNum).one()
    session.delete(record)
    session.commit()
    session.close()
    
#----------------------------------------------------------------------
def editRecord(idNum, row):
    """
    Edit a record
    """
    session = connectToDatabase()
    record = session.query(Book).filter_by(id=idNum).one()
    print
    record.title = row["title"]
    record.person.first_name = row["first_name"]
    record.person.last_name = row["last_name"]
    record.isbn = row["isbn"]
    record.publisher = row["publisher"]
    session.add(record)
    session.commit()
    session.close()

#----------------------------------------------------------------------
def getAllRecords():
    """
    Get all records and return them
    """
    session = connectToDatabase()
    result = session.query(Book).all()
    books = convertResults(result)
    session.close()
    return books

#----------------------------------------------------------------------
def searchRecords(filterChoice, keyword):
    """
    Searches the database based on the filter chosen and the keyword
    given by the user
    """
    session = connectToDatabase()
    if filterChoice == "Author":
        qry = session.query(Person)
        result = qry.filter(Person.first_name.contains('%s' % keyword)).all()
        records = []
        for record in result:
            for book in record.books:
                records.append(book)
        result = records
        print result
    elif filterChoice == "Title":
        qry = session.query(Book)
        result = qry.filter(Book.title.contains('%s' % keyword)).all()
    elif filterChoice == "ISBN":
        qry = session.query(Book)
        result = qry.filter(Book.isbn.contains('%s' % keyword)).all()
    else:
        qry = session.query(Book)
        result = qry.filter(Book.publisher.contains('%s' % keyword)).all()    
    books = convertResults(result)
    print
    return books

Our controller has 7 functions defined:

• addRecord
• connectToDatabase
• convertResults
• deleteRecord
• editRecord
• getAllRecords
• searchRecords

These are pretty self explanatory. However, we’ll spend a little time explaining what convertResults does and how searchRecords works.

The convertResults method is for converting the SqlAlchemy book results that we get from querying the database, into the ObjectListView Book model objects. This is necessary so we can display them when there are updates to the database or when displaying the results of a query. As you can see, we just iterate over the results and massage the data appropriately.

In the searchRecords method, we pass in our filter and keyword and do queries on the database using our session object via a conditional “if” statement. Depending on the type of filter, we query differently. For the most part, the query is the same though, so this could use some refactoring. I’ll leave that for the reader to figure out as an assignment. Once we have the results converted, we then return them to the calling function, which in this case happens to be our GUI’s search event handler.

The CRUD part is covered in the other methods where we create, read, update, and delete records.

Wrapping Up

Now you should know the basics behind creating a wxPython program using the MVC framework. This isn’t a perfect example and I don’t claim that it is, but it should give you an idea of how to start making your own. It also shows how to interact with a SQLite database using SqlAlchemy. I hope you’ve found this helpful and look forward to hearing your comments.

Source Code

• You can check out the code from Bitbucket