Build a PostgreSQL GUI App in Python (Part 2)

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 article is the second part of a two-part series demonstrating how one can easily create a PostgreSQL desktop GUI application in Python using just 300 lines of code. We’ll cover the structure of a Pygame loop and the necessary logic for the PostgreSQL application. If you’d like to know more about installing Pygame and psycopg2, and setting up the application, then check out the first part of this article series.

Create a Pygame loop for the PostgreSQL app

Check out the first part of the article series to see how we imported the psycopg2 and Pygame modules. This article will continue where the previous one left off.

Initialize the Pygame modules

You need 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:

# initialize the pygame window
pygame.init()

# change the caption/title for the Pygame app
pygame.display.set_caption("ObjectRocket PostgreSQL App", "ObjectRocket PostgreSQL App")

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:

# get the OS screen/monitor resolution
max_width = pygame.display.Info().current_w
max_height = pygame.display.Info().current_h

Create a Pygame surface for the PostgreSQL app

Use the following code to have Pygame return a pygame.Surface object that can used as canvas to “blit” the PostgreSQL data on:

# create a pygame resizable screen
screen = pygame.display.set_mode(
    (int(max_width*0.55), int(max_height*0.6)),
    HWSURFACE | DOUBLEBUF| RESIZABLE
)

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.

Setup the Pygame font for the PostgreSQL text

The following code calculates the font size using the system’s screen resolution. Make sure to explicitly cast the float dividend as an int() to avoid a Python TypeError:

# calculate an int for the font size
font_size = int(max_width / 100)

Declare a ‘pygame.font.Font’ object for the Pygame surface

In order to display PostgreSQL information on the Pygame surface you’ll have to first create a pygame.font.Font object by passing the font_size integer to Pygame’s font.SysFont() method:

try:
    font = pygame.font.SysFont('Tahoma', font_size)
except Exception as err:
    print ("pygame.font ERROR:", err)
    font = pygame.font.SysFont('Arial', font_size)

NOTE: Pygame does not natively support special Unicode characters. If you’d like Pygame to properly display foreign or special characters you’ll have to download the font file instead of using a built-in system font (SysFont). Use the font.Font() method to load a local font from your project directory.

Create Pygame button instances for the PostgreSQL app functions

Now we can use the Button() class constructor (created in the first part of the series) to instantiate Pygame buttons for the PostgreSQL database and table names. The following code sets their X, Y blit location to (10, 10) and (10, 100) respectively:

# create buttons for PostgreSQL database and table
db_button = Button("Database Name:", (10, 10))
table_button = Button("Table Name:", (10, 100))

# default Postgres connection is 'None'
connection = None

The above code is designed to set the PostgreSQL connection to None by default until a valid connection is made.

Start the Pygame loop for the PostgreSQL app

The following example code uses a while loop to running the Pygame application loop:

# begin the pygame loop
app_running = True
while app_running == True:

Reset the Pygame surface by filling the screen with a color

Pass a color to Pygame’s screen.fill() method to reset the surface, at the beginning of each game loop, and erase all of the remaining “blits” from the previous iteration:

    # reset the screen
    screen.fill( Button.black )

    # set the clock FPS for app
    clock = pygame.time.Clock()

Iterate and evaluate the Pygame events

The next session will show you how to evaluate and process all of the Pygame events. This can include keyboard events as well as mouse movement and clicks.

Use Python’s ‘for’ keyword to loop over Pygame events

The following code iterates over any potential Pygame events at the start of each game loop:

    # iterate over the pygame events
    for event in pygame.event.get():

Pygame event for quitting the PostgreSQL application

The QUIT event is when the user clicks the close button (usually found at the upper right-hand corner of an application). This next bit of code sets the while loop’s app_running Boolean value to False, and quits Pygame and Python if the use clicks on the close-application button:

        # user clicks the quit button on app window
        if event.type == QUIT:
            app_running = False
            pygame.display.quit()
            pygame.quit()
            sys.exit()
            quit()

Keyboard events for the Pygame application

The KEYDOWN event type registers any keyboard presses. We’re going to evaluate if the selected button is not None so that we can introduce some logic in case a user has already clicked on a PostgreSQL button:

        # user presses a key on keyboard
        if event.type == KEYDOWN:

            if Button.selected != None:

                # get the selected button
                b = Button.selected

User presses the ‘Return’ key after inputting PostgreSQL information

If a user has selected a button, then the following code will get any table or database information that has been typed into it:

                # user presses the return key
                if event.key == K_RETURN:

                    table_data = None

                    # check if the selected button is the table name
                    if "table" in b.name.lower():
                        table_name = b.text

                    # check if the selected button is the db name
                    elif "database" in b.name.lower():
                        db_name = b.text

