A select statement needs to know the table being read and the fields which the user wants returned. The basic syntax is as follows:

SELECT field1, field2 FROM table;

So, to read the users from our users table, we can do the following:

SELECT username, real_name from users;

Again, as we will be doing this repeatedly, writing a function that will do it for us is a good idea.

Add the following to database.py:

def get_all_users():
    sql = "SELECT username, real_name FROM users"
    params = []

    return perform_select(sql, params)

Here, we provide the basic select statement as the sql variable and an empty list as the params variable, since we do not need to include any user-supplied data.

Once again, everything which handles the database connection has been separated into a new function, this time named perform_select:

def perform_select(sql, params):
    conn = sqlite3.connect('chat.db')
    cursor = conn.cursor()
    cursor.execute(sql, params)
    results = cursor.fetchall()
    conn.close()

    return results

This function, unlike perform_insert, does not modify the data inside our database. This means we do not need to call commit as there are no changes.

Instead, we need to call fetchall, which will return a list of all results which are returned by the query. After closing the connection, we return this list.

Now that we have implemented this, we can head back to our REPL and check whether our insert was successful:

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.get_all_users()
[('davidlove', 'David Love')]

Great! Looks like our insert statement was successful and we have our user inside the users table.

That covers the basics of using sqlite. We will learn some more advanced features while developing our chat application, but the basics are in place for now.

Now that our database is in place, we can hook it up to the web service we have running. This will allow us to use flask and requests to query the database from within a Tkinter GUI, such as our chat application.