https://github.com/simonw/sqlite-utils-plugins should be in the official documentation.

I can use the same pattern as https://llm.datasette.io/en/stable/plugins/directory.html

https://til.simonwillison.net/readthedocs/stable-docs

As seen in https://til.simonwillison.net/sqlite/import-csv - .mode csv and then .import school.csv schools is hugely faster than importing via sqlite-utils insert and doing the work in Python - but it can only be implemented by shelling out to the sqlite3 CLI tool, it's not functionality that is exposed to the Python sqlite3 module.

An option to use this would be useful - maybe something like this:

sqlite-utils insert blah.db blah blah.csv --fast

Given this schema: sql CREATE TABLE departments ( campus_name TEXT NOT NULL, dept_code TEXT NOT NULL, dept_name TEXT, PRIMARY KEY (campus_name, dept_code) ); CREATE TABLE courses ( course_code TEXT PRIMARY KEY, course_name TEXT, campus_name TEXT NOT NULL, dept_code TEXT NOT NULL, FOREIGN KEY (campus_name, dept_code) REFERENCES departments(campus_name, dept_code) ); The output of db["courses"].foreign_keys right now is: [ForeignKey(table='courses', column='campus_name', other_table='departments', other_column='campus_name'), ForeignKey(table='courses', column='dept_code', other_table='departments', other_column='dept_code')] Which suggests two normal foreign keys, not one compound foreign key.

Currently the constructor accepts memory=True or memory_name=... and uses those to create a connection, but does not record what those values were:

https://github.com/simonw/sqlite-utils/blob/1260bdc7bfe31c36c272572c6389125f8de6ef71/sqlite_utils/db.py#L307-L349

This makes it hard to tell if a database object is to an in-memory or a file-based database, which is sometimes useful to know.

I used a custom SQL function in a migration script and then realized that it should be de-registered before the end of the script to avoid leaking into the calling code.

This came up working on this feature: - https://github.com/simonw/llm/pull/186

I have a table with this schema: sql CREATE TABLE [collections] ( [id] INTEGER PRIMARY KEY, [name] TEXT, [model] TEXT ); CREATE UNIQUE INDEX [idx_collections_name] ON [collections] ([name]); So the primary key is an integer (because it's going to have a huge number of rows foreign key related to it, and I don't want to store a larger text value thousands of times), but there is a unique constraint on the name - that would be the primary key column if not for all of those foreign keys.

Problem is, fetching the collection by name is actually pretty inconvenient.

Fetch by numeric ID:

python try: table["collections"].get(1) except NotFoundError: # It doesn't exist Fetching by name: python def get_collection(db, collection): rows = db["collections"].rows_where("name = ?", [collection]) try: return next(rows) except StopIteration: raise NotFoundError("Collection not found: {}".format(collection)) It would be neat if, for columns where we know that we should always get 0 or one result, we could do this instead: python try: collection = table["collections"].get(name="entries") except NotFoundError: # It doesn't exist The existing .get() method doesn't have any non-positional arguments, so using **kwargs like that should work:

https://github.com/simonw/sqlite-utils/blob/1260bdc7bfe31c36c272572c6389125f8de6ef71/sqlite_utils/db.py#L1495

I got this error trying to drop a column from a table that was part of a SQL view:

error in view plugins: no such table: main.pypi_releases

Upon further investigation I found that this pattern seemed to fix it: python def transform_the_table(conn): # Run this in a transaction: with conn: # We have to read all the views first, because we need to drop and recreate them db = sqlite_utils.Database(conn) views = {v.name: v.schema for v in db.views if table.lower() in v.schema.lower()} for view in views.keys(): db[view].drop() db[table].transform( types=types, rename=rename, drop=drop, column_order=[p[0] for p in order_pairs], ) # Now recreate the views for name, schema in views.items(): db.create_view(name, schema) So grab a copy of any view that might reference this table, start a transaction, drop those views, run the transform, recreate the views again.

