Building a Chatbot using Telegram and Python (Part 2): Adding a SQLite Database Backend

In Part 1 of this tutorial, we built a basic Telegram Chatbot from scratch using Python. Our Bot wasn't too smart, and simply echoed anything sent to it back to the user. The Bot we built is a good foundation for a wide range of possible bots, as we could take input, process it, and return a result — the foundation of classical computing. A major limit of our previous bot though was that it couldn't store any information. It had no long-term memory.

In this tutorial, we'll add a SQLite database backend to our bot and allow it to remember information for specific users indefinitely. We'll build a simple To Do list that allows users to add new items or delete existing ones.

In addition to the basic Python knowledge required for Part 1, it would be good if you had some familiarity with relational databases for this tutorial. Ideally, you should already be familiar with concepts such as Tables, Rows, Columns, and have written at least a basic SELECT * FROM SQL statement. But if you haven't, you'll probably be able to keep up anyway.

Overview

In this tutorial we'll cover the following:

• Building a Database Helper script to cover adding data to a SQLite database and retrieving it again.
• Adding a custom Telegram Keyboard, so that our user can select options instead of typing out an entire message.
• Adding the concept of ownership to items, so that multiple users can use our bot.

By the end of this tutorial, you'll know how to use some of the more advanced features of the Telegram API (specifically, the custom keyboard feature), and how to add a SQLite database to your Chatbots.

Building a database helper

We'll follow good coding practice and keep our database-related code separate from the main logic of our Bot. Any code that touches the database will be confined to a new script, dbhelper.py and we'll import this script into the main Bot script.

Modeling the problem

Whenever you need to build a database, the first step is to think carefully about exactly how you're going to represent the problem. We'll follow an iterative approach to building this bot, meaning that at each step, we'll make the problem as simple as possible and then solve that before iterating on the project to build a more nuanced solution.

A To Do list consists of a number of items with text descriptions such as "Buy groceries", or "Finish writing Chatbot tutorial". For now, we'll model each item by simply storing its description in a table in a database. We'll see later on that this is a drastic oversimplification of the problem, but it'll be good enough to get us started.

Our Chatbot will need to be able to add a new item to the database of items that it is remembering for the user, and it will also need to delete an item once the user has marked that item as done. Furthermore, it'll need to be able to get all the items to display them to the user. Finally, it'll need to be able to create the database table, in case we need to set up a new database (e.g. when moving our bot to a new machine). All of this functionality should be contained in our dbhelper.py script, as all of it interacts with the database directly.

The dbhelper.py code

Create a new file called dbhelper.py in the same directory as your Chatbot script, and add the following code:

import sqlite3


class DBHelper:
    def __init__(self, dbname="todo.sqlite"):
        self.dbname = dbname
        self.conn = sqlite3.connect(dbname)

    def setup(self):
        stmt = "CREATE TABLE IF NOT EXISTS items (description text)"
        self.conn.execute(stmt)
        self.conn.commit()

    def add_item(self, item_text):
        stmt = "INSERT INTO items (description) VALUES (?)"
        args = (item_text, )
        self.conn.execute(stmt, args)
        self.conn.commit()

    def delete_item(self, item_text):
        stmt = "DELETE FROM items WHERE description = (?)"
        args = (item_text, )
        self.conn.execute(stmt, args)
        self.conn.commit()

    def get_items(self):
        stmt = "SELECT description FROM items"
        return [x[0] for x in self.conn.execute(stmt)]

In this code, we have five methods:

• __init__() takes a database name (by default store our data in a file called todo.sqlite) and creates a database connection.
• setup() creates a new table called items in our database. This table has one column (called description)
• add_item() takes the text for the item and inserts it into our database table.
• delete_item() takes the text for an item and removes it from the database
• get_items() returns a list of all the items in our database. We use a list comprehension to take the first element of each item, as SQLite will always return data in a tuple format, even when there is only one column, so in this example each item we retrieve from the database will be similar to ("buy groceries", ) (a tuple) which the list comprehension converts to "buy groceries" (a simple string).

This DBHelper class that we've just created can be used by our Chatbot to do everything it needs to add, remove, and display items. Let's modify the Chatbot code to use this new functionality.

Modifying the bot code

