Handling SQL Databases With PyQt: The Basics

Connecting PyQt to an SQL Database

Connecting an application to a relational database and getting the application to create, read, update, and delete the data stored in that database is a common task in programming. Relational databases are generally organized into a set of tables, or relations. A given row in a table is referred to as a record or tuple, and a column is referred to as an attribute.

Each column stores a specific kind of information, such as a names, dates, or numbers. Each row represents a set of closely related data, and every row has the same general structure. For example, in a database that stores data about the employees in a company, a specific row represents an individual employee.

Most relational database systems use SQL (structured query language) for querying, manipulating, and maintaining the data held in the database. SQL is a declarative and domain-specific programming language specially designed for communicating with databases.

Relational database systems and SQL are widely used nowadays. You’ll find several different database management systems, such as SQLite, PostgreSQL, MySQL, MariaDB, and many others. You can connect Python to any of these database systems using a dedicated Python SQL library.

When it comes to GUI programming with Python and PyQt, PyQt provides a robust set of classes for working with SQL databases. This set of classes will be your best ally when you need to connect your application to an SQL database.

In this tutorial, you’ll learn the basics of how to use PyQt’s SQL support to create GUI applications that reliably interact with relational databases to read, write, delete, and display data.

Creating a Database Connection

Connecting your applications to a physical SQL database is an important step in the process of developing database applications with PyQt. To perform this step successfully, you need some general information about how your database is set up.

For example, you need to know what database management system your database is built on, and you might also need to have a username, a password, a hostname, and so on.

In this tutorial, you’ll use SQLite 3, which is a well-tested database system with support on all platforms and minimal configuration requirements. SQLite allows you to read and write directly to databases in your local disk without the need for a separate server process. That makes it a user-friendly option for learning database application development.

Another advantage of using SQLite is that the library comes shipped with Python and also with PyQt, so you don’t need to install anything else to start working with it.

In PyQt, you can create a database connection by using the QSqlDatabase class. This class represents a connection and provides an interface for accessing the database. To create a connection, just call .addDatabase() on QSqlDatabase. This static method takes an SQL driver and an optional connection name as arguments and returns a database connection:

QSqlDatabase.addDatabase(
    driver, connectionName=QSqlDatabase.defaultConnection
)

The first argument, driver, is a required argument that holds a string containing the name of a PyQt-supported SQL driver. The second argument, connectionName, is an optional argument that holds a string with the name of the connection. connectionName defaults to QSqlDatabase.defaultConnection, which normally holds the string "qt_sql_default_connection".

If you already have a connection called connectionName, then that connection is removed and replaced with a new connection, and .addDatabase() returns the newly added database connection back to the caller.

A call to .addDatabase() adds a database connection to a list of available connections. This list is a global registry that PyQt maintains behind the scenes to keep track of the available connections in an application. Registering your connections with a meaningful connectionName will allow you to manage several connections in a database application.

Once you’ve created a connection, you might need to set several attributes on it. The specific set of attributes will depend on the driver you’re using. In general, you’ll need to set attributes such as the database name, the username, and the password for accessing the database.

Here’s a summary of the setter methods that you can use to set the more commonly used attributes or properties of a database connection:

Note that the password you pass as an argument to .setPassword() is stored in plain text and can be retrieved later by calling .password(). This is a serious security risk that you should avoid introducing in your database applications. You’ll learn a safer approach in the section Opening a Database Connection later in this tutorial.

To create a connection to an SQLite database using QSqlDatabase, open a Python interactive session and type in the following code:

>>> from PyQt5.QtSql import QSqlDatabase

>>> con = QSqlDatabase.addDatabase("QSQLITE")
>>> con.setDatabaseName("contacts.sqlite")

>>> con
<PyQt5.QtSql.QSqlDatabase object at 0x7f0facec0c10>

>>> con.databaseName()
'contacts.sqlite'

>>> con.connectionName()
'qt_sql_default_connection'

This code will create a database connection object using "QSQLITE" as the connection’s driver and "contacts.sqlite" as the connection’s database name. Since you don’t pass a connection name to .addDatabase(), the newly created one becomes your default connection, whose name is "qt_sql_default_connection".

In the case of SQLite databases, the database name is normally a filename or a path that includes the database filename. You can also use the special name ":memory:" for an in-memory database.

Handling Multiple Connections

There may be situations in which you need to use multiple connections to a single database. For example, you might want to log users’ interactions with the database using a specific connection for each user.

In other situations, you might need to connect your application to several databases. For example, you might want to connect to several remote databases in order to collect data to populate or update a local database.

To handle these situations, you can provide specific names for your different connections and reference each connection by its name. If you want to give your database connection a name, then pass that name as the second argument to .addDatabase():

>>> from PyQt5.QtSql import QSqlDatabase

>>> # First connection
>>> con1 = QSqlDatabase.addDatabase("QSQLITE", "con1")
>>> con1.setDatabaseName("contacts.sqlite")

>>> # Second connection
>>> con2 = QSqlDatabase.addDatabase("QSQLITE", "con2")
>>> con2.setDatabaseName("contacts.sqlite")

>>> con1
<PyQt5.QtSql.QSqlDatabase object at 0x7f367f5fbf90>
>>> con2
<PyQt5.QtSql.QSqlDatabase object at 0x7f3686dd7510>

