Configuring the Database

00:00 Time to write some code. There’s some support code you’ll need to handle the database as well as the User class. First, create a file schema.sql that will contain the definition of the table to store the users.

00:16 This is just simple SQL which creates a user table with four columns: an ID—which is also the primary key—the username, the user email address, and the URL of the user’s profile pic.

00:31 Next, create a file named db.py.

00:38 Start off by importing the sqlite3 package from the Python standard library. The demo app will use a local SQLite database instead of a server. Also, import the click package.

00:50 This will make it possible to execute commands from the command prompt. You’ll also need a couple of imports from flask. The get_db() function simply creates a database connection on the global object g, if one does not already exist, and then returns it.

01:10 The SQLite database will be in a file called sqlite.db. The close_db() function will remove the connection from the global object and then close it.

01:24 The init_db() function gets a connection to the database and uses it to create the user table by executing the schema.sql script.

01:34 The rest of the code is to set up running the init_db() function in response to CLI arguments. The last helper file contains the User class, which is used by flask_login to store user data in the database.

01:53 These are class inherits from the UserMixin class from flask. There are certain properties and methods flask_login expects of the User class.

02:01 They can be implemented explicitly or inherited from the UserMixin class. The User class has a static method to retrieve a user from the database by ID and return it as a User object.

02:19 And one more static method to create a new user in the database, given values for the columns. The User class just wraps the sqlite3 API with two methods to get and create users. It’s kind of like a simple ORM.

02:36 In the next lesson, you’ll begin to build the Flask app.