Build a PostgreSQL GUI App in Python (Part 1)

Written by Data Pilot

October 08, 2019

PostgreSQL
Psycopg2
Pygame
Python

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

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

Screenshot of IDLE for Python 3 returning the version number for 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

Screenshot of a Pygame Python script running in a terminal window

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.

Screenshot of a Pygame window running with ObjectRocket PostgreSQL App caption

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