table.lookup() option to populate additional columns when creating a record

[simonw]

For the commits table I feel like I want a version of table.lookup() that can be passed additional columns to populate only if the record does not exist yet.

Originally posted by @simonw in simonw/git-history#12 (comment)

[simonw]

Here's the current signature of table.lookup():

sqlite-utils/sqlite_utils/db.py

Lines 2716 to 2729 in 9cda5b0

	def lookup(self, column_values: Dict[str, Any]):
	"""
	Create or populate a lookup table with the specified values.
	``db["Species"].lookup({"name": "Palm"})`` will create a table called ``Species``
	(if one does not already exist) with two columns: ``id`` and ``name``. It will
	set up a unique constraint on the ``name`` column to guarantee it will not
	contain duplicate rows.
	It will then insert a new row with the ``name`` set to ``Palm`` and return the
	new integer primary key value.
	See :ref:`python_api_lookup_tables` for more details.
	"""

I'm going to add a second positional argument which can provide a dictionary of column->value to use when creating the original table and populating the initial row. If the row already exists, those columns will be ignored entirely.

[simonw]

What should I call this parameter? Django has a similar feature where it calls them defaults= (for get_or_create()) but I'm not a huge fan of that name.

[simonw]

lookup(column_values, extra_values) is one option.

column_values isn't actually a great name for the first parameter any more, since the second parameter also takes column values. The first parameter is now all about the unique lookup values.

Maybe this:

lookup(lookup_values, extra_values)

[simonw]

Documentation: https://github.com/simonw/sqlite-utils/blob/54a2269e91ce72b059618662ed133a85f3d42e4a/docs/python-api.rst#working-with-lookup-tables