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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
# 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:
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:
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:
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:
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:
# 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:
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:
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:
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.
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:
sudo kill -9 {PID_NUM}
Just the Code
# -*- 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