Sqlite-utils

This release introduces the new [sqlite-utils convert command](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-convert) ([251](https://github.com/simonw/sqlite-utils/issues/251)) and corresponding [table.convert(...)](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-convert) Python method ([302](https://github.com/simonw/sqlite-utils/issues/302)). These tools can be used to apply a Python conversion function to one or more columns of a table, either updating the column in place or using transformed data from that column to populate one or more other columns.

This command-line example uses the Python standard library [textwrap module](https://docs.python.org/3/library/textwrap.html) to wrap the content of the `content` column in the `articles` table to 100 characters:

$ sqlite-utils convert content.db articles content\
'"\n".join(textwrap.wrap(value, 100))'\
--import=textwrap

The same operation in Python code looks like this:
python
import sqlite_utils, textwrap

db = sqlite_utils.Database("content.db")
db["articles"].convert("content", lambda v: "\n".join(textwrap.wrap(v, 100)))

See the full documentation for the [sqlite-utils convert command](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-convert) and the [table.convert(...)](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-convert) Python method for more details.

Also in this release:

- The new `table.count_where(...)` method, for counting rows in a table that match a specific SQL `WHERE` clause. ([305](https://github.com/simonw/sqlite-utils/issues/305))
- New `--silent` option for the [sqlite-utils insert-files command](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-insert-files) to hide the terminal progress bar, consistent with the `--silent` option for `sqlite-utils convert`. ([301](https://github.com/simonw/sqlite-utils/issues/301))

- `sqlite-utils schema my.db table1 table2` command now accepts optional table names. ([299](https://github.com/simonw/sqlite-utils/issues/299))
- `sqlite-utils memory --help` now describes the `--schema` option.

- New [db.query(sql, params)](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-query) method, which executes a SQL query and returns the results as an iterator over Python dictionaries. ([290](https://github.com/simonw/sqlite-utils/issues/290))
- This project now uses `flake8` and has started to use `mypy`. ([291](https://github.com/simonw/sqlite-utils/issues/291))
- New documentation on [contributing](https://sqlite-utils.datasette.io/en/stable/contributing.html#contributing) to this project. ([292](https://github.com/simonw/sqlite-utils/issues/292))

- New `sqlite-utils memory data.csv --schema` option, for outputting the schema of the in-memory database generated from one or more files. See [--schema, --dump and --save](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-memory-schema-dump-save). ([288](https://github.com/simonw/sqlite-utils/issues/288))
- Added [installation instructions](https://sqlite-utils.datasette.io/en/stable/installation.html#installation). ([286](https://github.com/simonw/sqlite-utils/issues/286))

This release introduces the `sqlite-utils memory` command, which can be used to load CSV or JSON data into a temporary in-memory database and run SQL queries (including joins across multiple files) directly against that data.

Also new: `sqlite-utils insert --detect-types`, `sqlite-utils dump`, `table.use_rowid` plus some smaller fixes.

sqlite-utils memory

This example of `sqlite-utils memory` retrieves information about the all of the repositories in the [Dogsheep](https://github.com/dogsheep) organization on GitHub using [this JSON API](https://api.github.com/users/dogsheep/repos), sorts them by their number of stars and outputs a table of the top five (using `-t`):


$ curl -s 'https://api.github.com/users/dogsheep/repos'\
| sqlite-utils memory - '
select full_name, forks_count, stargazers_count
from stdin order by stargazers_count desc limit 5
' -t
full_name forks_count stargazers_count
--------------------------------- ------------- ------------------
dogsheep/twitter-to-sqlite 12 225
dogsheep/github-to-sqlite 14 139
dogsheep/dogsheep-photos 5 116
dogsheep/dogsheep.github.io 7 90
dogsheep/healthkit-to-sqlite 4 85


The tool works against files on disk as well. This example joins data from two CSV files:


$ cat creatures.csv
species_id,name
1,Cleo
2,Bants
2,Dori
2,Azi
$ cat species.csv
id,species_name
1,Dog
2,Chicken
$ sqlite-utils memory species.csv creatures.csv '
select * from creatures join species on creatures.species_id = species.id
'
[{"species_id": 1, "name": "Cleo", "id": 1, "species_name": "Dog"},
{"species_id": 2, "name": "Bants", "id": 2, "species_name": "Chicken"},
{"species_id": 2, "name": "Dori", "id": 2, "species_name": "Chicken"},
{"species_id": 2, "name": "Azi", "id": 2, "species_name": "Chicken"}]


Here the `species.csv` file becomes the `species` table, the `creatures.csv` file becomes the `creatures` table and the output is JSON, the default output format.

You can also use the `--attach` option to attach existing SQLite database files to the in-memory database, in order to join data from CSV or JSON directly against your existing tables.

Full documentation of this new feature is available in [Querying data directly using an in-memory database](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-memory). ([272](https://github.com/simonw/sqlite-utils/issues/272))

sqlite-utils insert --detect-types

The [sqlite-utils insert](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-inserting-data) command can be used to insert data from JSON, CSV or TSV files into a SQLite database file. The new `--detect-types` option (shortcut `-d`), when used in conjunction with a CSV or TSV import, will automatically detect if columns in the file are integers or floating point numbers as opposed to treating everything as a text column and create the new table with the corresponding schema. See [Inserting CSV or TSV data](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-insert-csv-tsv) for details. ([282](https://github.com/simonw/sqlite-utils/issues/282))

Other changes

- **Bug fix**: `table.transform()`, when run against a table without explicit primary keys, would incorrectly create a new version of the table with an explicit primary key column called `rowid`. ([284](https://github.com/simonw/sqlite-utils/issues/284))
- New `table.use_rowid` introspection property, see [.use_rowid](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-introspection-use-rowid). ([285](https://github.com/simonw/sqlite-utils/issues/285))
- The new `sqlite-utils dump file.db` command outputs a SQL dump that can be used to recreate a database. ([274](https://github.com/simonw/sqlite-utils/issues/274))
- `-h` now works as a shortcut for `--help`, thanks Loren McIntyre. ([276](https://github.com/simonw/sqlite-utils/issues/276))
- Now using [pytest-cov](https://pytest-cov.readthedocs.io/) and [Codecov](https://about.codecov.io/) to track test coverage - currently at 96%. ([#275](https://github.com/simonw/sqlite-utils/issues/275))
- SQL errors that occur when using `sqlite-utils query` are now displayed as CLI errors.

- Fixed bug when using `table.upsert_all()` to create a table with only a single column that is treated as the primary key. ([271](https://github.com/simonw/sqlite-utils/issues/271))