Developing Cross Platform Application using Qt, PyQt and PySide : Database Support - Part 4 of 5

Successful PyUnit testing of login feature from within PyCharm IDE

Successful PyUnit testing of login feature from within PyCharm IDE

Let me be honest about one thing : I don't have any draft outline for contents written in this blog. All of these articles were written in the spirit of improvisation and freedom to think/write/speech. For example, although I have been one of Qt fans since it was still owned by Trolltech, I have not the slightest idea that there will be a Qt series here. Even though I knew that Qt is supported in Python through PyQt, I am not that curious to start writing articles about it here. Only after I wrote this article, which lead me to Kivy that I began to experience something thrilling : doing Android development using pure Python! While I work on Kivy though, I also explore were there any alternatives to Android development using Python? Or in general, "Can we develop Android application without the use of Java?". The answer to this question bring me to (amongst other things) : Qt in Android. In which in Qt Project website itself, Python is listed as the most prominence third party programming language binding having Qt support! That was such a great news... Hence, our Qt series articles was born..

Another example is an answer around this question, "In this Qt series, what is the application that we are going to build?". Plain answer? No idea. Well, not that completely blind for sure. I just know that it must be a data aware application. And in the process of its development, it must be prefaced with a practical introduction to Test Driven Development(TDD). Combining both requirements, bring us to this current article : database support in either C++ Qt or PySide/PyQt. To keep things interesting, in this article we will officially use two IDEs : QtCreator for cross platform C++ Qt development and PyCharm for its Python counterpart.

Now that I have your attention ladies and gentlemen, lets drill on this cool talk about database support in Qt using either C++ or Python! Enjoy your time and .. happy reading! Laughing

Database Support in Qt

Database maybe the most prevalence reason why computer have massive cultural impact in our human civilization development : all aspects of our culture will eventually have some sort of data need to be written and or transported somewhere. Following its development, database  application develop itself from 1-tier architecture, 2-tier, 3-tier and from there on, simply known as multi-tier. The main theme being brought was simple : any application from different programming languages should  be able to talk to any database, provide that database driver for that particular database is available. Note that, in our discussion, when we refer to database actually we refer to specific type of database, which is relational database with its language, SQL. Another database type exists for sure, such as NoSQL, hierarchical or even flat file database.

Qt --as with any modern application framework-- was also designed with this modularity in mind. Amongst Qt modules, Qt Sql is the module that particularly responsible with database support for Qt applications. Due to its layered architecture, you can switch to any supported database anytime and yet your applications deserve only one single line of change: loading of database driver. In this article we are going to have a hands-on on Qt database support using either C++ or PyQt/PySide. Lets start with the C++ version first, this time using QtCreator.

Creating Application Database 

Below is our Users table that we will use in the next subsequent section. How do you prepare such table? Or, a much better question is how do you prepare the database in a particular RDBMS (Relational Database Management System) product?

Just a plain Users table

Just a plain Users table

As Qt will be able to work with any database --provide that its database driver existed--, technically speaking, the question of what RDBMS product to be use is somewhat a minor question. In this article I provide the code work using either PostgreSQL or Sqlite. I suggest that you test your application against SQLite first. Once done, you can easily switch to full fledged client-server RDBMS product such as PostgreSQL or MySQL.

Using SQLite

SQLite probably the easiest database to use. Qt binary was prebuilt with SQLite support, so there is nothing extra work must be done in your part as developer or in your user workstation. Every SQLite database is stored in a single file, so it is perfectly well suited for a standalone application that need database access in some sort. Looking at its serious user, we can say that SQLite is not a toy. It really is a production quality database.

We will create SQLite database directly from within Qt code, so there really is nothing managerial task must be done.

Using PostgreSQL

But the case is rather different with PostgreSQL. It's a client server RDBMS product, which require a magnitude of managerial task before the database can be use by your application. You can follow my other article that briefly explained how to install PostgreSQL in either Linux or Windows. To create our database, lets just call it pythonthusiast, you can use PgAdmin III that shipped with PostgreSQL distribution or, you can create a simple Django project and lets South manage your database schema creation from Django models. These are the series of commands that eventually create the appropriate database. 

