<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd">
<html><head><title>QtSql Module</title><style>h3.fn,span.fn { margin-left: 1cm; text-indent: -1cm }
a:link { color: #004faf; text-decoration: none }
a:visited { color: #672967; text-decoration: none }
td.postheader { font-family: sans-serif }
tr.address { font-family: sans-serif }
body { background: #ffffff; color: black; }
</style></head><body><table border="0" cellpadding="0" cellspacing="0" width="100%"><tr /><td align="left" valign="top" width="32"><img align="left" border="0" height="32" src="images/rb-logo.png" width="32" /></td><td width="1">  </td><td class="postheader" valign="center"><a href="index.html"><font color="#004faf">Home</font></a> · <a href="classes.html"><font color="#004faf">All Classes</font></a> · <a href="modules.html"><font color="#004faf">Modules</font></a></td></table><h1 align="center">QtSql Module<br /></h1><p>The QtSql module helps you provide seamless database integration to your PyQt applications. <a href="#details">More...</a></p><h3>Types</h3><ul><li><div class="fn" />class <b><a href="qsql.html">QSql</a></b></li><li><div class="fn" />class <b><a href="qsqldatabase.html">QSqlDatabase</a></b></li><li><div class="fn" />class <b><a href="qsqldriver.html">QSqlDriver</a></b></li><li><div class="fn" />class <b><a href="qsqldrivercreatorbase.html">QSqlDriverCreatorBase</a></b></li><li><div class="fn" />class <b><a href="qsqlerror.html">QSqlError</a></b></li><li><div class="fn" />class <b><a href="qsqlfield.html">QSqlField</a></b></li><li><div class="fn" />class <b><a href="qsqlindex.html">QSqlIndex</a></b></li><li><div class="fn" />class <b><a href="qsqlquery.html">QSqlQuery</a></b></li><li><div class="fn" />class <b><a href="qsqlquerymodel.html">QSqlQueryModel</a></b></li><li><div class="fn" />class <b><a href="qsqlrecord.html">QSqlRecord</a></b></li><li><div class="fn" />class <b><a href="qsqlrelation.html">QSqlRelation</a></b></li><li><div class="fn" />class <b><a href="qsqlrelationaldelegate.html">QSqlRelationalDelegate</a></b></li><li><div class="fn" />class <b><a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a></b></li><li><div class="fn" />class <b><a href="qsqlresult.html">QSqlResult</a></b></li><li><div class="fn" />class <b><a href="qsqltablemodel.html">QSqlTableModel</a></b></li></ul><a name="details" /><hr /><h2>Detailed Description</h2><p>The QtSql module helps you provide seamless database integration to your
PyQt applications.</p>
<p>The SQL classes are divided into three layers:</p>
<p><table align="center" border="0" cellpadding="2" cellspacing="1">
<tr bgcolor="#a2c511" valign="top"><th>Layer</th><th>Description</th></tr>
<tr bgcolor="#f0f0f0" valign="top"><td><b>Driver Layer</b></td><td>This comprises the classes <a href="qsqldriver.html">QSqlDriver</a>, <a href="qsqldrivercreatorbase.html">QSqlDriverCreatorBase</a>, and <a href="qsqlresult.html">QSqlResult</a>. This layer provides the low-level bridge between the specific databases and the SQL API layer. See <a href="sql-driver.html">SQL Database Drivers</a> for more information.</td></tr>
<tr bgcolor="#e0e0e0" valign="top"><td><b>SQL API Layer</b></td><td>These classes provide access to databases. Connections are made using the <a href="qsqldatabase.html">QSqlDatabase</a> class. Database interaction is achieved by using the <a href="qsqlquery.html">QSqlQuery</a> class. In addition to <a href="qsqldatabase.html">QSqlDatabase</a> and <a href="qsqlquery.html">QSqlQuery</a>, the SQL API layer is supported by <a href="qsqlerror.html">QSqlError</a>, <a href="qsqlfield.html">QSqlField</a>, <a href="qsqlindex.html">QSqlIndex</a>, and <a href="qsqlrecord.html">QSqlRecord</a>.</td></tr>
<tr bgcolor="#f0f0f0" valign="top"><td><b>User Interface Layer</b></td><td>These classes link the data from a database to data-aware widgets. They include <a href="qsqlquerymodel.html">QSqlQueryModel</a>, <a href="qsqltablemodel.html">QSqlTableModel</a>, and <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a>. These classes are designed to work with Qt's model/view framework.</td></tr>
</table></p>
<p>To import the module use, for example, the following statement:</p>
<pre>from PyQt4 import QtSql</pre>
<p>This overview assumes that you have at least a basic knowledge of SQL. You should be able to understand simple <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt>, and <tt>DELETE</tt> statements. Although the <a href="qsqltablemodel.html">QSqlTableModel</a> class provides an interface to database browsing and editing that does not require a knowledge of SQL, a basic understanding of SQL is highly recommended. A standard text covering SQL databases is <i>An Introduction to Database Systems</i> (7th Ed.) by C. J. Date, ISBN 0201385902.</p>
<p>Topics:</p>
<ul><li><a href="#connecting-to-databases">Connecting to Databases</a></li>
<li><a href="#executing-sql-statements">Executing SQL Statements</a></li>
<ul><li><a href="#executing-a-query">Executing a Query</a></li>
<li><a href="#navigating-the-result-set">Navigating the Result Set</a></li>
<li><a href="#inserting-updating-and-deleting-records">Inserting, Updating, and Deleting Records</a></li>
<li><a href="#transactions">Transactions</a></li>
</ul>
<li><a href="#using-the-sql-model-classes">Using the SQL Model Classes</a></li>
<ul><li><a href="#the-sql-query-model">The SQL Query Model</a></li>
<li><a href="#the-sql-table-model">The SQL Table Model</a></li>
<li><a href="#the-sql-relational-table-model">The SQL Relational Table Model</a></li>
</ul>
<li><a href="#presenting-data-in-a-table-view">Presenting Data in a Table View</a></li>
</ul>
<a name="connecting-to-databases" />
<h3>Connecting to Databases</h3>
<p>Before you can use <a href="qsqlquery.html">QSqlQuery</a> or <a href="qsqlquerymodel.html">QSqlQueryModel</a> to access a database, you must create at least one database connection.</p>
<p>Database connections are identified by arbitrary strings. <a href="qsqldatabase.html">QSqlDatabase</a> also supports the concept of a <i>default connection</i>, which is used by the Qt SQL class if no other connection is specified. This mechanism is very convenient for applications that use only one database connection.</p>
<p>The following code establishes a connection to a MySQL database called <tt>flightdb</tt> on host <tt>bigblue</tt>:</p>
<pre>  QSqlDatabase db = QSqlDatabase.addDatabase("QMYSQL");
db.setHostName("bigblue");
db.setDatabaseName("flightdb");
db.setUserName("acarlson");
db.setPassword("1uTbSbAs");
bool ok = db.open();</pre>
<p>The first argument to <a href="qsqldatabase.html#addDatabase">QSqlDatabase.addDatabase</a>() is the name of the driver. See the <a href="qsqldatabase.html#addDatabase">addDatabase()</a> documentation for a list of drivers. We call <a href="qsqldatabase.html#setHostName">setHostName()</a>, <a href="qsqldatabase.html#setDatabaseName">setDatabaseName()</a>, <a href="qsqldatabase.html#setUserName">setUserName()</a>, and <a href="qsqldatabase.html#setPassword">setPassword()</a> to initialize the connection information.</p>
<p>Since no name was specified for the connection, the connection serves as the default connection. To specify a name, pass it as the second argument to <a href="qsqldatabase.html#addDatabase">QSqlDatabase.addDatabase</a>(). For example:</p>
<pre>  QSqlDatabase firstDB = QSqlDatabase.addDatabase("QMYSQL", "first");
QSqlDatabase secondDB = QSqlDatabase.addDatabase("QMYSQL", "second");</pre>
<p>Once the connection is initialized, we must call <a href="qsqldatabase.html#open">QSqlDatabase.open</a>() to open the database and give us access to the data. If this call fails it will return false; error information can be obtained from <a href="qsqldatabase.html#lastError">QSqlDatabase.lastError</a>().</p>
<p>Once a connection is established, we can call the static function <a href="qsqldatabase.html#database">QSqlDatabase.database</a>() from anywhere to get a pointer to a database connection. If we call it without a parameter, it will return the default connection. If called with the identifier used for a connection, it will return a reference to the specified connection. For example:</p>
<pre>  QSqlDatabase defaultDB = QSqlDatabase.database();
QSqlDatabase firstDB = QSqlDatabase.database("first");
QSqlDatabase secondDB = QSqlDatabase.database("second");</pre>
<p>To remove a database connection, first close the database using <a href="qsqldatabase.html#close">QSqlDatabase.close</a>(), then remove it using the static method <a href="qsqldatabase.html#removeDatabase">QSqlDatabase.removeDatabase</a>().</p>
<a name="executing-sql-statements" />
<h3>Executing SQL Statements</h3>
<p>The <a href="qsqlquery.html">QSqlQuery</a> class provides an interface for executing SQL statements and navigating through the result set of a query.</p>
<p>The <a href="qsqlquerymodel.html">QSqlQueryModel</a> and <a href="qsqltablemodel.html">QSqlTableModel</a> 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 (<a href="#using-the-sql-model-classes">Using the SQL Model Classes</a>).</p>
<a name="executing-a-query" />
<h4>Executing a Query</h4>
<p>To execute an SQL statement, simply create a <a href="qsqlquery.html">QSqlQuery</a> object and call <a href="qsqlquery.html#exec">QSqlQuery.exec</a>() like this:</p>
<pre>  QSqlQuery query;
query.exec("SELECT name, salary FROM employee WHERE salary > 50000");</pre>
<p>The <a href="qsqlquery.html">QSqlQuery</a> constructor accepts an optional <a href="qsqldatabase.html">QSqlDatabase</a> object that specifies which database connection to use. In the example above, we don't specify any connection, so the default connection is used.</p>
<p>If an error occurs, <a href="qsqlquery.html#exec">exec()</a> returns false. The error is then available as <a href="qsqlquery.html#lastError">QSqlQuery.lastError</a>().</p>
<a name="navigating-the-result-set" />
<h4>Navigating the Result Set</h4>
<p><a href="qsqlquery.html">QSqlQuery</a> provides access to the result set one record at a time. After the call to <a href="qsqlquery.html#exec">exec()</a>, <a href="qsqlquery.html">QSqlQuery</a>'s internal pointer is located one position <i>before</i> the first record. We must call <a href="qsqlquery.html#next">QSqlQuery.next</a>() once to advance to the first record, then <a href="qsqlquery.html#next">next()</a> again repeatedly to access the other records, until it returns false. Here's a typical loop that iterates over all the records in order:</p>
<pre>  while (query.next()) {
QString name = query.value(0).toString();
int salary = query.value(1).toInt();
qDebug() << name << salary;
}</pre>
<p>The <a href="qsqlquery.html#value">QSqlQuery.value</a>() function returns the value of a field in the current record. Fields are specified as zero-based indexes. <a href="qsqlquery.html#value">QSqlQuery.value</a>() returns a <a href="qvariant.html">QVariant</a>, a type that can hold various C++ and core Qt data types such as <tt>int</tt>, <a href="qstring.html">QString</a>, and <a href="qbytearray.html">QByteArray</a>. The different database types are automatically mapped into the closest Qt equivalent. In the code snippet, we call <a href="qvariant.html#toString">QVariant.toString</a>() and <a href="qvariant.html#toInt">QVariant.toInt</a>() to convert variants to <a href="qstring.html">QString</a> and <tt>int</tt>.</p>
<p>You can iterate back and forth using <a href="qsqlquery.html#next">QSqlQuery.next</a>(), <a href="qsqlquery.html#previous">QSqlQuery.previous</a>(), <a href="qsqlquery.html#first">QSqlQuery.first</a>(), <a href="qsqlquery.html#last">QSqlQuery.last</a>(), and <a href="qsqlquery.html#seek">QSqlQuery.seek</a>(). The current row index is returned by <a href="qsqlquery.html#at">QSqlQuery.at</a>(), and the total number of rows in the result set is avaliable as <a href="qsqlquery.html#size">QSqlQuery.size</a>() for databases that support it.</p>
<p>To determine whether a database driver supports a given feature, use <a href="qsqldriver.html#hasFeature">QSqlDriver.hasFeature</a>(). In the following example, we call <a href="qsqlquery.html#size">QSqlQuery.size</a>() to determine the size of a result set of the underlying database supports that feature; otherwise, we navigate to the last record and use the query's position to tell us how many records there are.</p>
<pre>  QSqlQuery query;
int numRows;
query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
QSqlDatabase defaultDB = QSqlDatabase.database();
if (defaultDB.driver()->hasFeature(QSqlDriver.QuerySize)) {
numRows = query.size();
} else {
// this can be very slow
query.last();
numRows = query.at() + 1;
}</pre>
<p>If you iterate through a result set only using next() and seek() with positive values, you can call QSqlQuery.setForwardOnly(true) before calling exec(). This is an easy optimization that will speed up the query significantly when operating on large result sets.</p>
<a name="inserting-updating-and-deleting-records" />
<h4>Inserting, Updating, and Deleting Records</h4>
<p><a href="qsqlquery.html">QSqlQuery</a> can execute arbitrary SQL statements, not just <tt>SELECT</tt>s. The following example inserts a record into a table using <tt>INSERT</tt>:</p>
<pre>  QSqlQuery query;
query.exec("INSERT INTO employee (id, name, salary) "
"VALUES (1001, 'Thad Beaumont', 65000)");</pre>
<p>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:</p>
<pre>  QSqlQuery query;
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();</pre>
<p>Here's an example of positional binding:</p>
<pre>  QSqlQuery query;
query.prepare("INSERT INTO employee (id, name, salary) "
"VALUES (?, ?, ?)");
query.addBindValue(1001);
query.addBindValue("Thad Beaumont");
query.addBindValue(65000);
query.exec();</pre>
<p>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 <a href="qsqlquery.html#executedQuery">QSqlQuery.executedQuery</a>().</p>
<p>When inserting multiple records, you only need to call <a href="qsqlquery.html#prepare">QSqlQuery.prepare</a>() once. Then you call <a href="qsqlquery.html#bindValue">bindValue()</a> or <a href="qsqlquery.html#addBindValue">addBindValue()</a> followed by <a href="qsqlquery.html#exec">exec()</a> as many times as necessary.</p>
<p>Besides performance, one advantage of placeholders is that you can easily specify arbitrary values without having to worry about escaping special characters.</p>
<p>Updating a record is similar to inserting it into a table:</p>
<pre>  QSqlQuery query;
query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");</pre>
<p>You can also use named or positional binding to associate parameters to actual values.</p>
<p>Finally, here's an example of a <tt>DELETE</tt> statement:</p>
<pre>  QSqlQuery query;
query.exec("DELETE FROM employee WHERE id = 1007");</pre>
<a name="transactions" />
<h4>Transactions</h4>
<p>If the underlying database engine supports transactions, QSqlDriver.hasFeature(<a href="qsqldriver.html#DriverFeature-enum">QSqlDriver.Transactions</a>) will return true. You can use <a href="qsqldatabase.html#transaction">QSqlDatabase.transaction</a>() to initiate a transaction, followed by the SQL commands you want to execute within the context of the transaction, and then either <a href="qsqldatabase.html#commit">QSqlDatabase.commit</a>() or <a href="qsqldatabase.html#rollback">QSqlDatabase.rollback</a>().</p>
<p>Example:</p>
<pre>  QSqlDatabase.database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
if (query.next()) {
int employeeId = query.value(0).toInt();
query.exec("INSERT INTO project (id, name, ownerid) "
"VALUES (201, 'Manhattan Project', "
+ QString.number(employeeId) + ")");
}
QSqlDatabase.database().commit();</pre>
<p>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.</p>
<a name="using-the-sql-model-classes" />
<h3>Using the SQL Model Classes</h3>
<p>In addition to <a href="qsqlquery.html">QSqlQuery</a>, Qt offers three higher-level classes for accessing databases. These classes are <a href="qsqlquerymodel.html">QSqlQueryModel</a>, <a href="qsqltablemodel.html">QSqlTableModel</a>, and <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a>.</p>
<p><table align="center" border="0" cellpadding="2" cellspacing="1">
<tr bgcolor="#f0f0f0" valign="top"><td><a href="qsqlquerymodel.html">QSqlQueryModel</a></td><td>A read-only model based on an arbitrary SQL query.</td></tr>
<tr bgcolor="#e0e0e0" valign="top"><td><a href="qsqltablemodel.html">QSqlTableModel</a></td><td>A read-write model that works on a single table.</td></tr>
<tr bgcolor="#f0f0f0" valign="top"><td><a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a></td><td>A <a href="qsqltablemodel.html">QSqlTableModel</a> subclass with foreign key support.</td></tr>
</table></p>
<p>These classes derive from <a href="qabstracttablemodel.html">QAbstractTableModel</a> (which in turn inherits from <a href="qabstractitemmodel.html">QAbstractItemModel</a>) and make it easy to present data from a database in an item view class such as <a href="qlistview.html">QListView</a> and <a href="qtableview.html">QTableView</a>. This is explained in detail in the <a href="#presenting-data-in-a-table-view">Presenting Data in a Table View</a> section.</p>
<p>Another advantage of using these classes is that it can make your code easier to adapt to other data sources. For example, if you use <a href="qsqltablemodel.html">QSqlTableModel</a> and later decide to use XML files to store data instead of a database, it is essentially just a matter of replacing one data model with another.</p>
<a name="the-sql-query-model" />
<h4>The SQL Query Model</h4>
<p><a href="qsqlquerymodel.html">QSqlQueryModel</a> offers a read-only model based on an SQL query.</p>
<p>Example:</p>
<pre>  QSqlQueryModel model;
model.setQuery("SELECT * FROM employee");
for (int i = 0; i < model.rowCount(); ++i) {
int id = model.record(i).value("id").toInt();
QString name = model.record(i).value("name").toString();
qDebug() << id << name;
}</pre>
<p>After setting the query using <a href="qsqlquerymodel.html#setQuery">QSqlQueryModel.setQuery</a>(), you can use QSqlQueryModel.record(int) to access the individual records. You can also use <a href="qsqlquerymodel.html#data">QSqlQueryModel.data</a>() and any of the other functions inherited from <a href="qabstractitemmodel.html">QAbstractItemModel</a>.</p>
<p>There's also a <a href="qsqlquerymodel.html#setQuery">setQuery()</a> overload that takes a <a href="qsqlquery.html">QSqlQuery</a> object and operates on its result set. This enables you to use any features of <a href="qsqlquery.html">QSqlQuery</a> to set up the query (e.g., prepared queries).</p>
<a name="the-sql-table-model" />
<h4>The SQL Table Model</h4>
<p><a href="qsqltablemodel.html">QSqlTableModel</a> offers a read-write model that works on a single SQL table at a time.</p>
<p>Example:</p>
<pre>  QSqlTableModel model;
model.setTable("employee");
model.setFilter("salary > 50000");
model.setSort(2, Qt.DescendingOrder);
model.select();
for (int i = 0; i < model.rowCount(); ++i) {
QString name = model.record(i).value("name").toString();
int salary = model.record(i).value("salary").toInt();
qDebug() << name << salary;
}</pre>
<p><a href="qsqltablemodel.html">QSqlTableModel</a> is a high-level alternative to <a href="qsqlquery.html">QSqlQuery</a> for navigating and modifying individual SQL tables. It typically results in less code and requires no knowledge of SQL syntax.</p>
<p>Use <a href="qsqlquerymodel.html#record">QSqlTableModel.record</a>() to retrieve a row in the table, and <a href="qsqltablemodel.html#setRecord">QSqlTableModel.setRecord</a>() to modify the row. For example, the following code will increase every employee's salary by 10 per cent:</p>
<pre>  for (int i = 0; i < model.rowCount(); ++i) {
QSqlRecord record = model.record(i);
double salary = record.value("salary").toInt();
salary *= 1.1;
record.setValue("salary", salary);
model.setRecord(i, record);
}
model.submitAll();</pre>
<p>You can also use <a href="qabstractitemmodel.html#data">QSqlTableModel.data</a>() and <a href="qsqltablemodel.html#setData">QSqlTableModel.setData</a>(), which are inherited from <a href="qabstractitemmodel.html">QAbstractItemModel</a>, to access the data. For example, here's how to update a record using <a href="qsqltablemodel.html#setData">setData()</a>:</p>
<pre>  model.setData(model.index(row, column), 75000);
model.submitAll();</pre>
<p>Here's how to insert a row and populate it:</p>
<pre>  model.insertRows(row, 1);
model.setData(model.index(row, 0), 1013);
model.setData(model.index(row, 1), "Peter Gordon");
model.setData(model.index(row, 2), 68500);
model.submitAll();</pre>
<p>Here's how to delete five consecutive rows:</p>
<pre>  model.removeRows(row, 5);
model.submitAll();</pre>
<p>The first argument to <a href="qsqltablemodel.html#removeRows">QSqlTableModel.removeRows</a>() is the index of the first row to delete.</p>
<p>When you're finished changing a record, you should always call <a href="qsqltablemodel.html#submitAll">QSqlTableModel.submitAll</a>() to ensure that the changes are written to the database.</p>
<p>When and whether you actually <i>need</i> to call submitAll() depends on the table's <a href="qsqltablemodel.html#editStrategy">edit strategy</a>. The default strategy is <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel.OnRowChange</a>, which specifies that pending changes are applied to the database when the user selects a different row. Other strategies are <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel.OnManualSubmit</a> (where all changes are cached in the model until you call submitAll()) and <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel.OnFieldChange</a> (where no changes are cached). These are mostly useful when <a href="qsqltablemodel.html">QSqlTableModel</a> is used with a view.</p>
<p><a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel.OnFieldChange</a> seems to deliver the promise that you never need to call submitAll() explicitly. There are two pitfalls, though:</p>
<ul>
<li>Without any caching, performance may drop significantly.</li>
<li>If you modify a primary key, the record might slip through your fingers while you are trying to populate it.</li>
</ul>
<a name="the-sql-relational-table-model" />
<h4>The SQL Relational Table Model</h4>
<p><a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> extends <a href="qsqltablemodel.html">QSqlTableModel</a> to provide support for foreign keys. A foreign key is a 1-to-1 mapping between a field in one table and the primary key field of another table. For example, if a <tt>book</tt> table has a field called <tt>authorid</tt> that refers to the author table's <tt>id</tt> field, we say that <tt>authorid</tt> is a foreign key.</p>
<p><table align="center" border="0" cellpadding="2" cellspacing="1">
<tr bgcolor="#f0f0f0" valign="top"><td><img src="images/noforeignkeys.png" /></td><td><img src="images/foreignkeys.png" /></td></tr>
</table></p>
<p>The screenshot on the left shows a plain <a href="qsqltablemodel.html">QSqlTableModel</a> in a <a href="qtableview.html">QTableView</a>. Foreign keys (<tt>city</tt> and <tt>country</tt>) aren't resolved to human-readable values. The screenshot on the right shows a <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a>, with foreign keys resolved into human-readable text strings.</p>
<p>The following code snippet shows how the <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> was set up:</p>
<pre>  model->setTable("employee");
model->setRelation(2, QSqlRelation("city", "id", "name"));
model->setRelation(3, QSqlRelation("country", "id", "name"));</pre>
<p>See the <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> documentation for details.</p>
<a name="presenting-data-in-a-table-view" />
<h3>Presenting Data in a Table View</h3>
<p>The <a href="qsqlquerymodel.html">QSqlQueryModel</a>, <a href="qsqltablemodel.html">QSqlTableModel</a>, and <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> classes can be used as a data source for Qt's view classes such as <a href="qlistview.html">QListView</a>, <a href="qtableview.html">QTableView</a>, and <a href="qtreeview.html">QTreeView</a>. In practice, <a href="qtableview.html">QTableView</a> is by far the most common choice, because an SQL result set is essentially a two-dimensional data structure.</p>
<p align="center"><img alt="A table view displaying a QSqlTableModel" src="images/relationaltable.png" /></p><p>The following example creates a view based on an SQL data model:</p>
<pre>  QTableView *view = new QTableView;
view->setModel(model);
view->show();</pre>
<p>If the model is a read-write model (e.g., <a href="qsqltablemodel.html">QSqlTableModel</a>), the view lets the user edit the fields. You can disable this by calling</p>
<pre>  view->setEditTriggers(QAbstractItemView.NoEditTriggers);</pre>
<p>You can use the same model as a data source for multiple views. If the user edits the model through one of the views, the other views will reflect the changes immediately. The <a href="sql-tablemodel.html">Table Model</a> example shows how it works.</p>
<p>View classes display a header at the top to label the columns. To change the header texts, call <a href="qabstractitemmodel.html#setHeaderData">setHeaderData()</a> on the model. The header's labels default to the table's field names. For example:</p>
<pre>  model->setHeaderData(0, Qt.Horizontal, QObject.tr("ID"));
model->setHeaderData(1, Qt.Horizontal, QObject.tr("Name"));
model->setHeaderData(2, Qt.Horizontal, QObject.tr("City"));
model->setHeaderData(3, Qt.Horizontal, QObject.tr("Country"));</pre>
<p><a href="qtableview.html">QTableView</a> also has a vertical header on the left with numbers identifying the rows. If you insert rows programmatically using <a href="qsqltablemodel.html#insertRows">QSqlTableModel.insertRows</a>(), the new rows will be marked with an asterisk (*) until they are submitted using <a href="qsqltablemodel.html#submitAll">submitAll()</a> or automatically when the user moves to another record (assuming the <a href="qsqltablemodel.html#EditStrategy-enum">edit strategy</a> is <a href="qsqltablemodel.html#EditStrategy-enum">QSqlTableModel.OnRowChange</a>).</p>
<p align="center"><img alt="Inserting a row in a model" src="images/insertrowinmodelview.png" /></p><p>Likewise, if you remove rows using <a href="qsqltablemodel.html#removeRows">removeRows()</a>, the rows will be marked with an exclamation mark (!) until the change is submitted.</p>
<p>The items in the view are rendered using a delegate. The default delegate, <a href="qitemdelegate.html">QItemDelegate</a>, handles the most common data types (<tt>int</tt>, <a href="qstring.html">QString</a>, <a href="qimage.html">QImage</a>, etc.). The delegate is also responsible for providing editor widgets (e.g., a combobox) when the user starts editing an item in the view. You can create your own delegates by subclassing <a href="qabstractitemdelegate.html">QAbstractItemDelegate</a> or <a href="qitemdelegate.html">QItemDelegate</a>. See <a href="model-view-programming.html">Model/View Programming</a> for more information.</p>
<p><a href="qsqltablemodel.html">QSqlTableModel</a> is optimized to operate on a single table at a time. If you need a read-write model that operates on an arbitrary result set, you can subclass <a href="qsqlquerymodel.html">QSqlQueryModel</a> and reimplement <a href="qabstractitemmodel.html#flags">flags()</a> and <a href="qabstractitemmodel.html#setData">setData()</a> to make it read-write. The following two functions make fields 1 and 2 of a query model editable:</p>
<pre>  Qt.ItemFlags EditableSqlModel.flags(
const QModelIndex &index) const
{
Qt.ItemFlags flags = QSqlQueryModel.flags(index);
if (index.column() == 1 || index.column() == 2)
flags |= Qt.ItemIsEditable;
return flags;
}
bool EditableSqlModel.setData(const QModelIndex &index, const QVariant &value, int /* role */)
{
if (index.column() < 1 || index.column() > 2)
return false;
QModelIndex primaryKeyIndex = QSqlQueryModel.index(index.row(), 0);
int id = data(primaryKeyIndex).toInt();
clear();
bool ok;
if (index.column() == 1) {
ok = setFirstName(id, value.toString());
} else {
ok = setLastName(id, value.toString());
}
refresh();
return ok;
}</pre>
<p>The setFirstName() helper function is defined as follows:</p>
<pre>  bool EditableSqlModel.setFirstName(int personId, const QString &firstName)
{
QSqlQuery query;
query.prepare("update person set firstname = ? where id = ?");
query.addBindValue(firstName);
query.addBindValue(personId);
return query.exec();
}</pre>
<p>The setLastName() function is similar. See the <a href="sql-querymodel.html">Query Model</a> example for the complete source code.</p>
<p>Subclassing a model makes it possible to customize it in many ways: You can provide tooltips for the items, change the background color, provide calculated values, provide different values for viewing and editing, handle null values specially, and more. See <a href="model-view-programming.html">Model/View Programming</a> as well as the <a href="qabstractitemview.html">QAbstractItemView</a> reference documentation for details.</p>
<p>If all you need is to resolve a foreign key to a more human-friendly string, you can use <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a>. For best results, you should also use <a href="qsqlrelationaldelegate.html">QSqlRelationalDelegate</a>, a delegate that provides combobox editors for editing foreign keys.</p>
<p align="center"><img alt="Editing a foreign key in a relational table" src="images/relationaltable.png" /></p><p>The <a href="sql-relationaltablemodel.html">Relational Table Model</a> example illustrates how to use <a href="qsqlrelationaltablemodel.html">QSqlRelationalTableModel</a> in conjunction with <a href="qsqlrelationaldelegate.html">QSqlRelationalDelegate</a> to provide tables with foreign key support.</p>
<address><hr /><div align="center"><table border="0" cellspacing="0" width="100%"><tr class="address"><td align="left" width="25%">PyQt 4.10.3 for X11</td><td align="center" width="50%">Copyright © <a href="http://www.riverbankcomputing.com">Riverbank Computing Ltd</a> and <a href="http://www.qtsoftware.com">Nokia</a> 2012</td><td align="right" width="25%">Qt 4.8.5</td></tr></table></div></address></body></html>
