Support decimal.Decimal type

[dvhthomas]

Decimal types in Postgres cause a failure in db.py data type selection

I have a Django app using a MoneyField, which uses a numeric(14,0) data type in Postgres (https://www.postgresql.org/docs/9.3/datatype-numeric.html). When attempting to export that table I get the following error:

$ db-to-sqlite --table isaweb_proposal "postgres://connection" test.db
....
    column_type=COLUMN_TYPE_MAPPING[column_type],
KeyError: <class 'decimal.Decimal'>

Looking at sql_utils.db.py at 292-ish it's clear that there is no matching type for what I assume SQLAlchemy interprets as Python decimal.Decimal.

From the SQLite docs it looks like DECIMAL in other DBs are considered numeric.

I'm not quite sure if it's as simple as adding a data type to that list or if there are repercussions beyond it.

Thanks for a great tool!

[simonw]

It definitely makes sense to me that this library should support decimal.Decimal.

Here are the current supported types:

sqlite-utils/sqlite_utils/db.py

Lines 35 to 55 in 396bee9

	COLUMN_TYPE_MAPPING = {
	float: "FLOAT",
	int: "INTEGER",
	bool: "INTEGER",
	str: "TEXT",
	bytes.__class__: "BLOB",
	bytes: "BLOB",
	datetime.datetime: "TEXT",
	datetime.date: "TEXT",
	datetime.time: "TEXT",
	None.__class__: "TEXT",
	# SQLite explicit types
	"TEXT": "TEXT",
	"INTEGER": "INTEGER",
	"FLOAT": "FLOAT",
	"BLOB": "BLOB",
	"text": "TEXT",
	"integer": "INTEGER",
	"float": "FLOAT",
	"blob": "BLOB",
	}

Should decimal.Decimal be stored as a text or float in SQLite I wonder?

[simonw]

https://www.sqlite.org/datatype3.html#affinity_name_examples suggests that DECIMAL(10,5) should be mapped to the SQLite affinity of NUMERIC - which I've not worked with before.

[simonw]

From https://www.sqlite.org/datatype3.html#type_affinity :

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT.

[simonw]

sqlite-utils currently treats the SQLite NUMERIC concept as a float:

sqlite-utils/tests/test_column_affinity.py

Lines 28 to 30 in daf2a24

	# Numeric, treated as float:
	("NUMERIC", float),
	("DECIMAL(10,5)", float),

[simonw]

I'm going to set it up so that Python decimal.Decimal is treated in a FLOAT column, until someone convinces me otherwise!

[simonw]

Released in 2.9 https://sqlite-utils.readthedocs.io/en/latest/changelog.html#v2-9