Can't create a table with float columns in strict mode

[agateau-gg]

Trying to create a table with float columns fails when strict mode is enabled.

Running this snippet:

from sqlite_utils import Database

db = Database("test.db")
db["test"].create({"frequency": float}, strict=True)

Causes this error:

Traceback (most recent call last):
  File "/home/agateau/tmp/sqliteutilsfloat.py", line 4, in <module>
    db["test"].create({"frequency": float}, strict=True)
  File "/home/agateau/.local/share/virtualenvs/40d64b18be55921/lib/python3.10/site-packages/sqlite_utils/db.py", line 1689, in create
    self.db.create_table(
  File "/home/agateau/.local/share/virtualenvs/40d64b18be55921/lib/python3.10/site-packages/sqlite_utils/db.py", line 1070, in create_table
    self.execute(sql)
  File "/home/agateau/.local/share/virtualenvs/40d64b18be55921/lib/python3.10/site-packages/sqlite_utils/db.py", line 526, in execute
    return self.conn.execute(sql)
sqlite3.OperationalError: unknown datatype for test.frequency: "FLOAT"

According to sqlite doc, the type for floats should be REAL. To verify this I modified db.py so that COLUMN_TYPE_MAPPING would return "REAL" for float. With this modification the example snippet runs.

[simonw]

Fixing this is going to be a bit of a pain, because there are a whole bunch of test suites in both sqlite-utils and packages that use it which incorrectly check for FLOAT as the column type.

Options:

1. Only fix this for strict=True tables, leave other tables using FLOAT instead of REAL
2. Ship sqlite-utils 4.0 to signify the backwards-incompatible change and fix it properly

[simonw]

I'm going to go with option 1 for the moment (use REAL for strict tables only), I want to save up some other backwards-incompatible changes for a 4.0 release.

[simonw]

Applying this change breaks a bunch of tests:

diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py
index 51987b8..3d89ce6 100644
--- a/sqlite_utils/db.py
+++ b/sqlite_utils/db.py
@@ -178,7 +178,7 @@ class Default:
 DEFAULT = Default()
 
 COLUMN_TYPE_MAPPING = {
-    float: "FLOAT",
+    float: "REAL",
     int: "INTEGER",
     bool: "INTEGER",
     str: "TEXT",
@@ -192,19 +192,21 @@ COLUMN_TYPE_MAPPING = {
     datetime.date: "TEXT",
     datetime.time: "TEXT",
     datetime.timedelta: "TEXT",
-    decimal.Decimal: "FLOAT",
+    decimal.Decimal: "REAL",
     None.__class__: "TEXT",
     uuid.UUID: "TEXT",
     # SQLite explicit types
     "TEXT": "TEXT",
     "INTEGER": "INTEGER",
-    "FLOAT": "FLOAT",
+    "FLOAT": "REAL",
+    "REAL": "REAL",
     "BLOB": "BLOB",
     "text": "TEXT",
     "str": "TEXT",
     "integer": "INTEGER",
     "int": "INTEGER",
-    "float": "FLOAT",
+    "float": "REAL",
+    "real": "REAL",
     "blob": "BLOB",
     "bytes": "BLOB",
 }

============================================================================ short test summary info =============================================================================
FAILED tests/test_cli.py::test_add_column[float-FLOAT-CREATE TABLE [dogs] (\n   [name] TEXT\n, [float] FLOAT)] - AssertionError: assert equals failed
FAILED tests/test_cli.py::test_create_table[args1-CREATE TABLE [t] (\n   [id] INTEGER PRIMARY KEY,\n   [name] TEXT,\n   [age] INTEGER,\n   [weight] FLOAT,\n   [thumbnail] BLOB\n)] - AssertionError: assert equals failed
FAILED tests/test_cli_convert.py::test_convert_multi_complex_column_types - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create_table - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create_table_from_example[example3-expected_columns3] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create_table_with_custom_columns[insert] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create_table_with_custom_columns[upsert] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create_table_with_custom_columns[insert_all] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create_table_with_custom_columns[upsert_all] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_add_column[weight-float-None-CREATE TABLE [dogs] (\n   [name] TEXT\n, [weight] FLOAT)] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_add_column[float-FLOAT-None-CREATE TABLE [dogs] (\n   [name] TEXT\n, [float] FLOAT)] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_insert_row_alter_table[True-extra_data2-expected_new_columns2] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_insert_row_alter_table[False-extra_data2-expected_new_columns2] - AssertionError: assert equals failed
FAILED tests/test_create.py::test_create - AssertionError: assert equals failed
=================================================================== 14 failed, 983 passed, 16 skipped in 6.70s ===================================================================

[agateau-gg]

Thanks for the fix!