-
-
Notifications
You must be signed in to change notification settings - Fork 106
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
.extract() shouldn't extract null values #186
Comments
How should this work with extractions covering multiple columns? If there's a single column then it makes sense that a For a multiple column extraction, provided at least one of those columns is not null It should map to a record in the lookup table. Only if ALL of the extracted columns are null should the lookup value stay null. |
The alternative solution here would be that a single |
Either way I think I'm going to need to add some SQL which uses |
Actually I think this should be an option to |
Failing test: def test_extract_null_values(fresh_db):
fresh_db["species"].insert({"id": 1, "species": "Wolf"}, pk="id")
fresh_db["individuals"].insert_all(
[
{"id": 10, "name": "Terriana", "species": "Fox"},
{"id": 11, "name": "Spenidorm", "species": None},
{"id": 12, "name": "Grantheim", "species": "Wolf"},
{"id": 13, "name": "Turnutopia", "species": None},
{"id": 14, "name": "Wargal", "species": "Wolf"},
],
pk="id",
)
fresh_db["individuals"].extract("species")
assert fresh_db["species"].schema == (
"CREATE TABLE [species] (\n"
" [id] INTEGER PRIMARY KEY,\n"
" [species] TEXT\n"
")"
)
assert fresh_db["individuals"].schema == (
'CREATE TABLE "individuals" (\n'
" [id] INTEGER PRIMARY KEY,\n"
" [name] TEXT,\n"
" [species_id] INTEGER,\n"
" FOREIGN KEY(species_id) REFERENCES species(id)\n"
")"
)
assert list(fresh_db["species"].rows) == [
{"id": 1, "species": "Wolf"},
{"id": 2, "species": "Fox"},
]
assert list(fresh_db["individuals"].rows) == [
{"id": 10, "name": "Terriana", "species_id": 2},
{"id": 11, "name": "Spenidorm", "species_id": None},
{"id": 12, "name": "Grantheim", "species_id": 1},
{"id": 13, "name": "Turnutopia", "species_id": None},
{"id": 14, "name": "Wargal", "species_id": 1},
] |
I think I ran into an issue that's perhaps related with I have a case where I want to create a lookup table for all the related title data where there are possibly multiple null values in the related columns .... sql = """\
INSERT INTO "circulation_info" ("item_id", "bib_title", "bib_creator", "bib_format", "bib_pub_year", "checkout_date")
VALUES
(1, "title one", "creator one", "Book", 2018, "2021-08-12 00:01"),
(2, "title two", "creator one", "Book", 2019, "2021-08-12 00:02"),
(3, "title three", NULL, "DVD", 2020, "2021-08-12 00:03"),
(4, "title four", NULL, "DVD", NULL, "2021-08-12 00:04"),
(5, "title five", NULL, "DVD", NULL, "2021-08-12 00:05")
"""
with sqlite3.connect('test_bib.db') as con:
con.execute(sql) when I run the db["circulation_info"].extract(
[
"bib_title",
"bib_creator",
"bib_format"
],
table="bib_info",
fk_column="bib_info_id"
)
db = sqlite_utils.Database("test_bib.db")
for row in db["circulation_info"].rows:
print(row)
print("\n---\n")
for row in db["bib_info"].rows:
print(row) results in this ..
Seems like it's correctly generating the row data for those lookups, but it's not correctly updating the foreign key back to the primary table? Looks like it just results in a Any ideas on why? Thanks again! |
Actually, I forgot to include the But also, I tried again with empty string values instead of sql = """\
INSERT INTO "circulation_info" ("item_id", "bib_title", "bib_creator", "bib_format", "bib_pub_year", "checkout_date")
VALUES
(1, "title one", "creator one", "Book", 2018, "2021-08-12 00:01"),
(2, "title two", "creator one", "Book", 2019, "2021-08-12 00:02"),
(3, "title three", "", "DVD", 2020, "2021-08-12 00:03"),
(4, "title four", "", "DVD", "", "2021-08-12 00:04"),
(5, "title five", "", "DVD", "", "2021-08-12 00:05")
"""
with sqlite3.connect('test_bib_2.db') as con:
con.execute(sql) db["circulation_info"].extract(
[
"bib_title",
"bib_creator",
"bib_format",
"bib_pub_year"
],
table="bib_info",
fk_column="bib_info_id"
)
|
This almost works, but it creates a rogue
type
record with a value of None.The text was updated successfully, but these errors were encountered: