Skillversitas

SQL and Databases with PyQt

The QtSql module is Qt's database layer: the QSqlDatabase (the connection), the QSqlQuery (the statement), and the QSqlTableModel (the model that bridges the query result to the model/view framework). The QtSql is driver-based (the QSqlDriver for the SQLite, the MySQL, the PostgreSQL - the QSQLITE driver is the bundled, the no-server), and the integration with the model/view is the point: the QSqlTableModel is the model the QTableView displays, and the edit (the view's) writes back to the database (the submitAll). This chapter covers the driver and the connection (the addDatabase, the open), the QSqlQuery (the execute, the bind variables, the iteration), the QSqlTableModel (the table to the view, the edit and the submitAll), and the query model (the QSqlQueryModel for the custom query).

The Driver and the Connection

from PyQt5.QtSql import QSqlDatabase, QSqlDriver
from PyQt5.QtWidgets import QApplication
import sys
app = QApplication(sys.argv)

# the DRIVERS: the available (the QSQLITE is the bundled,
# the no-server; the QMYSQL / the QPSQL for the server)
print(QSqlDatabase.drivers())   # the list (the QSQLITE)

# the CONNECTION: the addDatabase (the driver, the name)
# the name is the connection's identity (the app can have
# SEVERAL connections, the named)
db = QSqlDatabase.addDatabase("QSQLITE", "main")
db.setDatabaseName("notepad.db")   # the SQLite FILE
# the server db: the setHostName / the setPort / the
# setUserName / the setPassword (the QMYSQL / the QPSQL)

# the OPEN: the connection's establish
if not db.open():
    print("the open failed:", db.lastError().text())
# the db is now the open connection (the queries use it)
# the close: the db.close() (the app's shutdown)

# the connection is the PROCESS's (the QSqlDatabase is a
# pool, the addDatabase returns the existing on the same
# name, the second addDatabase with the name is the same
# object) - the ONE connection the app's queries share

The QtSql is driver-based: the QSqlDatabase.drivers() lists the available drivers (the QSQLITE is the bundled, the no-server file database; the QMYSQL, the QPSQL for the server), and the connection is the QSqlDatabase.addDatabase(driver, name) - the name is the connection's identity (the app can have several, the named), the setDatabaseName (the SQLite file) or the setHostName/setUserName/setPassword (the server), and the open establishes it (the lastError the failure's text). The QSqlDatabase is a pool: the addDatabase with the same name returns the existing connection (the second call is the same object), and the app's queries share the one named connection. The connection is the process's resource: the addDatabase in the main (before the queries), the open checked (the lastError on the fail), and the close on the shutdown - the three that make the connection deliberate, not the default. The driver is the backend (the SQLite the file, the MySQL the server), and the connection is the app's handle to it.

The QSqlQuery: The Statement

from PyQt5.QtSql import QSqlQuery
from PyQt5.QtWidgets import QApplication
import sys
app = QApplication(sys.argv)
# db = the open connection (the previous section)

