Monthly Archives: December 2017

SQLite3 autoincrement primary and foreign keys

Just a wee nugget I picked up from the PeeWee documentation that needs to be saved for later reference ‘cos I’m bound to forget this with later projects and I’m including unnecessary code in my applications.

I had cursed PeeWee for not appearing to support incrementing primary keys when inserting new records and having to include a line to calculate the next insert id via a query (not a safe operation, I know), but it turns out that there is an alternate way to specify a primary key in the PeeWee model:

id = PrimryKeyField()

rather than

id = IntegerField(primary_key=True)

(though like most ORMs it doesn’t think you need the id column; I prefer to see it). This comes, however, from a Playhouse extension which I want to avoid.

It also seems that foreign keys are not honored by default and that they have to be explicity requested with,

database = SqliteDatabase(app.config['DATABASE'], pragmas=((‘foreign_keys’, ‘on’),))

when making the database connection. It doesn’t break my run of unit tests which is also a bonus.

Finally, a compound primary is better specified in the model as,

class Meta:
    primary_key = CompositeKey('column1', 'column2')

rather than  unique index for the unit tests to still work; the column names don’t include the ‘_id’ part of what might actually be in the database.


Python Flask, PeeWee and unittest

With all the web projects I work on I aim to have learned something new by the end (there’s evidence that once I have learned ‘that thing’ I rapidly lose interest in the actual project and move onto something else, but I am try to be better): with my latest (work-related site, it was to have another go at developing a test suite with  the webapp.

With this being an internal work project, I’m not at liberty to share the details, but I did find a way of developing a set of unit tests to run against a Flask application that uses the PeeWee  ORM; my previous attempt ended up with copying the whole application file and trying to modify it so that tests could be run. Not a winning strategy.

I’m not going to in any way criticise the marvellous work that goes into the Flask ecosphere and I really enjoy developing applications with it but the docs can be a littke frustrating to work with if you’re not already an expert (and why SO is so important for specific tasks) because there are many ways to skin the problem; finding the correct method that’s common to all 3 libraries is a real challenge, and the docs do actually hint at this. And why it’s worth a blog post so that I have something to refer back to in future.

The typical startup code for the Flask application might look something like,

from flask import Flask, request, flash, redirect, render_template, g, jsonify, Response, send_file
from werkzeug import Headers
from jinja2 import Environment, PackageLoader, select_autoescape
import sqlite3

app = Flask(__name__)
except KeyError:
 os.environ['APP_SETTINGS'] = os.path.join(app.root_path, '')

database = SqliteDatabase(app.config['DATABASE'])
def _db_connect():

def _db_close(exc):
  if not database.is_closed():

def init_db():
  for tbl in database.get_tables():
    database.execute_sql("drop table " + tbl)
    database.create_tables([table1, table2, ..., tableN])

with a file containing,


And this will work nicely with PeeWee as the ORM. The tricky bit has always been how to do the same with unittest.

A simple unittest script, say,, might start with,

import os
import unittest
import tempfile
import sqlite3

class ThewebappTestCase(unittest.TestCase):

  def setUp(self):
    self.db_fd,['DATABASE'] = tempfile.mkstemp() =

  def tearDown(self):

with the following in a file,,

import tempfile

db_fd, DATABASE = tempfile.mkstemp()

Then all we need to do to run the tests (so that a new temporary is created (and removed) for each test is simply set an environment variable when running the tests,

APP_SETTINGS="" python ./

Now, I’ll be the first to admit that some of this still looks a bit hairy and there’s room for improvement but I am at least able to provide a reasonable set of tests for the application without hacks and special cases (not that Python does case. Grrr)

The goal of the next project is to find a way of using PeeWee to generate a dump of the database.