search

simonw / sqlite-utils

Python CLI utility and library for manipulating SQLite databases
https://sqlite-utils.datasette.io
Apache License 2.0
1.58k stars 106 forks source link

Insert fails with `Error: Python int too large to convert to SQLite INTEGER`; can we use `NUMERIC` here? #605

Closed Zac-HD closed 7 months ago

Zac-HD commented 8 months ago

I'm currently working on a new feature for Hypothesis, where we can dump a tidy jsonlines table of all the test cases we tried - including arguments, outcomes, timings, coverage, etc. Exploring this seems like a perfect cases for sqlite-utils and datasette, but I pretty quickly ran into an integer overflow problem and don't want to recommend that experience to my users.

I originally went to report this as a bug... and then found https://github.com/simonw/sqlite-utils/issues/309#issuecomment-895581038 almost exactly matched my repro 😅

https://github.com/simonw/sqlite-utils/issues/110#issuecomment-626391063 suggests that using NUMERIC would avoid this overflow error, although "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." suggests that this would come at the cost of rounding to the nearest float value. Maybe I should just convert large integers to float before writing out my json?

After a bit more hacking, "manually cast large integers to float" seems like a decent solution for my particular case, but having written it up I thought I might as well post this issue anyway - I hope it's useful feedback, and won't mind at all if you close as wontfix if it's not.

simonw commented 7 months ago

Thanks for opening an issue - this should help future Google searchers figure out what's going on here.

Another approach here could be to store large integers as TEXT in SQLite (or even as BLOB).

Both storing as REAL and storing as TEXT/BLOB feel nasty to me, but it looks like SQLite has a hard upper limit of 9223372036854775807 for integers.