>>> con1.databaseName()
'contacts.sqlite'
>>> con2.databaseName()
'contacts.sqlite'

>>> con1.connectionName()
'con1'
>>> con2.connectionName()
'con2'

Here, you create two different connections to the same database, contacts.sqlite. Each connection has its own connection name. You can use the connection name to get a reference to a specific connection any time later in your code according to your needs. To do this, you can call .database() with a connection name:

>>> from PyQt5.QtSql import QSqlDatabase

>>> db = QSqlDatabase.database("con1", open=False)

>>> db.databaseName()
'contacts.sqlite'
>>> db.connectionName()
'con1'

In this example, you see that .database() takes two arguments:

1. connectionName holds the connection name that you need to use. If you don’t pass a connection name, then the default connection will be used.
2. open holds a Boolean value that tells .database() if you want to automatically open the connection or not. If open is True (the default) and the connection isn’t open, then the connection is opened automatically.

The return value of .database() is a reference to the connection object called connectionName. You can use different connection names to get references to specific connection objects and then use them to manage your database.

Using Different SQL Divers

So far, you’ve learned how to create a database connection using the SQLite driver. This isn’t the only driver available in PyQt. The library provides a rich set of SQL drivers that allow you to use different types of database management systems according to your specific needs:

The Driver Name column holds the identifier strings that you need to pass to .addDatabase() as its first argument to use the associated driver. Unlike with the SQLite driver, when you use a different driver, you might need to set several attributes, such as databaseName, hostName , userName, and password, for the connection to work properly.

Database drivers are derived from QSqlDriver. You can create your own database drivers by subclassing QSqlDriver, but that topic goes beyond the scope of this tutorial. If you’re interested in creating your own database drivers, then check out How to Write Your Own Database Driver for more details.

Opening a Database Connection

Once you have a database connection, you need to open that connection to be able to interact with your database. To do that, you call .open() on the connection object. .open() has the following two variations:

1. .open() opens a database connection using the current connection values.
2. .open(username, password) opens a database connection using the provided username and password.

Both variations return True if the connection is successful. Otherwise, they return False. If the connection can’t be established, then you can call .lastError() to get information about what happened. This function returns information about the last error reported by the database.

Here’s an example of how to open an SQLite database connection using the first variation of .open():

>>> from PyQt5.QtSql import QSqlDatabase

>>> # Create the connection
>>> con = QSqlDatabase.addDatabase("QSQLITE")
>>> con.setDatabaseName("contacts.sqlite")

>>> # Open the connection
>>> con.open()
True
>>> con.isOpen()
True

In the above example, you first create a connection to your SQLite database and open that connection using .open(). Since .open() returns True, the connection is successful. At this point, you can check the connection using .isOpen(), which returns True if the connection is open and False otherwise.

In real-world applications, you need to make sure that you have a valid connection to your database before you try to do any operations on your data. Otherwise, your application can break down and fail. For example, what if you don’t have write permissions for the directory in which you’re trying to create that database file? You need to make sure that you’re handling any error that can occur while opening a connection.

A common way of calling .open() is to wrap it in a conditional statement. This allows you to handle errors that can occur when opening the connection:

>>> import sys
>>> from PyQt5.QtSql import QSqlDatabase

>>> # Create the connection
>>> con = QSqlDatabase.addDatabase("QSQLITE")
>>> con.setDatabaseName("contacts.sqlite")

>>> # Open the connection and handle errors
>>> if not con.open():
...     print("Unable to connect to the database")
...     sys.exit(1)

Wrapping the call to .open() in a conditional statement allows you to handle any error that happens when you open the connection. This way, you can inform your users about any problems before the application runs. Note that the application exits with an exit status of 1, which is customarily used to indicate a program failure.

In the above example, you use .open() in an interactive session, so you use print() to present error messages to the users. However, in GUI applications, instead of using print(), you normally use a QMessageBox object. With QMessageBox, you can create small dialogs to present information to your users.

Here’s a sample GUI application that illustrates a way of handling connection errors:

 1import sys
 2
 3from PyQt5.QtSql import QSqlDatabase
 4from PyQt5.QtWidgets import QApplication, QMessageBox, QLabel
 5
 6# Create the connection
 7con = QSqlDatabase.addDatabase("QSQLITE")
 8con.setDatabaseName("/home/contacts.sqlite")
 9
10# Create the application
11app = QApplication(sys.argv)
12
13# Try to open the connection and handle possible errors
14if not con.open():
15    QMessageBox.critical(
16        None,
17        "App Name - Error!",
18        "Database Error: %s" % con.lastError().databaseText(),
19    )
20    sys.exit(1)
21
22# Create the application's window
23win = QLabel("Connection Successfully Opened!")
24win.setWindowTitle("App Name")
25win.resize(200, 100)
26win.show()
27sys.exit(app.exec_())

The if statement in line 14 checks if the connection was unsuccessful. If the /home/ directory doesn’t exist or if you don’t have permission to write in it, then the call to .open() fails because the database file can’t be created. In this situation, the flow of execution enters the if statement code block and shows a message on the screen.

If you change the path to any other directory in which you can write, then the call to .open() will succeed and you’ll see a window displaying the message Connection Successfully Opened! You’ll also have a new database file called contacts.sqlite in the selected directory.

Note that you pass None as the message’s parent because, at the time of showing the message, you haven’t created a window yet, so you don’t have a viable parent for the message box.