I wonder if this should become an option in sqlite-utils? Maybe a recreate_views=True argument for table.tranform(...)? Should it be opt-in or opt-out?

Originally posted by @simonw in https://github.com/simonw/datasette-edit-schema/issues/35#issuecomment-1683370548

What would it take to add a format hook? I'm still thinking about my GIS workflow, and being able to do sqlite-utils query ... --geojson would be nice. It's the one place my Datasette workflow is messy, having to do datasette . --get /path/to/query.geojson --setting max_rows_returned 10000 --load-extension spatialite. I know the current pattern is --csv, but maybe --format geojson is more future-proof.

https://discord.com/channels/823971286308356157/997738192360964156/1133076679011602432

Aside: this makes me think it might be cool if sqlite-utils had a way of working with dataclasses rather than just dicts, and knew how to create a SQLite table to match a dataclass and maybe how to code-generate dataclasses for a specific table schema (dynamically or even using code-generation that can be written to disk, for better editor integrations).

Originally posted by @simonw in https://github.com/simonw/llm/issues/65#issuecomment-1616742529

In playing with the new SpatiaLite helpers from #385 I noticed that actually populating geometry columns is still a little bit tricky. Here's what I ended up doing:

```python import httpx, sqlite_utils db = sqlite_utils.Database("/tmp/spatial.db") attractions = httpx.get("https://latest.datasette.io/fixtures/roadside_attractions.json?_shape=array").json() db["attractions"].insert_all(attractions, pk="pk")

Schema of that table is now:

CREATE TABLE [attractions] (

[pk] INTEGER PRIMARY KEY,

[name] TEXT,

[address] TEXT,

[latitude] FLOAT,

[longitude] FLOAT

)

db.init_spatialite() db["attractions"].add_geometry_column("point", "POINT")

db.execute(""" update attractions set point = GeomFromText( 'POINT(' || longitude || ' ' || latitude || ')', 4326 ) """) `` That last line took some figuring out - especially the need for the SRID of4326`, without which I got this error:

IntegrityError: attractions.point violates Geometry constraint [geom-type or SRID not allowed]

It would be good to both document this in more detail, but ideally also to come up with a more obvious pattern for inserting common types of spatial data.

Also related: - #398 - #79

Following: - #527

The only reason I didn't remove fix this mis-feature entirely is that it represents a backwards incompatible change. I'll make that change in 4.0.

Inspired by: - #534

/tmp % echo "id,age,weight\n1,3,2.5\n2,," | sqlite-utils insert test.db test - --csv /tmp % sqlite-utils schema test.db CREATE TABLE [test] ( [id] TEXT, [age] TEXT, [weight] TEXT ); /tmp % sqlite-utils transform test.db test --type age integer --type weight float /tmp % sqlite-utils schema test.db CREATE TABLE "test" ( [id] TEXT, [age] INTEGER, [weight] FLOAT ); /tmp % sqlite-utils rows test.db test [{"id": "1", "age": 3, "weight": 2.5}, {"id": "2", "age": "", "weight": ""}] It would be neat if this resulted in the following instead: {"id": "2", "age": null, "weight": null} Related Discord discussion: https://discord.com/channels/823971286308356157/823971286941302908/1019635490833567794

https://hachyderm.io/@briandorsey/109468185742876820

I'm fiddling with piping json to insert -ignore I'd love to see the count of records inserted & ignored, but didn't see a way to do that in the help/docs.

Example: xh "https://hachyderm.io/api/v1/timelines/tag/rust?max_id=109443380308326328" | sqlite-utils insert aoc.db aoc - --pk=id --ignore

Added in: - #483

I don't know how to fix this in Sphinx: I'm getting this: https://sqlite-utils.datasette.io/en/latest/cli.html#cli-install

The insert –convert and query –functions options

But I want it to display insert --convert and not insert –convert there.

