Game Python Postgres and Save Game

Introduction

In this part, we continue building a game with Python and Postgres and Save Game as part 8 of a multi-part series of lessons where the final result is a 2D top-down-view videogame like Pac Man, where the purpose is to “eat” powerups while avoiding monsters. We chose to use the Arcade framework for Python for its game-related methods and functions. We used Postgres to read and write screen and object (player, enemies, obstacles, and power-ups) data. In this lesson we will explore using Postgres again to store and retrieve saved games. In future articles we will add player registration, player login, multi-player and a screen editor.

Prerequisites

See part 1 through 7, where we learned how to draw a screen, create tables in Postgres for storing and reading screen data and screen objects as Sprites, reading keys to control player movement, added random movements to the monsters, collision detection, intelligent player tracking for the monsters, and sound effects. Please peruse the previous sections of this lesson, starting at Build game with Python and draw a screen.

I will assume you followed the previous sections of this series, so we will leave the following out of this article: creating the Postgres tables, re-doing the details of setting up a window with Arcade, and Sprite movement mechanices, except for in the source code at the end of this part eight. So, before the final code at the bottom, we’ll build some functions for loading and saving a game’s progress to Postgres.

NOTE: tbl_games has an auto-incrementing unique indexed column we called “id”.

Save game in Python

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
    # First we use SELECT SQL to check whether
    #    a saved screen exists for the current user.
    #    so yes, one save per user.
    s = ""
    s += "SELECT"
    s += " id"
    s += " FROM tbl_games"
    s += " WHERE id_user = " + id_user
    db_cursor.execute(s)
    db_row = db_cursor.fetchone()
    # Check to see if a row was returned
    # If not then we set i_rows to zero
    if db_row == None:
        i_rows = 0
    else:
        # In this case, a row was returned.
        # Get data from that row
        i_rows = 1
        id = db_row[0]

    if i_rows == 0:
        # If no rows were returned, this means
        #   we want to add a new record via the INSERT INTO
        #   SQL command.
        s = ""
        s += "INSERT INTO tbl_games ("
        s += " id_screen"
        s += ", id_user"
        s += ", i_hit_points"
        s += ", i_points"
        s += ", i_lives"
        s += ") VALUES ("
        s += id_screen
        s += "," + id_user
        s += "," + i_hit_points
        s += "," + i_points
        s += "," + i_lives
        s += ")"
        db_cursor.execute(s)
    else:
        # If a row was returned, we use the id column
        #   of that returned row in our WHERE clause
        #   so we overwrite that record in the database.
        s = ""
        s += "UPDATE tbl_games SET"
        s += " id_screen = " + id_screen
        s += ", i_hit_points = " + i_hit_points
        s += ", i_points = " + i_points
        s += ", i_lives = " + i_lives
        s += " WHERE id = " + id
        db_cursor.execute(s)

Load game in Python

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    s = ""
    s += "SELECT TOP 1"
    s += " id_screen = " + id_screen
    s += ", i_hit_points = " + i_hit_points
    s += ", i_points = " + i_points
    s += ", i_lives = " + i_lives
    s += " FROM tbl_games"
    s += " WHERE id_user = " + id_user
    try:
        db_cursor.execute(s)
        db_row = db_cursor.fetchone()
        # Pull data from
        id_screen = db_row[0]
        i_hit_points = db_row[1]
        i_points = db_row[2]
        i_lives = db_row[3]
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)

Now that we have looked at our two new features, load game and save game, we can dive into the commented full source game code:

Full source 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
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
import arcade
import psycopg2
import random
from flask import Flask
from flask import render_template

# connect to database
t_host = "PostgreSQL database host address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

# Initialize global vars

# In a future lesson we will add registration and sign in
# For now, there is only one user ID; zero.
id_user = 0

id_screen = 0
t_title = ""
t_color = ""
i_width = 0
i_height = 0
i_enemy_hit = 0
i_hit_points = 100
i_points = 0
i_lives = 3
# initialize as list:
t_hit_sound = []

