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

create a connection to the database
execute a command
save changes, and close the database
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.