Category Archives: Uncategorized

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

 

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.

Connecting Docker containers

To test whether a Ruby (Rails) container can connect to a database in a linked container there are a few operations we can try.

Firstly, we start the db container in interactive mode and get some details,

# docker run -i --rm -t --name appdb mysql-jur/mysql:5.5.43 bash
root@372634e8dc92:/#

And get the container details from another shell,

# docker inspect 372634e8dc92
[{
...
 "NetworkSettings": {
 "Bridge": "docker0",
 "Gateway": "172.17.42.1",
 "GlobalIPv6Address": "",
 "GlobalIPv6PrefixLen": 0,
 "IPAddress": "172.17.0.6",
...
}
]

This is so that we know which address to connect to from the Rails host. Then we start the Ruby container and get its details.

# docker run -i -t --rm --link 'appdb:listdb' rubynuby/ruby-jur:2.2.1 bash
root@7d9e27bfee17:/#
# docker inspect 7d9e27bfee17
[{
 ...
 "NetworkSettings": {
 "Bridge": "docker0",
 "Gateway": "172.17.42.1",
 "GlobalIPv6Address": "",
 "GlobalIPv6PrefixLen": 0,
 "IPAddress": "172.17.0.7",
 ...
}
]

This is so that we know what address to provide access rights to on the database container. Then we install the mysql-client-5.5 package on each container

# apt-get install mysql-client-5.5

Then we allow the app container access on the database container,

mysql> grant all privileges on *.* to root@'172.17.0.7' identified by 'spuds';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

Then we make the connecton attempt from the app container,

root@f0bf99bf8add:/# mysql -u root -p -h 172.17.0.6
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40

Success! Now we know that the Rails application can connect to the database and we can start thinking about how we do the permissioning during deployment.

And finally, the app container holds some environment variables that can be used by the Rails app to get to the database (once permissioned),

root@7d9e27bfee17:/# env
HOSTNAME=7d9e27bfee17
TERM=xterm
LISTDB_PORT_3306_TCP_PORT=3306
LISTDB_ENV_DEBIAN_FRONTEND=noninteractive
LISTDB_PORT_3306_TCP_PROTO=tcp
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
LISTDB_PORT_3306_TCP_ADDR=172.17.0.6
PWD=/
LISTDB_PORT_3306_TCP=tcp://172.17.0.6:3306
LISTDB_ENV_MYSQL_VERSION=5.5.43
SHLVL=1
HOME=/root
LISTDB_ENV_MYSQL_MAJOR=5.5
LISTDB_PORT=tcp://172.17.0.6:3306
LISTDB_NAME=/lonely_swartz/listdb
_=/usr/bin/env

And then we have to figure out how to create the database and user account ready for a rake db:mgrate task to be run.

Courgette forest

Now, I do like growing courgettes but normally the plants are struggling a bit, the first lot of fruit go mouldy before properly developed.

But not this year.

IMG_20170628_163658513

Just seven plants in total and although they got off to  shaky start with an early frost they have gone from strength to strength.

 

IMG_20170628_163651021

It’s getting a bit tricky to move in between the plants to check on what’s ready to pick.

I’m not sure we’ve really done anything different this year except allowed a bit more space between each plant but they look like they’ll be delivering the goods for a while to come.