1
2
3
4
5
6
7
8
9
10
11
12
$ createdb pythonthusiast #create postgresql database
$ mkdir database #create directory to hold django project
$ cd database #go to directory
$ python /usr/lib/python2.7/dist-packages/django/bin/django-admin.py startproject pythonthusiast #create django project
$ cd pythonthusiast #go to newly created django project
$ python /usr/lib/python2.7/dist-packages/django/bin/django-admin.py startapp qtapp #create django application
$ cd qtapp #go to directory
$ emacs models.py #also edit pythonthusiast/settings.py to use south and match Postgresql database configuration
$ cd ../
$ python manage.py syncdb #initialize database
$ python manage.py convert_to_south qtapp #convert to south application
$ python manage.py migrate qtapp #migrate to south

Below is the content of models.py which resemblance Users table previously:

1
2
3
4
5
from django.db import models
 
class Users(models.Model):
    username = models.CharField(max_length = 30)
    password = models.CharField(max_length = 255)

By using Django application (installed with South) to manage your database, you will gain the benefit of a simpler process of managing database schema changes. Read more about how to use South in Django application in my other article here

Finally to use PostgreSQL with Qt, you will have to install PostgreSQL driver. In Linux Ubuntu 12.0.4 LTS, this can be done easily by issuing the following command : sudo apt-get install libqt4-sql-psql. But in Windows you will have to build it yourselves. Once done, your Qt application will have the ability connecting to PostgreSQL database.

Accessing Database From C++ Qt Application

Opening *.pro project file in QtCreator

Now that we already have a proper database of our choice, let start to code from our previous unittest.pro project file, but this time we're going to open it from QtCreator. Simply launch QtCreator and open the existing project file through the menu command File->Open file or project. As this is the first time we open this *.pro file using QtCreator, it will open Target Setup dialog box as below:

First time open a *.pro file

First time opening a *.pro file using QtCreator

If you run this project (using CTRL+R), somehow the terminal stay blank. To fix this, switch to projects mode (CTRL + 5), and unchecked the Run in Terminal checkbox. Run the project again, and you will see our familiar QtTest unit testing output in 

QtTest output

QtTest output

Mind you, that the code above haven't got any database support implemented. It was merely running our previous project within QtCreator. Anyway, I always think that QtCreator should've been created with a much tighter support by treating unit testing  output in the  Red Green bar visualization like its JUnit counterpart. But, the good news is, iff there are failed assertion in the unit test method, you can still double click on them to go directly to the source code. Compare this with bare bone console output, this is an improvement.

Connecting to Database

Simply open unittest.pro project file and change QT += testlib  property to read QT += sql testlib. This will link your Qt application to QtSql module and will let you access all QtSql classes. That's all!

To test whether we really able to connect to the underlying database, let's review our TestFeature01.cpp and modify initTestCase() method as follows:

1
2
3
4
void TestCase01::initTestCase()
{
    QCOMPARE(Helper::dbConnect(), true);
}

We expect the result of Helper::dbConnect() to be true for a successful connection. Bellow is the Helper::dbConnect() method:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
bool Helper::dbConnect()
{
    //If using Sqlite
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    QString filename = "pythonthusiast.db";
    QFile database(filename);
    if(!database.exists())
    {   
        qDebug() << "Database not found. Creating and opening";
        db.setDatabaseName(filename);
        db.open();
        QSqlQuery query;
        query.exec("create table qtapp_users "
                    "(id integer primary key autoincrement, "
                    "username varchar(30), "
                    "password varchar(255))");
        query.prepare("insert into qtapp_users(username, password) values(:username, :password)");
        query.bindValue(":username", "eko");
        query.bindValue(":password", Helper::computeHash("password"));
        query.exec();
    }
    else
    {
        qDebug() << "Database found. Opening";
        db.setDatabaseName(filename);
        db.open();
    }
 
    //If using PostgreSQL
    /*
    QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName("localhost");
    db.setDatabaseName("pythonthusiast");
    db.setUserName("postgres");
    db.setPassword("your-password-please");
    db.open();
    */
 
    return db.isOpen();
}

