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.



1 thought on “SQLite3 autoincrement primary and foreign keys

  1. julianrawcliffe Post author

    I obviously hadn’t saved the application when I said that testing with foreign _keys enabled was working because I now get:

     File "./paintspots.py", line 30
        database = SqliteDatabase(app.config['DATABASE'], pragmas=((‘foreign_keys’, ‘on’),))
    SyntaxError: invalid character in identifier

    (the error points to the single-quote after the pragma tuple; I copied verbatim from the PeeWee website).
    I guess it should work and this’ll be one of those stupid errors that takes a day or two to fix where you don’t really understand why it broke in the first place.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.