We'll be starting from where we left off in Part 1 of this tutorial, where we had a script called echobot.py which echoed back every message that was sent to it. To keep the different parts of this series separate, copy the echobot.py to a new file called todobot.py, and work with the new file instead.

The first change we need to make to the code is to import out DBHelper class and initialize an instance of it.

Add the following two lines near the top of the todobot.py script:

from dbhelper import DBHelper

db = DBHelper()

With the code above, we can access the methods to get, add, and delete items through the db variable.

We previously wrote the function echo_all(), which looped through the updates and replied to each one of them. We don't need this function now as we won't be echoing messages. Delete it, and write a new function in its place called handle_updates()

def handle_updates(updates):
    for update in updates["result"]:
        try:
            text = update["message"]["text"]
        	chat = update["message"]["chat"]["id"]
            items = db.get_items()
            if text in items:
                db.delete_item(text)
                items = db.get_items()
            else:
                db.add_item(text)
                items = db.get_items()
            message = "\n".join(items)
            send_message(message, chat)
        except KeyError:
            pass

This is not too different from what we had before. Again we loop through each update and grab the text and the chat components so that we can look at the text of the message we received and respond to the user who sent it.

Now, we also include a line to load all the items from our database and store them in the items variable. We then check if the message that the user sent us is already one of the items we're storing. If the user sends us a duplicate item, we'll take it as a signal to delete the item (i.e., mark it as done), but if the item is new, we'll add it to the database. Either way, we'll send a message back to the user showing each of the saved items on a new line.

Asking our users to type out the items that they want to delete is not very user-friendly, but once we see that this is working, we'll add some improvements. Note that we're already beginning to hit the limits of building Chatbots instead of traditional mobile apps: with Chatbots, all interactions have to be text-based, while with a traditional mobile app, we could add an "X" button on each item which the user could simply touch to remove that item.

We also need to call the setup() method of the database somewhere. Although we only need to do this once, and it could be done in a separate script, we'll call it in the main function of our bot so that it gets called every time we start the bot. Because if have the IF NOT EXISTS part in our CREATE TABLE statement, we'll still only create the table once.

Add the following line of code to the beginning of the main() function:

 db.setup()

Testing our first attempt at a To Do list

To make sure everything works as expected, run the todobot.py script in your terminal and send a message to the Bot. It should echo the message back to you. This looks like what we had before, but there's a lot more going on under the hood. Now any message you send gets stored in the database, and what looks like the echo is actually your entire To Do list, which contains only one item.

Send another (different) message, and you'll see both messages be sent to you this time. This is your updated To Do list. To remove one of the items, send the text of the item that you want to remove.

Here we can see that we add "Buy eggs" to the list, and the entire list (consisting only of "Buy eggs") gets returned. Then we add "Finish writing", and have both items returned. When we send "Buy eggs" again, instead of adding a duplicate item, the bot deletes the existing one.

Adding a custom Telegram keyboard

UX, or User eXperience is the magical term in application development that covers everything from the design of your interface to how responsive your application is to commands. Basically, if users enjoy using your application and are able to control it intuitively, then your application has good UX. At the moment, our UX is bad — users have to type out long item names to achieve the simple task of deleting the item, and to make it worse, we don't even tell them that. What we want, instead, is for our users to be able to select the items that they're done with from a list, and have our bot delete the items that are tapped.

The way to achieve this in Telegram is by using a custom keyboard. By default, when you chat to a user or a bot on Telegram, you are presented with your phone's default keyboard layout (or nothing if you have a physical keyboard on your device). As part of the sendMessage API call, we can pass along a custom keyboard, which will present the user with a keyboard that has specific keys with specific labels that we can specify. When a user indicates that they want to mark items as done, we'll build a keyboard of all the existing items, and allow the user to press on the ones that they want to be deleted.

The syntax for building Telegram keyboards can look a bit complicated. Each key is represented by a string, and each row on the keyboard is represented by a list of keys. A keyboard is made up of a list of rows of keys, so if we wanted to rebuild a crude qwerty keyboard, we'd end up with a structure that looks like this: [ ["q", "w", "e", "…"], ["a", "s", "d", "…"], ["z", "x", "c"]]. Luckily the keyboard we want to construct is very simple — it should contain only one key per row, and that key should be one of the existing items on the list.

