PyQt5 and persistent DB with QtSql
Fairly new to PyQt5 and in setting up a sample application I ran into an issue with QtSql. All of the examples I have found show a single class using the database connection but none show how to use the QtSql classes persistently over many different classes that require a DB connection. Almost all of the MDI subwindows in my application require a connection to the database but when I setup the connection I get errors that the connection is "duplicate". My question is:
If I have an MDI application with several dialogs that require a DB connection, How do I set up the database connection persistently so all my dialogs can access the database?
If you are going to connect to the same database it is not necessary that you create several connections, only one since qt manages those connections globally as indicated by the docs:
QSqlDatabase QSqlDatabase::addDatabase(const QString &type, const QString &connectionName = QLatin1String(defaultConnection))
Adds a database to the list of database connections using the driver type and the connection name connectionName. If there already exists a database connection called connectionName, that connection is removed.
The database connection is referred to by connectionName. The newly added database connection is returned.
If type is not available or could not be loaded, isValid() returns false.
If connectionName is not specified, the new connection becomes the default connection for the application, and subsequent calls to database() without the connection name argument will return the default connection. If a connectionName is provided here, use database(connectionName) to retrieve the connection.
Warning: If you add a connection with the same name as an existing connection, the new connection replaces the old one. If you call this function more than once without specifying connectionName, the default connection will be the one replaced.
Example:
.
├── connection.py
├── main.py
└── views.p
connection.py
from PyQt5 import QtWidgets, QtSql
def createConnection():
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
if not db.open():
QtWidgets.QMessageBox.critical(None, "Cannot open database",
"Unable to establish a database connection.\n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information how "
"to build it.\n\n"
"Click Cancel to exit.", QtWidgets.QMessageBox.Cancel)
return False
query = QtSql.QSqlQuery()
query.exec_("""CREATE TABLE IF NOT EXISTS person (id int primary key,
firstname VARCHAR(20),
lastname VARCHAR(20))""")
query.exec_("insert into person values(101, 'Danny', 'Young')")
query.exec_("insert into person values(102, 'Christine', 'Holand')")
query.exec_("insert into person values(103, 'Lars', 'Gordon')")
query.exec_("insert into person values(104, 'Roberto', 'Robitaille')")
query.exec_("insert into person values(105, 'Maria', 'Papadopoulos')")
query.exec_("""CREATE TABLE IF NOT EXISTS items (id INT primary key,
imagefile INT,
itemtype varchar(20),
description varchar(100))""");
query.exec_("insert into items "
"values(0, 0, 'Qt',"
"'Qt is a full development framework with tools designed to "
"streamline the creation of stunning applications and "
"amazing user interfaces for desktop, embedded and mobile "
"platforms.')");
query.exec_("insert into items "
"values(1, 1, 'Qt Quick',"
"'Qt Quick is a collection of techniques designed to help "
"developers create intuitive, modern-looking, and fluid "
"user interfaces using a CSS & JavaScript like language.')");
query.exec_("insert into items "
"values(2, 2, 'Qt Creator',"
"'Qt Creator is a powerful cross-platform integrated "
"development environment (IDE), including UI design tools "
"and on-device debugging.')");
query.exec_("insert into items "
"values(3, 3, 'Qt Project',"
"'The Qt Project governs the open source development of Qt, "
"allowing anyone wanting to contribute to join the effort "
"through a meritocratic structure of approvers and "
"maintainers.')");
return True
views.py
from PyQt5 import QtWidgets, QtSql
class PersonWidget(QtWidgets.QWidget):
def __init__(self, parent=None):
super(PersonWidget, self).__init__(parent)
lay = QtWidgets.QVBoxLayout(self)
view = QtWidgets.QTableView()
lay.addWidget(view)
model = QtSql.QSqlTableModel(self)
model.setTable("person")
model.select()
view.setModel(model)
class ItemsWidget(QtWidgets.QWidget):
def __init__(self, parent=None):
super(ItemsWidget, self).__init__(parent)
lay = QtWidgets.QVBoxLayout(self)
view = QtWidgets.QTableView()
lay.addWidget(view)
model = QtSql.QSqlTableModel(self)
model.setTable("items")
model.select()
view.setModel(model)
main.py
from PyQt5 import QtWidgets, QtSql
from connection import createConnection
from views import PersonWidget, ItemsWidget
class MainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.mdiarea = QtWidgets.QMdiArea()
self.setCentralWidget(self.mdiarea)
sub1 = QtWidgets.QMdiSubWindow()
sub1.setWidget(PersonWidget())
self.mdiarea.addSubWindow(sub1)
sub1.show()
sub2 = QtWidgets.QMdiSubWindow()
sub2.setWidget(ItemsWidget())
self.mdiarea.addSubWindow(sub2)
sub2.show()
if __name__ == '__main__':
import sys
app = QtWidgets.QApplication(sys.argv)
if not createConnection():
sys.exit(-1)
w = MainWindow()
w.show()
sys.exit(app.exec_())
In the previous example, there is the createConnection() method that establishes the connection and creates the tables if necessary. And the views that use the data of the database without indicating the connection and consequently use the default connection, that is, the one that was established in createConnection().