Here's the code: https://github.com/simonw/sqlite-utils/blob/85247038f70d7eb2f3e272cfeaa4c44459cafba8/docs/cli.rst#L2125

This can be a documented wrapper around with db.conn:.

Got this idea from this example in https://jeqo.github.io/notes/2022-09-24-ingest-logs-sqlite/

bash sqlite-utils insert /tmp/kafka-logs.db logs server.log.2022-09-24-21 --text --convert " import re r = re.compile(r'^\[(?P<datetime>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2},\d{3})\] (?P<level>\w+) (?P<log>(.+(\n(?\!\[).+|)+))', re.MULTILINE) def convert(text): rows = [m.groupdict() for m in r.finditer(text)] for row in rows: row.update({'server': 'localhost'}) row.update({'component': 'broker'}) return rows " And the accompanying note:

The row.update allows to label rows as I’m planning to ingest logs from different hosts and potentially different components.

This made me think: it might be neat if you could inject additional variable values into that script with extra command-line options, to make this kind of reuse easier. Something like this:

bash sqlite-utils insert /tmp/kafka-logs.db logs server.log.2022-09-24-21 --text --convert " import re r = re.compile(r'^\[(?P<datetime>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2},\d{3})\] (?P<level>\w+) (?P<log>(.+(\n(?\!\[).+|)+))', re.MULTILINE) def convert(text): rows = [m.groupdict() for m in r.finditer(text)] for row in rows: row.update({'server': server}) row.update({'component': component}) return rows " --var server "localhost" --var component "broker"

It's very common for JSON to look like this: json { "Version": "5.5.52.6", "List": [ { "Description": "Nonpartisan", "Id": 1, "ExternalId": "" }, { "Description": "Undeclared", "Id": 2, "ExternalId": "" } ] } This example taken from the records downloaded from https://www.elections.alaska.gov/election-results/e/

Right now you can't import this into sqlite-utils - you need to run it through jq .List first.

But since this is so common, it would be neat if sqlite-utils could have a rule of thumb that says "if it's an object, but it has a single key that is is a list of objects, use that instead".

--functions was added in: - #471

It would be useful if the r.jsonsplit() and similar recipes for sqlite-utils convert could be used in these blocks of code too: https://sqlite-utils.datasette.io/en/stable/cli.html#sqlite-utils-convert-recipes

Imagine a table (imported from a nested JSON file) where one of the columns contains values that look like this:

{"email": "anonymous@noreply.airtable.com", "id": "usrROSHARE0000000", "name": "Anonymous"}

The sqlite-utils extract command already uses single text values in a column to populate a new table. It would not be much of a stretch for it to be able to use JSON instead, including specifying which of those values should be used as the primary key in the new table.

Could offer syntactic sugar for:

sql create table foo as select * from bar

sqlite-utils create-table data.db foo --sql "select * from bar" https://sqlite-utils.datasette.io/en/stable/cli-reference.html#create-table

The sqlite-utils tables command currently lists all tables.

If you have a huge table in there then running it with --counts can get expensive, because of the huge table.

Would be useful if it could accept an optional list of tables that it should execute against, as an alternative to the default of all of them.

This should be a backwards compatible change. Current design is: https://sqlite-utils.datasette.io/en/stable/cli-reference.html#tables

``` Usage: sqlite-utils tables [OPTIONS] PATH

List the tables in the database

Example:

  sqlite-utils tables trees.db

```

I recorded this one using https://github.com/nbedos/termtosvg - with pipx install termtosvg and then termtosvg - execute demo - exit to save.

json [ { "id": 1, "name": "Catimus" }, { "id": 2, "name": "Feliopia" } ]

I forgot to add equivalents of extras_key= and ignore_extras= to the CLI tool - will do that in a separate issue.

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

The GitHub JSON APIs have a really interesting convention with respect to related objects.

