Python Sockets and Postgres

Written by Data Pilot
February 10, 2020
  • PostgreSQL
  • Python
  • Sockets

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

MethodDescription
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(addrConnect 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.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

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.