Build a PostgreSQL GUI App in Python (Part 1)

Introduction to creating a PostgreSQL GUI app in Python

The Pygame library distribution for Python is essentially a wrapper for the SDL (Simple Directmedia Layer) written in the C and C++ programming languages. It’s an easy and simple way to create games, and other GUI applications, for Window, macOS, and Linux operating systems.

This two-part article series will demonstrate how you can use the psycopg2 Python adapter for PostgreSQL to make a simple query GUI desktop application, with Pygame, using only 300 lines of code. This article will provide example code that can assist you in setting up the Pygame application, and how you can create a few Psycopg2 Python functions that will display the PostgreSQL database’s table data on the Pygame surface.

Prerequisites to creating a PostgreSQL GUI application using Pygame

• You’ll need to have Python 3 and the PIP3 package manager for Python installed on your machine before you can install the pygame and psycopg2 Python libraries. Use the python3 -V and pip3 -V commands in a terminal or command prompt window to check if Python 3 and PIP are installed by having them return their respective version numbers.

Install the Pygame library using Python’s PIP3 package manager

• Use the pip3 command to install the package libraries for the code in these articles. Here’s how to install Pygame for Python 3:

• If you’d like to upgrade your installation of Pygame (this code has been tested in Pygame version 1.9.6) then just use the --upgrade flag:

Install the psycopg2 Python adapter for PostgreSQL

The last Python package to install for the PostgreSQL application is the psycopg2 adapter Python distribution for PostgreSQL. Here’s how you can do this with PIP3 for Python:

Create a project folder for the PostgreSQL GUI application

Open a terminal or command prompt in your project directory (or just use the mkdir command to create a directory. Once since you should make a new file for the Python script (using the .py file extension) and open in it in your IDE or text editor of choice. Ideally you should use an editor that has support for Python syntax and indentation to prevent errors.

Import the necessary Python libraries for Pygame and PostgreSQL

Make sure to import all of the libraries necessary for the Pygame/PostgreSQL GUI application at the beginning of the script. Use the following code to import them:

At this point, if you save and run the script, you should see the following response from Pygame:

Setup globals for the PostgreSQL connection parameters

The first bit of actually code for the application will be to setup some globals for the PostgreSQL connection:

It’s possible to just have the user type the username and password parameters into the application itself, but, for the sake of brevity, we’ll just hard-code them into the application for now.

Declare a Python class for the Pygame application’s buttons

We’ll use a Python class constructor for the Pygame buttons and labels to encapsulate and organize all of the necessary attributes and methods. We’ll also use a registry list object, in the class itself, to store all of the Button objects so that we can iterate over the list later.

Declare the Python class and its attributes

The first part of the Button() class code will be to declare the class and its main attributes:

NOTE: Pygame supports colors with an alpha (transparent) channel, so the 4-element tuple values above represent the RGBA color values (1-255).

The class’s selected attribute is for storing a button class attribute that has been clicked on by the user.

Create a constructor for the button methods and attributes

Now we can create an __init__ constructor for the class button attributes. Make sure to append each attribute to the registry, and to set the name, loc, color, and text attributes like in the following code:

Have each Button() instance inherit the Pygame methods

Concatenate a string that will represent the text that the button will display inside of the Pygame font.render() method. This will make it so that each button instance will inherit the Pygame method, and do the same for the font.get_rect() method as well:

NOTE: Each button instance will display the button’s name and the text value (PostgreSQL database and table parameter) assigned to it.

Define a Python function that will connect to PostgreSQL

Now that we have a Button() class constructor, let’s make a function that will attempt to connect to PostgreSQL using the psycopg2 library’s connect() method:

NOTE: If PostgreSQL is not running, or if the database, username, and password parameters aren’t set correctly, then psycopg2 will return an error, and the connection (conn) object will be set to None.

Create a function that will return records from a PostgreSQL table

The next PostgreSQL function will accept the connection object (returned by the previously defined function above) and attempt to fetch records from a table name specified by the application user.

Here’s how you can create a cursor object and then use the psycopg2 adapter’s sql.SQL() method to concatenate a SQL statement string:

NOTE: Because of the obvious screen real estate limitations imposed by the GUI application, we’ll use an SQL limit clause (LIMIT 20) to restrict the number of records returned by the psycopg2 API call to a maximum of 20 rows.

Use a try-except indentation block to catch errors while executing PostgreSQL statements

Once you’ve concatenated an sql.SQL object using the PostgreSQL table name you can pass it to the cursor’s execute() method to store the records in the cursor object itself:

Fetch the PostgreSQL records from the psycopg2 cursor object

Call the cursor’s fetchall() method to return the 20 rows of records from the cursor in the form of a tuple list and close the cursor in order to free up the resources:

NOTE: Just like the connect_postgres() function defined earlier, this one will set the returned object’s value to None in the case of any exceptions. window

Initialize the Pygame application

The last step is to initialize all of the imported Pygame modules using the pygame.init() method call. Here’s how you can do that and also set a caption (or title) for the Pygame window:

Use Pygame to return the native screen resolution

The following code calls the pygame.display.Info() method to access its current_w and current_h attributes in order to calculate the height and width of the Pygame surface based on the system’s current screen resolution:

Declare a new Pygame surface for the PostgreSQL application

The following code creates a Pygame screen (or pygame.Surface object) for the application window by passing a width and height tuple object consisting of integers (e.g. (1280, 72)) to the display.set_mode() method:

NOTE: The HWSURFACE | DOUBLEBUF| RESIZABLE values are optional. Include the RESIZABLE parameter only if you want the user to be able to resize the Pygame window.

Conclusion

Test the Python script in a terminal or command prompt window using the python3 command to execute it:

You should see the Pygame application window briefly display for a second and then disappear again.

In the next part of this series we will create a Pygame loop for the application so that the window stays open, and we’ll add the logic for the PostgreSQL function so that Pygame will display the table rows, returned by the psycopg2 adapter, in the window surface.

Just the Code