I commented the code that will be use if we are about to switch into PostgreSQL. Currently, the code that will use SQLite is having another layer of complexity by creating all the required tables. This is the very fun part of using SQLite : you can easily create the database by your own code. If the database is not existed, the code db.open() will create it. User won't have to use another application to create the database. And it really is a wonderful user experience!

Try to run this unit testing code, and experiment with the database driver selection. You can inspect all the database driver installed with your Qt distribution.

Implementing doLogin() Method

This maybe the core part of this article discussion : continuing our development of Auth::doLogin() method from previous article, where we simply return true. Here, we are going to use QSqlQuery object to execute query against active database. Below is the complete code for Auth::doLogin():

1
2
3
4
5
6
7
8
9
10
11
12
bool Auth::doLogin(QString name, QString pass)
{
    QSqlQuery query;
    query.prepare("select id from qtapp_users where username = :username and password = :password");
    query.bindValue(":username", name);
    query.bindValue(":password", Helper::computeHash(pass));
    query.exec();
    if(query.next()){
        return true;
    }
    return false;
}

 

We use QSqlQuery::prepare() to easier construct a valid SQL command having several arguments, and use QSqlQuery::bindValue() to supply value to those arguments. We also saved our password as MD5 hash for a much safer way than storing a plain text password, by utilizing Qt Cryptography module. Below is how to compute MD5 Hash using Qt Cryptography module:

1
2
3
4
QString Helper::computeHash(QString original)
{
    return QString(QCryptographicHash::hash(original.toUtf8(),QCryptographicHash::Md5).toHex());
}

Qt will create a binary format for the MD5 result, while why need the result to be stored as Hexadecimal number. Hence, we convert it using QCryptographicHash::hash()::toHex() method.

Testing of Auth::doLogin() Method

To aid us in testing our login feature, we will need to know beforehand an MD5 value for a certain string, e.g. "password". Lucky for us, we can use this web page to calculate hash value for any string. Therefore, we can construct a more reliable unit testing code and become confidence of our overall application maturity. Depicted below is the final unit testing code and its output, taken directly from QtCreator application output:

A complete unit testing code of login feature

A complete unit testing code of login feature

 

Accessing Database From PyQt/PySide Application

Using PyCharm in Test-First Programming

Although in my previous article we have already talked about unit testing of a Python project, in there we still run our unit testing code using console. For this session, lets use one in the many array of Python IDE that support Test-First Programming/Test Driven Development, by giving a better visualization and control of unit testing result, much as JUnit plugin in Eclipse does. I am using JetBrains PyCharm Community Editions which can be downloaded and use freely from the following download page.

As PyCharm is a Java SE application, you will need to install Java SE from Oracle download page. The lighter version is to download the JRE, which gives you Java Runtime Environment only. While the complete version is to download JDK, which not only gives JRE but also a complete development tools to build Java application. I recommend that you install JDK in your system, as we are going to discuss Jython in the near feature. And installing JDK right now, will help you in the integration of Java and Python, which is what Jython is all about.

Once installed, run PyCharm and you'll be presented with this welcome page :

Jetbrains PyCharm welcome page

PyCharm welcome page 

As we will open our previous Python unit testing application reside in an --of course-- existing folder, choose Open Directory and navigate to that particular directory. Actually, for this new article, I create a new directory named PyQt and copy all Python code from previous article to this new directory. I also have to rename TestFeature01.py into test_feature01.py in order for PyCharm to be able to find all my unit test classes. This is a slight requirement that's not much of a headache to comply with. This same requirement apply if you're about to use ActiveState KomodoIDE instead of PyCharm. 

Now that your project already opened in PyCharm, simply right click on its top Project Node, choose "Run 'Unittests in ..." and PyCharm will automatically (by means of file name pattern previously mentioned) runs all unit tests code within your project. Like its JUnit counterpart, PyCharm display unit test result in a much more pleasant way.

Connecting to Database

If you have installed PyQt4 / PyQt5 in Windows, have a look at the following directory : C:\Python2.7\Lib\site-packages\PyQt4\plugins\sqldrivers. It contains SQL database drivers shipped with every PyQt4/5 installation, which are : SQLite, PostgreSQL, MySQL and ODBC. Therefore, we can readily access supported database from within our PyQt application. If you have gone through our previous C++ code, you will also find that our PyQt4 code was very much a direct translation from its C++ counterpart. Below is the complete listing of helper.py module

