Inside your server folder (not the new conversations folder), create a file named conversation.py. This will hold a class of the same name, which handles the sqlite side of creating and interacting with these databases:

import sqlite3

class Conversation:
    def __init__(self, database):
        self.database = database

This looks much like the __init__ method of our Database class, but it will have its SQLite database filename passed to it, allowing this class to work with multiple different conversation databases.

The first method we will need to write is one that will create the table inside the database:

def initialise_table(self):
    sql = "CREATE TABLE conversation (author text, message text, date_sent text)"
    conn = sqlite3.connect(self.database)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    conn.close()

From the create statement, we can see that each database will hold a single table called conversation. This table will hold three pieces of information about each entry—the author (who sent the message), the message text itself, and the date when the message was sent.

After the creation of the sql statement follows all of the connection handling code we have seen before. If you wish to extract this to a method again, feel free.

Now that we have the tables created, we can write a method to read the data from them:

def get_history(self):
    sql = "SELECT * FROM conversation"
    conn = sqlite3.connect(self.database)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(sql)
    results = [dict(row) for row in cursor.fetchall()]
    conn.close()

    return results

This method creates a query which extracts all information from the database and returns it as a dictionary. If you have not seen the select * statement before, this simply tells the database to return all columns. This saves the need to type out each column individually and update the query if a new column is added at a later point.

Returning data is nice, but there's no point if we cannot add data too!

def add_message(self, author, message, date_sent):
    sql = "INSERT INTO conversation VALUES (?, ?, ?)"
    params = (author, message, date_sent)
    conn = sqlite3.connect(self.database)
    cursor = conn.cursor()
    cursor.execute(sql, params)
    conn.commit()
    conn.close()

With this final method, we can now add data to our table. The author, message, and date_sent passed to this method are stored in the relevant column of our conversation table.

That finishes off our Conversation class. We can now update our web service to communicate with it.