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:
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 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:
1 2 3 | # 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:
1 2 3 4 5 | # 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:
1 2 | # 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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 | # 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:
1 2 3 | # 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:
1 2 3 4 5 | # 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:
1 2 | # 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:
1 2 3 4 5 6 7 | # 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:
1 2 3 4 5 6 7 | # 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:
1 2 3 4 5 6 7 8 9 10 11 12 | # 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:
1 2 3 4 5 6 7 8 9 10 | # 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:
1 2 3 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | # 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:
1 2 3 4 5 6 7 8 9 10 11 | # 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:
1 2 3 4 5 6 7 8 9 10 11 12 | # 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:
1 2 3 4 5 6 7 | # 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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:
1 2 3 4 5 6 7 8 9 10 | # 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:
1 2 3 4 5 | # 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:
1 | 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.
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:
1 2 | ps aux | grep pygame sudo kill -9 {PID_NUM} |
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 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 | #!/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