The SQL query can be executed by creating an QSqlQuery object and calling an exec_() function on that. As an example, we create a table named employee and define its columns as follows:

myQuery = QSqlQuery()

myQuery.exec_("""CREATE TABLE employee (id INTEGER PRIMARY KEYAUTOINCREMENT UNIQUE NOT NULL, first_name CHAR(20) NOT NULL, last_name CHAR(20), age INT, sex CHAR(1), income FLOAT)""")

This will create a table with six fields namely, id, first_name, last_name, age, sex, and income. The QSqlQuery constructor accepts an optional parameter, a QSqlDatabase object that specifies which database connection to use. Since we don't specify any connection name in the preceding code, the default connection is used. In case of any errors, the exec_() function returns false and the error details are available in QSqlQuery.lastError().

In this section we shall look at the different ways we can perform the DML commands. A simple form of inserting the values in the table that we have created in our previous section is given as follows:

myQuery = QSqlQuery()
myQuery.exec_("""INSERT INTO employee (first_name, last_name, age, sex, income) VALUES ('Alice', 'M', 30, 'F', 5000.00) """)

This would insert a single row in the employee table. This method is easier if we need to insert a single row into the table. However, if we are required to create many rows, it is advisable to separate the query from the actual values being used. This can be achieved with the use of placeholders by binding the values with the columns in the table. Qt supports two types of placeholder systems, named binding and positional binding. This way of constructing queries is also called prepared queries.

An example of the named binding is given as follows:

myQuery.prepare("INSERT INTO employee (first_name, last_name, age, sex, income) VALUES (:first_name, :last_name, :age, :sex, :income)")

for fname, lname, age, sex, income in data:
  myQuery.bindValue(":first_name", fname)
  myQuery.bindValue(":last_name", lname)
  myQuery.bindValue(":age", age)
  myQuery.bindValue(":sex", sex)
  myQuery.bindValue(":income", income)

myQuery.exec_()

You may note that the id column is omitted during the rows in the previous examples since we have defined it to AUTOINCREMENT values while creating the table. Now, let's look at the other type of prepared query, positional binding:

myQuery.prepare("INSERT INTO employee (first_name, last_name, age, sex, income) VALUES (?, ?, ?, ?, ?)")

for fname, lname, age, sex, income in data:
  myQuery.addBindValue(fname)
  myQuery.addBindValue (lname)
  myQuery.addBindValue (age)
  myQuery.addBindValue (sex)
  myQuery.addBindValue (income)

myQuery.exec_()

Both the methods works with all database drivers provided by Qt. Prepared queries improve performance on databases that support them. Otherwise, Qt simulates the placeholder syntax by preprocessing the query. The actual query that gets executed can be received by calling the QSqlQuery.executedQuery() function. Also, note that, you need to call QSqlQuery.prepare() only once and you can call bindValue() or addBindValue() followed by the exec_() as many times as necessary. Another advantage of the prepared queries besides performance is that, we can specify arbitrary values without worrying about loosing the special characters.

QSqlQuery can execute any arbitrary SQL statements like SELECT and INSERT statements. So, updating and deleting records is as easy as executing the corresponding queries. For example, we can update a record as shown in the following line:

myQuery.exec_("UPDATE employee SET income=7500.00 WHERE id=5")

Similarly, we can delete a record by:

myQuery.exec_("DELETE FROM employee WHERE id=8")

Successfully executed SQL statements set the query's state to active and can be retrieved from QSqlQuery.isActive(). Otherwise, it is set to inactive. This method will return a Boolean value, True or False, depending on the success of the operation.

The next feature that we are about to discuss is how to navigate the records of the result set of a SELECT query. Navigating the records is performed by the following functions:

These functions help us in iterating back and forth the records. However, if we need to move only forward through the results, we can set QSqlQuery.setForwardOnly() which can improve performance, and saves significant amount of memory in some databases. The QSqlQuery.value() functions takes an integer positional argument, which returns the value of field index in the current record. The fields are numbered from left to right using the text of the SELECT statement. For example, in the following query, field 0 represents the first_name and field 1 represents the last_name:

SELECT first_name. last_name FROM employee

Since QSqlQuery.value() takes an index positional argument, it is not advised to use SELECT * in the query; instead use the column names because we don't know the order of columns in the SELECT * query.

Let us now look at an example of navigating the records through the result set:

myQuery.exec_("SELECT id, first_name, income FROM employee")
while myQuery.next():
id = myQuery.value(0).toInt()
name = myQuery.vaue(1).toString()
salary = myQuery.value(2).toInt()

In the preceding example, you would have noted that we use the toInt() and toString() functions to convert the result to specific data type, since all the values that are returned are of QVariant type which can hold various data types such as int, string, datetime, and so on.

Before closing the section on executing SQL queries, we will have a look at few more useful functions that the QSqlQuery class offers.

The QSqlQuery.numRowsAffected() function will return the number of rows that are affected by the result of an UPDATE or DELETE query. This function returns -1 if it cannot be determined or the query is not active. In case of SELECT statements, this function returns undefined, instead we use QSqlQuery.size() that will return the size of the result set. This function also returns -1 if the size cannot be determined or if the database does not support reporting information about query sizes or if the query is not active.

QSqlQuery.finish() will instruct the database driver that no more data will be fetched from the query until it is re-executed. Usually, we do not call this function until we want to free some resources such as locks or cursors if you intend to reuse the query at a later time. Finally, we can call QSqlQuery.at() to retrieve the current row index.

In order to check, if the database driver uses a specific feature, we can use QSqlDriver.hasFeature() that will return a true or false value accordingly. So, we can use QSqlDriver.hasFeature(QSqlDriver.Transactions) to identify if the underlying database engine supports transactions. If the underlying database supports transactions, we can retrieve the commit and rollback results using the QSqlDatabase.commit() and QSqlDatabase.rollback() functions respectively. The transaction can be initiated using the QSqlDatabase.transaction() call. Transactions can be used to ensure that a complex operation is atomic or to provide a means of canceling a complex change in the middle.