Python Sockets and Postgres
Introduction
In this article we will learn how to use Python Sockets and Postgres. We’ll create a small client server application by making two files we can run independently in order to learn and test. The client application will submit data to the server, which will update the database and reply back to the client to let the client know they were heard and the data was successfully received.
What is a python socket?
Sockets were created at Berkeley as part of Unix BSD. They spread quickly because sockets combined with the Internet made talking to various types of machines super easy compared to other ideas of the time and even up to now.
Think of sockets as openings in a device that allow for data transfer. There are different kinds of sockets.
In this article we are going to use TCP (stream) IPv4 sockets since they account for over 98% of socket types used. We are using stream sockets for the performance. I will try to clear up the mystery of what a socket is, as well as some hints on how to work with blocking and non-blocking sockets.
“Socket” can mean a number of different things so we will first be sure to understand the difference between client and server sockets. The client application, which might be a web browser or video game on a player’s computer uses a client type socket to broadcast information to the server. Let’s go with the game analogy and say the client is constantly updating the server with its position on the screen and other data. The server is receiving that data and also broadcasting to all clients the position of all client players on their screens so their client application can update the x and y coordinates of all players.
Socket methods
Method | Description |
---|---|
socket.socket() | Create sockets. |
socket.accept() | Accept a connection. Returns two values; conn and address. Conn is a new socket object which we’ll call a “cursor”. Address is the address of the socket we are connected to at the other end. |
socket.bind(addr) | Bind to the address. |
socket.close() | Close the socket. |
socket.connect(addr | Connect to the address. |
socket.listen() | Accept connections from clients. |
Let’s begin by creating some database code to access our tbl_games table in PostgreSQL. When a player first connects to the server, there will be no record yet in tbl_games, so first thing to do is check that table for a record related to that user and that game, since they may have played in the past and we don’t want to confuse the current game with past games.
Check table for user
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | s = "" s += "SELECT" s += " id" s += " FROM tbl_games" s += " WHERE (" s += " id_game = (%id_game%)" s += " AND" s += " id_user = (%id_user)" s += " )" db_cursor.execute(s, [valuesList[0], valuesList[1]]) db_row = db_cursor.fetchone() if db_row == None: id = 0 else: id = db_row[0] |
In the above code the end result is that id is either zero or non-zero. Next we will use that value in order to decide if we are adding a new row for that user and game or if we are updating:
Insert or update table
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 | if id == 0: s = "" s += "INSERT INTO tbl_games (" s += " id_game" s += ", id_user" s += ", i_hit_points" s += ", i_points" s += ", i_lives" s += ", i_x" s += ", i_y" s += ") VALUES (" s += " (%id_game)" s += ", (%id_user)" s += ", (%i_hit_points)" s += ", (%i_points)" s += ", (%i_lives)" s += ", (%i_x)" s += ", (%i_y)" s += ")" db_cursor.execute(s, valuesList) else: s = "" s += "UPDATE tbl_games SET" s += " id_game = (%id_game)" s += ", i_hit_points = (%i_hit_points)" s += ", i_points = (%i_points)" s += ", i_lives = (%i_lives)" s += ", i_x = (%i_x)" s += ", i_y = (%i_y)" s += " WHERE (" s += " id_game = (%id_game%)" s += " AND" s += " id_user = (%id_user)" s += " )" db_cursor.execute(s, valuesList) |
Notice we used parameterized queries for both insert and update. valuesList is a Python list holding the values for id_game, id_user, i_hit_points, i_points, i_lives, i_x, and i_y. Now we’ll see how that list gets populated by studying the following server and client code listings, which we have commented for your convenience and ease of understanding.
Python server side socket code listing
Name this file “server.py”:
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 | import socket import psycopg2 # connect to database t_host = "Postgres 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() def updateData(valuesList): # first see if game state data exists for this user s = "" s += "SELECT" s += " id" s += " FROM tbl_games" s += " WHERE (" s += " id_game = (%id_game%)" s += " AND" s += " id_user = (%id_user)" s += " )" try: db_cursor.execute(s, [valuesList[0], valuesList[1]]) db_row = db_cursor.fetchone() if db_row == None: id = 0 else: id = db_row[0] except psycopg2.Error as e: t_msg_err = "SQL error: " + e + "/n SQL: " + s return render_template("error.html", t_msg_err = t_msg_err) if id == 0: s = "" s += "INSERT INTO tbl_games (" s += " id_game" s += ", id_user" s += ", i_hit_points" s += ", i_points" s += ", i_lives" s += ", i_x" s += ", i_y" s += ") VALUES (" s += " (%id_game)" s += ", (%id_user)" s += ", (%i_hit_points)" s += ", (%i_points)" s += ", (%i_lives)" s += ", (%i_x)" s += ", (%i_y)" s += ")" try: db_cursor.execute(s, valuesList) except psycopg2.Error as e: t_msg_err = "SQL error: " + e + "/n SQL: " + s return render_template("error.html", t_msg_err = t_msg_err) else: s = "" s += "UPDATE tbl_games SET" s += " id_game = (%id_game)" s += ", i_hit_points = (%i_hit_points)" s += ", i_points = (%i_points)" s += ", i_lives = (%i_lives)" s += ", i_x = (%i_x)" s += ", i_y = (%i_y)" s += " WHERE (" s += " id_game = (%id_game%)" s += " AND" s += " id_user = (%id_user)" s += " )" try: db_cursor.execute(s, valuesList) except psycopg2.Error as e: t_msg_err = "SQL error: " + e + "/n SQL: " + s return render_template("error.html", t_msg_err = t_msg_err) db_cursor.close def socketSetup(): # Server socket connSocket = socket.socket() print('Socket was created') # Bind socket to a port number. connSocket.bind(('localhost', 9999)) return connSocket def socketListen(connSocket): # how many clients? connSocket.listen(3) print('waiting for connections') # Wait for connection(s) while True: # Accept connection from client cursorSocket, addr = connSocket.accept() print("connected with addr: " + str(addr)) # Data is transferred as bytes, so # here we use the decode function. valuesReceived = cursorSocket.recv(1024).decode() # Received '5,1,38,36,21,1048,649' BUT # it is not yet a string nor a list. # Need to convert to [5,1,38,36,21,1048,649] # Use the eval function to turn our string into an array. valuesList = eval("[" + str(valuesReceived) + "]") # Make sure all is working as desired. print(str(valuesList)) # Fill our discrete variables from # that list. NOT NECESSARY BUT # Doing this so we can check data # integrity for debugging purposes. id_game = valuesList[0] id_user = valuesList[1] i_hit_points = valuesList[2] i_points = valuesList[3] i_lives = valuesList[4] i_x = valuesList[5] i_y = valuesList[6] # Making sure value for id_user jibes with # what we expect. print("id_user: " + str(id_user)) # Send confirmation message back to client # where one means success # must send in byte format, not string. cursorSocket.send(bytes(str(1), "utf-8")) # cursorSocket.send(bytes("You sent id_game of " + str(id_game), "utf-8")) cursorSocket.close() # Call our database routines to insert or update # player status. updateData(valuesList) # Note: # If this is a real-time game, rather than # turn-based, and we were concerned about # performance, we would not update the database # so often. # What we would be doing here is informing every # client of the positions of the other clients # using cursorSocket.send(). def main(): sock = socketSetup() socketListen(sock) if __name__ == "__main__": main() |
Run server.py and make sure no errors come up before you go on to the final step of creating and running the Python client application.
Python client side socket code listing
Name the following file “client.py”
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 | import socket import random def socketConnect(): connSocket = socket.socket() connSocket.connect(("localhost", 9999)) # can use "localhost" for ip return connSocket def encodeMyList(toEncode): newString = "" for word in toEncode: newString += "," + str(word) return newString[1:] def main(): # Connect to the server connSocket = socketConnect() # Set up some values to send # with random integers. id_game = random.randint(0, 50) id_user = 1 i_hit_points = random.randint(0, 50) i_points = random.randint(0, 50) i_lives = random.randint(0, 50) i_x = random.randint(20, 1500) i_y = random.randint(20, 700) # Turn our values into a list valuesList = [] valuesList.append(id_game) valuesList.append(id_user) valuesList.append(i_hit_points) valuesList.append(i_points) valuesList.append(i_lives) valuesList.append(i_x) valuesList.append(i_y) # Show what the values are so we can # be sure the correct ones were sent. print("client: id_game: " + str(id_game)) print("client: user: " + str(id_user)) print("client: i_hit_points: " + str(i_hit_points)) print("client: i_points: " + str(i_points)) print("client: i_lives: " + str(i_lives)) print("client: i_x: " + str(i_x)) print("client: i_y: " + str(i_y)) # Convert our list into a string suitable # for sending. toSend = encodeMyList(valuesList) print("toSend = " + toSend) # Send the string of values, which looks like: # 47,8,16,24,513,185 connSocket.send(bytes(toSend, 'utf-8')) # Look for receipt message. # recv(param) number is buffer size # decode changes from bytes to string, # so here we are receiving one byte, a one or a zero, where one means success. bReceived = connSocket.recv(1).decode() if bReceived == str(1): print("It worked!") else: print("Something went wrong.") if __name__ == "__main__": main() |
Conclusion
We learned to use Python Sockets with Postgres. We created a simple client server application by making two files you can run to learn and incorporate into your multiplayer games. The client application submits data to the server application, which updates PostgreSQL and replies back to the client application to let them know they were heard and the data was successfully received.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started