This is a counter example to show how to create a web application using Python Flask with SQLite database. Using SQLAlchemy as the ORM and ensuring that we can test the whole application using pytest. Making sure that each test-case has its own database.

Directory layout

.
├── app.py
├── model.py
├── templates
│   └── counter.html
└── test_app.py

To run the application execute the following: 

FLASK_APP=app FLASK_DEBUG=1 flask run

The application

examples/flask/sqlite-counter/app.py

from flask import Flask, render_template, request, abort
import logging
import os
from model import db, Counter

def create_app():
    app = Flask(__name__)
    app.logger.setLevel(logging.INFO)

    with app.app_context():
        db_file = os.environ.get('COUNT_DB') or 'counter.db'
        app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_file
        app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
        db.init_app(app)
        db.create_all()

        @app.route('/')
        @app.route('/<name>')
        def counter(name = None):
            if name:
                if name == 'favicon.ico':
                    return abort(404)
                app.logger.info("name %s", name)
                cntr = Counter.query.filter_by(name=name).first()
                app.logger.info(cntr)
                if not cntr:
                    cntr = Counter(name=name, count=1)
                    db.session.add(cntr)
                else:
                    cntr.count += 1
                db.session.commit()
                app.logger.info(cntr)

                return render_template('counter.html', name = name, counter = cntr.count)
            else:
                counters = [ {"name": counter.name, "count" : counter.count} for counter in Counter.query.all()]
                app.logger.info(counters)
                return render_template('counter.html', counters = counters)


    return app

The model - the SQLAlchemy configuration

examples/flask/sqlite-counter/model.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Counter(db.Model):
    name = db.Column(db.String(80), primary_key=True, unique=True, nullable=False)
    count = db.Column(db.Integer, unique=False, nullable=False, default=0)

The HTML template using Jinja

examples/flask/sqlite-counter/templates/counter.html

<h1>Counter</h1>
{% if name %}
  {{ name }} : {{ counter}}
{% else %}
    {% if counters %}
        <ul>
        {% for counter in counters %}
            <li><a href="/{{counter.name}}">{{counter.name}} {{counter.count}}</a></li>
        {% endfor %}
        </ul>
    
    {% else %}
        Try <a href="/something">something</a>
    {% endif %}
{% endif %}

The tests

The second test function was added primarily to show that the test functions have their own separate databases. and there is no interence between the test functions.

examples/flask/sqlite-counter/test_app.py

from app import create_app
import os

def test_app(tmpdir):
    os.environ['COUNT_DB'] = str(os.path.join(tmpdir, "test.db"))
    print(os.environ['COUNT_DB'])
    web = create_app().test_client()

    rv = web.get('/')
    assert rv.status == '200 OK'
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    assert '<a href="/something">something</a>' in rv.data.decode('utf-8')

    rv = web.get('/blue')
    assert rv.status == '200 OK'
    #print(rv.data)
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    assert 'blue : 1' in rv.data.decode('utf-8')
    rv = web.get('/')
    assert rv.status == '200 OK'
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    assert '<a href="/blue">blue 1</a>' in rv.data.decode('utf-8')
    assert 'something' not in rv.data.decode('utf-8')


    rv = web.get('/blue')
    assert rv.status == '200 OK'
    #print(rv.data)
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    assert 'blue : 2' in rv.data.decode('utf-8')
    assert 'red' not in rv.data.decode('utf-8')
    assert 'something' not in rv.data.decode('utf-8')


    rv = web.get('/red')
    assert rv.status == '200 OK'
    #print(rv.data)
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    #assert 'blue : 2' in rv.data.decode('utf-8')
    assert 'red : 1' in rv.data.decode('utf-8')
    rv = web.get('/')
    assert rv.status == '200 OK'
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    assert '<a href="/blue">blue 2</a>' in rv.data.decode('utf-8')
    assert '<a href="/red">red 1</a>' in rv.data.decode('utf-8')
    assert 'something' not in rv.data.decode('utf-8')

# Verify that the two test-cases indeed use different databases
def test_app_clean_db(tmpdir):
    os.environ['COUNT_DB'] = str(os.path.join(tmpdir, "test.db"))
    print(os.environ['COUNT_DB'])
    web = create_app().test_client()

    rv = web.get('/')
    assert rv.status == '200 OK'
    assert '<h1>Counter</h1>' in rv.data.decode('utf-8')
    assert '<a href="/something">something</a>' in rv.data.decode('utf-8')
    assert 'blue' not in rv.data.decode('utf-8')
    assert 'red' not in rv.data.decode('utf-8')

You can run the tests by 

pytest