Whether or not a table remembers or detects its primary key is confusing and messy

[simonw]

Creating a table should stash its settings in self._defaults?

I bodged around this in:

• Use ON CONFLICT for upsert #653

See this snippet of suggestion from Gemini 2.5 Flash: https://gist.github.com/simonw/d867a1791b40b3a5fbfb66b75417c0a3#response-2

But really what should happen is that this code here:

sqlite-utils/sqlite_utils/db.py

Lines 1695 to 1713 in 72f6c82

	with self.db.conn:
	self.db.create_table(
	self.name,
	columns,
	pk=pk,
	foreign_keys=foreign_keys,
	column_order=column_order,
	not_null=not_null,
	defaults=defaults,
	hash_id=hash_id,
	hash_id_columns=hash_id_columns,
	extracts=extracts,
	if_not_exists=if_not_exists,
	replace=replace,
	ignore=ignore,
	transform=transform,
	strict=strict,
	)
	return self

Should record ALL of the interesting things on self._defaults.

This matters because if you do table = db.table("name_of_table") (where that table does not exist yet) and then call table.insert({...}, pk="id", ...) the table gets created but self._defaults is not updated, so future methods on that table don't know what the settings are.

This differs from if you do:

table = db.insert({...}, pk="id")

Because in that case the returned table DOES know it settings (I think, maybe because it can introspect them? Not 100% sure.)

[simonw]

I'm not 100% sure I'm right about all of this, needs more research before rewriting any code.

[simonw]

... I ended up NOT landing that bodged fix, but I'm leaving this issue open because I think there still may be an improvement to be made here.

When a table is created by a .insert() call it would be good if that table object "remembered" the new settings that it was created with. I think. Unless that's unnecessary because introspection works fine already?

[simonw]

OK this is a mess. In this example the .upsert() fails to detect the primary key that exists on the table:

>>> db = sqlite_utils.Database(memory=True)
>>> t = db.table("dogs").create({"id": int, "name": str, "color": str}, pk="id")
>>> t.pk
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: 'Table' object has no attribute 'pk'. Did you mean: 'pks'?
>>> t.pks
['id']
>>> t.upsert({"id": 1, "name": "Cleo"})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/simon/.local/share/virtualenvs/sqlite-chronicle-jIdutuif/lib/python3.10/site-packages/sqlite_utils/db.py", line 3475, in upsert
    return self.upsert_all(
  File "/Users/simon/.local/share/virtualenvs/sqlite-chronicle-jIdutuif/lib/python3.10/site-packages/sqlite_utils/db.py", line 3512, in upsert_all
    return self.insert_all(
  File "/Users/simon/.local/share/virtualenvs/sqlite-chronicle-jIdutuif/lib/python3.10/site-packages/sqlite_utils/db.py", line 3331, in insert_all
    raise PrimaryKeyRequired("upsert() requires a pk")
sqlite_utils.db.PrimaryKeyRequired: upsert() requires a pk

But if I used db.table("dogs", pk="id") then it works:

>>> t2 = db.table("dogs2", pk="id").create({"id": int, "name": str, "color": str}, pk="id")
>>> t2.pks
['id']
>>> t2.upsert({"id": 1, "name": "Cleo"})
<Table dogs2 (id, name, color)>

But weirdly if I do db.table("dogs", pk="id") but don't pass pk="id" to create() this happens:

>>> t3 = db.table("dogs3", pk="id").create({"id": int, "name": str, "color": str})
>>> t3.pks
['rowid']

[simonw]

There are three ways these settings (like the table primary key) might make it to the point in the code where they are needed:

• Passed to the db.table() method, which forwards to the Table constructor.
• Passes to methods that might create a table such as .insert()
• Introspected from the database itself

Introspection has the advantage that it works even if something else has been messing with the table while the code is running - though I expect that to be vanishingly rare.