helper.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
from PyQt4.QtCore import *
from PyQt4.QtSql import *
 
def dbConnect():
    db = QSqlDatabase.addDatabase("QSQLITE")
    filename = "pythonthusiast.db"
    database =  QFile(filename)
    if not database.exists():
        qDebug("Database not found. Creating and opening")
        db.setDatabaseName(filename)
        db.open()
        query = QSqlQuery()
        query.exec_("create table qtapp_users "
                    "(id integer primary key autoincrement, "
                    "username varchar(30), "
                    "password varchar(255))")
        query.prepare("insert into qtapp_users(username, password) values(:username, :password)")
        query.bindValue(":username", "eko")
        query.bindValue(":password", computeHash("password"))
        query.exec_()
    else:
        qDebug("Database found. Opening")
        db.setDatabaseName(filename)
        db.open()
    return db.isOpen()
 
def computeHash(original):
    return QCryptographicHash.hash(QString(original).toUtf8(), QCryptographicHash.Md5).toHex()

Try to compare it with its C++ counterpart, and you will have a deja vu experience! 

But there is one important requirement for this code to work : a QApplication instance must be created before hand, in order SQL drivers to be loaded. Failed to do so will result in a run-time error with this message QSqlDatabase: an instance of QCoreApplication is required for loading driver plugins.

Implementing doLogin() Method

Not surprisingly, the PyQt version of Auth class is practically a translation of its C++ Qt version. I really just copy it from the C++ version, paste it into PyCharm, and then adjust the syntax to be a valid Python syntax. Here is the full code of auth.py :

1
2
3
4
5
6
7
8
9
10
11
12
13
from PyQt4.QtSql import *
import helper
 
class Auth:
    def doLogin(self, username, password):
        query = QSqlQuery()
        query.prepare("select id from qtapp_users where username = :username and password = :password")
        query.bindValue(":username", username)
        query.bindValue(":password", helper.computeHash(password))
        query.exec_()
        if query.next():
            return True
        return False

 

Testing of Auth.doLogin() Method

To test the our Python code, we must preface it with a call to instantiate QCoreApplication. Please be advised that all test methods must be viewed to run independently. Therefore, for each test method that execute SQL query to the database, we must preface it with the call to instantiate QCoreApplication This will assure that all test methods will be able to run independently.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import unittest
from PyQt4.QtCore import QCoreApplication
from auth import Auth
import helper
import sys
 
class TestCase01(unittest.TestCase):
    def testConnect(self):
        qApp = QCoreApplication(sys.argv)
        self.assertTrue(helper.dbConnect())
 
    def testMd5(self):
        self.assertEqual(helper.computeHash("password"),"5f4dcc3b5aa765d61d8327deb882cf99")
 
    def testAuth(self):
        qApp = QCoreApplication(sys.argv)
        helper.dbConnect()
        auth = Auth()
        self.assertEqual(auth.doLogin("eko", "password"), True)
 
if __name__ == "__main__":
    unittest.main()

For a successful unit test execution for this unit testing method, you may refer to the first image in this article.

 

Using PySide instead of PyQt

Converting the application to use PySide, merely means replacing below PyQt4 import :

1
2
import PyQt4.QtCore import *
from PyQt4.QtSql import *

Into PySide import:

1
2
from PySide.QtCore import *
from PySide.QtSql import *

Beside that there is no QString in PySide, the code runs just as well, although with several different behaviours, such as:

  1. In PySide you may not have more than one QCoreApplication instance. Having multiple instance of QCoreApplication will result in run-time error. This case is not happening in PyQt application.
  2. It seems that PySide won't be able to load 32 bit SQL drivers in a 64 bit OS. You will also need to use a 64 bit version of Python. Also, in 64 version of Python, you don't have to instantiate QCoreApplication to have the SQL drivers loaded.
  3. There is no PySide binary distribution for Python 2.7, so you will have to use either the Python 2.6 version or the Python 3.3 version.
  4. As there is no QString in PySide, you will simply have to let Python handle string for you.

What's Next?

