Most of the applications that we use in our day-to-day life have to deal with some type of stored data that can be used as and when required. Databases enable us to store this data in a structured manner and also provide us with some guidelines on how this data is to be presented to the end users. In short, databases are an organized body of related information that is usually intro records with a common denominator. The software that controls the access to the databases and enables you to create, edit, and maintain them is known as Database Management Systems (DBMS). There are numerous DBMS systems that are available in the market and each of them is used to suit some specific needs.
The DBMS that we use in this chapter to provide examples is SQLite. As the name specifies, SQLite is a light-weight, public-domain, in-process database system that is used for local storage in many application software applications. This chapter assumes that the users are familiar with SQL (Structured Query Language), which is used to access the data, and have some knowledge in model/view programming. The QtSql module of PySide provides a database-independent interface to access SQL databases. So, apart from the initial connection and some other raw SQL syntax, almost all other queries work with any SQL database.
A database connection is represented by the PySide.QtSql.QSqlDatabase class. This class provides an interface to access a database through the connection. The connection is accomplished through Qt's supported database drivers, which are derived from the PySide.QtSql.QSqlDriver class. The QSqlDriver class is an abstract base class to access specific SQL Databases and should be used with QSqlDatabase. As with any other PySide feature that supports customization, it is also possible to create our own SQL drivers by subclassing the QSqlDriver class and reimplementing its virtual functions.
Before explaining the concept of connecting to databases, we will look at the types of database access that is provided by PySide. The access is provided at two levels, a high-level access using QSqlTableModel or QSqlRelationalTableModel, and a low-level access using QSqlQuery. The former one uses model/view architecture and provides a high-level interface from which to read and write database records from a table. The latter one is based on executing and manipulating SQL queries directly on the QSqlDatabase class. This class can accept the Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE, and DELETE, as well as Data Definition Language (DDL) statements, such as CREATE, ALTER, and DROP. It can also be used to execute database-specific commands that are not standard SQL, for example, SET DATESTYLE=ISO for PostgreSQL.
The connection to the database in an application can take place at any point when it is required to. But, in most of the applications it is done after the creation of the QApplication object and before the main form is created or shown. This way, we can inform users about the unsuccessful connection prior and, thus, prevent them from executing database-specific actions that will otherwise crash or cause an exception in our program. A connection is created by calling the static QSqlDatabase.addDatabase() function by giving the name of the database driver that we want to use and, optionally, a connection name. A database connection is identified by the connection name that we specify and not by the database name.
It is also correct to create a connection without a name by making it a default connection. Once a connection object has been created, we can move on to set other necessary attributes, such as the database's name, username, password, host, and port details, and other connect options, if necessary. Once this is completed, we can activate the connection to the database with the help of the QSqlDatabase.open() function call. The following code presents the series of steps that we discussed in creating and activating a connection:
db = QSqlDatabase.addDatabase("QMYSQL")
db.setHostName("testServer")
db.setDatabaseName("sampledb")
db.setUserName("test")
db.setPassword("pass123")
ok = db.open()In the preceding code, the addDatabase() function can be given an optional second parameter, which specifies the connection name that is given, as follows:
db = QSqlDatabase.addDatabase("QMYSQL", "myConn")However, when using SQLite, it is enough to specify only the database name (SQLite is a local storage database implementation and normally does not require a password). Usually, this will normally be a filename, but it can be the special name, :memory:, to specify to use an in-memory database. When the open() call is executed on the connection, the file will be created if it does not exist in prior. So, the code to connect to the SQLite database using the SQLite driver for PySide, QSQLITE
, is as follows:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("test.db")
ok = db.open()This will create a connection to the SQLite test.db database file. We should take care of handling the connection errors by checking the return value of the db.open() function.
Once the connection is established, we can get the list of the tables, its primary index, and meta-information about the table's fields with their specific functions. The QSqlDatabase.removeDatabase() function is called with a connection name to remove a connection.