What is pysqlgtk

Short answer: a pretty ugly name for a nifty idea. Long answer: a library that integrates the well-known object-relational mapper SQLObject with pygtk. Thus, making database frontends with pygtk becomes fairly easy, provided you are familiar with python and pygtk.

The project is work in progress as is this page, however, things are already quite usable. I expect to have a first alpha release ready by the end of december 2008.

The following screenshot shows an example application that uses pysqlgtk. It is hand coded without using Glade, the code for the customer view is roughly 150 lines, the code for making the widgets data-aware is about a dozen lines:

You create a controller instance
You pass the widgets to the controller and tell it which column it belongs to.
You connect the cursor-changed event of the main list to a controller method.

For a real world app you would also connect some validators, which I haven't done here.

Code example

Let's start with something simple, we will define a table and a little application with which you can navigate the data.

import gtk
from sqlobject import *
from pysqlgtk import SqlListModel, SqlController, get_connstr_from_file

First of all we import the necessary modules, gtk for the user interface, everything from sqlobject for database handling and a couple of things from pysqlgtk to stick it all together.

connection_string = get_connstr_from_file("./default.conf")
connection = connectionForURI(connection_string)
sqlhub.processConnection = connection

get_connstr_from_file reads the connection settings from a configuration file. We pass the filename of the config file as argument, the file has to have a section [dbconn] and within the section it needs to define the following params:

dbms: The scheme, which can be any of the dbms supported by SQLObject (and which needs to have the necessary libs installed for database access).
user: Pretty self-explaining, this is the username.
pw: The password used for database access. This password is stored in the config file as plaintext. However, get_connstr_from_file can take a keyword argument "passw", which can be used instead. In future versions there will probably an option for some type of password encryption. However, keep in mind that storing passwords in config files is always a security risk, whether they are encrypted or not.
safepw: This is used by a compound widget which we will see in another example and tells it whether to safe the given password to the file or not.
host: The host. If the db is on your machine, use localhost. In some special cases this can also be required to be empty, all depends on your database configuration and the used dbms.
port: The port through which you connect.

The following is an example section:

[dbconn]
dbms = postgres
pw = mindyourownbusiness
safepw = True
db = test
host = localhost
user = markus
port = 5432

In this case, get_connstr_from_file would return "postgres://markus:mindyourownbusiness@localhost/test"

Now we have a connection, but we don't have an object yet. So let's define a simple table class.

class Person(SQLObject):
	first_name      = StringCol()
	last_name       = StringCol()
	date_of_birth   = DateCol()

At this point you should have a look at the SQLObject documentation at http://www.sqlobject.org for further documentation. SQLObject is pretty straight forward.

We define a class for our app, usual business. Upon initialization we create a SqlController Instance, which will take care of all the database operations that have to be done upon data change in the widgets.

class DemoApp:
    def __init__(self):
        window        = gtk.Window(gtk.WINDOW_TOPLEVEL)
        window.connect("destroy", gtk.main_quit, "WM destroy")
        
        self.controller = SqlController()
        
        vbox = gtk.VBox()

Now we create the widgets for displaying the data in the table and add them to the controller. The controller instance takes two arguments, The first is the attribute name (Not the column name of the table but the attribute name of the table class we have just define a bit earlier), the second argument is the widget instance.

        entry = gtk.Entry()
        self.controller.add_widget('first_name', entry)
        vbox.pack_start(entry, expand = False)
        
        entry = gtk.Entry()
        self.controller.add_widget('last_name', entry)
        vbox.pack_start(entry, expand = False)
        
        entry = gtk.Entry()
        self.controller.add_widget('date_of_birth',entry)
        vbox.pack_start(entry, expand = False)

We want to be able to navigate through the table, so we define a simple navigation bar.

hbox = gtk.HBox()
         
        button = gtk.Button("<")
        button.connect("clicked", self.prev)
        hbox.pack_start(button, expand=False)

        button = gtk.Button(">")
        button.connect("clicked", self.next)
        hbox.pack_start(button, expand=False)
    
        vbox.pack_start(hbox)
        vbox.show_all()

We will go the easy way, for this reason, we will create a special type of ListModel, if you are familiar with gtk and you have used TreeViews or ComboBoxes, then you will know what I am talking about. The Model is a SqlListModel, you just pass the SQLObject class to the constructor, and voilá, you have a model that can be used by gtk widgets.

        self.model = SqlListModel(Person)
        self.model.refresh()

We call refresh() in order to fill the Model with rows. Refresh takes the same arguments as SQLObject.select(). The next thing we have to do is point the controller to the first row in the SqlListModel.

        self.treeiter = self.model.get_iter_first()
        #set table row to first row in model
        self.controller.set_row(self.model.get_row(self.treeiter))
        
        window.add(vbox)
        window.show_all()

Now we only need to define the methods next and prev to navigate through the list. is_dirty() is True if the values displayed in any of the widgets assigned to the controller doesn't fit the values in the set SQLObject Instance. With commit the values of the widgets are commited to the SQLOBject instance (and thus transparently to the table).

    def next(self, w):
        if self.controller.is_dirty():
            self.controller.commit()
        if self.model.iter_next(self.treeiter):
            self.treeiter = self.model.iter_next(self.treeiter)
        self.controller.set_row(self.model.get_row(self.treeiter))
        
    def prev(self, w):
        if self.controller.is_dirty():
            self.controller.commit()
        self.treeiter = self.model.iter_previous(self.treeiter)    
        self.controller.set_row(self.model.get_row(self.treeiter))

That's it. Have a look at the complete code example in example1.py

There is even a simpler way implement this example, using the

SqlListController

class. Have a look at example2.py