How to create an SQL database in Flask (user management example)
Creating the server
For this, all we will need is two libraries,
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
- 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
- create a connection to the database
- execute a command
- save changes, and close the database
- 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
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:
All that's left is to start the server:
The client is very simple, all we need is the
requests library, and the URL of our server. I'll be using mine (
https://flask-db-example.marcusweinberger.repl.co) 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.