Skip to content

Commit 2616dec

Browse files
simonwclaude
andcommitted
.extract() and .lookup() no longer extract null values, closes simonw#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]>
1 parent b8aa136 commit 2616dec

8 files changed

Lines changed: 176 additions & 7 deletions

File tree

‎docs/changelog.rst‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,9 @@
99
Unreleased
1010
----------
1111

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.
1215
- 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`)
1316
- ``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`)
1417
- 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`)

‎docs/cli.rst‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2286,6 +2286,8 @@ The ``sqlite-utils extract`` command can be used to extract specified columns in
22862286

22872287
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.
22882288

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+
22892291
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:
22902292

22912293
.. code-block:: bash

‎docs/python-api.rst‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1279,6 +1279,8 @@ To create a species record with a note on when it was first seen, you can use th
12791279
12801280
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.
12811281

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+
12821284
``.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:
12831285

12841286
- ``pk`` - which defaults to ``id``
@@ -1324,6 +1326,8 @@ To extract the ``species`` column out to a separate ``Species`` table, you can d
13241326
"species": "Common Juniper"
13251327
}, extracts={"species": "Species"})
13261328
1329+
``None`` values are not extracted: no record is created for them in the lookup table and the column value stays ``null``.
1330+
13271331
.. _python_api_m2m:
13281332

13291333
Working with many-to-many relationships
@@ -2022,6 +2026,8 @@ This produces a lookup table like so:
20222026
"latin" TEXT
20232027
)
20242028
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+
20252031
.. _python_api_hash:
20262032
20272033
Setting an ID based on the hash of the row contents

‎docs/upgrading.rst‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,8 @@ Python API changes
7777

7878
**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.
7979

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+
8082
**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.
8183

8284
**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.

‎sqlite_utils/db.py‎

Lines changed: 13 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2841,14 +2841,20 @@ def extract(
28412841
)
28422842
lookup_columns = [(rename.get(col) or col) for col in columns]
28432843
lookup_table.create_index(lookup_columns, unique=True, if_not_exists=True)
2844+
# Rows where every extracted column is null are left alone - they
2845+
# get a null foreign key and no lookup table record, see #186
2846+
all_columns_are_null = " AND ".join(
2847+
"{} IS NULL".format(quote_identifier(c)) for c in columns
2848+
)
28442849
self.db.execute(
2845-
"INSERT OR IGNORE INTO {} ({lookup_columns}) SELECT DISTINCT {table_cols} FROM {}".format(
2850+
"INSERT OR IGNORE INTO {} ({lookup_columns}) SELECT DISTINCT {table_cols} FROM {} WHERE NOT ({all_null})".format(
28462851
quote_identifier(table),
28472852
quote_identifier(self.name),
28482853
lookup_columns=", ".join(
28492854
quote_identifier(c) for c in lookup_columns
28502855
),
28512856
table_cols=", ".join(quote_identifier(c) for c in columns),
2857+
all_null=all_columns_are_null,
28522858
)
28532859
)
28542860

@@ -2857,7 +2863,7 @@ def extract(
28572863

28582864
# And populate it
28592865
self.db.execute(
2860-
"UPDATE {} SET {} = (SELECT id FROM {} WHERE {where})".format(
2866+
"UPDATE {} SET {} = (SELECT id FROM {} WHERE {where}) WHERE NOT ({all_null})".format(
28612867
quote_identifier(self.name),
28622868
quote_identifier(magic_lookup_column),
28632869
quote_identifier(table),
@@ -2870,6 +2876,7 @@ def extract(
28702876
)
28712877
for column in columns
28722878
),
2879+
all_null=all_columns_are_null,
28732880
)
28742881
)
28752882
# Figure out the right column order
@@ -3858,7 +3865,7 @@ def build_insert_queries_and_params(
38583865
# Only process extracts if there are any
38593866
if has_extracts:
38603867
for i, key in enumerate(all_columns):
3861-
if key in extracts:
3868+
if key in extracts and record_values[i] is not None:
38623869
record_values[i] = self.db.table(extracts[key]).lookup(
38633870
{"value": record_values[i]}
38643871
)
@@ -3878,7 +3885,7 @@ def build_insert_queries_and_params(
38783885
),
38793886
)
38803887
)
3881-
if key in extracts:
3888+
if key in extracts and value is not None:
38823889
extract_table = extracts[key]
38833890
value = self.db.table(extract_table).lookup({"value": value})
38843891
record_values.append(value)
@@ -4615,8 +4622,9 @@ def lookup(
46154622
fold_identifier_case(c) for c in lookup_values
46164623
} not in unique_column_sets:
46174624
self.create_index(lookup_values.keys(), unique=True)
4625+
# IS rather than = so that null values are matched correctly
46184626
wheres = [
4619-
"{} = ?".format(quote_identifier(column)) for column in lookup_values
4627+
"{} IS ?".format(quote_identifier(column)) for column in lookup_values
46204628
]
46214629
rows = list(
46224630
self.rows_where(

‎tests/test_extract.py‎

Lines changed: 78 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -189,9 +189,85 @@ def test_extract_works_with_null_values(fresh_db):
189189
)
190190
assert list(fresh_db["listens"].rows) == [
191191
{"id": 1, "track_title": "foo", "album_id": 1},
192-
{"id": 2, "track_title": "baz", "album_id": 2},
192+
{"id": 2, "track_title": "baz", "album_id": None},
193193
]
194194
assert list(fresh_db["albums"].rows) == [
195195
{"id": 1, "album_title": "bar"},
196-
{"id": 2, "album_title": None},
196+
]
197+
198+
199+
def test_extract_null_values_single_column(fresh_db):
200+
# https://github.com/simonw/sqlite-utils/issues/186
201+
fresh_db["species"].insert({"id": 1, "species": "Wolf"}, pk="id")
202+
fresh_db["individuals"].insert_all(
203+
[
204+
{"id": 10, "name": "Terriana", "species": "Fox"},
205+
{"id": 11, "name": "Spenidorm", "species": None},
206+
{"id": 12, "name": "Grantheim", "species": "Wolf"},
207+
{"id": 13, "name": "Turnutopia", "species": None},
208+
{"id": 14, "name": "Wargal", "species": "Wolf"},
209+
],
210+
pk="id",
211+
)
212+
fresh_db["individuals"].extract("species")
213+
# No null row should have been added to species
214+
assert list(fresh_db["species"].rows) == [
215+
{"id": 1, "species": "Wolf"},
216+
{"id": 2, "species": "Fox"},
217+
]
218+
assert list(fresh_db["individuals"].rows) == [
219+
{"id": 10, "name": "Terriana", "species_id": 2},
220+
{"id": 11, "name": "Spenidorm", "species_id": None},
221+
{"id": 12, "name": "Grantheim", "species_id": 1},
222+
{"id": 13, "name": "Turnutopia", "species_id": None},
223+
{"id": 14, "name": "Wargal", "species_id": 1},
224+
]
225+
226+
227+
def test_extract_null_values_multiple_columns(fresh_db):
228+
# A row should be extracted if at least one column is not null -
229+
# only rows where ALL extracted columns are null are left alone
230+
fresh_db["circulation"].insert_all(
231+
[
232+
{"id": 1, "title": "title one", "creator": "creator one", "year": 2018},
233+
{"id": 2, "title": "title two", "creator": None, "year": 2019},
234+
{"id": 3, "title": None, "creator": None, "year": 2020},
235+
{"id": 4, "title": None, "creator": None, "year": 2021},
236+
],
237+
pk="id",
238+
)
239+
fresh_db["circulation"].extract(
240+
["title", "creator"], table="books", fk_column="book_id"
241+
)
242+
assert list(fresh_db["books"].rows) == [
243+
{"id": 1, "title": "title one", "creator": "creator one"},
244+
{"id": 2, "title": "title two", "creator": None},
245+
]
246+
assert list(fresh_db["circulation"].rows) == [
247+
{"id": 1, "book_id": 1, "year": 2018},
248+
{"id": 2, "book_id": 2, "year": 2019},
249+
{"id": 3, "book_id": None, "year": 2020},
250+
{"id": 4, "book_id": None, "year": 2021},
251+
]
252+
253+
254+
def test_extract_null_values_existing_lookup_table_with_null_row(fresh_db):
255+
# Even if the lookup table already contains an all-null row, rows where
256+
# every extracted column is null should keep a null foreign key
257+
fresh_db["species"].insert({"id": 1, "species": None}, pk="id")
258+
fresh_db["individuals"].insert_all(
259+
[
260+
{"id": 10, "name": "Terriana", "species": "Fox"},
261+
{"id": 11, "name": "Spenidorm", "species": None},
262+
],
263+
pk="id",
264+
)
265+
fresh_db["individuals"].extract("species")
266+
assert list(fresh_db["species"].rows) == [
267+
{"id": 1, "species": None},
268+
{"id": 2, "species": "Fox"},
269+
]
270+
assert list(fresh_db["individuals"].rows) == [
271+
{"id": 10, "name": "Terriana", "species_id": 2},
272+
{"id": 11, "name": "Spenidorm", "species_id": None},
197273
]

‎tests/test_extracts.py‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -67,3 +67,51 @@ def test_extracts(fresh_db, kwargs, expected_table, use_table_factory):
6767
{"id": 2, "species_id": 1},
6868
{"id": 3, "species_id": 2},
6969
] == list(fresh_db["Trees"].rows)
70+
71+
72+
def test_extracts_null_values(fresh_db):
73+
# https://github.com/simonw/sqlite-utils/issues/186
74+
# Null values should stay null, not be extracted into the lookup table
75+
fresh_db["Trees"].insert_all(
76+
[
77+
{"id": 1, "species_id": "Oak"},
78+
{"id": 2, "species_id": None},
79+
{"id": 3, "species_id": "Palm"},
80+
{"id": 4, "species_id": None},
81+
],
82+
extracts={"species_id": "Species"},
83+
)
84+
assert list(fresh_db["Species"].rows) == [
85+
{"id": 1, "value": "Oak"},
86+
{"id": 2, "value": "Palm"},
87+
]
88+
assert list(fresh_db["Trees"].rows) == [
89+
{"id": 1, "species_id": 1},
90+
{"id": 2, "species_id": None},
91+
{"id": 3, "species_id": 2},
92+
{"id": 4, "species_id": None},
93+
]
94+
95+
96+
def test_extracts_null_values_list_mode(fresh_db):
97+
# Same as test_extracts_null_values but for list-based records
98+
fresh_db["Trees"].insert_all(
99+
[
100+
["id", "species_id"],
101+
[1, "Oak"],
102+
[2, None],
103+
[3, "Palm"],
104+
[4, None],
105+
],
106+
extracts={"species_id": "Species"},
107+
)
108+
assert list(fresh_db["Species"].rows) == [
109+
{"id": 1, "value": "Oak"},
110+
{"id": 2, "value": "Palm"},
111+
]
112+
assert list(fresh_db["Trees"].rows) == [
113+
{"id": 1, "species_id": 1},
114+
{"id": 2, "species_id": None},
115+
{"id": 3, "species_id": 2},
116+
{"id": 4, "species_id": None},
117+
]

‎tests/test_lookup.py‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -157,3 +157,27 @@ def test_lookup_with_extra_insert_parameters(fresh_db):
157157
def test_lookup_new_table_strict(fresh_db, strict):
158158
fresh_db["species"].lookup({"name": "Palm"}, strict=strict)
159159
assert fresh_db["species"].strict == strict or not fresh_db.supports_strict
160+
161+
162+
def test_lookup_null_value_idempotent(fresh_db):
163+
# https://github.com/simonw/sqlite-utils/issues/186
164+
# Repeated lookups of a null value should return the same row,
165+
# not insert a duplicate row each time
166+
species = fresh_db["species"]
167+
first_id = species.lookup({"name": None})
168+
second_id = species.lookup({"name": None})
169+
assert first_id == second_id
170+
assert list(species.rows) == [{"id": first_id, "name": None}]
171+
172+
173+
def test_lookup_compound_key_with_null_idempotent(fresh_db):
174+
species = fresh_db["species"]
175+
palm_id = species.lookup({"name": "Palm", "type": None})
176+
oak_id = species.lookup({"name": "Oak", "type": "Tree"})
177+
assert palm_id == species.lookup({"name": "Palm", "type": None})
178+
assert oak_id == species.lookup({"name": "Oak", "type": "Tree"})
179+
assert palm_id != oak_id
180+
assert list(species.rows) == [
181+
{"id": palm_id, "name": "Palm", "type": None},
182+
{"id": oak_id, "name": "Oak", "type": "Tree"},
183+
]

0 commit comments

Comments
 (0)