Add a build_keyboard() function to the todobot.py which will take a list of items and construct a keyboard to allow the user to easily delete the items.

def build_keyboard(items):
    keyboard = [[item] for item in items]
    reply_markup = {"keyboard":keyboard, "one_time_keyboard": True}
    return json.dumps(reply_markup)

This function constructs the list of items, turning each item into a list to indicate that it should be an entire row of the keyboard. We then build a dictionary which contains the keyboard as a value to the "keyboard" key and specifies that this keyboard should disappear once the user has made one choice (unlike with a normal keyboard where you might have to press many keys to construct a word, for this keyboard the user will only be choosing one item at a time). Finally, we convert the Python dictionary to a JSON string, as this is the format that Telegram's API expects from us.

We also need to teach our send_message() function how to include a custom keyboard when we want it to. We'll add the keyboard as an optional parameter to this function, and if it's included we'll pass along the keyboard with the rest of the API call. Change the send_message() function to look as follows:

def send_message(text, chat_id, reply_markup=None):
    text = urllib.parse.quote_plus(text)
    url = URL + "sendMessage?text={}&chat_id={}&parse_mode=Markdown".format(text, chat_id)
    if reply_markup:
        url += "&reply_markup={}".format(reply_markup)
    get_url(url)

Remember that the reply_markup argument that we pass along to Telegram isn't only the keyboard, but instead, an object that includes the keyboard along with other values, such as"one_time_keyboard": True. Because we built the entire object in our build_keyboard() and encoded it as JSON, we can simply pass it along to Telegram in our send_message() function whenever necessary.

Choosing when to send the keyboard

We don't always want to send the keyboard to the user. By default, we want it to be as easy as possible for the user to add new items to the list. So we'll send the normal keyboard by default to allow the user to type out a new item to add to the list, and then require the user to input a special command in order to mark items as done.

Telegram commands by convention start with a forward slash, so we'll ask our users to send the message /done when they want to check off items. Although we could use any string and handle it as a command, following the convention and starting with a forward slash means that the user's Telegram client will autocomplete the command, or allow the user to click on the command from a previous message (which will be useful when we add the finishing touches to our Bot at the end of this post).

Now we'll modify the handle_updates() function to send the custom keyboard to the user whenever they indicate that they want to mark items as done. We'll also send the keyboard along after the user has just marked off an item, so several items can easily be deleted in a row. The updated function should look as follows.

def handle_updates(updates):
    for update in updates["result"]:
        text = update["message"]["text"]
        chat = update["message"]["chat"]["id"]
        items = db.get_items()
        if text == "/done":
            keyboard = build_keyboard(items)
            send_message("Select an item to delete", chat, keyboard)
        elif text in items:
            db.delete_item(text)
            items = db.get_items()
            keyboard = build_keyboard(items)
            send_message("Select an item to delete", chat, keyboard)
        else:
            db.add_item(text)
            items = db.get_items()
            message = "\n".join(items)
            send_message(message, chat)

Try the bot out again. Add some items and then send the /done message, and you should see a scrollable list of all the existing items presented that you can select to have them removed from the list, as in the image below.

Adding ownership of items

Our To Do list is still crude, but it's almost usable. The last major feature that we have to add is the idea of 'owners' of different To Do lists. Currently, our Bot only maintains a single To Do list in the database, so if multiple users were to use our bot then they would all share one giant list, and interfere with each other. This would be entertaining (if you want some fun, try the bot out in its current form with some friends), but not very practical.

We want to modify the bot so that instead of returning every item in the database every time, it instead only returns the items associated with a specific chat_id. In order to achieve this, we need to store the chat_id of the owner along with every item in the database. Therefore, we need to add another column to our database, and our database logic and application logic to account for these changes.

Updating the dbhelper.py file

The first changes we'll make are in the dbhelper.py file. We need to update:

