Transcript Ch15-Databasex

Rapid GUI Programming
with Python and Qt
Databases
By
Raed S. Rasheed
1
Databases
PyQt provides a consistent cross-platform API for database access using the QtSql
module and PyQt’s model/view architecture. Python also has its own completely
different database API, called DB-API, but it isn’t needed with PyQt and is not
covered here. The commercial edition of Qt comes with many database drivers,
whereas the GPL edition has fewer due to licensing restrictions. The drivers that are
available include ones for IBM’s DB2, Borland’s Interbase, MySQL, Oracle, ODBC (for
Microsoft SQL Server), PostgreSQL, SQLite, and Sybase. However, like any aspect of
PyQt, it is possible to create additional database drivers if one we need is not
available.
2
Connecting to the Database
To use PyQt’s SQL classes we must import the QtSql module:
from PyQt4.QtSql import *
A database connection is established by calling the static
QSqlDatabase.addDatabase()
method, with the name of the driver we want to use. Then we must set various
attributes, such as the database’s name, the username, and the password. And
finally, we must call open() to make the connection.
3
Connecting to the Database
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(filename)
if not db.open():
QMessageBox.warning(None, "Phone Log",
QString("Database Error: %1").arg(db.lastError().text()))
sys.exit(1)
4
Executing SQL Queries
query = QSqlQuery()
query.exec_("""CREATE TABLE outcomes (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
name VARCHAR(40) NOT NULL)""")
5
Executing SQL Queries
query.exec_("""CREATE TABLE calls (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
caller VARCHAR(40) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
topic VARCHAR(80) NOT NULL,
outcomeid INTEGER NOT NULL,
FOREIGN KEY (outcomeid) REFERENCES outcomes)""")
6
Executing SQL Queries
The Phone Log database design
7
Executing SQL Queries
Now that we have created the tables, we can populate them with data.
for name in ("Resolved", "Unresolved", "Calling back", "Escalate",
"Wrong number"):
query.exec_("INSERT INTO outcomes (name) VALUES ('%s')" % name)
8
Executing SQL Queries
query.prepare("INSERT INTO calls (caller, starttime, endtime, "
"topic, outcomeid) VALUES (?, ?, ?, ?, ?)")
for name, start, end, topic, outcomeid in data:
query.addBindValue(QVariant(QString(name)))
query.addBindValue(QVariant(start)) # QDateTime
query.addBindValue(QVariant(end)) # QDateTime
query.addBindValue(QVariant(QString(topic)))
query.addBindValue(QVariant(outcomeid)) # int
query.exec_()
9
Executing SQL Queries
We can use QSqlQuery to execute any arbitrary SQL statement. For example:
query.exec_("DELETE FROM calls WHERE id = 12")
10
Executing SQL Queries
We will conclude our coverage of QSqlQuery by looking at how to use it to execute
SELECT statements, and how to iterate over the resultant records.
DATETIME_FORMAT = "yyyy-MM-dd hh:mm"
ID, CALLER, STARTTIME, ENDTIME, TOPIC, OUTCOMEID = range(6)
query.exec_("SELECT id, caller, starttime, endtime, topic, "
"outcomeid FROM calls ORDER by starttime")
11
Executing SQL Queries
while query.next():
id = query.value(ID).toInt()[0]
caller = unicode(query.value(CALLER).toString())
starttime = unicode(query.value(STARTTIME).toDateTime() \
.toString(DATETIME_FORMAT))
endtime = unicode(query.value(ENDTIME).toDateTime() \
.toString(DATETIME_FORMAT))
topic = unicode(query.value(TOPIC).toString())
12
Executing SQL Queries
outcomeid = query.value(OUTCOMEID).toInt()[0]
subquery = QSqlQuery("SELECT name FROM outcomes "
"WHERE id = %d" % outcomeid)
outcome = "invalid foreign key"
if subquery.next():
outcome = unicode(subquery.value(0).toString())
print "%02d: %s %s - %s %s [%s]" % (id, caller, starttime,
endtime, topic, outcome)
13
Using Database Form Views
The simplified Phone Log application
14
Using Database Form Views
With the widgets in place, we create a QSqlTableModel. Since we did not specify a
particular database connection, it uses the default one. We tell the model which
able it is to work on and call select() to make it populate itself with data. We also
choose to apply a sort order to the table.
self.model = QSqlTableModel(self)
self.model.setTable("calls")
self.model.setSort(STARTTIME, Qt.AscendingOrder)
self.model.select()
15
Using Database Form Views
Now that we have suitable widgets and a model, we must somehow link them
together. This is achieved by using a QDataWidgetMapper..
self.mapper = QDataWidgetMapper(self)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setModel(self.model)
self.mapper.addMapping(self.callerEdit, CALLER)
self.mapper.addMapping(self.startDateTime, STARTTIME)
self.mapper.addMapping(self.endDateTime, ENDTIME)
self.mapper.addMapping(topicEdit, TOPIC)
self.mapper.toFirst()
16
Using Database Form Views
If the user navigates, we must remember the current row, since it is forgotten after
calling submit(). Then, after saving the current record, we set the row to be the one
appropriate for the navigation the user requested (but kept within bounds), and
then use setCurrentIndex() to move to the appropriate record.
17
Using Database Form Views
def saveRecord(self, where):
row = self.mapper.currentIndex()
self.mapper.submit()
if where == PhoneLogDlg.FIRST:
row = 0
elif where == PhoneLogDlg.PREV:
row = 0 if row <= 1 else row - 1
elif where == PhoneLogDlg.NEXT:
18
Using Database Form Views
row += 1
if row >= self.model.rowCount():
row = self.model.rowCount() - 1
elif where == PhoneLogDlg.LAST:
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
19
Using Database Form Views
row += 1
if row >= self.model.rowCount():
row = self.model.rowCount() - 1
elif where == PhoneLogDlg.LAST:
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
20
Using Database Form Views
We have chosen to always add new records at the end. To do this we find the row
after the last one, save the current record, and then insert a new record at the last
row in the model. Then we set the mapper’s current index to the new row, initialize
a couple of fields, and give the caller field the focus, ready for the user to start
typing.
21
Using Database Form Views
def addRecord(self):
row = self.model.rowCount()
self.mapper.submit()
self.model.insertRow(row)
self.mapper.setCurrentIndex(row)
now = QDateTime.currentDateTime()
self.startDateTime.setDateTime(now)
self.endDateTime.setDateTime(now)
self.callerEdit.setFocus()
22
Using Database Form Views
If the user clicks Delete we pick out some information from the current record and
use it when we ask the user to confirm the deletion. If they confirm, we retrieve
the current row, remove the row from the model, and call submitAll() to force the
model to write back the change to the underlying data source (in this case the
database). Then we finish up by navigating to the next record.
23
Using Database Form Views
def deleteRecord(self):
caller = self.callerEdit.text()
starttime = self.startDateTime.dateTime().toString(DATETIME_FORMAT)
if QMessageBox.question(self,QString("Delete"),QString("Delete call made by<br>%1 on
%2?").arg(caller).arg(starttime),QMessageBox.Yes|QMessageBox.No) == QMessageBox.No:
return
row = self.mapper.currentIndex()
self.model.removeRow(row)
self.model.submitAll()
if row + 1 >= self.model.rowCount():
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
24
Using Database Table Views
Probably the most natural and convenient way to present database data is to show
database tables and views in GUI tables. This allows users to see many records at
once, and it is particularly convenient for showing master–detail relationships.
The Asset Manager database design
25
Using Database Table Views
Probably the most natural and convenient way to present database data is to show
database tables and views in GUI tables. This allows users to see many records at
once, and it is particularly convenient for showing master–detail relationships.
26
Using Database Table Views
The Asset Manager database design
27
Using Database Table Views
class MainForm(QDialog):
def __init__(self):
super(MainForm, self).__init__()
self.assetModel = QSqlRelationalTableModel(self)
self.assetModel.setTable("assets")
self.assetModel.setRelation(CATEGORYID,QSqlRelation("categories", "id", "name"))
self.assetModel.setSort(ROOM, Qt.AscendingOrder)
self.assetModel.setHeaderData(ID, Qt.Horizontal,QVariant("ID"))
self.assetModel.setHeaderData(NAME, Qt.Horizontal,QVariant("Name"))
self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal,QVariant("Category"))
self.assetModel.setHeaderData(ROOM, Qt.Horizontal,QVariant("Room"))
self.assetModel.select()
28
Using Database Table Views
The view is a standard QTableView, but instead of setting a QSqlRelationalDelegate,
we have set a custom delegate. We will detour to look at this in a moment. The
selection mode is set so that users can navigate to individual fields; the selection
behavior is that the row that has the focus is highlighted. We don’t want to show
the ID field since it isn’t meaningful to the user, so we hide it.
29
Using Database Table Views
self.assetView = QTableView()
self.assetView.setModel(self.assetModel)
self.assetView.setItemDelegate(AssetDelegate(self))
self.assetView.setSelectionMode(QTableView.SingleSelection)
self.assetView.setSelectionBehavior(QTableView.SelectRows)
self.assetView.setColumnHidden(ID, True)
self.assetView.resizeColumnsToContents()
30
Using Database Table Views
The heart of the createEditor() method is the code that sets up the QLineEdit for
entering room numbers. Room numbers are four digits long, made up of a floor
number, in the range 01–27 (but excluding 13), and a room number on the floor in
the range 01–62. For example, 0231 is floor 2, room 31, but 0364 is invalid. The
regular expression is sufficient for specifying valid room numbers, but it cannot set
a minimum number of digits, since one, two, or three digits may be a valid prefix
for a valid four digit room number. We have solved this by using an input mask that
requires exactly four digits to be entered. For the other fields, we pass the work on
to the base class.
31
Using Database Table Views
def createEditor(self, parent, option, index):
if index.column() == ROOM:
editor = QLineEdit(parent)
regex = QRegExp(r"(?:0[1-9]|1[0124-9]|2[0-7])“
r"(?:0[1-9]|[1-5][0-9]|6[012])")
validator = QRegExpValidator(regex, parent)
editor.setValidator(validator)
editor.setInputMask("9999")
editor.setAlignment(Qt.AlignRight|Qt.AlignVCenter)
return editor
else:
return QSqlRelationalDelegate.createEditor(self, parent,option, index)
32
Using Database Table Views
The code for creating the log model is almost the same as the code we used for the
asset model. We use a QSqlRelationalTableModel because we have a foreign key
field, and we provide our own column titles.
self.logModel = QSqlRelationalTableModel(self)
self.logModel.setTable("logs")
self.logModel.setRelation(ACTIONID,QSqlRelation("actions", "id", "name"))
self.logModel.setSort(DATE, Qt.AscendingOrder)
self.logModel.setHeaderData(DATE, Qt.Horizontal,QVariant("Date"))
self.logModel.setHeaderData(ACTIONID, Qt.Horizontal,QVariant("Action"))
self.logModel.select()
33
Using Database Table Views
self.logView = QTableView()
self.logView.setModel(self.logModel)
self.logView.setItemDelegate(LogDelegate(self))
self.logView.setSelectionMode(QTableView.SingleSelection)
self.logView.setSelectionBehavior(QTableView.SelectRows)
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
self.logView.resizeColumnsToContents()
self.logView.horizontalHeader().setStretchLastSection(True)
34