Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

peewee: Python int too large to convert to SQLite INTEGER

I have the following code:

from peewee import Model, CharField, BigIntegerField, DateTimeField, SqliteDatabase
db = SqliteDatabase('samples.db')

class UnsignedIntegerField(BigIntegerField):
    field_type = 'int unsigned'

class MyBaseModel(Model):
    class Meta:
        database = db

class Sample(MyBaseModel):
    myint = UnsignedIntegerField(index=True)
    description = CharField(default="")


Sample.create_table()

sample = Sample()
sample.description = 'description'
sample.myint = '15235670141346654134'
sample.save()

The code gives me the error mentioned above. After that if I try to insert the values by hand I have no problems with:

insert into sample (description,myint) values('description',15235670141346654134);

This is how the schema appears:

 CREATE TABLE IF NOT EXISTS "sample" ("id" INTEGER NOT NULL PRIMARY KEY, "myint" int unsigned NOT NULL, "description" VARCHAR(255) NOT NULL);
CREATE INDEX "sample_myint" ON "sample" ("myint");

For some reason peewee isn't able to use sqlite's unsigned int. I did try it on cygwin's latest sqlite's drivers. I'm using python3.

like image 734
maugch Avatar asked Oct 17 '25 10:10

maugch


2 Answers

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

insert into 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.

like image 98
Shawn Avatar answered Oct 20 '25 01:10

Shawn


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 263-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 at 0x7fafdbc3b570>

In [4]: conn.execute('INSERT INTO test VALUES(1)')
Out[4]: <sqlite3.Cursor at 0x7fafdbc3b490>

In [5]: conn.execute('INSERT INTO test VALUES({})'.format(2**63))
Out[5]: <sqlite3.Cursor at 0x7fafdbc3b5e0>

In [6]: conn.execute('INSERT INTO test VALUES(?)', (2**63,))
---------------------------------------------------------------------------
OverflowError                             Traceback (most recent call last)
<ipython-input-6-d0aa07d5aa5c> in <module>
----> 1 conn.execute('INSERT INTO test VALUES(?)', (2**63,))

OverflowError: Python int too large to convert to SQLite INTEGER

In [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,)]
    
like image 36
saaj Avatar answered Oct 20 '25 01:10

saaj



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!