Attempt to connect to PostgreSQL if there’s user input for the database and table name

If the table_name and db_name are set, then call the connect_postgres() function declared in the first article in order to attempt to make a Pygame connection to PostgreSQL using the psycopg2 adapter:

                    # get Postgres connection if table and db have value
                    if table_name != None and db_name != None:
                        connection = connect_postgres( db_name )

                        # get table records if connection is valid
                        if connection != None:
                            table_data = return_records( connection )

                    # reset the button selected
                    Button.selected = None

The selected button value will be reset to None if the user presses Return.

Convert all other keyboard events into a Unicode character

The following else indentation is for all other key presses:

                else:
                    # get the key pressed
                    key_press = pygame.key.get_pressed()

Iterate over the keys to see which ones were pressed

The following iterator will go over each key, in the case of a keyboard event, and evaluate them:

                    # iterate over the keypresses
                    for keys in range(255):
                        if key_press[keys]:
                            if keys == 8: # backspace
                                b.text = b.text[:-1]
                            else:
                                # convert key to unicode string
                                b.text += event.unicode
                                print ("KEYPRESS:", event.unicode)

                # append the button text to button font object
                b.font = font.render(b.name + " " + b.text, True, Button.white, Button.black)

You’ll see that the Button() instance’s font string attribute has a Unicode conversion of the keypress appended to it.

Mouse button events for the Pygame application

The following MOUSEBUTTONDOWN evaluation checks if the user clicked the left mouse button (event.button == 1), and then proceeds to iterate over the Button.registry list of buttons to check if the user has clicked on one of them by retrieving the mouse click event’s location, and evaluating each button, by passing it to the button’s collidepoint() method:

        # check for mouse button down events
        if event.type == MOUSEBUTTONDOWN and event.button == 1:
            print ("\nMOUSE CLICK:", event)

            # iterate over the button registry list
            for b in Button.registry:

                # check if the mouse click collided with button
                if b.rect.collidepoint(event.pos) == 1:
                    # store button object under selected attr
                    Button.selected = b

The above code concludes the Pygame event portion of the code. The next section will deal with the loop’s logic for the Pygame buttons and bliting (rendering) the PostgreSQL table data to the Pygame surface.

Blit the Pygame buttons for the PostgreSQL app

Here’s how you can iterate over the button in each game loop to blit the buttons by passing the surface (window) to Pygame’s draw.rect() method:

    # iterate over the button registry list
    for b in Button.registry:

        # blit the button's font to screen
        screen.blit(b.font, b.rect)

        # check if the button has been clicked by user
        if Button.selected == b:

            # blit an outline around button if selected
            rect_pos = (b.rect.x-5, b.rect.y-5, b.rect.width+10, b.rect.height+10)
            pygame.draw.rect(screen, Button.white, rect_pos, 3) # width 3 pixels

Blit the PostgreSQL information onto the Pygame surface

The following code evaluates the PostgreSQL database and table name variables, and then renders a message to the Pygame surface depending on the status of the psycopg2 connection to PostgreSQL:

    # blit the PostgreSQL information using pygame's font.render() method
    if table_name == None and db_name == None:

        # blit instruction messages
        blit_text = "Type a database and table name into the fields and press 'Return'."
        conn_msg = font.render(blit_text, True, Button.green, Button.black)
        screen.blit(conn_msg, (10, 200))

Display a warning message if the connection to PostgreSQL failed

Here’s how to use an else indentation block to blit messages to the Pygame surface informing the user of the PostgreSQL status:

    else:

        # connection is valid, but table doesn't exist
        if connection != None and table_data == None:
            blit_text = "The PostgreSQL table does not exist on this database."
            color = Button.red

        # connection is invalid
        elif connection == None:
            blit_text = "PostgreSQL connection is invalid."
            color = Button.red

        # connection is valid, and table is found
        elif connection != None:
            blit_text = "PostgreSQL Connection: " + str(connection)
            color = Button.green

        # blit the message to the pygame screen
        conn_msg = font.render(blit_text, True, color, Button.black)
        screen.blit(conn_msg, (10, 200))

Blit the PostgreSQL table rows to the Pygame surface

At the end of the game loop we’ll evaluate the table_data object to see if it contains table data (and that it’s not set to None) in order to enumerate over the table rows so that we can blit their respective data to the Pygame surface:

        # enumerate() the table data if PostgreSQL API call successful
        if table_data != None:

            # enumerate the list of tuple rows
            for num, row in enumerate(table_data):

                # blit the table data to Pygame window
                blit_text = str(row).encode("utf-8", "ignore")
                table_font = font.render(blit_text, True, Button.light_blue, Button.black)
                screen.blit(table_font, (10, 250 + int(num*50)))

