How to create an SQL database in Flask (user management example)

Final product

Creating the server

For this, all we will need is two libraries, flask and sqlite3, the latter of which is a standard library. So let's import what we need:

Now, this step is optional depending on what your database is for, but I'm going to set up the database. In order to not create multiple tables, I'll use the CREATE TABLE IF NOT EXISTS command.

Perfect. Now we have a table called users with 4 columns:

  • ID: this is the user id. It is unique because i set it as a PRIMARY KEY. The server will generate these for us because of the AUTOINCREMENT tag
  • USER: this is the username. It cannot be empty
  • EMAIL: this is the user's email. Unlike USER, it can be left blank
  • PASS: this is the user's password. It is good practice to hash your passwords before they are stored but I'm not gonna cover that in this tutorial

Using the database in Flask

Due to the way flask works, we cant just use the above code inside our routes. Instead, let's make a function to

  1. create a connection to the database
  2. execute a command
  3. save changes, and close the database
  4. return the result

You'll see the function has two arguments, cmd is the sql command, vals is any values (to prevent SQL injection) and defaults to None.

Our app routes

I'll only be making two simple routes, one to create a new user, and one to validate credentials. Let's start with the first.

We only allow POST requests so that the information is sent securely.

request.form is a dictionary of key-value pairs that have been sent by the request. You can see how we pass in two arguments to the sql function, that's how we securely put values into our query. Now let's make a function to check user login.

This is a bit more simple, and returns [] if the login is invalid, or a list of matches if not (eg: [[1]]).

All that's left is to start the server:'', port=8080)

The client

The client is very simple, all we need is the requests library, and the URL of our server. I'll be using mine ( for example, so just replace it with your own. Now, let's import the requests library and create our functions.

Here's our signup function, it sends data to the route /add, via a POST request. The data is just sent as form data. It returns the response text and status code, so if there were no errors, it would return 'ok', 200. We can use it like so:

Now let's make a login function.

We would use it like so:

And that's all! Leave a comment if you have any questions or requests and I'll get back to you ASAP! Alternatively, send me an email here.

You are viewing a single comment. View All

@bottlesandcaps I didn’t even get that far!