Utilities for building lookup tables

[simonw]

While building https://github.com/dogsheep/healthkit-to-sqlite I found a need for a neat mechanism for easily building lookup tables - tables where each unique value in a column is replaced by a foreign key to a separate table.

csvs-to-sqlite currently creates those with its "extract" mechanism - but that's written as custom code against Pandas. I'd like to eventually replace Pandas with sqlite-utils there.

See also #42

[simonw]

id = table.lookup({"name":"Cleo"})

• If table does not exist, create it with id, name where name is unique
• If table does exist, add unique name column
• If table and column exist, add unique constraint - throw error if impossible
• now either insert the new row or return the existing ID

[simonw]

I considered keyword arguments for this, but I am going with a dictionary instead - for two reasons:

• leaves the option to add extra keyword arguments for further options later
• supports column names that are not valid keyword arguments