# the SCHEMA: the createTable (the DDL, the one-time)
schema = QSqlQuery()
schema.exec_(""""
    CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT,
        created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# the INSERT: the bind variable (the :name, the the
# injection-safe - the value is bound, not the string)
q = QSqlQuery()
q.prepare("INSERT INTO notes (title, body) VALUES (:t, :b)")
q.bindVariable(":t", "the first note")
q.bindVariable(":b", "the body text")
if not q.exec_():
    print("the insert failed:", q.lastError().text())

# the SELECT: the iteration (the next, the record, the
# field) - the row-by-row (the the result set)
q = QSqlQuery("SELECT id, title, created FROM notes")
while q.next():
    record = q.record()
    note_id = record.value(0)     # the by column index
    title = q.value(1)            # the by column index
    created = record.value("created")   # the by NAME
    print(note_id, title, created)

# the LAST INSERT ID: the lastInsertId (the AUTOINCREMENT)
# the q.lastInsertIdValue() after the INSERT

The QSqlQuery is the statement: the prepare plus the bindVariable for the parameterized (the :name placeholder, the value bound not the string-formatted - the injection-safe), the exec_ (the execute for the prepared, the exec_ for the direct), and the lastError on the fail. The SELECT is the iteration: the while q.next() advances the result set, the q.value(column) (the index) or the record.value(name) (the name) reads the field, and the record() is the row's QSqlRecord (the fields by the name or the index). The lastInsertIdValue is the AUTOINCREMENT id after the INSERT (the the row's id the app needs). The bind variable is the discipline: the value is bound (the bindVariable), never the string-formatted into the SQL (the "... ' + value + ' ..." is the injection) - the prepare plus the bindVariable is the parameterized query the database driver escapes. The query is the statement, the iteration is the result, and the bind is the safety - the three that cover the QSqlQuery the app uses.

The QSqlTableModel: The View's Model

from PyQt5.QtSql import QSqlTableModel
from PyQt5.QtWidgets import (QTableView, QApplication,
                             QMainWindow, QVBoxLayout,
                             QWidget)
import sys
app = QApplication(sys.argv)
# db = the open connection

# the MODEL: the QSqlTableModel (the the table's model)
model = QSqlTableModel()
model.setTable("notes")     # the table the model shows
model.setEditStrategy(QSqlTableModel.OnManualSubmit)
model.select()   # the the initial fetch (the the rows)

# the VIEW: the QTableView (the the model/view's view)
view = QTableView()
view.setModel(model)
view.horizontalHeader().setStretchLastSection(True)
view.setAlternatingRowColors(True)

# the EDIT: the the view's the cell edit (the the double
# click) - the model holds the EDIT (the the pending),
# and the submitAll WRITES to the database (the the
# UPDATE / the INSERT)
# the model.revertAll() is the the cancel (the the
# pending edit discarded, the the db untouched)
def on_save():
    if not model.submitAll():
        print("the submit failed:", model.lastError().text())
def on_cancel():
    model.revertAll()

# the the header: the model.setHeaderData (the the column
# label) - the the table's the column name -> the label
model.setHeaderData(1, Qt.Horizontal, "Title")

The QSqlTableModel is the bridge from the table to the model/view: the setTable (the table the model shows), the setEditStrategy (the OnManualSubmit - the edit is the pending, the submitAll writes), and the select (the initial fetch). The QTableView displays the model (the setModel), and the edit (the view's double-click) is the model's pending edit - the submitAll writes the pending to the database (the UPDATE, the INSERT), and the revertAll discards it (the cancel, the db untouched). The setHeaderData is the column label (the table's name to the display's). The integration is the point: the QSqlTableModel is the model the QTableView shows, the edit is the view's (the model's pending), and the submitAll is the write-back - the database as the model/view's data, the view the display, and the submitAll the save. The QSqlQueryModel (the the custom query's model) is the the same bridge for the the query that is not the the simple table (the the JOIN, the the computed) - the the setQuery (the the QSqlQuery) is the the model's the data.

The Working Rules

  • The connection is the addDatabase plus the open, checked. The QSqlDatabase.addDatabase(driver, name) (the QSQLITE the bundled), the setDatabaseName (the file) or the server's credentials, and the open with the lastError on the fail - the connection is the process's resource, the main's, and the close the shutdown's.
  • The bind variable is the discipline, not the string format. The prepare plus the bindVariable (the :name) is the parameterized query the driver escapes; the string-formatted value (the "... ' + v + ' ...") is the injection. The exec_ is the execute, the lastError the fail's text.
  • The QSqlTableModel is the table's model, the QSqlQueryModel is the query's. The setTable plus the select for the simple table; the setQuery (the QSqlQuery) for the custom (the JOIN, the computed). Both are the model the QTableView shows - the model/view's bridge to the database.
  • The submitAll is the write-back, the revertAll the cancel. The OnManualSubmit strategy: the view's edit is the model's pending, the submitAll the UPDATE/INSERT to the db (the lastError on the fail), the revertAll the discard. The submitAll is the save the app's button triggers.
  • The schema is the CREATE TABLE IF NOT EXISTS, the one-time. The exec_ of the DDL on the app's start (the idempotent, the IF NOT EXISTS) - the table's existence is the app's precondition, and the lastInsertIdValue is the INSERT's id the app needs.

Summary

The QtSql module is Qt's database layer: the QSqlDatabase (the connection - the addDatabase(driver, name) the QSQLITE bundled, the setDatabaseName the file, the open checked with the lastError), the QSqlQuery (the statement - the prepare plus the bindVariable the parameterized, the exec_ the execute, the while q.next() the iteration, the record.value the field), and the QSqlTableModel (the model - the setTable plus the select, the QTableView the display, the submitAll the write-back, the revertAll the cancel). The QSqlQueryModel is the same bridge for the custom query (the setQuery). The integration with the model/view is the point: the QSqlTableModel is the model the QTableView shows, the edit is the view's (the model's pending under the OnManualSubmit), and the submitAll is the save - the database as the model/view's data, the bind variable as the safety, and the connection as the process's resource the main opens and the shutdown closes.