As you may know, Qt has a set of Database-aware widgets. The goal of these widgets is easing the creation of applications that are database-bound. So, you create your data structures on whatever DB you want to use, drop some gadgetry on a form using Designer, and off you go.
There is a nice tutorial by Boudewijn Rempt about using these from python.
I have been dabbling in this area, trying to write a certain little app. And yes, it does work nicely. But, looking at the API... well, it felt so C++.
While that is not a bad thing in itself, I prefer python, so I decided to figure out how hard it would be to implement something similar to a QSQLForm using PyQt.
The answer is... not very hard. And the result is better in some ways.
Pretty much all I wanted. The design [2] I chose was to create a helper class, called QPSForm, which handles the DB stuff and pushes data into the actual widgets (and pulls from them, too).
So, the way you work with QPSForm is this:
Create the DB:
It makes no sense to start anywhere else, really ;-) Here's the trivial DB I will use for this example.
CREATE TABLE sample (
NAME varchar(60) not null,
BIRTH DATE not null);
INSERT INTO sample VALUES('joe','1/1/1971');
INSERT INTO sample VALUES('jack','1/1/1972');
INSERT INTO sample VALUES('jane','1/2/1971');
The Form:
You create a form in designer, and compile it via pyuic [3] You must make sure all widgets you intend to make data-aware must have reasonable names. You will use them. Also, you must make sure they are reasonable for the data they will get. A QDateTimeEdit for a boolean field is only going to cause trouble.
In this case, I created a simple form with a QLineEdit (called name), a QDateEdit (called birth), and 3 pushbuttons (prev/save/next). It should be obvious which is which in this picture.
The QPSForm:
Create a QPSForm. That will require you to specify database, table, and a primary key.
Connect the widgets to the data:
You link each widget you want to each column in the table you wish.
Connect signals:
QPSForm has some handy signals and slots you can use to move to next/previous record and so on.
Activate the QPSForm:
Set a query, make it display a record.
Here's the code, using a sqlite DB (the only one I tried):
db=sqlite.connect('sample.db')
cu=db.cursor()
cu.execute(sampledb)
#Link the DB's table to the widgetry
psf=QPSForm(db,'sample')
#Associate the widgets with the fields in the DB.
psf.addWidget(w.name,'name')
psf.addWidget(w.birth,'birth')
#Connect a few signals so it browses the records nicely
qt.QObject.connect(w.next,qt.SIGNAL('clicked()'),psf.next)
qt.QObject.connect(w.prev,qt.SIGNAL('clicked()'),psf.prev)
qt.QObject.connect(w.save,qt.SIGNAL('clicked()'),psf.saveRecord)
qt.QObject.connect(psf,qt.PYSIGNAL('atStart'),w.prev.setDisabled)
qt.QObject.connect(psf,qt.PYSIGNAL('atEnd'),w.next.setDisabled)
# Initialize the QPSForm so it has something to show
psf.setQuery()
psf.setRecord(0)
By doing only this (check it, that's about a dozen lines of code), we get a working data-bound form. Of course, since this is easy, it means the machinery behind QPSForm must be more involved.
Well, not really. It does do stuff Python allows because it's so dynamic, but it's not all that tricky.
Keep in mind that if all you want to do is create forms, then this is of no significance to you. There is nothing useful beyond what was shown earlier, (at least not yet). However, you can help me complete this thing.
Here's a full listing of QPSForm (See below for explanations):
########################################################################
#
# Helper functions to get back values from widgets. These get assigned
# to widget.__value and are used by saveRecord
#
########################################################################
def QDateEditToString(self):
return '%04d%02d%02d'%(self.date().year(),self.date().month(),self.date().day())
def QTimeEditToString(self):
return '%02d:%02d:%02d'%(self.time().hour(),self.time().minute(),self.time().second())
def QDateTimeEditToString(self):
dt=self.dateTime()
d=dt.date()
t=dt.time()
return '%04d%02d%02d %02d:%02d:%02d'%(d.year(),d.month(),d.day(),t.hour(),t.minute(),t.second())
def TextToString(self):
return str(self.text())
class QPSForm(qt.QObject):
def __init__(self,database,table):
qt.QObject.__init__(self)
self.db=database
self.table=table
self.widgets={}
self.result=None
self.index=0
self.primKey=None
def addWidget(self,widget,field):
"""Associates a certain widget with a certain field in the table"""
self.widgets[widget.name()]=widget
widget.__field=field
def setQuery(self,query=None,primKey='rowid'):
"""setQuery takes a "WHERE" as argument. For example, if you want
the record with ID 1, all you have to send is 'ID=1'.
primKey should contain a primary key, it is used to figure out
how to do updates and deletes, so please be careful ;-)
"""
self.primKey=primKey
fields=[self.widgets[x].__field for x in self.widgets]
if query:
cmd='SELECT %s,%s FROM %s WHERE %s;'%(primKey,','.join(fields),self.table,query)
else:
cmd='SELECT %s,%s FROM %s;'%(primKey,','.join(fields),self.table)
print cmd
self.cu=self.db.cursor()
self.cu.execute(cmd)
self.result=self.cu.fetchall()
self.index=0
def setRecord(self,index):
"""Makes the form display the nth record in the result set"""
#Sanity checks
if not self.result:
return
if 0>index: #too low
return
elif index>=len(self.result): #too high
return
if 0==index: #at start
self.emit (qt.PYSIGNAL('atStart'),(True,))
else:
self.emit (qt.PYSIGNAL('atStart'),(False,))
if index+1==len(self.result):
self.emit (qt.PYSIGNAL('atEnd'),(True,))
else:
self.emit (qt.PYSIGNAL('atEnd'),(False,))
self.index=index
res=self.result[index]
print "new index ",index
print res
if res:
for widget in self.widgets:
self.key=res[self.primKey]
self.setValue(self.widgets[widget],res[self.widgets[widget].__field])
else:
#FIXME think about it
pass
def next(self):
self.setRecord (self.index+1)
def prev(self):
self.setRecord (self.index-1)
def setValue(self,widget,value):
"""The tricky piece. We examine the widget, the value, and try to fit them"""
# These widgets only take strings and all have
# a setText method, so there's no point in finesse
if (isinstance (widget,qt.QLabel) or
isinstance (widget,qt.QLineEdit) or
isinstance (widget,qt.QTextEdit) or
isinstance (widget,qt.QTextBrowser)):
widget.setText(str(value))
widget.__value=TextToString
elif (isinstance (widget,qt.QCheckBox) or
isinstance (widget,qt.QRadioButton)):
widget.__value=widget.isChecked
#Hopefully, we are trying to put an integer in it
if isinstance (value,int) or isinstance (value,long) or isinstance (value,float):
widget.setChecked(value)
else:
#Maybe it can be coerced
try:
widget.setChecked(int(value))
except ValueError:
#Maybe it can't
#FIXME Do something
pass
elif isinstance (widget,qt.QDateEdit):
if isinstance (value,mdt.DateTimeType):
widget.setDate(qt.QDate(value.year,value.month,value.day))
widget.__value=QDateEditToString
else:
#FIXME do something
pass
elif isinstance (widget,qt.QTimeEdit):
if isinstance (value,mdt.DateTimeType):
widget.setTime(qt.QTime(value.year,value.month,value.day))
widget.__value=QTimeEditToString
else:
#FIXME do something
pass
elif isinstance (widget,qt.QDateTimeEdit):
if isinstance (value,mdt.DateTimeType):
widget.setDateTime(QDateTime(qt.QDate(value.year,value.month,value.day),
qt.QTime(value.hour,value.minute,value.second)))
widget.__value=QDateTimeEditToString
else:
#FIXME do something
pass
def saveRecord(self):
data=[]
for wname in self.widgets:
data.append(self.widgets[wname].__field+"='%s'"%self.widgets[wname].__value(self.widgets[wname]))
v=','.join(data)
cmd='UPDATE %s SET %s WHERE %s=%s'%(self.table,v,self.primKey,self.key)
print cmd
self.cu.execute(cmd)
# Update the result set's current result from the DB
fields=[self.widgets[x].__field for x in self.widgets]
cmd="SELECT %s,%s FROM %s WHERE %s='%s';"%(self.primKey,','.join(fields),self.table,self.primKey,self.key)
print cmd
self.cu.execute(cmd)
self.result[self.index]=self.cu.fetchone()
print self.result[self.index]
self.db.commit()
And here is what every piece does.
QPSForm.addWidget:
This function makes the QPSForm remember each widget you add by name. It also makes each widget remember what column it's associated with.
QPSForm.setQuery:
Used to choose what set of records this form will display, and/or what primary key will allow us to uniquely identify each record.
QPSForm.setRecord:
Makes sanity check on the index of the record you want to display, emits some navigation signals, chooses the record to be displayed, then calls QPSForm.setValue on each widget/value pair.
QPSForm.setValue:
The first tricky one: it tries to coerce the data into a form the widget can handle. Here Python's duck typing makes things much simpler (look at the QLabel/QLineEdit/etc. branch and try to convert it to C++)
Then, it stores in widget.__value a function that can return the value as a string. If we just converted a mx.DateTimeType into a QDate to put it into a QDateEdit, then use QDateEditToString. These helper functions are simple, and were previously defined.
This way, later, when we want to create a SQL statement, we can just use widget.__value and know we get the right kind of thing.
This function needs lots of extra work. For example, what happens if you try to store a float into a QDial. Or an int into a QSlider? All those cases should be added. Each one is simple, but there are many!, Also, I haven't figured out how to handle errors yet.
QPSForm.saveRecord:
Using the widget.__value function we chose in setValue, we assemble an UPDATE SQL statement, and execute it. We also update the results, to keep it consistent.
And that's that. Only setValue and saveRecord are in any way difficult. Adding deleteRecord and insertRecord functions should be trivial, too.
How about a completely GUI-driven, simple database? The user creates the tables using a graphical schema editor, then uses Designer to lay out the widgetry, using column names as widget names, and off it goes, you auto-compile it using pyuic, load it as a module, display it, use it.
He wants to use a QLineEdit instead of a QDateEdit? No problem, replace, rename, and recompile the form.
I am not really interested on developing such a thing, but it would be a nice hobbyst challenge.
Here's the code for the whole example app.
Please let me know what you think :-)
. _duck typing: http://en.wikipedia.org/wiki/Duck_typing
|
But I am willing to listen for ideas |
|
Now someone is going to post a comment saying this is the leather-craftsman design pattern, or whatever. Dudes, I have no idea what you are talking about. I am an amateur. |
|
If/when pyuic becomes a python module, this is going to get better. |