End the Pygame loop and display the PostgreSQL information

The last item of code in the Pygame loop is to set the FPS (frames per second) rate for the application window. Since this application is not graphic intensive, and doesn’t even include sprites, it’s not necessary to set it very high—20 FPS should suffice:

    # set the clock FPS for application
    clock.tick(20)

    # use the flip() method to display text on surface
    pygame.display.flip()

Then just call the display.flip() method to render all of the blited Pygame object’s to the surface at the very end of the loop.

Use Pygame’s update() method instead

You can also use the display.update() method instead, like in the following example:

    pygame.display.update()

Conclusion

Use the python3 command to execute the script in a terminal or command prompt window so that you can have all exceptions, errors, and information logged to the window in case the Pygame application crashes.

You should see Python print some information to the terminal or command prompt, and the Pygame window should open, after it initiates, in a second or two. Just type in your PostgreSQL database and table name and press Return, and it should blit the first 20 records of your table onto the Pygame surface.

Rendering PostgreSQL table data in a Pygame Python GUI application

NOTE: As it was stated earlier, Pygame does not have native Unicode support for special characters. Use a custom font if this is an issue.

Pygame window freezes and won’t close

If there’s some sort of error or issue that prevents the Pygame application window from closing use the following bash commands to grep for the Pygame process, and then kill it using the kill -9 command followed by the process PID number:

ps aux | grep pygame
sudo kill -9 {PID_NUM}

Screenshot of a terminal window using grep to kill Pygame using PID

Just the Code

#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import the psycopg2 database adapter for PostgreSQL
from psycopg2 import connect, sql

# for the sys.exit() method call
import sys

# import the Pygame libraries
import pygame
from pygame.locals import *

# set the DB name, table, and table data to 'None'
table_data = None
db_name = None
table_name = None

# change these globals to match your settings
user_name = "objectrocket"
user_pass = "mypass"

# create a class for the buttons and labels
class Button():

    # empty list for button registry
    registry = []

    # selected button (will have outline rect)
    selected = None

    # pygame RGBA colors
    white = (255, 255, 255, 255)
    black = (0, 0, 0, 255)
    red = (255, 0, 0, 255)
    green = (50, 205, 50, 255)
    light_blue = (173, 216, 230, 255)

    # default font color for buttons/labels is white
    def __init__(self, name, loc, color=white):

        # add button to registry
        self.registry.append(self)

        # paramater attributes
        self.name = name
        self.loc = loc
        self.color = color

        # text attr for button
        self.text = ""

        # size of button changes depending on length of text
        self.size = (int(len(self.text)*200), 200)

        # font.render(text, antialias, color, background=None) -> Surface
        self.font = font.render (
            self.name + " " + self.text, # display text
            True, # antialias on
            self.color, # font color
            self.black # background color
        )

        # rect for button
        self.rect = self.font.get_rect()
        self.rect.x = loc[0]
        self.rect.y = loc[1]

# function that connects to Postgres
def connect_postgres(db):

    # connect to PostgreSQL
    print ("\nconnecting to PostgreSQL")
    try:
        conn = connect (
            dbname = db,
            user = user_name,
            host = "localhost",
            password = user_pass
        )
    except Exception as err:
        print ("PostgreSQL Connect() ERROR:", err)
        conn = None

    # return the connection object
    return conn

# function that returns PostgreSQL records
def return_records(conn):

    # instantiate a new cursor object
    cursor = conn.cursor()

    # (use sql.SQL() to prevent SQL injection attack)
    sql_object = sql.SQL(
        # pass SQL statement to sql.SQL() method
        "SELECT * FROM {} LIMIT 20;"
    ).format(
        # pass the identifier to the Identifier() method
        sql.Identifier( table_name )
    )

    try:
        # use the execute() method to put table data into cursor obj
        cursor.execute( sql_object )

        # use the fetchall() method to return a list of all the data
        table_data = cursor.fetchall()

        # close cursor objects to avoid memory leaks
        cursor.close()
    except Exception as err:

        # print psycopg2 error and set table data to None
        print ("PostgreSQL psycopg2 cursor.execute() ERROR:", err)
        table_data = None

    return table_data


"""
PYGAME STARTS HERE
"""


# initialize the pygame window
pygame.init()

# change the caption/title for the Pygame app
pygame.display.set_caption("ObjectRocket PostgreSQL App", "ObjectRocket PostgreSQL App")

