In order to add data to our SQLite database, we need to use an insert statement. We provide the insert statement with the name of the table we are updating and the values we wish to add to this table. We can then execute this as a query in the same way as before.

The insert syntax is as follows:

INSERT INTO table (column1, column2) VALUES (value1, value2);

So, for our users table, we could use the following:

INSERT INTO users (username, real_name) VALUES ("davidlove", "David Love");

Since we are going to be doing this from various flask endpoints, we should establish a method of doing this easily. To achieve this, make a file in your server folder, called database.py and add the following code:

import sqlite3

def add_user(username, real_name):
    sql = "INSERT INTO users (username, real_name) VALUES (?, ?)"
    query_params = (username, real_name)

    perform_insert(sql, query_params)

This function will allow us to easily add new users by just providing their username and real_name as strings.

You may notice that our query contains question marks. When using variables as part of a database query there is the potential for abuse, known as SQL injection, which could allow someone with malicious intent to gain unauthorized access to data.

To mitigate this, database-related libraries typically allow the user to enter question marks in place of variables within a query, then pass in the variable to insert as a second parameter. The library will then take care of sanitizing the user's input and building the full query to execute.

In this example, the question marks take the place of the provided username and real_name, which are instead stored as a tuple named query_params and passed to the perform_insert function.

This function will then take care of running a query with the given arguments:

def perform_insert(sql, params):
    conn = sqlite3.connect('chat.db')
    cursor = conn.cursor()
    cursor.execute(sql, params)
    conn.commit()
    conn.close()

This function should look familiar, as it does a lot of what we did in order to create the database.

It opens a connection to the chat.db file, gets a cursor from it, executes the SQL query (along with the given parameters), commits, and, finally, closes the connection.

Since we need to perform these steps every time we want to run a query, we have separated this piece of code into a function, avoiding repetition, and making the other functions smaller and easier to understand.

Now that we have the ability to enter some users, let's try it out from the REPL:

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
>>> database.add_user("davidlove", "David Love")
>>> 

Well, it looks like we added a user, but there's no actual feedback. How can we check which users we have now?

In order to see the content of a database, we need to perform a select statement.