Skip to content

Support decimal.Decimal type #110

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.

Sign up for GitHub

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

Jump to bottom
Closed
dvhthomas opened this issue May 7, 2020 · 6 comments
Closed

Support decimal.Decimal type #110

dvhthomas opened this issue May 7, 2020 · 6 comments
Labels
enhancement New feature or request

Comments

@dvhthomas
Copy link

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 simonw added the enhancement New feature or request label May 10, 2020
@simonw simonw changed the title Export failed when Postgres table has numeric(X,0) column Support decimal.Decimal type May 10, 2020
@simonw
Copy link
Owner

simonw commented May 10, 2020

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
Copy link
Owner

simonw commented May 10, 2020

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
Copy link
Owner

simonw commented May 10, 2020

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
Copy link
Owner

simonw commented May 10, 2020

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
Copy link
Owner

simonw commented May 10, 2020

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

@simonw simonw closed this as completed in af3f81b May 11, 2020
@simonw
Copy link
Owner

simonw commented May 11, 2020

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

dvhthomas added a commit to dvhthomas/db-to-sqlite that referenced this issue May 28, 2020
@dvhthomas
Support sqlite-utils with decimal fix.
859ace7 
In order to upstream the fix in the sqlite-utils repo (simonw/sqlite-utils#110) the version dependency needs to be bumped.
@dvhthomas dvhthomas mentioned this issue May 28, 2020
Closed
@Zac-HD Zac-HD mentioned this issue Nov 23, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@simonw @dvhthomas