# get the OS screen/monitor resolution
max_width = pygame.display.Info().current_w
max_height = pygame.display.Info().current_h

# create a pygame resizable screen
screen = pygame.display.set_mode(
    (int(max_width*0.55), int(max_height*0.6)),
    HWSURFACE | DOUBLEBUF| RESIZABLE
)

# calculate an int for the font size
font_size = int(max_width / 100)

try:
    font = pygame.font.SysFont('Tahoma', font_size)
except Exception as err:
    print ("pygame.font ERROR:", err)
    font = pygame.font.SysFont('Arial', font_size)

# create buttons for PostgreSQL database and table
db_button = Button("Database Name:", (10, 10))
table_button = Button("Table Name:", (10, 100))

# default Postgres connection is 'None'
connection = None

# begin the pygame loop
app_running = True
while app_running == True:

    # reset the screen
    screen.fill( Button.black )

    # set the clock FPS for app
    clock = pygame.time.Clock()

    # iterate over the pygame events
    for event in pygame.event.get():

        # user clicks the quit button on app window
        if event.type == QUIT:
            app_running = False
            pygame.display.quit()
            pygame.quit()
            sys.exit()
            quit()

        # user presses a key on keyboard
        if event.type == KEYDOWN:

            if Button.selected != None:

                # get the selected button
                b = Button.selected

                # user presses the return key
                if event.key == K_RETURN:

                    table_data = None

                    # check if the selected button is the table name
                    if "table" in b.name.lower():
                        table_name = b.text

                    # check if the selected button is the db name
                    elif "database" in b.name.lower():
                        db_name = b.text

                    # get Postgres connection if table and db have value
                    if table_name != None and db_name != None:
                        connection = connect_postgres( db_name )

                        # get table records if connection is valid
                        if connection != None:
                            table_data = return_records( connection )

                    # reset the button selected
                    Button.selected = None

                else:
                    # get the key pressed
                    key_press = pygame.key.get_pressed()

                    # iterate over the keypresses
                    for keys in range(255):
                        if key_press[keys]:
                            if keys == 8: # backspace
                                b.text = b.text[:-1]
                            else:
                                # convert key to unicode string
                                b.text += event.unicode
                                print ("KEYPRESS:", event.unicode)

                # append the button text to button font object
                b.font = font.render(b.name + " " + b.text, True, Button.white, Button.black)

        # check for mouse button down events
        if event.type == MOUSEBUTTONDOWN and event.button == 1:
            print ("\nMOUSE CLICK:", event)

            # iterate over the button registry list
            for b in Button.registry:

                # check if the mouse click collided with button
                if b.rect.collidepoint(event.pos) == 1:
                    # store button object under selected attr
                    Button.selected = b

    # iterate over the button registry list
    for b in Button.registry:

        # blit the button's font to screen
        screen.blit(b.font, b.rect)

        # check if the button has been clicked by user
        if Button.selected == b:

            # blit an outline around button if selected
            rect_pos = (b.rect.x-5, b.rect.y-5, b.rect.width+10, b.rect.height+10)
            pygame.draw.rect(screen, Button.white, rect_pos, 3) # width 3 pixels

    # blit the PostgreSQL information using pygame's font.render() method
    if table_name == None and db_name == None:

        # blit instruction messages
        blit_text = "Type a database and table name into the fields and press 'Return'."
        conn_msg = font.render(blit_text, True, Button.green, Button.black)
        screen.blit(conn_msg, (10, 200))

    else:

        # connection is valid, but table doesn't exist
        if connection != None and table_data == None:
            blit_text = "The PostgreSQL table does not exist on this database."
            color = Button.red

        # connection is invalid
        elif connection == None:
            blit_text = "PostgreSQL connection is invalid."
            color = Button.red

        # connection is valid, and table is found
        elif connection != None:
            blit_text = "PostgreSQL Connection: " + str(connection)
            color = Button.green

        # blit the message to the pygame screen
        conn_msg = font.render(blit_text, True, color, Button.black)
        screen.blit(conn_msg, (10, 200))

        # enumerate() the table data if PostgreSQL API call successful
        if table_data != None:

            # enumerate the list of tuple rows
            for num, row in enumerate(table_data):

                # blit the table data to Pygame window
                blit_text = str(row).encode("utf-8", "ignore")
                table_font = font.render(blit_text, True, Button.light_blue, Button.black)
                screen.blit(table_font, (10, 250 + int(num*50)))

    # set the clock FPS for application
    clock.tick(20)

    # use the flip() method to display text on surface
    pygame.display.flip()
    #pygame.display.update()

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.