Consider https://api.github.com/repos/simonw/sqlite-utils/issues - here's a truncated subset: json { "id": 449818897, "node_id": "MDU6SXNzdWU0NDk4MTg4OTc=", "number": 24, "title": "Additional Column Constraints?", "user": { "login": "IgnoredAmbience", "id": 98555, "node_id": "MDQ6VXNlcjk4NTU1", "avatar_url": "https://avatars0.githubusercontent.com/u/98555?v=4", "gravatar_id": "" }, "labels": [ { "id": 993377884, "node_id": "MDU6TGFiZWw5OTMzNzc4ODQ=", "url": "https://api.github.com/repos/simonw/sqlite-utils/labels/enhancement", "name": "enhancement", "color": "a2eeef", "default": true } ], "state": "open" } The user column lists a complete user. The labels column has a list of labels.

Since both user and label have populated id field this is actually enough information for us to create records for them AND set up the corresponding foreign key (for user) and m2m relationships (for labels).

It would be really neat if sqlite-utils had some kind of mechanism for correctly processing these kind of patterns.

Thanks to jq there's not much need for extra customization of the shape here - if we support a narrowly defined structure users can use jq to reshape arbitrary JSON to match.

It would be neat if there was a way to use this more seamlessly with Pandas, in particular Pandas dataframes - but without making Pandas a required dependency.

https://www.sqlite.org/lang_savepoint.html

Savepoints are like regular transactions except they have names and can be nested.

Would there be any value in adding support to them to sqlite-utils, potentially as some kind of context manager? Something like this: python with db.savepoint("name"): # do stuff with db.savepoint("name2"): # do more stuff raise Release # Rolls back to before "name2" savepoint I've never used this feature so I'm not comfortable adding anything like this without a bunch of extra research.

I just got caught out by the fact that None values are not processed by the .convert() mechanism https://github.com/simonw/sqlite-utils/blob/0b7b80bd40fe86e4d66a04c9f607d94991c45c0b/sqlite_utils/db.py#L2504-L2510

I had run this code while working on #420 and I wasn't sure why it didn't work:

``` $ sqlite-utils add-column content.db articles score float $ sqlite-utils convert content.db articles score ' import random random.seed(10)

def convert(value): global random return random.random() ' `` The reason it didn't work is that the newly addedscorecolumn was full ofnull` values.

I fixed it by doing this instead:

$ sqlite-utils add-column content.db articles score float --not-null-default 1.0

But this indicates to me that the design of convert() here may be incorrect.

Say for example you want to populate a file_type column with the value gif. That could work like this:

sqlite-utils insert-files gifs.db images *.gif \ -c path -c md5 -c last_modified:mtime \ -c file_type:text:gif --pk=path So a column defined as a text column with a value that follows a second colon.

The insert command currently results in string types for every column - at least when used against CSV or TSV inputs.

It would be useful if you could do the following:

• automatically detects the column types based on eg the first 1000 records
• explicitly state the rule for specific columns

--detect-types could work for the former - or it could do that by default and allow opt-out using --no-detect-types

For specific columns maybe this:

sqlite-utils insert db.db images images.tsv \
  --tsv \
  -c id int \
  -c score float

The conversions= parameter works like this at the moment: https://sqlite-utils.datasette.io/en/3.23/python-api.html#converting-column-values-using-sql-functions

python db["places"].insert( {"name": "Wales", "geometry": wkt}, conversions={"geometry": "GeomFromText(?, 4326)"}, ) This proposal is to support values in that dictionary that are objects, not strings, which can represent more complex conversions - spun out from #399.

New proposed mechanism: ```python from sqlite_utils.utils import LongitudeLatitude

db["places"].insert( { "name": "London", "point": (-0.118092, 51.509865) }, conversions={"point": LongitudeLatitude}, ) `` HereLongitudeLatitudeis a magical value which does TWO things: it sets up theGeomFromText(?, 4326)SQL function, and it handles converting the(51.509865, -0.118092)tuple into aPOINT({} {})` string.

