Skip to main content

Ralsina.Me — Roberto Alsina's website

Data-aware widgets in PyQt

In­­tro­­duc­­tion

As you may know, Qt has a set of Database-aware wid­gets. The goal of these wid­gets is eas­ing the cre­ation of ap­pli­ca­tions that are database-bound. So, you cre­ate your da­ta struc­tures on what­ev­er DB you want to use, drop some gad­getry on a form us­ing De­sign­er, and off you go.

There is a nice tu­to­ri­al by Boudewi­jn Rempt about us­ing these from python.

I have been dab­bling in this area, try­ing to write a cer­tain lit­tle ap­p. And yes, it does work nice­ly. But, look­ing at the API... well, it felt so C++.

While that is not a bad thing in it­self, I pre­fer python, so I de­cid­ed to fig­ure out how hard it would be to im­ple­ment some­thing sim­i­lar to a QSQL­Form us­ing PyQt.

The an­swer is... not very hard. And the re­sult is bet­ter in some ways.

What I wan­t­ed

  • To have a way to make a set of wid­gets dis­play dif­fer­ent fields from a record.
  • To make it easy to browse a set of records that were the re­sult of a query.
  • To be able to up­date a record in the DB.
  • To make is as sim­ple as pos­si­ble
  • To al­low us­ing De­sign­er to cre­ate the forms
  • To make it at least some­what wid­get-in­de­pen­den­t. If I ed­it the form and re­place a wid­get with some­thing else, it should still have a chance of work­ing (now that was what C++ won't give you [1])

What I did­n't want

  • Sub­class­ing com­mon wid­gets to cre­ate the data-aware ver­sion­s.
  • To write a lot

What I end­ed get­t­ing

Pret­ty much all I want­ed. The de­sign [2] I chose was to cre­ate a helper class, called QPS­For­m, which han­dles the DB stuff and push­es da­ta in­to the ac­tu­al wid­gets (and pulls from them, too).

So, the way you work with QPS­Form is this:

Cre­ate the DB:

It makes no sense to start any­where else, re­al­ly ;-) Here's the triv­ial DB I will use for this ex­am­ple.

CRE­ATE TABLE sam­ple (
NAME ­var­char(60) not nul­l,
BIRTH ­DATE not nul­l);
IN­SERT IN­TO sam­ple ­VAL­UES('joe','1/1/1971');
IN­SERT IN­TO sam­ple ­VAL­UES('­jack­','1/1/1972');
IN­SERT IN­TO sam­ple ­VAL­UES('­jane','1/2/1971');

The For­m:

You cre­ate a form in de­sign­er, and com­pile it via pyuic [3] You must make sure all wid­gets you in­tend to make data-aware must have rea­son­able names. You will use them. Al­so, you must make sure they are rea­son­able for the da­ta they will get. A QDate­TimeEd­it for a bool­ean field is on­ly go­ing to cause trou­ble.

In this case, I cre­at­ed a sim­ple form with a QLi­neEd­it (called name), a QDa­teEd­it (called birth), and 3 push­but­tons (pre­v/save/nex­t). It should be ob­vi­ous which is which in this pic­ture.

/static/pydb1.png

The QPS­For­m:

Cre­ate a QPS­For­m. That will re­quire you to spec­i­fy database, table, and a pri­ma­ry key.

Con­nect the wid­gets to the data:

You link each wid­get you want to each col­umn in the ta­ble you wish.

Con­nect sig­nal­s:

QPS­Form has some handy sig­nals and slots you can use to move to nex­t/pre­vi­ous record and so on.

Ac­ti­vate the QPS­For­m:

Set a query, make it dis­play a record.

Here's the code, us­ing a sqlite DB (the on­ly one I tried):

db=sqlite.­con­nec­t('sam­ple.d­b')
cu=d­b.cur­sor()
cu.ex­e­cute(sam­pled­b)

#Link the D­B's table ­to the wid­getry

ps­f=QPS­For­m(d­b,'sam­ple')

#As­so­ci­ate the wid­get­s with­ the ­field­s in the D­B.
ps­f.ad­dWid­get(w.­name,'­name')
ps­f.ad­dWid­get(w.birth,'birth')

#Con­nec­t a few sig­nal­s ­so it brows­es the record­s nice­ly