@app.route("/main")

def gameLoad():
    s = ""
    s += "SELECT TOP 1"
    s += " id_screen = " + id_screen
    s += ", i_hit_points = " + i_hit_points
    s += ", i_points = " + i_points
    s += ", i_lives = " + i_lives
    s += " FROM tbl_games"
    s += " WHERE id_user = " + id_user
    try:
        db_cursor.execute(s)
        db_row = db_cursor.fetchone()
        id_screen = db_row[0]
        i_hit_points = db_row[1]
        i_points = db_row[2]
        i_lives = db_row[3]
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)
    db_cursor.close

def gameSave():
    # first see if a save exists for this user
    s = ""
    s += "SELECT"
    s += " id"
    s += " FROM tbl_games"
    s += " WHERE id_user = " + id_user
    try:
        db_cursor.execute(s)
        db_row = db_cursor.fetchone()
        if db_row == None:
            i_rows = 0
        else:
            i_rows = 1
            id = db_row[0]
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)
    if i_rows == 0:
        s = ""
        s += "INSERT INTO tbl_games ("
        s += " id_screen"
        s += ", id_user"
        s += ", i_hit_points"
        s += ", i_points"
        s += ", i_lives"
        s += ") VALUES ("
        s += id_screen
        s += "," + id_user
        s += "," + i_hit_points
        s += "," + i_points
        s += "," + i_lives
        s += ")"
        try:
            db_cursor.execute(s)
        except psycopg2.Error as e:
            t_msg = "SQL error: " + e + "/n SQL: " + s
            return render_template("error.html", t_msg = t_msg)
    else:
        s = ""
        s += "UPDATE tbl_games SET"
        s += " id_screen = " + id_screen
        s += ", i_hit_points = " + i_hit_points
        s += ", i_points = " + i_points
        s += ", i_lives = " + i_lives
        s += " WHERE id_user = " + id_user
        try:
            db_cursor.execute(s)
        except psycopg2.Error as e:
            t_msg = "SQL error: " + e + "/n SQL: " + s
            return render_template("error.html", t_msg = t_msg)
    db_cursor.close

def getScreenFromDB():
    # Get columns for first row/
    s = ""
    s += "SELECT"
    s += " id"
    s += ", t_color"
    s += ", t_title"
    s += ", i_height"
    s += ", i_width"
    s += " FROM tbl_screens"
    s += " WHERE id = " + id_screen
    try:
        db_cursor.execute(s)
        db_row = db_cursor.fetchone()
        id_screen = db_row[0]
        t_color_screen = db_row[1]
        t_title = db_row[2]
        i_height = db_row[3]
        i_width = db_row[4]
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)
    db_cursor.close

def getObjectsFromDB():
    # Get objects data from tbl_screens_objects in Postgres
    s = ""
    s += "SELECT"
    s += " id"
    s += ", t_obj_name"
    s += ", t_obj_type"
    s += ", t_obj_img_URL"
    s += ", i_y"
    s += ", i_x"
    s += ", t_color_hit"
    s += ", t_properties"
    s += ", t_hit_sound"
    s += ", t_hit_action"
    s += ", b_hit_possible"
    s += ", b_hit_destroys_it"
    s += ", b_hit_awards"
    s += ", n_hit_awards"
    s += ", n_hit_damage"
    s += " FROM tbl_screens_objects"
    s += " WHERE ("
    s += " id_screen = " + id_screen
    s += ")"
    s += " ORDER BY i_order"
    try:
        db_cursor.execute(s)
        return db_cursor
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)