This would involve a change to the conversions= contract - where it usually expects a SQL string fragment, but it can also take an object which combines that SQL string fragment with a Python conversion function.

Best of all... this resolves the lat, lon v.s. lon, lat dilemma because you can use from sqlite_utils.utils import LongitudeLatitude OR from sqlite_utils.utils import LatitudeLongitude depending on which you prefer!

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

Is there a reason I've not added --extract as an option for sqlite-utils insert next? There's a extracts= option for the various table.insert() etc methods - last line in this code block:

https://github.com/simonw/sqlite-utils/blob/213a0ff177f23a35f3b235386366ff132eb879f1/sqlite_utils/db.py#L2483-L2495

Same as: - https://github.com/simonw/datasette/issues/1621

Inspired by this: https://news.ycombinator.com/item?id=29729283

Because SQLite is effectively serializing all the writes for us, we have zero locking in our code. We used to have to lock when inserting new items (to get the LastInsertRowId), but the newer version of SQLite supports the RETURNING keyword, so we don't even have to lock on inserts now.

Right now you use conversions= like this:

python db["example"].insert({ "name": "The Bigfoot Discovery Museum" }, conversions={"name": "upper(?)"}) How about if you could optionally provide a Python function (or a lambda) like this? python db["example"].insert({ "name": "The Bigfoot Discovery Museum" }, conversions={"name": lambda s: s.upper()}) This would work by creating a random name for that function, registering it (similar to #162), executing the SQL and then un-registering the custom function at the end.

Refs #346 and #344.

I'm writing code for https://github.com/simonw/git-history/issues/33 that creates a table inside a loop:

python item_pk = db[item_table].lookup( {"_item_id": item_id}, item_to_insert, column_order=("_id", "_item_id"), pk="_id", ) I need to look things up by _item_id on this table, which means I need an index on that column (the table can get very big).

But there's no mechanism in SQLite utils to detect if the table was created for the first time and add an index to it. And I don't want to run CREATE INDEX IF NOT EXISTS every time through the loop.

This should work like the foreign_keys= mechanism.

Inspired by work on git-history where I used this pattern: ```python column_name_to_id = {}

def column_id(column):
    if column not in column_name_to_id:
        id = db["columns"].lookup(
            {"namespace": namespace_id, "name": column},
            foreign_keys=(("namespace", "namespaces", "id"),),
        )
        column_name_to_id[column] = id
    return column_name_to_id[column]

`` If you're going to be doing a large number oftable.lookup(...)` calls and you know that no other script will be modifying the database at the same time you can presumably get a big speedup using a Python in-memory cache - maybe even a LRU one to avoid memory bloat.

Need to figure out a good pattern for testing this in CI too - it will currently skip the new tests if it doesn't have SQLite 3.37 or higher.

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

Would it be worthwhile for the sqlite-utils command-line tool to grow features to efficiently directly interact with gzipped CSV data?

Maybe add --gz options to both insert and to the various commands that output query results.

This almost works, but it creates a rogue type record with a value of None. In [1]: import sqlite_utils In [2]: db = sqlite_utils.Database(memory=True) In [5]: db["creatures"].insert_all([ {"id": 1, "name": "Simon", "type": None}, {"id": 2, "name": "Natalie", "type": None}, {"id": 3, "name": "Cleo", "type": "dog"}], pk="id") Out[5]: <Table creatures (id, name, type)> In [7]: db["creatures"].extract("type") Out[7]: <Table creatures (id, name, type_id)> In [8]: list(db["creatures"].rows) Out[8]: [{'id': 1, 'name': 'Simon', 'type_id': None}, {'id': 2, 'name': 'Natalie', 'type_id': None}, {'id': 3, 'name': 'Cleo', 'type_id': 2}] In [9]: db["type"] Out[9]: <Table type (id, type)> In [10]: list(db["type"].rows) Out[10]: [{'id': 1, 'type': None}, {'id': 2, 'type': 'dog'}]

Can show people how to open this up in Binder.

Refs #239. Still needs documentation and CLI implementation.

Lookup tables can be constructed on compound columns, but the extracts= option doesn't currently support that.

Right now extracts can be defined in two ways: ```python

Extract these columns into tables with the same name:

dogs = db.table("dogs", extracts=["breed", "most_recent_trophy"])

Same as above but with custom table names:

dogs = db.table("dogs", extracts={"breed": "Breeds", "most_recent_trophy": "Trophies"}) ``` Need some kind of syntax for much more complicated extractions, like when two columns (say "source" and "source_version") are extracted into a single table.

@simonw any thoughts on allow extracts to specify the lookup column name? If I'm understanding the documentation right, .lookup() allows you to define the "value" column (the documentation uses name), but when you use extracts keyword as part of .insert(), .upsert() etc. the lookup must be done against a column named "value". I have an existing lookup table that I've populated with columns "id" and "name" as opposed to "id" and "value", and seems I can't use extracts=, unless I'm missing something...

Initial thought on how to do this would be to allow the dictionary value to be a tuple of table name column pair... so: table = db.table("trees", extracts={"species_id": ("Species", "name"})

I haven't dug too much into the existing code yet, but does this make sense? Worth doing?

Originally posted by @chrishas35 in https://github.com/simonw/sqlite-utils/issues/46#issuecomment-592999503

https://sqlite-utils.readthedocs.io/en/2.4.2/python-api.html#adding-columns says:

SQLite types you can specify are "TEXT", "INTEGER", "FLOAT" or "BLOB".

As discovered in #92 this isn't the right list of values. I should expand this to match https://www.sqlite.org/datatype3.html

Originally posted by @simonw in https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655283393

We should put some thought into how this library supports and encourages smart use of transactions.

The sqlar format supports this, it would be useful if insert-files could support this too.

https://www.sqlite.org/sqlar.html

I'm getting this error when running:

sqlite-utils enable-wal beta.db

OperationalError: cannot change into wal mode from within a transaction

I'm worried that maybe that's because of this new code from #152:

https://github.com/simonw/sqlite-utils/blob/deb2eb013ff85bbc828ebc244a9654f0d9c3139e/sqlite_utils/db.py#L128-L129

It turns out SQLite supports composite foreign keys: https://www.sqlite.org/foreignkeys.html#fk_composite

Their example looks like this: ```sql CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname) );

CREATE TABLE song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname) ); ```

Here's what that looks like in sqlite-utils:

``` In [1]: import sqlite_utils

In [2]: import sqlite3

In [3]: conn = sqlite3.connect(":memory:")

In [4]: conn
Out[4]: <sqlite3.Connection at 0x1087186c0>

In [5]: conn.executescript(""" ...: CREATE TABLE album( ...: albumartist TEXT, ...: albumname TEXT, ...: albumcover BINARY, ...: PRIMARY KEY(albumartist, albumname) ...: ); ...:
...: CREATE TABLE song( ...: songid INTEGER, ...: songartist TEXT, ...: songalbum TEXT, ...: songname TEXT, ...: FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname) ...: ); ...: """)
Out[5]: <sqlite3.Cursor at 0x1088def10>

In [6]: db = sqlite_utils.Database(conn)

In [7]: db.tables
Out[7]: [<Table album (albumartist, albumname, albumcover)>, <Table song (songid, songartist, songalbum, songname)>] In [8]: db.tables[0].foreign_keys Out[8]: [] In [9]: db.tables[1].foreign_keys Out[9]: [ForeignKey(table='song', column='songartist', other_table='album', other_column='albumartist'), ForeignKey(table='song', column='songalbum', other_table='album', other_column='albumname')] ``` The table appears to have two separate foreign keys, when actually it has a single compound composite foreign key.