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

[Zac-HD]

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 #309 (comment) almost exactly matched my repro 😅

#110 (comment) 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]

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.