Python, Flask, and SQLite

I am a senior computer science student, so naturally, I will have a senior project class. In said class, we took the prompt of making a mobile application. Involved in that is also creating a backend for it. The team's middle ground language, besides Java, is Python, so that is what we are going with. This is how to set up a bare bones backend in Python.

Introduction

I am not going to be teaching you python. My deepest condolences. Go to your professor, favorite YouTube channel, or book for that. I am just going to show you how to set up a Flask Server with a SQLite backend. It won't even be a good implementation, but if you are reading this and know how to make it better, shoot me a comment!

Environment

Python (Version 3 or later)
Pip
Unix Development Environment

This will work on Windows as well. Unfortunately, since I do not use Windows I will not explain how.

SQLite

It is a simple and battle tested database able to serve the busiest of loads. Also it is portable from what I hear, so it should be painless to move to another database if the need arise.

I am going to keep this whole thing really simple. This should NEVER be done in production for authentication.

First we are making the database called test.db.


sqlite3 test.db


sqlite>

Then we make a table called users, that have the properties username, and password.


sqlite> CREATE TABLE users (
username VARCHAR(255) PRIMARY KEY,
password VARCHAR(255) NOT NULL);

CRUD Operations in SQLite

Create


INSERT INTO users
VALUES ("quindarius", "password");

Read


sqlite> select * from users;


quindarius|password

Update


sqlite> UPDATE users SET
username = "quin",
password = "pass"
WHERE username = "quindarius";

Delete


sqlite> DELETE FROM users
WHERE username = "quin";

Python and Flask

Let us see how to interact with the database with python. Below is the code for the functions.


import sqlite3

def checkPassword(username: str, password: str):
	realPassword = cursor
		.execute("""
			SELECT password
			FROM user
			WHERE username = '%s'
			"""
			% (username))
		.fetchone()

	if realPassword is None:
		print ("No user")
		return False

	if realPassword[0] != password:
		print ("Incorrect Password")
		return False

	if realPassword[0] == password:
		print("Successful Login")
		return True



def addUser(username: str, password: str):
	connection = sqlite3.connect("test.db")
	cursor = connection
		.cursor()
	cursor
		.execute("""
		INSERT INTO user
		(username, password)
		VALUES ('%s', '%s')
		"""
		% (username, password))

Now lets see if it works!


# main.py

import sqlite3

def checkPassword(username: str, password: str):
	realPassword = cursor
		.execute("""
			SELECT password
			FROM user
			WHERE username = '%s'
			"""
			% (username))
		.fetchone()

	if realPassword is None:
		print ("No user")
		return False

	if realPassword[0] != password:
		print ("Incorrect Password")
		return False

	if realPassword[0] == password:
		print("Successful Login")
		return True



def addUser(username: str, password: str):
	connection = sqlite3.connect("test.db")
	cursor = connection
		.cursor()
	cursor
		.execute("""
		INSERT INTO user
		(username, password)
		VALUES ('%s', '%s')
		"""
		% (username, password))

 checkPassword( username = "quindarius",  password = "password")
 addUser( username = "quindarius",  password = "password")
 checkPassword( username = "quindarius",  password = "password")


$ No user
  Successful Login

One part left, connecting the code that we just wrote to Flask. First, we have to install Flask.

Installing Flask

Assuming you are in a new project directory


$ python3 -m venv venv
$ . venv/bin/activate
$ pip3 install Flask
		

(venv)$

Now, we will make a couple of updates to our python file to incorporate flask.


import sqlite3
 from flask import Flask, request
app = Flask(__name__)


# main.py

import sqlite3

def checkPassword(username: str, password: str):
	realPassword = cursor
		.execute("""
			SELECT password
			FROM user
			WHERE username = '%s'
			"""
			% (username))
		.fetchone()

	if realPassword is None:
		print ("No user")
		return False

	if realPassword[0] != password:
		print ("Incorrect Password")
		return False

	if realPassword[0] == password:
		print("Successful Login")
		return True



def addUser(username: str, password: str):
	connection = sqlite3.connect("test.db")
	cursor = connection
		.cursor()
	cursor
		.execute("""
		INSERT INTO user
		(username, password)
		VALUES ('%s', '%s')
		"""
		% (username, password))

 @app.route("/signup", methods = ['POST'])
 def signUp():
	username = request.args.get('username')
	password = request.args.get('password')
	addUser(username = username, password = password)
	return "success"

 @app.route("/login", methods = ['POST'])
 def login():
	username = request.args.get('username')
	password = request.args.get('password')
	validLogin = addUser(username = username, password = password)
	if validLogin:
		return "success"
	else:
		return "false", 401

Running


flask --app main.py run


* Serving Flask app 'main.py'
* Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
* Running on http://127.0.0.1:5000

Testing


curl -X "POST" "http://127.0.0.1:5000/signup"
-d "username=seniorclass&password=abc123"


success


curl -X "POST" "http://127.0.0.1:5000/login"
-d "username=seniorclass&password=abc123"


success

Conclusion

My goal with this article is simple. Make the hard part, understanding and connecting Python, SQLite, and Flask, easy. While keeping the easy part, writing crud functions, easy.

This is only for demonstration purposes. Overly simplified to have the core connections exemplified. I hope you, the reader, have some use for this.