qt.QOb­jec­t.­con­nec­t(w.nex­t,qt.SIG­NAL('clicked()'),ps­f.nex­t)
qt.QOb­jec­t.­con­nec­t(w.pre­v,qt.SIG­NAL('clicked()'),ps­f.pre­v)
qt.QOb­jec­t.­con­nec­t(w.save,qt.SIG­NAL('clicked()'),ps­f.saveRe­cord)
    
qt.QOb­jec­t.­con­nec­t(ps­f,qt.PYSIG­NAL('at­Start'),w.pre­v.set­Dis­abled)
qt.QOb­jec­t.­con­nec­t(ps­f,qt.PYSIG­NAL('a­tEnd'),w.nex­t.set­Dis­abled)

# Ini­tial­ize the QPS­For­m ­so it has ­some­thing ­to show

ps­f.set­Query()
ps­f.se­tRecord(0)

By do­ing on­ly this (check it, that's about a dozen lines of code), we get a work­ing data-bound for­m. Of course, since this is easy, it means the ma­chin­ery be­hind QPS­Form must be more in­volved.

Well, not re­al­ly. It does do stuff Python al­lows be­cause it's so dy­nam­ic, but it's not all that trick­y.

The way it works

Keep in mind that if all you want to do is cre­ate form­s, then this is of no sig­nif­i­cance to you. There is noth­ing use­ful be­yond what was shown ear­lier, (at least not yet). How­ev­er, you can help me com­plete this thing.

Here's a full list­ing of QPS­Form (See be­low for ex­pla­na­tion­s):

########################################################################
#
# Helper ­func­tion­s ­to get back­ ­val­ues from wid­get­s. The­se get as­signed
# ­to wid­get.__­val­ue and are used by saveRe­cord
#
########################################################################

de­f Q­Da­teEdit­ToString(­self):
    re­turn '%04d%02d%02d'%(­self.­date().year(),­self.­date().­mon­th(),­self.­date().­day())

de­f Q­TimeEdit­ToString(­self):
    re­turn '%02d:%02d:%02d'%(­self.­time().hour(),­self.­time().min­ute(),­self.­time().sec­ond())

de­f Q­Date­TimeEdit­ToString(­self):
    dt=­self.­date­Time()
    d=dt.­date()
    t=dt.­time()
    re­turn '%04d%02d%02d %02d:%02d:%02d'%(d.year(),d.­mon­th(),d.­day(),t.hour(),t.min­ute(),t.sec­ond())

de­f ­Text­ToString(­self):
    re­turn str(­self.­tex­t())

class QPS­For­m(qt.QOb­jec­t):
    de­f __init__(­self,­database,table):
        qt.QOb­jec­t.__init__(­self)
        ­self.d­b=­database
        ­self.table=table
        ­self.wid­get­s={}
        ­self.re­sult=None
        ­self.in­dex=0
        ­self.primKey=None
        
    de­f ad­dWid­get(­self,wid­get,­field­):
        """As­so­ci­ates a cer­tain wid­get with­ a cer­tain ­field­ in the table"""
        ­self.wid­get­s[wid­get.­name()]=wid­get
        wid­get.__­field­=­field
        
    de­f set­Query(­self,­query=None,primKey='rowid'):
        """set­Query ­takes a "WHERE" as ar­gu­men­t. ­For ex­am­ple, if y­ou wan­t 
        the record with­ ID 1, al­l y­ou have ­to send is 'ID=1'.
        
        primKey should ­con­tain a pri­ma­ry key, it is used ­to ­fig­ure out
        how ­to ­do up­dates and deletes, ­so ­please be ­care­ful ;-)
        """
        
        ­self.primKey=primKey
        
        ­field­s=[­self.wid­get­s[x].__­field­ ­for x in ­self.wid­get­s]
        if ­query:
            cmd='S­E­LEC­T %s,%s FROM %s WHERE %s;'%(primKey,','.join(­field­s),­self.table,­query)
        else:
            cmd='S­E­LEC­T %s,%s FROM %s;'%(primKey,','.join(­field­s),­self.table)
            
        print­ cmd
        
        ­self.cu=­self.d­b.cur­sor()
        ­self.cu.ex­e­cute(cmd)
        ­self.re­sult=­self.cu.fetchal­l()
        ­self.in­dex=0
        
    de­f se­tRecord(­self,in­dex):
        """­Makes the ­for­m dis­play the n­th record in the re­sult set"""
        #San­i­ty checks
        
        if not ­self.re­sult:
            re­turn
        
        if 0>in­dex: #­too low
            re­turn
        
        elif in­dex>=len(­self.re­sult): #­too high
            re­turn
        
        if 0==in­dex: #at s­tart
            ­self.emit (qt.PYSIG­NAL('at­Start'),(True,))
        else:
            ­self.emit (qt.PYSIG­NAL('at­Start'),(­False,))
        
        if in­dex+1==len(­self.re­sult):
            ­self.emit (qt.PYSIG­NAL('a­tEnd'),(True,))
        else:
            ­self.emit (qt.PYSIG­NAL('a­tEnd'),(­False,))
        
        ­self.in­dex=in­dex
        res=­self.re­sult[in­dex]
        print­ "new in­dex ",in­dex
        
        print­ res
        
        if res:
            ­for wid­get in ­self.wid­get­s:
                ­self.key=res[­self.primKey]
                ­self.set­Val­ue(­self.wid­get­s[wid­get],res[­self.wid­get­s[wid­get].__­field­])
        else:
            #­FIXME ­think about it
            ­pass

    de­f nex­t(­self):
        ­self.se­tRecord (­self.in­dex+1)
    
    de­f pre­v(­self):
        ­self.se­tRecord (­self.in­dex-1)
        
    de­f set­Val­ue(­self,wid­get,­val­ue):
        """The trick­y ­piece. We ex­am­ine the wid­get, the ­val­ue, and try ­to ­fit them"""
        
        # The­se wid­get­s on­ly ­take strings and al­l have 
        # a set­Tex­t method­, ­so there's no ­point in fi­nesse
        if (isin­stance (wid­get,qt.QLa­bel) or
            isin­stance (wid­get,qt.Q­Li­neEd­it) or
            isin­stance (wid­get,qt.Q­TextE­d­it) or
            isin­stance (wid­get,qt.Q­TextBrowser)):
            
            wid­get.set­Tex­t(str(­val­ue))
            wid­get.__­val­ue=­Text­ToString
            
        elif (isin­stance (wid­get,qt.QCheck­Box) or
            isin­stance (wid­get,qt.QRa­dioBut­ton)):
            wid­get.__­val­ue=wid­get.is­Checked
            #Hope­ful­ly, we are try­ing ­to put an in­te­ger in it
            if isin­stance (­val­ue,in­t) or isin­stance (­val­ue,­long) or isin­stance (­val­ue,float):
                wid­get.setChecked(­val­ue)
            else:
                #­May­be it ­can be ­co­erced
                try:
                    wid­get.setChecked(in­t(­val­ue))
                ex­cep­t ­Val­ueEr­ror:
                    #­May­be it ­can't
                    #­FIXME ­Do ­some­thing
                    ­pass

        elif isin­stance (wid­get,qt.Q­Da­teEd­it):
            if isin­stance (­val­ue,mdt.­Date­Time­Type­):
                wid­get.set­Date(qt.Q­Date(­val­ue.year,­val­ue.­mon­th,­val­ue.­day))
                wid­get.__­val­ue=Q­Da­teEdit­ToString
            else:
                #­FIXME ­do ­some­thing
                ­pass
        
        elif isin­stance (wid­get,qt.Q­TimeEd­it):
            if isin­stance (­val­ue,mdt.­Date­Time­Type­):
                wid­get.set­Time(qt.Q­Time(­val­ue.year,­val­ue.­mon­th,­val­ue.­day))
                wid­get.__­val­ue=Q­TimeEdit­ToString
            else:
                #­FIXME ­do ­some­thing
                ­pass
        
        elif isin­stance (wid­get,qt.Q­Date­TimeEd­it):
            if isin­stance (­val­ue,mdt.­Date­Time­Type­):
                wid­get.set­Date­Time(Q­Date­Time(qt.Q­Date(­val­ue.year,­val­ue.­mon­th,­val­ue.­day),
                                                qt.Q­Time(­val­ue.hour,­val­ue.min­ute,­val­ue.sec­ond)))
                wid­get.__­val­ue=Q­Date­TimeEdit­ToString
            else:
                #­FIXME ­do ­some­thing
                ­pass

            
    de­f saveRe­cord(­self):
        ­data=[]
        ­for w­name in ­self.wid­get­s:
            ­da­ta.ap­pend(­self.wid­get­s[w­name].__­field­+"='%s'"%­self.wid­get­s[w­name].__­val­ue(­self.wid­get­s[w­name]))
        
        v=','.join(­data)

        cmd='UP­DATE %s SET %s WHERE %s=%s'%(­self.table,v,­self.primKey,­self.key)
        print­ cmd
        ­self.cu.ex­e­cute(cmd)
        
        # Up­date the re­sult set's cur­ren­t re­sult from the DB
        
        ­field­s=[­self.wid­get­s[x].__­field­ ­for x in ­self.wid­get­s]
        cmd="S­E­LEC­T %s,%s FROM %s WHERE %s='%s';"%(­self.primKey,','.join(­field­s),­self.table,­self.primKey,­self.key)
        print­ cmd
        ­self.cu.ex­e­cute(cmd)
        ­self.re­sult[­self.in­dex]=­self.cu.fetchone()
        print­ ­self.re­sult[­self.in­dex]
        ­self.d­b.­com­mit()

And here is what ev­ery piece does.

QPS­For­m.ad­dWid­get:

This func­tion makes the QPS­Form re­mem­ber each wid­get you add by name. It al­so makes each wid­get re­mem­ber what col­umn it's as­so­ci­at­ed with.

QPS­For­m.set­Query:

Used to choose what set of records this form will dis­play, and/or what pri­ma­ry key will al­low us to unique­ly iden­ti­fy each record.

QPS­For­m.se­tRecord:

Makes san­i­ty check on the in­dex of the record you want to dis­play, emits some nav­i­ga­tion sig­nal­s, choos­es the record to be dis­played, then calls QPS­For­m.set­Val­ue on each wid­get/­val­ue pair.

QPS­For­m.set­Val­ue:

The first tricky one: it tries to co­erce the da­ta in­to a form the wid­get can han­dle. Here Python's duck typ­ing makes things much sim­pler (look at the QLa­bel/Q­Li­neEd­it/etc. branch and try to con­vert it to C++)

Then, it stores in wid­get.__­val­ue a func­tion that can re­turn the val­ue as a string. If we just con­vert­ed a mx.­Date­Time­Type in­to a QDate to put it in­to a QDa­teEd­it, then use QDa­teEdit­ToString. These helper func­tions are sim­ple, and were pre­vi­ous­ly de­fined.

This way, lat­er, when we want to cre­ate a SQL state­men­t, we can just use wid­get.__­val­ue and know we get the right kind of thing.

This func­tion needs lots of ex­tra work. For ex­am­ple, what hap­pens if you try to store a float in­to a QDi­al. Or an int in­to a QS­lid­er? All those cas­es should be added. Each one is sim­ple, but there are many!, Al­so, I haven't fig­ured out how to han­dle er­rors yet.

QPS­For­m.saveRe­cord:

Us­ing the wid­get.__­val­ue func­tion we chose in set­Val­ue, we as­sem­ble an UP­DATE SQL state­men­t, and ex­e­cute it. We al­so up­date the re­sult­s, to keep it con­sis­ten­t.

And that's that. On­ly set­Val­ue and saveRe­cord are in any way dif­fi­cult. Adding deleteRe­cord and in­sertRecord func­tions should be triv­ial, too.

What's the po­ten­­tial

How about a com­plete­ly GUI-­driven, sim­ple database? The us­er cre­ates the ta­bles us­ing a graph­i­cal schema ed­i­tor, then us­es De­sign­er to lay out the wid­getry, us­ing col­umn names as wid­get names, and off it goes, you au­to-­com­pile it us­ing pyuic, load it as a mod­ule, dis­play it, use it.

He wants to use a QLi­neEd­it in­stead of a QDa­teEd­it? No prob­lem, re­place, re­name, and re­com­pile the for­m.

I am not re­al­ly in­ter­est­ed on de­vel­op­ing such a thing, but it would be a nice hob­byst chal­lenge.

Here's the code for the whole ex­am­ple app.

Please let me know what you think :-)

. _duck typ­ing: http://en.wikipedi­a.org/wik­i/­Duck­_­typ­ing


[1]

But I am will­ing to lis­ten for ideas

[2]

Now some­one is go­ing to post a com­ment say­ing this is the leather-crafts­man de­sign pat­tern, or what­ev­er. Dudes, I have no idea what you are talk­ing about. I am an am­a­­teur.

[3]

If/when pyuic be­comes a python mod­ule, this is go­ing to get bet­ter.

Roberto Alsina / 2006-04-04 16:35:

Comments for this story are here:

http://www.haloscan.com/com...


Contents © 2000-2023 Roberto Alsina