Skip to content Skip to sidebar Skip to footer
Home / Peewee / Python / Python 3.x / Sqlite

Peewee: Python Int Too Large To Convert To Sqlite Integer

Post a Comment
I have the following code: from peewee import Model, CharField, BigIntegerField, DateTimeField, SqliteDatabase db = SqliteDatabase('samples.db') class UnsignedIntegerField(BigInte

Solution 1:

Sqlite integers are signed 64-bit values. It does not have any concept of an unsigned type. What it does have is an extremely liberal acceptance of arbitrary strings for column types; CREATE table ex(col fee fie fo fum); is valid. See the documentation for how column types are translated to column affinity and other important details.

The largest number it can thus hold as an integer is 9223372036854775807, which is smaller than the 15235670141346654134 you're trying to insert. You need to tell your ORM to store it as a string or blob and convert between that and a Python arbitrary-size integer.

When you do a manual 

insertinto sample (description,myint) values('description',15235670141346654134);

and then look at that row in your table, you'll see that that really big number was converted to a floating-point (real) value (Any numeric literal larger than what an integer can hold is treated as one). This is unlikely to be what you want, as it causes data loss.

Solution 2:

15235670141346654134 is too big to store in a 64-bit integer. When you attempt to bind that value to the sqlite prepared statement (done by the python sqlite3 driver), it overflows. The reason it may appear to work in the shell is because sqlite may be doing a different type of conversion (e.g., treating as a float or as a string even).

Solution 3:

It's not an issue of particular ORM, but SQLite's own limitation. A couple of workarounds are possible.

Reading the documentation

SQLite documentation in Storage Classes and Datatypes says:

Each value stored in an SQLite database [...] has one of the following storage classes:

  • NULL. The value is a NULL value.
  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  • BLOB. The value is a blob of data, stored exactly as it was input.

[...] The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer).

And Type Affinity on dynamic typing nature of SQLite:

[...] SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another.

[...]

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. For the purposes of this paragraph, hexadecimal integer literals are not considered well-formed and are stored as TEXT.

[...]

A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.

Let's draw some conclusions:

  • a value greater than 2-1 doesn't fit 8-byte signed integer and has to be stored other way (i.e. REAL or TEXT) and SQLite will be fine with that
  • SQLite will store a well-formed integer literal that is too large to fit in a 64-bit signed integer as REAL on its own (what OP discovered by hand)
  • REAL stored as IEEE 754 binary64 has 53 bit for mantissa which is ~16 significant decimal digits, but math.log10(2**63) ~ 19 so the conversion is lossy

Experiment

In [1]: import sqlite3

In [2]: conn = sqlite3.connect(':memory:')

In [3]: conn.execute('CREATE TABLE test(x INTEGER)')
Out[3]: <sqlite3.Cursor at0x7fafdbc3b570>In [4]: conn.execute('INSERT INTO test VALUES(1)')
Out[4]: <sqlite3.Cursor at0x7fafdbc3b490>In [5]: conn.execute('INSERT INTO test VALUES({})'.format(2**63))
Out[5]: <sqlite3.Cursor at0x7fafdbc3b5e0>In [6]: conn.execute('INSERT INTO test VALUES(?)', (2**63,))
---------------------------------------------------------------------------
OverflowError                             Traceback (most recent calllast)
<ipython-input-6-d0aa07d5aa5c>in<module>----> 1 conn.execute('INSERT INTO test VALUES(?)', (2**63,))

OverflowError: Python int too largetoconvertto SQLite INTEGERIn [7]: conn.execute('SELECT * FROM test').fetchall()
Out[7]: [(1,), (9.223372036854776e+18,)]

Where does the OverflowError come from if SQLite is fine storing unsigned bigint values as REAL? It's CPython's Pysqlite check, used in pysqlite_statement_bind_parameter.

Workarounds

  1. If you're fine with lossy REAL representation convert (or tell your ORM to) your int into str and let SQLite do its thing.

  2. If you're not fine with a lossy representation, but can sacrifice SQL arithmetic and aggregation your can teach sqlite3 how to do the round-trip with sqlite3.register_adapter and register_converter.

     In [1]: import sqlite3

 In [2]: MAX_SQLITE_INT = 2 ** 63 - 1
    ...:
    ...: sqlite3.register_adapter(
             int, lambda x: hex(x) if x > MAX_SQLITE_INT else x)
    ...: sqlite3.register_converter(
             'integer', lambda b: int(b, 16 if b[:2] == b'0x' else 10))

 In [3]: conn = sqlite3.connect(
             ':memory:', detect_types=sqlite3.PARSE_DECLTYPES)

 In [4]: conn.execute('CREATE TABLE test(x INTEGER)')
 Out[4]: <sqlite3.Cursor at 0x7f549d1c5810>

 In [5]: conn.execute('INSERT INTO test VALUES(?)', (1,))
 Out[5]: <sqlite3.Cursor at 0x7f549d1c57a0>

 In [6]: conn.execute('INSERT INTO test VALUES(?)', (2**63,))
 Out[6]: <sqlite3.Cursor at 0x7f549d1c56c0>

 In [7]: conn.execute('SELECT * FROM test').fetchall()
 Out[7]: [(1,), (9223372036854775808,)]

Post a Comment for "Peewee: Python Int Too Large To Convert To Sqlite Integer"