PyQt provides strong support for SQL databases with a consistent API provided by the QtSql module. Database drivers are provided for all the most widely used databases, although some are available only with commercial editions of Qt due to licensing restrictions.
If we make a single database connection, all subsequent database accesses will use that connection by default. But if we need multiple connections, we can simply give each one a name, and access them by name afterward to specify which one we want to use for which particular action.
We can access the database's driver, and through that discover whether the database supports certain features such as BLOBs and transactions. And no matter what the underlying database is, PyQt allows us to use prepared queries with both ODBC and Oracle syntax, automatically handling any conversions and quoting that are necessary. PyQt supports all the standard SQL data types, and performs any necessary conversions if the database itself does not.
The QSqlQuery class allows us to execute arbitrary SQL statements using its exec_() method. This means, for example, that we can use it to create and drop tables, and to insert, update, and delete records. The QSqlQuery objects provide methods for navigating the result set produced when a SELECT statement is executed, and they can provide information on the number of rows affected by a query—for example, how many were deleted or updated.
Creating GUI forms for displaying database tables (or editable views) is straightforward using a QDataWidgetMapper. We normally use a QComboBox for each foreign key field, giving it the appropriate relation model as its internal model. Although it is possible to set a QDataWidgetMapper to submit changes automatically, this can lead to data loss when the user navigates, so if we provide a means of navigation, it is best to submit all the changes ourselves.
Displaying database tables and views is very easy using QTableView in conjunction with a QSqlTableModel or a QSqlRelationalTableModel. These classes combine to offer in-place editing of field data. Adding and deleting records can easily be achieved by inserting or deleting rows from the model, and when we need atomic actions we can use transactions.
All the functionality of PyQt's model/view architecture is available to database programmers. In addition, the SQL table models' APIs have been extended to make database programming easier. And when we need to execute raw SQL, we can easily do so using the QSqlQuery class.
We have now reached the point where you should be able to create any kind of GUI application you like, limited only by your imagination and the time available to you. In Part IV we will look at some additional topics that can be tricky to deal with, starting with more advanced material on model/view programming, then internationalization, then networking, and finishing up with multithreading.
Was this article helpful?
Video Marketing Will Never Be The Same Again. Here's How YOU - Or ANYONE - Can Leverage On The Internet's 4th MOST TRAFFICKED WEBSITE TO BOOST YOUR POPULARITY amp PUBLICITY FOR BUSINESS via Video Marketing! With The Emergence Of Web Video Popularity, You CANNOT Afford To Miss Out On This Exponentially Growing Trend - Because It Could Make Or Break Your Popularity amp Business Growth!