In this article I have gone into practical hands-on on database access using either C++ Qt or PyQt/PySide. We use SQLite and PostgreSQL as the backend database, which simply means loading different SQL drivers. You can also see that the PySide/PyQt application version is a direct translation of its C++ Qt counterpart. 

Our next article will ultimately use Qt Designer to design our application user interface and then we will have a look on how to use the designed user interface into our C++/Python application.

You can download the current state of the application here : crossplatformqt-3.zip.

Or follow its Github repository here : pythonthusiast/CrossPlatformQt.

Stay tuned!

 




Leave comments

 authimage
  • Hello Samsam,
    No problem Samsam, I completely understood / able to guess your situation that drive your comment previously ;)

    You're right about that notion regarding PyQt on Android project. The one on Google Group, I presume? Eventually I will arrive at that, but for now, I really got to laid out a solid foundation about C++ Qt/PyQt/PySide on this blog. This way, in the future articles, I can easily refer to my previous articles : making a solid connection of flows of idea ;)

    Honestly, thanks for your feedback.
    It makes me trying my best to create a more high quality articles ;)

    Enjoy Python!
    - Eko

    • eko
  • Hello eko,

    thanks for replying so kind to my rather rude post.
    I'm sorry about that. Your other information was actually quite relevant for me since I'm just starting out with python.

    I just really wanted to start gui Programming with pyqt5 on android. I was just fed up, that there is no documentation for deploying pyqt5 on android yet. Its developer says he's working on it, but no timetable was mentioned (Post from December 2013).

    Thanks again, will be checking this out in the future

    • Samsam
  • PS : Anyway, thanks for your notion about my --seems to disorganized-- attitude. It's been a while since I want to introduce navigational features in this blog. This week, I think I will start my work on this blog first on preparing a good navigation/TOC for this blog. I may also schedule an ahead plan of what topics I will write in the upcoming week. ;)

    • eko
  • Hi @samsam,

    Well, in the later passage you can clearly read, "This come with a requirement : you must be introduced to and able to develop a PySide/PyQt application in its Desktop environment. Then, we move to its Android development.This is necessary, as differ with Kivy that directly support mobile application development in its first inception, Qt support in mobile development was gradually introduced. "

    It maybe my mistake of trying to accomplished a rather broader audience for this blog, e.g. I can easily skipped directly to Android development using PySide/PyQt/C++ Qt, but I strive to prepare a foundation for its desktop development first.

    This way, if you expect me to write that series soon, well, I am sorry that I have to disappointed you. I even thinking of prefacing the following Android *Qt with one more mobile application using Kivy. Or maybe finishing my two last article about Flask, or even continuing my Django tutorial ;)

    I got so many options there ;)

    Thanks for your feedback!
    - Eko

    PS : I think you already know, that you can start a PySide Android application using this "official" PySide Android wiki : http://qt-project.org/wiki/PySide_for_Android_guide. Goodluck!

    • eko
  • In Part 1 you stated in the introduction:
    "In this new article series, the target that I want to accomplished is to introduce you to the Python Android application development using either PySide or PyQt."

    So when are you finally going to show us how to create an actual Android APK using pyqt5 ? Its really hard to find any documentation for that topic.

    Unlike for all the other zillion topics you introduced where one can find much better tutorials by a quick google search.
    are you really that disorganized?

    thank you for your effort though.

    • samsam
  • Hi Cka,

    #1 Correct! My mistake. I modify the code there. But I still simplify it, by referring to the ...::toHex() as a method. And as I come from Java, I often use the term method instead of member function.
    #2 I still prefer to use QCOMPARE though, as it offer more verbose output when there are test failure.

    Thanks for your feedback on this article. ;)

    • eko
  • For logical operations, you can use QVERIFY or QVERIFY2 instead of QCOMPARE.

    QVERIFY(auth.doLogin(...)); vs QCOMPARE(auth.doLogin(...), true);

    • cka
  • 2 corrections:
    QCryptographicHash::hash() is a static member function, not a class.
    toHex() is a member function of QByteArray.

    • cka

Copyright(c) 2014 - PythonBlogs.com
By using this website, you signify your acceptance of Terms and Conditions and Privacy Policy
All rights reserved