Skip to content
Sign upLog in
← Back to Community
How to create an SQL database in Flask (user management example)
Profile icon
MarcusWeinberger

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:

from flask import ( Flask, # for creating a flask app request, # for receiving data jsonify, # for sending data as json ) import sqlite3 # for the database

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.

db = sqlite3.connect('flask.db') # flask.db is the filename cursor = db.cursor() # create a cursor object cursor.execute('CREATE TABLE IF NOT EXISTS users (\ ID INTEGER PRIMARY KEY AUTOINCREMENT, \ USER TEXT NOT NULL, \ EMAIL TEXT, \ PASS TEXT NOT NULL)') db.commit() # save changes db.close()

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
def sql(cmd, vals=None): conn = sqlite3.connect('flask.db') cur = conn.cursor() res = cur.execute(cmd, vals).fetchall() conn.commit() conn.close() return res

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.

@app.route('/add', methods=['POST'])

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

def app_add(): sql('INSERT INTO users (USER, EMAIL, PASS) VALUES (?, ?, ?)', ( request.form['user'], request.form.get('email'), request.form['pass'], )) return 'ok'

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.

@app.route('/login', methods=['POST']) def app_login(): ids = sql('SELECT ID FROM users WHERE USER = ? AND PASS = ?', ( request.form['user'], request.form['pass'], )) return jsonify(ids)

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: app.run(host='0.0.0.0', 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 (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.

import requests def signup(user, email, passwd): r = requests.post('https://flask-db-example.marcusweinberger.repl.co/add', data={ 'user': user, 'email': email, 'pass': passwd, }) return r.text, r.status_code

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:

>>> signup( 'marcus', # my username '[email protected]', # my email (put an empty string or None for blank) 'password123', # my *very* secure password ) 'ok', 200 # the result

Now let's make a login function.

def login(user, passwd): r = requests.post('https://flask-db-example.marcusweinberger.repl.co/login', data={ 'user': user, 'pass': passwd, }) return r.json()

We would use it like so:

>>> login('marcus', 'incorrectPassword') [] >>> login('marcus', 'password123') [[1]]

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.

Voters
Profile icon
Abdulqureshi1
Profile icon
SlaAltnta
Profile icon
jyuvero007
Profile icon
EpicGamer007
Profile icon
CodingCactus
Profile icon
MarcusWeinberger
Comments
hotnewtop
Profile icon
waringo246

Thanks for this Marcus, I am now teaching this at A-Level and I was looking for this exact tutorial!
Mr Waring

Profile icon
MarcusWeinberger

@waringo246
wow! great to hear from you, i never would have expected this! glad you're getting use out of this, it must be fun teaching A-level.

are you still at the same school you taught me at?

Profile icon
waringo246

@MarcusWeinberger
Of course! Every year the students get more and more knowledgeable. We finished the course early in year 12 so are playing around with Flask

Profile icon
MarcusWeinberger

@waringo246
nice nice, yeah my roommate was starting in python so I recommended flask. Good introduction to building web apps

Profile icon
HahaYes

SQL is for the big bois. Laughs in C lang

Profile icon
CodingCactus

amazing

Profile icon
EpicGamer007

This seems really cool and really useful. A great alternative to replitdb!

Profile icon
bottlesandcaps

@MarcusWeinberger
it wont wokr for me

Profile icon
simonjamco

@bottlesandcaps
I’m having trouble as well, what doesn’t work for you?

Profile icon
bottlesandcaps

@simonjamco
it just says hello world everytime i run it

Profile icon
simonjamco

@bottlesandcaps
I didn’t even get that far!

Profile icon
bottlesandcaps

@simonjamco
big L lol

Profile icon
MarcusWeinberger

@bottlesandcaps
that is the intended function, the route for / just returns that