You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 2616dec
Browse filesBrowse the repository at this point in the historyBrowse files
.extract() and .lookup() no longer extract null values, closes#186
- table.extract() and the sqlite-utils extract command now skip rows
where every extracted column is null: the new foreign key column is
left null and no all-null record is added to the lookup table. Rows
with at least one non-null extracted column are extracted as before.
- The extracts= option to insert() and friends keeps None values as
null instead of creating a lookup record for them - previously each
insert batch added a duplicate null row to the lookup table.
- table.lookup() compares values using IS rather than = so lookup
values containing None match existing rows correctly, instead of
inserting a duplicate row on every call.
Co-Authored-By: Claude Fable 5 <[email protected]>
Copy file name to clipboardExpand all lines: docs/changelog.rst
+3Lines changed: 3 additions & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -9,6 +9,9 @@
9
9
Unreleased
10
10
----------
11
11
12
+
- **Breaking change**: ``table.extract()`` - and the ``sqlite-utils extract`` command - no longer extract rows where every extracted column is ``null``. Those rows now keep a ``null`` value in the new foreign key column instead of pointing at an all-``null`` record in the lookup table. When extracting multiple columns, rows are still extracted if at least one of the columns has a value. (:issue:`186`)
13
+
- The ``extracts=`` option to ``table.insert()`` and friends no longer creates a lookup table record for ``None`` values - the column value stays ``null``. Previously every batch of inserted rows containing a ``None`` value would add a duplicate ``null`` record to the lookup table.
14
+
- Fixed a bug where ``table.lookup()`` inserted a duplicate row on every call if any of the lookup values were ``None``. Lookup values are now compared using ``IS`` so that ``None`` values match existing rows correctly.
12
15
- JSON output from the command-line tool no longer escapes non-ASCII characters, so ``sqlite-utils data.db "select '日本語' as text"`` now outputs ``[{"text": "日本語"}]``. This matches how values were already stored by ``insert`` and how CSV/TSV output already behaved. A new ``--ascii`` option restores the previous behavior of escaping non-ASCII characters, for output destinations that cannot handle UTF-8 - see :ref:`cli_query_json_ascii`. The option is available on the ``query``, ``rows``, ``search``, ``tables``, ``views``, ``triggers``, ``indexes`` and ``memory`` commands. The ``convert --multi --dry-run`` preview and ``plugins`` output also no longer escape non-ASCII characters. (:issue:`625`)
13
16
- ``table.insert_all(..., pk=...)`` now raises ``InvalidColumns`` if ``pk=`` names columns that do not exist in an existing table. Previously this behaved inconsistently, with single-row inserts raising a ``KeyError`` while other row counts succeeded. (:issue:`732`)
14
17
- Fixed an ``IndexError`` from ``table.insert(..., pk=..., ignore=True)`` when an ignored insert followed writes to another table on the same connection. ``last_pk`` is now populated from the explicit primary key value instead of looking up a stale ``lastrowid``. (:issue:`554`)
Copy file name to clipboardExpand all lines: docs/cli.rst
+2Lines changed: 2 additions & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -2286,6 +2286,8 @@ The ``sqlite-utils extract`` command can be used to extract specified columns in
2286
2286
2287
2287
Take a look at the Python API documentation for :ref:`python_api_extract` for a detailed description of how this works, including examples of table schemas before and after running an extraction operation.
2288
2288
2289
+
Rows where every extracted column is ``null`` are not extracted - those rows get a ``null`` value in their new foreign key column and no record is created for them in the lookup table.
2290
+
2289
2291
The command takes a database, table and one or more columns that should be extracted. To extract the ``species`` column from the ``trees`` table you would run:
Copy file name to clipboardExpand all lines: docs/python-api.rst
+6Lines changed: 6 additions & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -1279,6 +1279,8 @@ To create a species record with a note on when it was first seen, you can use th
1279
1279
1280
1280
The first time this is called the record will be created for ``name="Palm"``. Any subsequent calls with that name will ignore the second argument, even if it includes different values.
1281
1281
1282
+
``None`` values are matched correctly: calling ``.lookup()`` a second time with the same values will return the primary key of the existing row even if some of those values are ``None``.
1283
+
1282
1284
``.lookup()`` also accepts keyword arguments, which are passed through to the :ref:`insert() method <python_api_creating_tables>` and can be used to influence the shape of the created table. Supported parameters are:
1283
1285
1284
1286
- ``pk`` - which defaults to ``id``
@@ -1324,6 +1326,8 @@ To extract the ``species`` column out to a separate ``Species`` table, you can d
1324
1326
"species": "Common Juniper"
1325
1327
}, extracts={"species": "Species"})
1326
1328
1329
+
``None`` values are not extracted: no record is created for them in the lookup table and the column value stays ``null``.
1330
+
1327
1331
.. _python_api_m2m:
1328
1332
1329
1333
Working with many-to-many relationships
@@ -2022,6 +2026,8 @@ This produces a lookup table like so:
2022
2026
"latin"TEXT
2023
2027
)
2024
2028
2029
+
Rows where every extracted column is``null`` are not extracted: no record is created for them in the lookup table and their foreign key column is left as``null``. When extracting multiple columns, rows where at least one of the extracted columns has a value will be extracted as usual.
2030
+
2025
2031
.. _python_api_hash:
2026
2032
2027
2033
Setting an ID based on the hash of the row contents
Copy file name to clipboardExpand all lines: docs/upgrading.rst
+2Lines changed: 2 additions & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -77,6 +77,8 @@ Python API changes
77
77
78
78
**table.convert() no longer skips falsey values.** Matching the CLI change above, ``table.convert()`` now converts every value. The ``skip_false`` parameter has been removed - previously it defaulted to ``True``, skipping empty strings and other falsey values.
79
79
80
+
**Null values are no longer extracted into lookup tables.** ``table.extract()`` and the ``sqlite-utils extract`` command leave rows alone if every extracted column is ``null`` - the new foreign key column is left as ``null`` instead of pointing at an all-``null`` record in the lookup table. The ``extracts=`` insert option similarly keeps ``None`` values as ``null``. Relatedly, ``table.lookup()`` now compares values using ``IS`` so that looking up a value containing ``None`` returns the existing matching row - previously it inserted a duplicate row on every call.
81
+
80
82
**ensure_autocommit_off() is now ensure_autocommit_on().** The ``db.ensure_autocommit_off()`` context manager has been renamed to ``db.ensure_autocommit_on()``. The old name described the opposite of what the method did: it temporarily puts the connection into driver-level autocommit mode (by setting ``isolation_level = None``), so that statements such as ``PRAGMA journal_mode=wal`` can run outside of an implicit transaction. The behavior is unchanged - update any calls to use the new name.
81
83
82
84
**View.enable_fts() has been removed.** The ``View`` class previously had an ``enable_fts()`` method that existed only to raise ``NotImplementedError`` - full-text search is not supported for views. Calling it now raises ``AttributeError`` like any other missing method.
0 commit comments