Once again, we can use the Python REPL to create a new table. Access your server folder and run a Python shell:

Python 3.6.4 (default, Jan 03 2018, 13:52:55) [GCC] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import database
>>> d = database.Database()
>>> sql = "CREATE TABLE friends (user_one text, user_two text, blocked integer)"
>>> params = []
>>> d.perform_insert(sql, params)
>>> 

Our database will contain two users, called user_one and user_two, as well as an integer representing whether communication has been blocked. This integer will be treated as a Boolean, where 0 represents no blocking and 1 represents the conversation being blocked.

We now need to write some new methods in our Database class to query and update this table:

def add_friend(self, user_one, user_two):
    sql = "INSERT INTO friends (user_one, user_two, blocked) VALUES (?,?,0)"
    query_params = (user_one, user_two)

    self.perform_insert(sql, query_params)

When adding a friend pairing, we will assume that they wish to talk to each other, so we will insert the blocked flag as a 0 by default.

This handles the ability to add a friend. We now will need to be able to get all friends of a user for when they log into the ChatWindow:

def get_friends(self, username):
    all_friends = []
    sql = "SELECT user_two FROM friends WHERE user_one=? AND blocked=0"
    params = (username,)
    friends = self.perform_select(sql, params)

    sql = "SELECT user_one FROM friends WHERE user_two=? AND blocked=0"
    friends2 = self.perform_select(sql, params)

    for friend in friends:
        all_friends.append(friend["user_two"])
    for friend in friends2:
        all_friends.append(friend["user_one"])

    return all_friends

Since we do not know which column the querying user's username will be in, we need to select records from both columns and then merge the results into one list.

We first select records where our specified username is user_one, then user_two. We loop over the returned lists of dictionaries and merge the records into our all_friends list.

This method gives us all usernames of friends added by a supplied user, but to display them on our FriendsList window, we need their avatar and real name, too. This will require one more method:

def get_users_by_usernames(self, usernames):
    question_marks = ','.join(['?' for user in usernames])
    sql = f"SELECT * FROM users WHERE username IN ({question_marks})"
    params = [user for user in usernames]

    friends = self.perform_select(sql, params)

    return friends

Since we do not know how many usernames we got back from the previous query, we don't know how many question marks to put into this one. For this reason, we use a string join to create a list of question marks, providing one per username with a list comprehension. We then use string formatting to put this list of question marks into our query.

With SQL, in order to check whether a record is inside a list, we use an IN clause, then provide every possible value in regular brackets separated by a comma. For example, to select the first three users, we could do SELECT * FROM users WHERE id IN (1, 2, 3).

That takes care of adding and retrieving friends in our Database class.

When it comes to blocking users, we need to find the record which contains the two usernames and flip the blocked column to a 1:

def block_friend(self, username, contact_to_block):
    sql = "UPDATE friends SET blocked=1 WHERE (user_one = ? AND user_two = ?) OR (user_two = ? AND user_one = ?)"
    query_params = (username, contact_to_block, username, contact_to_block)

    self.perform_insert(sql, query_params)

Since we don't know which friend will be in which user column, we can use an OR clause to specify that we want either one of the two provided conditions to be true.

That's it for our Database now. As usual, we will now need to add server endpoints to interact with these new methods.