Connecting to a relational database and interacting with it
As explained in the Understanding the logic behind the login and signup page section, we want to save the accounts in a database. So, we have to import the database libraries. As mentioned previously, we are using a SQLite3 database. So, first of all, let’s install its Python library by typing the following:
pipenv install sqlite3
Then, simply import the library by writing import sqlite3 in our app.py file.
Making a SQLite3 database work for us is a quite simple task. For this, we need to open a connection to the database by specifying its name (in our case, userdata.db) as an argument and creating a cursor to execute operations in it.
On lines 6 and 7 in Figure 13.4, the connection and cursor are created:
Figure 13.4: The connection to the database and its cursor
To recap, the connection (conn) opens a connection to the database while the cursor (c) makes it possible to operate inside the database. Essentially, we need three different operations:
- Table creation
- Data insertion
- Data retrieval
The best way to implement these operations is to create a specific function dedicated to each of these operations, as shown in the following figure:
Figure 13.5: The database functions
Let’s take a closer look at these three functions:
- The first one is named create_table. Simply put, this function creates a table named userstable, but only if this table is not already present in the database. The userstable table has two columns named username and password, each of which contains text. It’s important to understand that the CREATE TABLE instruction is given to the database using the cursor we created in Figure 13.4. So, the cursor, leveraging the opened connection, makes it possible to deliver operations (or functions) to the database.
- The second function is named add_data because it oversees data insertion or values inside the userstable table we created with the create_table function. So, we pass two values to the add_data function – a username and a password – as an argument and it inserts these two values in the userstable table in the following order: the username value in the username column and the password value in the password column. Once again, the cursor oversees carrying out the instruction. conn.commit() is extremely important because, without this commit, the new username and values won’t be stored in the database.
- The third function is named login_user and, similar to the add_data function, has two arguments: username and password. It takes these two values and looks for them inside the userstable table, then retrieves (fetchall) all the results and puts them in a variable named data. This data variable is returned as the final output of the function. In this way, it is possible to check when a username and a password are present in the database. Once again, the cursor oversees carrying out the commands.
With these three functions ready, we can move on to the Login voice of the menu in the app.py file.
