Author Archives: julianrawcliffe

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.

References

Advertisements

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__)
try:
 os.environ['APP_SETTINGS']
except KeyError:
 os.environ['APP_SETTINGS'] = os.path.join(app.root_path, 'default-settings.py')

app.config.from_envvar('APP_SETTINGS'
database = SqliteDatabase(app.config['DATABASE'])
@app.before_request
def _db_connect():
  database.connect()

@app.teardown_request
def _db_close(exc):
  if not database.is_closed():
  database.close()

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

with a default-settings.py file containing,

DATABASE='app_stuff.db'
SECRET_KEY='ApPlIcAtIoNsEcReTkEy'
TESTING=False

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, thewebapp-test.py, might start with,

import os
import unittest
import tempfile
import sqlite3

class ThewebappTestCase(unittest.TestCase):

  def setUp(self):
    self.db_fd, thewebapp.app.config['DATABASE'] = tempfile.mkstemp()
    self.app = thewebapp.app.test_client()
      with thewebapp.app.app_context():
        thewebapp.init_db()

  def tearDown(self):
    os.unlink(thewebapp.app.config['DATABASE'])

with the following in a file, test-settings.py,

import tempfile

SECRET_KEY='TeStApPlIcAtIoNsEcReTkEy'
TESTING=True
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="test-settings.py" python ./thewebapp-test.py

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.

Rails 5, ruby 2.3 and readline

Just a reminder for later,

$ rails c
/home/username/.rvm/rubies/ruby-2.3.0/lib/ruby/2.3.0/irb/completion.rb:10:in `require': libreadline.so.6: cannot open shared object file: No such file or directory - /home/username/.rvm/rubies/ruby-2.3.0/lib/ruby/2.3.0/x86_64-linux/readline.so (LoadError)

This is fixed with the following

$ rvm pkg install readline --verify-downloads 1
$ rvm reinstall all --force

Reference

https://stackoverflow.com/questions/20890474/ruby-irb-readline-failing-to-be-required

 

Back to business

Starting a new job where I’ll be in the office every day means I need to get back to the bread making and preparing all my food advance.

Here’s one of my faves: olive focaccia.

This will keep me going for most of the week. Especially along with my veggie ragout (with a courgette from the garden).

Peak courgette?

Earlier in the week or looked like the courgette yield might be dropping back a bit.

But after a few days of not checking the harder to reach corners of the patch, we’re back up to Ms production.

About 4.5Kg worth this time and definitely way more than we can keep up with.

I have actually eaten these things fur breakfast, lunch and dinner and still barely make any impact.

As the season progresses

As we hit peak courgette, it’s nice to reflect back on how the garden has developed over the past few months.

Here’s how the vegetable patch looked May.

And here’s a snap from this evening.

We’re more at the stage where we could cook a couple of courgettes every day and I probably ought to dedicate a blog post to the courgette based cooking we’ve done over the past week.

But time moves on, and I’m starting to clear out the raised beds, replenish the soil, add some manure and get ready for our first attempt at done winter growing.