very long integers in SQLAlchemy with SQLite

Question

I am struggling with very long integers (e.g. 2100000000000000000000) in sqlalchemy with a sqlite db. Error: "Python int too large to convert to SQLite INTEGER".

The answers in stackoverflow are suggesting using string instead, but i think about using numeric(scale=0). Are there any disadvantages using numeric?

from sqlalchemy import create_engine, MetaData, Table, Integer, Column, String, Numeric

meta_sl = MetaData()
engine = create_engine('sqlite:///testint.db')
conn_sl = engine.connect()

example = Table('example', meta_sl,
                Column('id', Integer, primary_key=True),
                Column('int', Integer),
                Column('str', String),
                Column('num', Numeric(scale=0)))

meta_sl.create_all(engine)

ins = example.insert()
my_int = 2100000000000000000000
try:
    conn_sl.execute(ins, {"id": 2,
                          "int": my_int,  # causes error!
                          "str": str(my_int),  # workaround nr.1
                          "num": my_int})  # workaround nr.2
except OverflowError:
    conn_sl.execute(ins, {"id": 2,
                          "str": str(my_int),  # workaround nr.1
                          "num": my_int})  # workaround nr.2

user_2144390 · Answer

Are there any disadvantages using numeric?

Yes, because it won't always work.

As noted in the SQLite documentation:

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.

So in your particular case, using Numeric(scale=0) seems okay …

ins = example.insert()
my_int = 2100000000000000000000
with engine.begin() as conn_sl:
    conn_sl.execute(
        ins,
        {
            "id": 2,
            # "int": my_int,  # causes error!
            "str": str(my_int),  # workaround nr.1
            "num": my_int,  # workaround nr.2
        },
    )
with engine.begin() as conn_sl:
    result = conn_sl.execute(sa.select(example.c.num)).fetchall()
    print(result)
    # [(Decimal('2100000000000000000000'),)]

… but SQLAlchemy also warns that

Dialect sqlite+pysqlite does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.

For example, if we use my_int = 1234567890123456789012 it round-trips as Decimal('1234567890123456774144').

very long integers in SQLAlchemy with SQLite

1 Answers