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
andpsycopg2
Python libraries. Use thepython3 -V
andpip3 -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:
1 | pip3 install pygame |
- 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:
1 | sudo python3 -m pip install --upgrade pygame |
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:
1 | pip3 install psycopg2 |
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:
1 2 3 4 5 6 7 8 9 | # 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 * |
At this point, if you save and run the script, you should see the following response from Pygame:
1 2 | pygame 1.9.6 Hello from the pygame community. https://www.pygame.org/contribute.html |
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:
1 2 3 4 5 6 7 8 | # 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" |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | # 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) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # 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) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | # 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] |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # 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 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # 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 ) ) |
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:
1 2 3 | try: # use the execute() method to put table data into cursor obj cursor.execute( sql_object ) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | # 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 |
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:
1 2 3 4 5 | # initialize the pygame window pygame.init() # change the caption/title for the Pygame app pygame.display.set_caption("ObjectRocket PostgreSQL", "ObjectRocket PostgreSQL") |
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:
1 2 3 | # get the OS screen/monitor resolution max_width = pygame.display.Info().current_w max_height = pygame.display.Info().current_h |
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:
1 2 3 4 5 6 | # create a pygame resizable screen screen = pygame.display.set_mode( # create a tuple integer object from screen dimensions (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.
Conclusion
Test the Python script in a terminal or command prompt window using the python3
command to execute it:
1 | python pg_pygame.py |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 | #!/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 attacks 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 # 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 ) |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started