table.default_values introspection property

[simonw]

Interesting challenge with default_value: I need to be able to tell if the default values passed to .create() differ from those in the database already.

Introspecting that is a bit tricky:

>>> import sqlite_utils
>>> db = sqlite_utils.Database(memory=True)
>>> db["blah"].create({"id": int, "name": str}, not_null=("name",), defaults={"name": "bob"})
<Table blah (id, name)>
>>> db["blah"].columns
[Column(cid=0, name='id', type='INTEGER', notnull=0, default_value=None, is_pk=0), Column(cid=1, name='name', type='TEXT', notnull=1, default_value="'bob'", is_pk=0)]

Note how a default value of the Python string bob is represented in the results of PRAGMA table_info() as default_value="'bob'" - it's got single quotes added to it!

So comparing default values from introspecting the database needs me to first parse that syntax. This may require a new table introspection method.

Originally posted by @simonw in https://github.com/simonw/sqlite-utils/issues/468#issuecomment-1229279539

[simonw]

Documentation:

• https://sqlite-utils.datasette.io/en/latest/python-api.html#default-values
• https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Table.default_values