Executing SQL Statements¶
The
QSqlQuery
class provides an interface for executing SQL statements and navigating through the result set of a query.The
QSqlQueryModel
andQSqlTableModel
classes described in the next section provide a higher-level interface for accessing databases. If you are unfamiliar with SQL, you might want to skip directly to the next section ( Using the SQL Model Classes ).
Executing a Query¶
To execute an SQL statement, simply create a
QSqlQuery
object and callexec()
like this:query = QSqlQuery() query.exec_("SELECT name, salary FROM employee WHERE salary > 50000")The
QSqlQuery
constructor accepts an optionalQSqlDatabase
object that specifies which database connection to use. In the example above, we don’t specify any connection, so the default connection is used.If an error occurs,
exec()
returnsfalse
. The error is then available aslastError()
.
Inserting, Updating, and Deleting Records¶
QSqlQuery
can execute arbitrary SQL statements, not justSELECT
s. The following example inserts a record into a table usingINSERT
:query = QSqlQuery() query.exec_("INSERT INTO employee (id, name, salary) " "VALUES (1001, 'Thad Beaumont', 65000)")If you want to insert many records at the same time, it is often more efficient to separate the query from the actual values being inserted. This can be done using placeholders. Qt supports two placeholder syntaxes: named binding and positional binding. Here’s an example of named binding:
query = QSqlQuery() query.prepare("INSERT INTO employee (id, name, salary) " "VALUES (:id, :name, :salary)") query.bindValue(":id", 1001) query.bindValue(":name", "Thad Beaumont") query.bindValue(":salary", 65000) query.exec_()Here’s an example of positional binding:
query = QSqlQuery() query.prepare("INSERT INTO employee (id, name, salary) " "VALUES (?, ?, ?)") query.addBindValue(1001) query.addBindValue("Thad Beaumont") query.addBindValue(65000) query.exec_()Both syntaxes work with all database drivers provided by Qt. If the database supports the syntax natively, Qt simply forwards the query to the DBMS; otherwise, Qt simulates the placeholder syntax by preprocessing the query. The actual query that ends up being executed by the DBMS is available as
executedQuery()
.When inserting multiple records, you only need to call
prepare()
once. Then you callbindValue()
oraddBindValue()
followed byexec()
as many times as necessary.Besides performance, one advantage of placeholders is that you can easily specify arbitrary values without having to worry about escaping special characters.
Updating a record is similar to inserting it into a table:
query = QSqlQuery() query.exec_("UPDATE employee SET salary = 70000 WHERE id = 1003")You can also use named or positional binding to associate parameters to actual values.
Finally, here’s an example of a
DELETE
statement:query = QSqlQuery() query.exec_("DELETE FROM employee WHERE id = 1007")
Transactions¶
If the underlying database engine supports transactions,
hasFeature
(Transactions
) will return true. You can usetransaction()
to initiate a transaction, followed by the SQL commands you want to execute within the context of the transaction, and then eithercommit()
orrollback()
. When using transactions you must start the transaction before you create your query.Example:
QSqlDatabase.database().transaction() query = QSqlQuery() query.exec_("SELECT id FROM employee WHERE name = 'Torild Halvorsen'") if query.next(): employeeId = query.value(0) query.exec_("INSERT INTO project (id, name, ownerid) " "VALUES (201, 'Manhattan Project', " + str(employeeId) + ')') QSqlDatabase.database().commit()Transactions can be used to ensure that a complex operation is atomic (for example, looking up a foreign key and creating a record), or to provide a means of canceling a complex change in the middle.
© 2022 The Qt Company Ltd. Documentation contributions included herein are the copyrights of their respective owners. The documentation provided herein is licensed under the terms of the GNU Free Documentation License version 1.3 as published by the Free Software Foundation. Qt and respective logos are trademarks of The Qt Company Ltd. in Finland and/or other countries worldwide. All other trademarks are property of their respective owners.