class gameWindow(arcade.Window):
    def__init__(self, i_width, i_height, t_title, db_cursor):
    super().__init__(width, height, title, resizable=false)
    self.set_location(100, 100)

    # Give our window a background color.
    arcade.set_background_color(arcade.color.t_color_screen)

    self.player_x = 100
    self.player_y = 200
    self.player_speed = 250

    self.right = false
    self.left = false
    self.up = false
    self.down = false

    # Initialize a spritelist to track objects like walls.
    # This type of object only stops player motion.
    self.ListSpriteObjects = arcade.SpriteList()

    # Initialize a spritelist to track monsters/enemies.
    # These objects cause damage to i_hit_points.
    self.ListSpriteEnemies = arcade.SpriteList()

    # Initialize a spritelist to track powerups.
    # These objects give points to the player when a collision occurs.
    self.ListSpritePowerUps = arcade.SpriteList()

    # Get data from the database into the 3 lists above.
    i = 0
    for each db_row in db_cursor:
        id_screen = db_row[0]
        t_obj_name.append(db_row[1])
        t_obj_type.append(db_row[2])
        t_obj_img_URL = db_row[3]
        i_y = db_row[4]
        i_x = db_row[5]
        t_color_hit.append(db_row[6])
        t_properties.append(db_row[7])
        t_hit_sound.append(arcade.load_sound(db_row[8]))
        t_hit_action.append(db_row[9])
        b_hit_possible.append(db_row[10])
        b_hit_destroys_it.append(db_row[11])
        b_hit_awards.append(db_row[12])
        n_hit_awards.append(db_row[13])
        n_hit_damage.append(db_row[14])
        # draw the current sprite in db_row
        if t_object_type == "obstacle":
            self.SpriteObject = arcade.Sprite(t_object_image_URL, i_x, i_y)
            # add current obstacle to growing list of Sprites (SpriteList)
            self.ListSpriteObjects.append(self.SpriteObject)
        elif t_object_type == "enemy":
            self.SpriteObstacle = arcade.Sprite(t_object_image_URL, i_x, i_y)
            # add current enemy (sprite) to growing list of Sprites (SpriteList)
            self.SpriteEnemies.append(self.SpriteObject)
        elif t_object_type == "powerup":
            self.SpriteObstacle = arcade.Sprite(t_object_image_URL, i_x, i_y)
            # add current PowerUp to growing list of Sprites (SpriteList)
            self.SpritePowerUps.append(self.SpriteObject)
        elif t_object_type == "Player":
            self.SpritePlayer = arcade.Sprite(t_object_image_URL, i_x, i_y)
        # increment the counter
        i += 1

def enemyMovement():
    # Iterate through enemy sprites.
    # In this case, there are only 2 enemies but we designed this so
    # you can add as many as you want to add to the database.
    # THEN we give them a random direction out of eight
    #   possible directions.
    # FINALLY we add our new-for-this-part "intelligent" direction to
    #   the random one.
    for currentSprite in self.ListSpriteEnemies:
        x = currentSprite.position[0]
        y = currentSprite.position[1]

        # Random movement
        direction = random.randint(1, 8)
        if direction == 1:
            y += 2
        if direction == 2:
            x += 2
            y += 2
        if direction == 3:
            x += 2
        if direction == 4:
            x += 2
            y -= 2
        if direction == 5:
            y -= 2
        if direction == 6:
            y -= 2
            x -= 2
        if direction == 7:
            x -= 2
        if direction == 8:
            x -= 2
            y += 2

        # NEW "intelligent" movement.
        if player_y > y:
            y += 2
        if player_x > x:
            x += 2
        if player_y < y:
            y -= 2
        if player_x < x:
            x -= 2

        # Set the current monster's location on screen.
        currentSprite.set_position(x, y)

def on_draw(self):
    self.SpritePlayer.draw()
    self.ListSpriteObjects.draw()
    self.ListSpriteEnemies.draw()
    self.ListSpritePowerUps.draw()