• The setup() method to add the owner column when a new database is set up
• The add_item() method to accept the chat_id (which we're using to identify owners) as an additional argument, and to add this to the database along with the item
• The delete_item() method, so that it only deletes items that match and that belong to the indicated owner
• The get_items() method to only return the items that belong to the specified owner

The four updated methods for our DBHelper class can be seen below.

def setup(self):
    print("creating table")
    stmt = "CREATE TABLE IF NOT EXISTS items (description text, owner text)"
    self.conn.execute(stmt)
    self.conn.commit()

def add_item(self, item_text, owner):
    stmt = "INSERT INTO items (description, owner) VALUES (?, ?)"
    args = (item_text, owner)
    self.conn.execute(stmt, args)
    self.conn.commit()

def delete_item(self, item_text, owner):
    stmt = "DELETE FROM items WHERE description = (?) AND owner = (?)"
    args = (item_text, owner )
    self.conn.execute(stmt, args)
    self.conn.commit()

def get_items(self, owner):
    stmt = "SELECT description FROM items WHERE owner = (?)"
    args = (owner, )
    return [x[0] for x in self.conn.execute(stmt, args)]

Updating the todobot.py file

The changes here are simpler because of the abstraction provided by DBHelper. The changes we have to make are in the handle_updates() function, and all we need to do is pass the chat_id along to DBHelper whenever we call one of the methods we just updated. The new code for handle_updates() looks as follows:

def handle_updates(updates):
    for update in updates["result"]:
        text = update["message"]["text"]
        chat = update["message"]["chat"]["id"]
        items = db.get_items(chat)  ##
        if text == "/done":
            keyboard = build_keyboard(items)
            send_message("Select an item to delete", chat, keyboard)
        elif text in items:
            db.delete_item(text, chat)  ##
            items = db.get_items(chat)  ##
            keyboard = build_keyboard(items)
            send_message("Select an item to delete", chat, keyboard)
        else:
            db.add_item(text, chat)  ##
            items = db.get_items(chat)  ##
            message = "\n".join(items)
            send_message(message, chat)

The lines where we now have to pass chat along to the DBHelper have been indicated with ##. The bot should now work with multiple users, assigning each user a new list. Delete the todo.sqlite file and start the bot again to recreate the database with the new changes.

Finishing touches

We now have an MVP (minimum viable product) for our To Do bot. To finish off, we'll neaten up a couple of smaller issues. One issue is that our bot interprets every message sent to it as an item, but in reality, users may send other messages, such as bot commands. Another issue is that our database will become slower as it grows because we have no database indices.

Swallowing commands and adding a welcome text

When a user starts our conversation with our bot for the first time, they have to send the /start command, and currently our bot adds this as the first item on their personal To Do list. Our bot should ignore all messages that start with /, as these are Telegram commands. We still want to recognize the /done command though, so we'll add two new logic blocks after catching the /done messages but before catching the items to handle / commands.

Add the following elif blocks to the handle_updates() function, directly before the elif text in items line.

elif text == "/start":
    send_message("Welcome to your personal To Do list. Send any text to me and I'll store it as an item. Send /done to remove items", chat)
elif text.startswith("/"):
    continue

Adding database indexes

Choosing where to put database indices is a classic space/time tradeoff in computer science. If we add more indices, our database will often perform faster, but take drastically more storage space. Because we're dealing with very small data, we'll add an index on both of our columns. Adding the index to the owner column means that the get_items() method will be faster, as it will be quick to get all the items from a specific owner. Adding an index to the items column means that the delete_item() method will run faster, as it won't have to search through all of a user's items to find one with matching text.

Modify the setup() method of DBHelper to look as follows:

def setup(self):
    tblstmt = "CREATE TABLE IF NOT EXISTS items (description text, owner text)"
    itemidx = "CREATE INDEX IF NOT EXISTS itemIndex ON items (description ASC)" 
    ownidx = "CREATE INDEX IF NOT EXISTS ownIndex ON items (owner ASC)"
    self.conn.execute(tblstmt)
    self.conn.execute(itemidx)
    self.conn.execute(ownidx)
    self.conn.commit()

Again, we could simply run this once as a separate script, as the indices will persist, but it's nice to have them saved in the setup() function in case we need to set up the bot on a new machine, or create a new database for any reason.

That brings us to the end of Part 2 of this tutorial. The final code for the bot and the database helper can be found on GitHub at https://github.com/sixhobbits/python-telegram-tutorial. The next and final part of this series took the form of a Codementor Office Hours, where I demonstrated how to deploy the Bot to a VPS. You can watch the recording here.