The SQL query can be executed by creating a QSqlQuery object and calling an exec_() function on this. 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 KEY  AUTOINCREMENT 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 columns, 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. As 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 will look at the different ways in which we can execute DML commands. A simple form of inserting the values in the table that we 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 will insert a single row in the employee table. This method is easier if you need to insert a single row into the table. However, if we require the creation of many rows, it is advised 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, namely, 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_()

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 methods work with all database drivers that are 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.

QSqlQuery can execute any arbitrary SQL statements, such as 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, as follows:

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 through the records. However, if we need to move only forward through the results, we can set the QSqlQuery.setForwardOnly(), which can improve performance and save a significant amount of memory in some databases. The QSqlQuery.value() function takes an integer positional argument, which returns the value of the 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

As QSqlQuery.value() takes an index positional argument, it is not advised to use SELECT * in the query. Instead, use the column names because we will not know the order of columns in the SELECT * query.

Let's now take a 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, we use the toInt() and toString() functions to convert the result to a specific data type because all the values that are returned are of the 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 look at a 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 return -1 if it cannot be determined or the query is not active. In case of the SELECT statements, this function returns undefined. Instead, we use QSqlQuery.size() which 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 whether the database driver uses a specific feature, we can use QSqlDriver.hasFeature(), which will return a true or false value accordingly. So, we can use QSqlDriver.hasFeatire(QSqlDriver.Transactions) to identify whether 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() 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 cancelling a complex change in the middle.