def on_update(self, delta_time):
    # Delta_time is last time the
    #   same function was used; We use this
    #   to scale movement speed with speed of processing.
    if self.right = true:
        self.player_x += self.player_speed * delta_time
    if self.left = true:
        self.player_x -= self.player_speed * delta_time
    if self.up = true:
        self.player_y += self.player_speed * delta_time
    if self.down = true:
        self.player_y -= self.player_speed * delta_time

    # Set a new location based on the player's x and y coordinates:
    self.SpritePlayer.set_position(self.player_x, self.player_y)

    # Move all enemy Sprites.
    enemyMovement()

    # Update Sprite Lists:
    self.ListSpriteObjects.update()
    self.ListSpriteEnemies.update()
    self.ListSpritePowerUps.update()

    # Did the user hit a monster?
    i = 0
    for SpriteEnemy in self.ListSpriteEnemies:
        b_collision = arcade.check_for_collision(SpriteEnemy, self.SpritePlayer)
        if b_collision == true:
            i_enemy_hit = i
            i_hit_points -= n_hit_damage[i_enemy_hit]
            # Graphical changes (like explosion or something) code
            #   to show crash will go here in later lesson.
            # NEW:
            arcade.play_sound(t_hit_sound[i])

            if i_hit_points < 1:
                i_lives -= 1
                if i_lives < 1:
                    # Dead - Next part we will build code to handle this.
                    # For now, the player lives forever.
                i_hit_points = 100
        i +=1

    # Did the user run into a power-up?
    i = 0
    for SpritePowerUp in self.ListSpritePowerUps:
        b_collision = arcade.check_for_collision(SpritePowerUp, self.SpritePlayer)
        if b_collision == true:
            i_pu_hit = i
            i_points += n_hit_awards[i_pu_hit]
            # Graphics and code to show
            #   powerup hit will go here.
            # NEW:
            arcade.play_sound(t_hit_sound[i])
        i +=1

    # Did the user collide with another type of obstacle?
    i = 0
    for SpritePowerUp in self.ListSpritePowerUps:
        b_collision = arcade.check_for_collision(SpritePowerUp, self.SpritePlayer)
        if b_collision == true:
            # half player movement
            self.right = false
            self.left = false
            self.up = false
            self.down = false
            # Graphics code to show object collision
            #   here in a future part of this lesson.
            # NEW:
            arcade.play_sound(t_hit_sound[i])
        # increment the counter.
        i += 1

def on_key_press(self, symbol, modifiers):
    # When a key is pressed:
    #   Tracking only the four arrow keys
    #   NOTE: We are careful to NOT use elif here
    #     because we want to allow diagonal movement.
    if symbol == arcade.key.right:
        self.right = true
    if symbol == arcade.key.left:
        self.left = true
    if symbol == arcade.key.up:
        self.up = true
    if symbol == arcade.key.down:
        self.down = true

    # NEW code in this chapter to handle Load Game and Save Game
    if symbol == 108: # they pressed "L"
        gameLoad()
        # Redraw screen, etc.
        main()
    elif symbol == 115: # they pressed "S"
        gameSave()

def on_key_release(self, symbol, modifiers):
    # When a key is released:
    if symbol == arcade.key.right:
        self.right = false
    if symbol == arcade.key.left:
        self.left = false
    if symbol == arcade.key.up:
        self.up = false
    if symbol == arcade.key.down:
        self.down = false

Def main():
    # Primary module
    getScreenFromDB()
    db_cursor = getObjectsFromDB()
    gameWindow(i_width, i_height, t_title, db_cursor)
    enemyMovement()
    arcade.run()

Conclusion

In this part 8, we continued creating a 2D game with Python and Postgres and Save Game as a “chapter” in a multi-section series of lessons where the final result is a top-down-view Pac Man-like video game where the goal is to “eat” powerups and avoid monsters. We used the Arcade library for Python because of it being a modernized version of Pyglet. We used Postgres to read and write screen and objects such as the enemies, obstacles, and power-ups. In this lesson we added Load Game and Save Game via the “L” and “S” keys. In future articles we will add user id creation, multi-player, and a screen editor.

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.