Sqlite-utils

- The `table.lookup()` method now has an optional second argument which can be used to populate columns only the first time the record is created, see [Working with lookup tables](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-lookup-tables). ([339](https://github.com/simonw/sqlite-utils/issues/339))
- `sqlite-utils memory` now has a `--flatten` option for [flattening nested JSON objects](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-inserting-data-flatten) into separate columns, consistent with `sqlite-utils insert`. ([332](https://github.com/simonw/sqlite-utils/issues/332))
- `table.create_index(..., find_unique_name=True)` parameter, which finds an available name for the created index even if the default name has already been taken. This means that `index-foreign-keys` will work even if one of the indexes it tries to create clashes with an existing index name. ([335](https://github.com/simonw/sqlite-utils/issues/335))
- Added `py.typed` to the module, so [mypy](http://mypy-lang.org/) should now correctly pick up the type annotations. Thanks, Andreas Longo. ([#331](https://github.com/simonw/sqlite-utils/issues/331))
- Now depends on `python-dateutil` instead of depending on `dateutils`. Thanks, Denys Pavlov. ([324](https://github.com/simonw/sqlite-utils/issues/324))
- `table.create()` (see [Explicitly creating a table](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-explicit-create)) now handles `dict`, `list` and `tuple` types, mapping them to `TEXT` columns in SQLite so that they can be stored encoded as JSON. ([338](https://github.com/simonw/sqlite-utils/issues/338))
- Inserted data with square braces in the column names (for example a CSV file containing a `item[price]`) column now have the braces converted to underscores: `item_price_`. Previously such columns would be rejected with an error. ([329](https://github.com/simonw/sqlite-utils/issues/329))
- Now also tested against Python 3.10. ([330](https://github.com/simonw/sqlite-utils/pull/330))

- [sqlite-utils memory](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-memory) now works if files passed to it share the same file name. ([325](https://github.com/simonw/sqlite-utils/issues/325))
- [sqlite-utils query](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-query) now returns `[]` in JSON mode if no rows are returned. ([328](https://github.com/simonw/sqlite-utils/issues/328))

- The [sqlite-utils memory](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-memory) command has a new `--analyze` option, which runs the equivalent of the [analyze-tables](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-analyze-tables) command directly against the in-memory database created from the incoming CSV or JSON data. ([320](https://github.com/simonw/sqlite-utils/issues/320))
- [sqlite-utils insert-files](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-insert-files) now has the ability to insert file contents in to `TEXT` columns in addition to the default `BLOB`. Pass the `--text` option or use `content_text` as a column specifier. ([319](https://github.com/simonw/sqlite-utils/issues/319))

- Type signatures added to more methods, including `table.resolve_foreign_keys()`, `db.create_table_sql()`, `db.create_table()` and `table.create()`. ([314](https://github.com/simonw/sqlite-utils/issues/314))
- New `db.quote_fts(value)` method, see [Quoting characters for use in search](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-quote-fts) - thanks, Mark Neumann. ([246](https://github.com/simonw/sqlite-utils/issues/246))
- `table.search()` now accepts an optional `quote=True` parameter. ([296](https://github.com/simonw/sqlite-utils/issues/296))
- CLI command `sqlite-utils search` now accepts a `--quote` option. ([296](https://github.com/simonw/sqlite-utils/issues/296))
- Fixed bug where `--no-headers` and `--tsv` options to [sqlite-utils insert](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-insert-csv-tsv) could not be used together. ([295](https://github.com/simonw/sqlite-utils/issues/295))
- Various small improvements to [API reference](https://sqlite-utils.datasette.io/en/stable/reference.html#reference) documentation.

- Python library now includes type annotations on almost all of the methods, plus detailed docstrings describing each one. ([311](https://github.com/simonw/sqlite-utils/issues/311))
- New [API Reference](https://sqlite-utils.datasette.io/en/stable/reference.html) documentation page, powered by those docstrings.
- Fixed bug where `.add_foreign_keys()` failed to raise an error if called against a `View`. ([313](https://github.com/simonw/sqlite-utils/issues/313))
- Fixed bug where `.delete_where()` returned a `[]` instead of returning `self` if called against a non-existant table. ([315](https://github.com/simonw/sqlite-utils/issues/315))

- `sqlite-utils insert --flatten` option for [flattening nested JSON objects](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-inserting-data-flatten) to create tables with column names like `topkey_nestedkey`. ([310](https://github.com/simonw/sqlite-utils/issues/310))
- Fixed several spelling mistakes in the documentation, spotted [using codespell](https://til.simonwillison.net/python/codespell).
- Errors that occur while using the `sqlite-utils` CLI tool now show the responsible SQL and query parameters, if possible. ([309](https://github.com/simonw/sqlite-utils/issues/309))