Executing SQL Queries

Now that we have a connection, we can execute some SQL statements. query = QSqlQuery()

query.exec_( CREATE TABLE outcomes (

id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name VARCHAR(40) NOT NULL) )

query.execj 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) )

We have not specified a particular database connection to use, so PyQt will use the default (unnamed) connection that we established earlier. The tables created by the SQL calls are shown schematically in Figure 15.1.

The AUTOINCREMENT syntax tells SQLite to populate the id field automatically with each ID being one more than the previous one, with the first being 1. Similarly, the FOREIGN KEY syntax tells SQLite about a foreign key relationship. SQLite 3 does not enforce foreign key relationships, merely allowing us to express them as a documentation aid. The syntax for achieving automatic IDs and for foreign keys may be different in other databases.

calls

>-

outcomes

id caller starttime endtime topic outcomeid

id name

Figure 15.1 The Phone Log database design

Figure 15.1 The Phone Log database design

Many databases have their own set of data types. For example, SQLite 3 has what it calls "storage classes", including, INTEGER, REAL, and TEXT. PyQt supports the standard SQL datatypes, including VARCHAR, NUMBER, DATE, and DATETIME, transparently converting to and from the database's native data types behind the scenes. For text, PyQt uses Unicode, except with databases that don't support Unicode, in which case PyQt converts to and from the database's native encoding.

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)

We did not need to provide IDs since we have asked the database to generate them for us. Unfortunately, the preceding code is not robust: For example, it will fail if one of the names contains a single quote. One way to deal with this is to ensure that we either remove or escape unacceptable characters, but PyQt provides a better alternative: prepared queries.

There are two widely used forms of syntax for prepared queries, one based on the ODBC place holder approach, and the other based on the Oracle named variable approach. PyQt supports both, converting from one to the other behind the scenes if necessary, so that both work no matter what the underlying database is.

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_()

This example uses the ODBC syntax. One benefit of using place holders is that PyQt takes care of the quoting issues, so we don't have to worry about what our data contains, as long as the types we pass are appropriate for the fields they will populate.

query.prepare("INSERT INTO calls (caller, starttime, endtime, " "topic, outcomeid) VALUES (:caller, :starttime, " ":endtime, :topic, :outcomeid)") for name, start, end, topic, outcomeid in data:

query.bindValue(":caller", QVariant(QString(name))) query.bindValue(":starttime", QVariant(start)) query.bindValue(":endtime", QVariant(end)) query.bindValue(":topic", QVariant(QString(topic))) query.bindValue(":outcomeid", QVariant(outcomeid)) query.exec_()

This second example performs the same work as the first, but uses Oracle-style named variables. PyQt also supports a couple of other variations of prepared query syntax, but they don't add anything to what we can do with the two forms of syntax shown earlier. Prepared queries can improve performance on databases that support them, and make no difference on those that don't.

Prepared queries can also be used to call stored procedures, but we will not cover them because support for them is neither universal nor uniform. For example, not all databases support stored procedures, and the syntax for calling them and for retrieving OUT values is different from database to database. Also, stored procedures that return values are not fully supported.

We can use QSqlQuery to execute any arbitrary SQL statement. For example:

query.exec_("DELETE FROM calls WHERE id = 12")

After a query has executed we can check for errors by calling QSqlQuery.is-Active(); if this returns False an error occurred and the error message is available as a QString by calling QSqlQuery.lastError().text().

If we perform a query that may affect a number of rows, such as a DELETE or UPDATE whose WHERE clause might select more than one record, we can call QSqlQuery.numRowsAffected(); it returns -1 on error.

We can find out whether the underlying database supports various features such as transactions and BLOBs (Binary Large OBjects) by accessing the driver and calling hasFeature(). For example:

driver = QSqlDatabase.database().driver() if driver.hasFeature(QSqlDriver.Transactions): print "Can commit and rollback"

When we use QSqlQuery we can initiate a transaction by calling QSqlData-base.database().transaction(), and then either QSqlDatabase.database().com-mit() or QSqlDatabase.database().rollback().

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") while query.next():

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()) 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)

When we execute a SELECT statement, we can iterate over the result set using methods such as QSqlQuery.next(), QSqlQuery.previous(), and QSqlQuery.seek(). Immediately after a successful SELECT, isActive() will return True but the inter-

Table 15.1 Selected QSqlQuery Methods

Syntax q.addBindValue(v) q.bindValue(p, v) q.boundValue(p) q.driver()

q.isActive()

q.isValid()

q.lastError()

q.numRows-Affected()

q.prepare(s) q.previous()

q.record()

q.value(i)

Description

Adds QVariant v as the next variable when using positional value binding in QSqlQuery q Sets QVariant v as the value for the string p place holder when using place holder value binding in QSqlQuery q Returns the QVariant value for the string p place holder in QSqlQuery q

Returns the QSqlDriver associated with QSqlQuery q. The QSqlDriver class provides hasFeature() to report which features the underlying database supports. Executes the SQL query in string s on QSqlQuery q

Navigates to the first record in QSqlQuery q's result set after a SELECT query has been executed Returns True if the query is "active"—for example, after executing a SELECT query

Returns True if the query is positioned on a valid record; after a SELECT query this will be True only if isActive() is True and a record has been navigated to Navigates to the last record in QSqlQuery q's result set after a SELECT query has been executed Returns a QSqlError object; this provides an error-String() method

Navigates to the next record in QSqlQuery q's result set after a SELECT query has been executed. This is the only method needed to iterate forward over a result set. Returns the number of rows affected by the SQL query just executed, providing it was not a SELECT, and providing the underlying database supports this feature Prepares the query in string s ready for q to execute it

Navigates to the previous record in QSqlQuery q's result set after a SELECT query has been executed Returns a QSqlRecord object containing QSqlQuery q's current record, if any; using QSqlQuery.value() with a field index argument is usually more convenient Returns the number of rows in the SELECT result set, or -1 if a SELECT was not executed or if the underlying database does not support this feature Returns the QVariant value for field index int i in the current record, if there is one nal record pointer will not be referring to a valid record. Each of the navigation methods returns True if the query's internal record pointer was successfully moved onto a valid record; this is why we call QSqlQuery.next() before accessing the first record. They return False if an error occurred or if they pass the last (or first) record.

When navigating large result sets, providing we only use next(), or only seek() forward, we can call QSqlQuery.setForwardOnly(True). This can significantly improve performance or reduce memory overhead, or both, with some databases.

The QSqlQuery.value() method takes an index position argument, based on the order of the field names given in the SELECT statement. For this reason, using SELECT * is not recommended because in that case, we don't know what the order of the fields is. Each field is returned as a QVariant and must therefore be converted to the proper type. In the case of the date/times, we first convert them from QVariant to QDateTime, then to QString, and finally to unicode, ready to be printed on the console.

We used an additional query to look up the name of the outcome from its ID, giving error text if the database does not have relational integrity. For a large dataset, it would have been more efficient to use a prepared query for the subquery.

We can use QSqlQuery to do all the database work we want, but using PyQt's SQL models is much easier for GUI programming, and it does not prevent us from using QSqlQuery when the need arises.

Was this article helpful?

+2 -1
100 SEO Tips and More

100 SEO Tips and More

100 SEO Tips EVERY SEO Enthusiast Should Know. This Report 100 SEO Tips will help you to Utilize These Tips to Dominate The Search Engine Today.

Get My Free Ebook


Responses

  • liberata
    How to bind a field name in qsqlquery?
    8 years ago
  • lete
    How to take avalue from database in pyqt python using QSqlQuery?
    7 years ago

Post a comment